Open Forum

Expand all | Collapse all

Management Reporter Structure

  • 1.  Management Reporter Structure

    TOP CONTRIBUTOR
    Posted Nov 09, 2018 01:40 PM
    Fellow GPUG'ers, I need to pick the brains of our brilliant Management Reporter users, please.​

    We have approximately 400 dept(county)/location combinations in our organization. I would like to create a report that will pull the Income, Expense then display Net Income for each of them then have them all on one page split by dept. I am able to use our location tree with the row definition below to get one dept(county)/location combination per page. Is there a code that I can use on the row definition that tells it to repeat what I have in rows 10-190 to get them all on one page OR do I have to copy/paste rows 10-190 98 more times?

    I have the same setup for a dept(county) report but it only has 17 that I had to copy. LOL  I know that I could do it the hard way and add the same 7 lines for all 400 of them BUT I was trying to find an easier way. 8-)

    This is the Row Definition:


    This is the tree:


    Thanks!!

    ------------------------------
    Joni Finnell
    GP Projects/Troubleshooter
    Consumer Support Services, Inc.
    Newark OH
    ------------------------------
    GPUG Summit - Post


  • 2.  RE: Management Reporter Structure

    GPUG ALL STAR
    Posted Nov 09, 2018 02:51 PM
    Hi, @Joni Finnell,

    To tell you truth, I wasn't aware that one could use the @UnitName in the Row Definition. I presume that that allows you to avoid entering the Unit into the "Related Formulas / Rows / Units" column.

    So, in your dept(county) report, you use that @UnitName designation in the Row Def? And each dept(county) follows directly after the previous one? I'm having difficulty picturing what that looks like. Would you mind posting a piece of your dept(county) Row Def that shows two-three blocks of rows?

    Regards,

    ​​​

    ------------------------------
    "Sparkly" Steve Erbach - QMS Manager and Business Development Analyst
    GLC Minerals, LLC - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    GP 2016 R2 (16.00.0579) / MR 2012 CU16
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    ------------------------------
    Welcome to Dynamics!
    You do not have permission.
    Call Steve.
    (hat tip: Lou Spevack)
    ------------------------------

    GPUG Summit - Post


  • 3.  RE: Management Reporter Structure

    TOP CONTRIBUTOR
    Posted Nov 09, 2018 03:06 PM
    Edited by Joni Finnell Nov 09, 2018 03:07 PM
    ​Hi Sparkly @Steve Erbach,

    This is a screenshot of the row definition for the dept(county) one that I mentioned​ that does work. I did it the hard way and added a line for each unit(county), which is only 17. So, I didn't need to use the @UnitName option. This is the first time that I'm trying the @UnitName option. Since I now need 400 of them I am trying various options. 8-)




    Thanks




    ------------------------------
    Joni Finnell
    GP Projects/Troubleshooter
    Consumer Support Services, Inc.
    Newark OH
    ------------------------------

    GPUG Summit - Post


  • 4.  RE: Management Reporter Structure

    GPUG ALL STAR
    Posted Nov 09, 2018 03:46 PM
    @Joni Finnell,

    OK, that squares with what I imagined the @UnitName flag might mean.​​​ I'm still wondering how @UnitName would work with the County + Location combination...

    I'm also wondering if one would be better off extracting Income and Expense information into an Excel workbook and creating a PivotTable.

    Hmmm... Management Reporter Help doesn't give any examples of using @UnitName in Row Defintions...just in Column Definitions. Have you used @UnitName successfully in any of your Row Definitions?

    Regards,




    ------------------------------
    "Sparkly" Steve Erbach - QMS Manager and Business Development Analyst
    GLC Minerals, LLC - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    GP 2016 R2 (16.00.0579) / MR 2012 CU16
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    ------------------------------
    Welcome to Dynamics!
    You do not have permission.
    Call Steve.
    (hat tip: Lou Spevack)
    ------------------------------

    GPUG Summit - Post


  • 5.  RE: Management Reporter Structure

    TOP CONTRIBUTOR
    Posted Nov 09, 2018 04:17 PM
    @Steve Erbach:

    The @UnitName pulled the UnitName from my reporting tree and the new report produced the correct information for each of the UnitNames. The problem is each of the units were on a separate page and I need all of them on one page for each county. 8-)

    I chose UnitName because it has the best description. I have never tried it before. I was surprised that it worked too. LOL



    When you say "I'm also wondering if one would be better off extracting Income and Expense information into an Excel workbook and creating a PivotTable.​​" How would you describe the best way to "extract"?

    Thanks


    ------------------------------
    Joni Finnell
    GP Projects/Troubleshooter
    Consumer Support Services, Inc.
    Newark OH
    ------------------------------

    GPUG Summit - Post


  • 6.  RE: Management Reporter Structure

    GPUG ALL STAR
    Posted Nov 09, 2018 05:31 PM
    @Joni Finnell,

    I was thinking of a PowerPivot, actually. I did a sample with the TWO database since that has a multi-part account number:

    SELECT p.[TRX Date]
    , p.[Account Number]
    , p.[Account Description]
    , p.[Debit Amount]
    , p.[Credit Amount]
    , p.[Account Category Number]
    , p.Segment1 AS Division
    , p.Segment2 AS Account
    , p.Segment3 AS Department
    , p.[Posting Type]
    , p.[Typical Balance] 
    FROM [dbo].[AccountTransactions] AS p 
    WHERE p.Segment2 BETWEEN '4000' AND '8999'
    AND p.[Open Year] = 2017
    AND p.[Document Status] = 'Open';

    That's the SQL statement in Power Query.

    Add that result to the Data Model and add a Column:

    =IF(tblPandLTrx[Account]<"4730","Income",IF(AND(tblPandLTrx[Account]>"4729",tblPandLTrx[Account]<"5000"),"Balance Sheet","Expense"))

    ... name the column IncomeExpense. Then make the Pivot.

    In the Pivot,


    The Pivot looks like this collapsed to the highest level:


    Then we open up the top level:


    ...and open up further:


    ... and we're starting to look like what you want. The 100/200/300 level is your County; the 00/01/02 level is your Location.

    Does this help?

    Sincerely,

    ------------------------------
    "Sparkly" Steve Erbach - QMS Manager and Business Development Analyst
    GLC Minerals, LLC - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    GP 2016 R2 (16.00.0579) / MR 2012 CU16
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    ------------------------------
    Welcome to Dynamics!
    You do not have permission.
    Call Steve.
    (hat tip: Lou Spevack)
    ------------------------------

    GPUG Summit - Post


  • 7.  RE: Management Reporter Structure

    TOP CONTRIBUTOR
    Posted Nov 12, 2018 08:32 AM
    ​Morning Sparkly @Steve Erbach:

    Thanks for the details and I appreciate the example but the debits and credits can't be in separate columns. I think I forgot to mention that my column layout is all 12 months with budget numbers where actuals haven't happened yet. 8-> So, the row is where it all has to happen.


    ------------------------------
    Joni Finnell
    GP Projects/Troubleshooter
    Consumer Support Services, Inc.
    Newark OH
    ------------------------------

    GPUG Summit - Post


  • 8.  RE: Management Reporter Structure

    GPUG ALL STAR
    Posted Nov 12, 2018 12:45 PM
    @Joni Finnell,

    ​As far as the Debits and Credits being in separate columns, that is easily dealt with in the query. Instead of the separate lines for Debits and Credits, you can combine them into one with:

    , p.[Debit Amount] - p.[Credit Amount] AS Amount

    Then the resulting Pivot would have one fewer summaries in the Values pane; there would only be Amount.

    >> I think I forgot to mention that my column layout is all 12 months with budget numbers where actuals haven't happened yet. 8-> <<

    That does change things just a teensy bit!

    One can construct a SQL query to feed a refex based on your requirements, but the query would look something like this one on Victoria Yudin's site.

    You might be faced with copying that block of Row Definition rows for as many County/Location blocks that you want to define... what you were trying to avoid in the first place!

    Regards,

    ------------------------------
    "Sparkly" Steve Erbach - QMS Manager and Business Development Analyst
    GLC Minerals, LLC - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    GP 2016 R2 (16.00.0579) / MR 2012 CU16
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    ------------------------------
    Welcome to Dynamics!
    You do not have permission.
    Call Steve.
    (hat tip: Lou Spevack)
    ------------------------------

    GPUG Summit - Post


  • 9.  RE: Management Reporter Structure

    TOP CONTRIBUTOR
    Posted Nov 13, 2018 04:48 PM
    ​Thanks for the additional info Sparkly @Steve Erbach. I wish I could use one of Victoria's fabulous SQL scripts but it sounds like I am going to have to do it the hard way. 8-(​


    ------------------------------
    Joni Finnell
    GP Projects/Troubleshooter
    Consumer Support Services, Inc.
    Newark OH
    ------------------------------

    GPUG Summit - Post


  • 10.  RE: Management Reporter Structure

    GPUG ALL STAR
    Posted Nov 13, 2018 04:55 PM
    @Joni Finnell,

    I have a feeling that @Derek Krebs or @Noah Moseley of the MSX Group could offer another perspective on this. Your report came out properly except for the fact that each County/Location combination printed on a separate page, right?

    Since I've never seen the use of​​​ @UnitName like you've used it, I'd say that the MSX guys could give you a better insight into your report trials.

    Regards,


    ------------------------------
    "Sparkly" Steve Erbach - QMS Manager and Business Development Analyst
    GLC Minerals, LLC - Green Bay, WI
    Co-Chair, GPUG WI (Green Bay) Chapter
    GP 2016 R2 (16.00.0579) / MR 2012 CU16
    Blog: https://www.gpug.com/blogs/steve-erbach
    Twitter: twitter.com/serbach
    ------------------------------
    Welcome to Dynamics!
    You do not have permission.
    Call Steve.
    (hat tip: Lou Spevack)
    ------------------------------

    GPUG Summit - Post


  • 11.  RE: Management Reporter Structure

    GOLD CONTRIBUTOR
    Posted Nov 09, 2018 05:45 PM
    Hi @Joni Finnell

    OK, please take into account that it's just past 5 on Friday afternoon.  I'm not sure how to get your report to behave.  But I wanted to toss out another way to experiment.

    Maybe you could use the Column Format to draw the Unit Name and the Data -- and do the Net Income calculation.

    Then use the Row Format to list all the Units.......

    I don't know if this gets you any further -- take it for what it's worth.

    I envision something like this for the column Format:​


    And then can you somehow draw in the Unit Names on the Row Format??

    Good luck -- I'm wrestling with formats, too, so I wanted to give a suggestion -- even if it doesn't work, maybe it will turn on a light bulb somewhere in the land of GPUG!

    Maureen

    ------------------------------
    Maureen Martin
    Finance Manager
    Home Repair Services
    Grand Rapids MI
    ------------------------------

    GPUG Summit - Post


  • 12.  RE: Management Reporter Structure

    TOP CONTRIBUTOR
    Posted Nov 12, 2018 08:23 AM
    ​Hi @Maureen Martin:

    Thanks for the idea. I have used the column definition to pull in the dimensions on other reports. But it won't work for this report since it needs to show columns for all 12 months. 8-/



    ------------------------------
    Joni Finnell
    GP Projects/Troubleshooter
    Consumer Support Services, Inc.
    Newark OH
    ------------------------------

    GPUG Summit - Post


  • 13.  RE: Management Reporter Structure

    SILVER CONTRIBUTOR
    Posted Nov 12, 2018 08:25 AM
    Hi Joni,

    Sorry I am a little late to the game.  On the report definition there is a setting under the Microsoft Excel Options to generate to a single worksheet.  The will place all tree branches on a single worksheet.  Now this only works when you export to Excel.  When you look at it on the screen every branch will still come up independently.  You may have to play with the header options to get things to look exactly like you would want going down the page and not have to many headers between each  section.



    ------------------------------
    Annette Brown
    Columbus
    Ballerup
    ------------------------------

    GPUG Summit - Post


  • 14.  RE: Management Reporter Structure

    GOLD CONTRIBUTOR
    Posted Nov 13, 2018 05:05 PM
    I'm onboard with Annette.

    ------------------------------
    Noah Moseley, Senior Consultant at the MSX Group
    Noah.Moseley@MSXGroup.com
    Email me if you need training or consulting services for Management Reporter, Forecaster or FRx.

    View our GPUG Partner MSX Group listing: https://www.gpug.com/engage/findacompany/companyprofile?UserKey=275d1f3a-73b4-4a58-b0d8-7395d682dbc3
    ------------------------------

    GPUG Summit - Post


  • 15.  RE: Management Reporter Structure

    TOP CONTRIBUTOR
    Posted Nov 13, 2018 05:06 PM
    ​Thanks @Annette Brown. I never thought of that. Even though we typically PDF I would've considered processing it through Excel first. But the output has too many blank lines in between the units even with the no header option. Someone  would end up altering the spreadsheet every month. I guess I am better off to put in the extra time to set it all up now instead of editing it every month. 8-)

    ------------------------------
    Joni Finnell
    GP Projects/Troubleshooter
    Consumer Support Services, Inc.
    Newark OH
    ------------------------------

    GPUG Summit - Post


  • 16.  RE: Management Reporter Structure

    SILVER CONTRIBUTOR
    Posted Nov 13, 2018 05:09 PM
    You could try removing the extra rows in the row definition so that you don't have blank rows at the bottom.  That is what is probably added all the extra lines.


    ------------------------------
    Annette Brown
    Columbus
    Ballerup
    ------------------------------

    GPUG Summit - Post


  • 17.  RE: Management Reporter Structure

    TOP CONTRIBUTOR
    Posted Nov 13, 2018 06:39 PM
    ​Hi, one way I like to create and analyze easily this report is to build the row on Dept and Location (not Account) and then in column have columns for Rev then Exp then Net Income (for Month & Ytd and/or Act & Bud).  You can fit a lot of rows on one page for analysis, even sort these rows of Dept & Loc to see who best profit producer is or sort by variance to budget.

    ------------------------------
    Derek Krebs
    Sr Consultant
    MSX Group
    derek.krebs@msxgroup.com
    ------------------------------

    GPUG Summit - Post


  • 18.  RE: Management Reporter Structure

    TOP CONTRIBUTOR
    Posted Nov 14, 2018 04:21 PM
    ​Thanks for your response @Derek Krebs. I wish it were that simple but our CFO wants all 12 months in the columns. So, I have to put Revenue, Expense and Net Income in the rows. 8-(

    I was trying to avoid building a row definition with Revenue, Expense and Net Income for all 400 of our locations​.

    EUREKA! I have a break through! I was still able to use @UnitName in the Description of the Row Definition to pull all of the locations from an existing tree then export to Excel to get them all on one report. There is just a minor glitch. 8->

    Here's how I finished it. I took @Annette Brown's advice (thanks Annette) and removed extra lines from the Row Definition then unchecked the "Include headers and footers" box in the Report Definition before I ran the report then again when I downloaded to Excel from the Report Viewer. They are now appearing on one page. However, it still includes the column headers (Current JAN, Current FEB, etc), which looks really redundant. Does anyone know how to stop them from exporting other than removing them from the Column Definition all together?

    I'm excited that I got it this far. LOL

    This is the row definition:


    This is part of my tree:


    This is my column:


    This is part of my results (the # are on purpose-the column just needs expanded)


    Thanks!!
    ​​

    ------------------------------
    Joni Finnell
    GP Projects/Troubleshooter
    Consumer Support Services, Inc.
    Newark OH
    ------------------------------

    GPUG Summit - Post


  • 19.  RE: Management Reporter Structure

    GOLD CONTRIBUTOR
    Posted Nov 14, 2018 04:28 PM
    @Joni Finnell
    No ideas from me -- just want to congratulate you on your persistence and the success you've had to this point.  Way to stick with it and find solutions!!
    Maureen​

    ------------------------------
    Maureen Martin
    Finance Manager
    Home Repair Services
    Grand Rapids MI
    ------------------------------

    GPUG Summit - Post


  • 20.  RE: Management Reporter Structure

    TOP CONTRIBUTOR
    Posted Nov 19, 2018 08:39 AM
    ​Thanks @Maureen Martin! It has been a journey. 8-)



    ------------------------------
    Joni Finnell
    GP Projects/Troubleshooter
    Consumer Support Services, Inc.
    Newark OH
    ------------------------------

    GPUG Summit - Post


  • 21.  RE: Management Reporter Structure

    Posted Jan 04, 2019 04:00 PM
    Hi @Joni Finnell

    You can always write a quick macro in Excel that will automatically remove extra headers and spaces in one quick swoop.


    GPUG Summit - Post


  • 22.  RE: Management Reporter Structure

    TOP CONTRIBUTOR
    Posted Jan 07, 2019 08:23 AM
    ​Thanks @Kenn Lucas. I was trying for very little manual intervention for the non-techy users but that is worth checking out.​​

    ------------------------------
    Joni Finnell
    GP Projects/Troubleshooter
    Consumer Support Services, Inc.
    Newark OH
    ------------------------------

    GPUG Summit - Post


  • 23.  RE: Management Reporter Structure

    Posted Jan 07, 2019 11:01 AM
    Sure thing @Joni Finnell.   Most definitely the goal would be to streamline the process to avoid manual intervention, both for the user and yourself.  There are different ways you could accomplish it.   I don't know your environment, of course, so this suggestion may be useless -- Setup a base excel file with the macro (vbac code) and connection to the generated report.  Have a shortcut link to the base spreadsheet so when the user opens it, the newly generated report is automatically brought in and formatted as defined in your macro.

    Have a remarkable week.

    Kenn

    ------------------------------
    Kenn Lucas
    I Love Data and Doritos
    ------------------------------

    GPUG Summit - Post


If you've found this thread useful, dive deeper into User Group community content by role