Dynamics Community Forum Thread Details

This will be a bit involved. I wouldn't be surprised if your eyes glaze over before I'm finished with my question!

When I became the GP Admin at my company, the first things I worked on were the financial reports using Management Reporter. Our main P&L is quite involved. Our CFO gave me these requirements:

Show Current Year, Prior Year, $ difference, and % difference in a single column, not side-by-side.
The reason for 1. was that the CFO wanted to see all 12 months of the fiscal year side-by-side, including quarterly subtotal and YTD total columns. If the items in 1. were side-by-side, the report would have over 60 columns.
Prior year amounts should show up for every month of the fiscal year, while current year amounts should only go through the most recent closed month. No data for the current partial month should show up.
$ and % difference amounts should be presented with a favorable/(unfavorable) formatting. That is, if current year revenue is greater than prior year revenue, that's a favorable outcome, so the differences should be positive. But if current year expenses are greater than prior year expenses, that's an unfavorable outcome, so the $ and % differences should be negative.
And, the pièce de résistance, show a current month vs. prior month variance in $ and % as the last columns in the report. So, since May is the most recent closed month, the columns would show $ & % variances for May vs. April.

It took a lot of finagling, but I got the report to come out looking like this (from an Excel export so I could trim down the column widths):

So it's possible!

Here's the Row Definition for this block of rows (from an Excel copy of the Row Def):

And here's the Column Definition (ditto from Excel):

Eye's glazing over yet?

Cutting to the chase, my issue is with the CALC column AB in the Column Definition. (I've already set the Report Definition's Calculation priority to "Perform column calculation first and then row".) If the Current Year row has account numbers in the "Link to Financial Dimensions" column of the Row Def, that Column Def CALC formula in column AB works fine.

However, if the Row Def Current Year row is a TOT row -- a summary of several Revenue or several Expense sections -- then the CALC column goes haywire. Instead of paying attention to the values on that row of the report, it SUMS the values in the Current Year rows above it... including summing the percentage values. Ack!

E.g., If the Current Year current month vs. prior month $ Variances are 1768 and (12,665), the Total $ Variance for that section is fine: (10,897). The corresponding % Variances though are the problem. For the example $ values above, the % Variances are 0.2% and (7.9%). The screwy CALC sums the percentages to get (7.7%).

[By the way, the BASEROW notation you see in the Column Def CALC columns above refer to the values in rows 4990 and 5020 of the Row Def. The CBR rows in the Row Def change the BASEROW to either 1 or -1. This was the only way I could make the month-over-month Variances come out favorable/(unfavorable).]

The Current Year summary rows in the Row Def therefore have to repeat the accounts and categories given in the Current Year rows that are being summarized. This makes for long generation times since MR has to pull each account twice: once for the original Current Year row, and twice in the Summary row... that's the only way to get those #&%(#&!@!* CALC columns in the Column Def to work right!

Anyway, if this is too much to take in, I can hardly blame you. This is the result of a couple man-weeks of effort.

Can you see any way to do this differently? Do you think I've enabled my CFO too much?

Sincerely,