May.

9

2013

Waiting for View Subtotals? Here’s how to create a Pivot from Management Reporter

by Jan Harrigan CPA

Looking for Subtotals in MR? Me too. While we’re waiting, here’s how to create a pivot in Excel that’s a lot more work, but a half decent substitute.

Here’s a visual on what Subtotals give you

subtotals2
Currently when you drill down in Management Reporter, you get a list showing the full account structure. Just like what’s shown on the left.
Subtotals functionality will give you the option to see, in a couple of clicks, what’s shown on the right. Want to ask Microsoft to add this? Please vote here.

Meanwhile, I’ll show you how to create an Excel pivot table that looks like this

pivot2

Using data from a Management Reporter report.

This is the Management Reporter row for this report

media_1367958817099.png
I’ve used a wildcard to pull all accounts, but I’ve backed out 9010 which is the account for square footage. Nothing quite like square footage to screw up a trial balance. Segment2 is the natural account in this database.

This is the Management Reporter column for this report

media_1367958859427.png

Settings to change in the report definition

detaillevel

Be sure to set the detail level to Financial & Account.

media_1367959073244.png

Make sure you mark these two.

Generate and Download to Excel

media_1367959189731.png
I’m on the latest rollup, RU5, so the report automatically opens in the Web Viewer. Download to Excel.

Results in Excel

media_1367959405278.png
If you don’t have an ‘-A’ tab (that stands for Account by the way), it means your detail level wasn’t set to Financial & Account.
BTW, I’m using Excel 2007 today.

Use the View tab to set the Normal view

media_1367959494866.png
And space the columns so you can actually see them.

Add 3 blank columns

media_1367959625020.png
…to prepare for the next step.

Parse the Data

media_1367959734422.png
I’m going to use Data>Text to Columns to put each segment of the account structure in a separate column. Highlight the data. Click Text to Columns.

Switch to Fixed Width

media_1367959781232.png
Aside to AX users—I’ve had lots of trouble trying to parse using Fixed Width because of varying dimension lengths. And your Main Account is in the middle of the string because it’s sorted alphabetically for crying out loud. You’ll probably have to experiment with Delimited instead.

Use the mouse to create break lines

textstep2
Here I’ve created 6 lines that will break everything into separate segments. Sort of like the scores in a Hershey bar.

Tell the wizard to skip the 3 unwanted columns

media_1367959969231.png
Set the hyphen and the blank columns to Do not import column (skip).

Set the columns with the accounts to text

media_1367964350151.png

If you set it to text here, Excel won’t think it’s a number and try to foot it or count it.

Results after Text to Columns

media_1367960087834.png

Add column headers

media_1367960222314.png

Add another column that marries the Account number with its Description

media_1367960347412.png
Do that with the concatenate function. Use it to combine the Account in cell B3 with its description in D3. Add 3 spaces in between in double quotes.

Copy that function all the way to the bottom

media_1367960404000.png

Delete the blank row or rows at the top

media_1367960849706.png

Click anywhere in the data and Format as Table

media_1367960897549.png

media_1367964801698.png
I picked Light 9. Be sure to check the My Table Has Headers checkbox.

Results of Format as Table

media_1367961075546.png

Insert PivotTable

media_1367961156847.png

Let this default

media_1367961180657.png

Results

media_1367961234721.png
Don’t freak out. This is easy.

Don’t drag anything anywhere. Just check 2 boxes.

media_1367962118112.png
I know this looks dreadful. But not for long.

At the bottom right, drag the Amount tile over to the Values section

media_1367962272607.png
I know—it shows the count and not the amount. I’ll fix that next.

Use Value Field Settings

media_1367961736425.png

Select Sum, then the Number Format button

media_1367961775443.png

Make these 3 changes using the Number Format button

media_1367961842108.png

Results—Account list in a pivot

media_1367962364468.png
You can lose the Row Labels in cell A3 by clicking the Field Headers button at the top right if you want.
This looks great, but don’t get too excited yet.

Unfortunately the Account Description field doesn’t quite sort correctly

media_1367962462152.png
For instance, take a look at account 6180 Rent Expense. There are two of them. Here’s what to do.

Click the Account checkbox and move its tile above Account Description

pivot
Voila. NOW it’s sorted correctly.

Now the fun begins—monkey around with the Pivot

media_1367962889413.png
Move tiles around. For instance, put Department in the column.
Experiment. Uncheck +/- and Field Headers buttons. See what you get.

You can also turn off Subtotals on the Design tab

media_1367963869812.png
So much more you can do with this. I also like to fool with the Field Settings, found on the dropdown triangle on each tile—you can lose the outline and go tabular, control subtotals, and lots more. So incredibly powerful.

Honestly, though, there’s really no substitute for the look of Subtotals that could be built into Management Reporter. It was beautiful functionality in FRx—lots of oooohhhs and aaahhhhs when I taught it in class. If you’d like to ask Microsoft to add the Subtotals feature to Management Reporter, so you can do pivottable-esque things on the fly, please vote here. You won’t get 100% of the functionality of a real pivot, but it’s a truly helpful feature that’s really needed.

CHEERS! Jan

Be Sociable, Share!

    { 7 comments… read them below or add one }

    Geri Kramer May 9, 2013 at 1:56 pm

    Wow…this is great. I actully learned a little more about pivot tables too :)

    Jan Harrigan CPA May 9, 2013 at 5:44 pm

    Very glad to hear it Geri!

    Chris Gibson May 9, 2013 at 11:31 pm

    Nice workaround! Having just taught the subtotal feature to a group of users at an internal conference (who were very excited to learn about it) and discussing the eventual move away from FRx (when our corporate headquarters makes a call on our future reporting solution), I was very disappointed to come home and learn that this feature is missing from MR. I was #50 in support of MR subtotals by the way.

    David Evans May 10, 2013 at 1:40 am

    Jan, Very good. I hope we do not have to do other workarounds for ‘lost’ functionality. The loss of ‘user-defined’ dimensions going forward is another issue. Replaced with attribute values is not ideal.

    Regards, David.

    Jan Harrigan CPA May 10, 2013 at 9:00 am

    Thanks Chris…excellent comment re your disappointment…and thanks for vote 50! Jan

    Jan Harrigan CPA May 10, 2013 at 9:05 am

    Thanks David…right there with you!

    Jan Harrigan CPA June 24, 2013 at 1:49 pm

    There’s additional information about voting and the status of subtotals in this related post:
    Beer and View Subtotals

    Leave a Comment

    Previous post:

    Next post: