I'm just catching up on my "to blog" folder and finding a few Dynamics GP or Management Reporter topics that I intended to write about a few months ago. This is another one that actually may end up being more than one post, as I think there are multiple "quirks" going on here.

This one just doesn't make sense to me and describing it to someone on the Microsoft Partner Technical forums, they said it should work based on how I described it but they cannot reproduce the issue. This is all based on CU14, Mgmt. Reporter 2012 for the record.

The Issue

Using a tree with dimension filtering on the various units, and then specifying those units in a column layout, the columns are ignoring the filtering from the tree.

Example: A tree for a company with 3 divisions (say this is in Segment 1 of the chart of accounts), Division 100, 200, 300 and a total.

  • Unit 1 is set to have no dimension filtering as it should be the summary of the units beneath it
  • Unit 2 is set to filter Segment1 = 100
  • Unit 3 is set to filter Segment2 = 200
  • Unit 4 is set to filter Segment3 = 300
  • etc. if I had more "divisions".

On the column layout, say I want to do an "at a glance" P&L by division, one page, columns for each division and then the total for a given period.  Here is an example of that layout:

  • Column A is the Description
  • Column B is Division 100, restricted to Reporting Unit 2 of the tree above
  • Column C is Division 200, restricted to Reporting Unit 3 of the tree above
  • Column D is Division 300, restricted to Reporting Unit 4 of the tree above
  • Column E is Total, simply no unit restrictions so it should pull all data

This, in a nutshell, is what I was doing with some reports. In my client's case, it was more complicated, as it was different companies, not divisions and segmenting different types of Elimination entries from one another depending on the level of consolidation.

When I run the report using essentially that type of setup, the results were that every column had the same amount, regardless of the Reporting Unit Restriction. It wasn't filtering to what the Tree specified.

The Workaround

I needed the filtering in the tree as I had, AND I needed to use Dimension Filtering in the column instead of a Reporting Unit restriction, to get this to report properly. It worked, but makes no sense.

What I haven't tried on my own data is something literally as simple as this example, to make sure that it doesn't work in another environment. In my client's environment, some of the row formats needed to specify segments that are also in the Tree dimension filtering and Microsoft warned me that this is probably the issue. However, I had an issue with rows in my report that had no segments used in the dimension filtering (segment 1) so that simply didn't make sense as the real reason. I can see where one could override the other but in this case, that wasn't the answer.

I ended up creating a support case with Microsoft to look at our specific data and we never resolved it. Unfortunately, we couldn't agree on the resolution as the support engineer kept getting stuck on one weird part of the client's account structure that he couldn't wrap his head around, instead of ignoring that and looking at other parts of the report where that situation wasn't present but the same error persisted. I managed to get the case to be non-chargeable even though we didn't agree that this was a bug.

Hopefully, I find more time someday to try to reproduce it with simple Fabrikam data!