245
I was trying to create a report that had actuals for YTD and Budget for the remaining year, non-printing with just the totals.
MR doesn't seem to allow BASE:12 as an option.
this is super high level but this is what i am trying to accomplish:
| Actual | Budget | Budget | Calc |
| 1:BASE | BASE:12 | BASE | a+b-c |
| NP | NP | NP |
so basically in period 8 it would be
Actuals 1:8
+Budget 8:12
-Budget 8
------
Actual 1-8 and Budget 9-12
thanks,
-MH
*This post is locked for comments
Thanks for this link! It was very useful and I was able to create an annual trend.
Now, I need to create this in a quarterly format (a report that only has 4 columns, one for each quarter. So, in January, I need the Q1 column to include Jan Actual + Feb Budget + Mar Budget.
Here's what I did: I inserted a column after the 6 columns for Q1 and did a sum for the periods. It correctly added Jan Actual + Feb Budget + Mar Budget. Now, to throw in the wrench...I tried suppressing the 6 columns, to only show the total Q1 column. (Essentially, the Print Controls were NP, P<=B (Actual column) and NP, P>B (Budget column). When I ran the report, it only showed Q1, but the totals were all zero. Am I missing something? It doesn't appear to like the double print control, for some reason?
Thanks again for the help!
Vance
Just found some great documentation from the CPM team on Management Reporter and this setup is given as an example in Columns.
Here is the link: This documentation is absolutely great, came out when CU8 was out.
I was unaware of the feature Susan points out, which was supported by FRx - Management Reporter does support the following:
Column A = 1:BASE - if you use this for the FD: ACTUAL, this should get you through the whole year.
Column B = 2:12 - if you use this for your FD:BUDGET, this should present budget info for 2-12, in subsequent periods you would deduct one from the numeric value in this column, until you reach year-end.
Use Print Control: NP on columns A and B so they do not show on the report.
Column C = CALC; Formula = A + B
I would recommend, you create a separate report for each fiscal period, to be run in that fiscal period, so changes don't need to be made monthly or annually to these reports. I think the issue with the functionality as you desire it, is you're using one financial dimension (Actual) in the first column, and another financial dimension (Budget) in the second column, so the feature that support a rolling 12 month period does not apply.
My solution only works if these reports are meant to cover the periods in a year, rather than produce a true rolling 12 month period analysis. Perhaps someone at Microsoft can weigh in on whether or not we're missing a supported function here?
this suggestion is 12 different column formats?
if that was the case you wouldn't need to to 1:base...you would do 1:1 for January, 1:2 for february. correct?
i also think the Budget column would need to be 2:12 for january as base+11 only provides a single period (12)
again, trying to simplify the solution and not create multiple reports to accomplish something that i think should be simple functionality. It leads to potential errors in running the wrong report in the wrong period.
January 1:base with a date of June would provide them a YTD for 1-6.
thanks,
BASE+1:12 isn't a valid column "period" as far as i can tell. also, it poses problems in period 12 because it generates 13:12.
also, p<=base works if you want 12 columns to show in december. the client wants 1 column that represents YTD actuals PLUS remaining year budgets. I am looking to automate this and not require them to change the column format each month.
You are almost there. But this used to be called the Dynamic Report in FRx
On your headers you put @month, @year. The header for Actual is usually 1:BASE and the Budget Header is BASE+1:12.
You put in two columns after your first column: One will be an actual and the other Budget, then use P<=Base (Print if less than or =Base on the actual row, on the budget row you put P>Base.
You also put on the columns on the BASE Period, 1,2,3,4,5,6,7,8, 9,10, 11, 12
And btw, an excellent source is frxbuzz.com for both Frx and Management Reporter and you can also go to Microsofts site for management Reporter: blogs.msdn.com/.../dynamics_financial_reporting They have all sort of tips and tricks for using Management reporter
Perhaps I am over-simplifying this, but I believe the proper approach to this report is to design it for each month of the year once, and name it for the month it should be run in, so you don't have to adjust it every month or year:
Actual Budget CALC
Jan: 1:BASE 2:12 a+b
Feb: 1:BASE 3:12 a+b
Mar: 1:BASE 4-12 a+B
NP NP
etc., etc., etc.
thanks. that is difficult to offer to a client that would have to be willing and aware to go into the report and make the changes every month. It isn't in the plan or budget to be onsite for the long term to keep manually updating the reports.
There have been a few times I couldn't get exactly what a client wanted out of FRx or Management Reporter the easy way.
In these instances, where there are no simple solutions, I have found it necessary to call out my periods specifically, and add a NP print definition, which suppresses printing. Then I use calculated columns to display exactly the data I want in my printed columns.
This approach is typically 100% effective.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,269
Super User 2024 Season 2
#2
Martin Dráb
230,198
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (