web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Management Reporter - ...
Microsoft Dynamics GP (Archived)

Management Reporter - Column/Period options

(0) ShareShare
ReportReport
Posted on by HCG 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

I have the same question (0)
  • Suggested answer
    Redbeard Profile Picture
    Redbeard 12,931 on at

    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.

  • HCG Profile Picture
    HCG 245 on at

    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.

  • Suggested answer
    Redbeard Profile Picture
    Redbeard 12,931 on at

    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.

  • Suggested answer
    sueconrod Profile Picture
    sueconrod 337 on at

    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

  • HCG Profile Picture
    HCG 245 on at

    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.

  • HCG Profile Picture
    HCG 245 on at

    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,

  • Suggested answer
    Redbeard Profile Picture
    Redbeard 12,931 on at

    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?

  • Suggested answer
    sueconrod Profile Picture
    sueconrod 337 on at

    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.

    technet.microsoft.com/.../dn435963.aspx

  • Community Member Profile Picture
    Community Member on at

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans