Dynamics Community Forum Thread Details

Management Reporter - Summary and Units Multiplier

I have am grabbing data from a specific GP account that has the days of the month - i.e. a value of 31 for January, and 28 for February, etc. I use this data in other fields to calculate averages per day.

I am running this for many facilities. This data shows correctly when I look at a specific facility, but when I go to the summary/consolidated amount it shows that value multiplied by the number of facilities.

For example it January has 31 days and I have 100 facilties. I want each facility to have 31 in that row and I also want the summary to show 31. Currently the summary will show 3100.

Categories:

Microsoft Dynamics GP

There is a way to do this so that the days per month do not roll up to the summary level. Below is the formatting you'd need to do.

In the row we need to add two Link to FD columns. The first will have the unit account plus all the GL accounts. The second will only have the unit account. This one is needed so that we can get the unit account at the summary level, without it rolling up. Rows 100 and 130 both have the unit account. Row 100 will use the XR print control. This will suppress this from rolling up. This allows the unit account to appear on the child units and stops it from rolling up. The X0 on both rows will prevent them from printing when they are zero. If we don’t add the X0, we’ll see two “Days of the Month” rows on every unit.

In the tree, we create a dummy unit just for the unit account (row 2 above). This unit will be linked to the second FD column from the row. This one will roll up to the summary while all the others will be suppressed. The Days unit of the tree can be made non-printing (NP in column J).

In the report definition, we need to check the ‘Use row definition from reporting tree’ option. This will ensure that the row links are used.

At the summary level, the report will look at this:

The days unit will only have the unit account:

If you wanted, you could make this unit non-printing. To do that, select NP in Column J (Page Options) of the tree definition.

If I want to create a calculation that will divide the number of days, I need to make a few adjustments to the row.

In the row, I will make the two existing rows non-printing. I then add a CAL with an IF THEN ELSE statement. The reason for this is that I need one row to reference for the calculation in the column. If I just use the existing two rows, my calculation will not work at the summary level. The IF THEN ELSE statement will place the days value at the summary and child, all on one row. I can then reference that row (row 100) in my column calculation.