Sorting in Management Reporter 2012
Here’s sort of a hidden setting that I think almost everyone should know about. It impacts how your accounts sort when you drill down.
I’ve drilled down on Operating Expense
Notice these accounts are sorting by segment 1 which happens to be department in this database. But I want my drilldown to sort by the natural account and definitely not by the department as shown here.
By default, Management Reporter sorts accounts by segment 1, whether or not it’s the natural account.
So this can drive you crazy if you don’t know about the setting I’m about to show you.
The ‘Sort by Natural’ checkbox is buried on the Account & Transaction Detail tab
I think it should be checked on by default. But it’s not.
But here’s where it is:
In the report definition, Settings tab, Other button, Account & Transaction Detail tab, check ‘Sort by natural or main segment’.
Results are sorted by the natural account
Here’s the same drilldown with the checkbox on. This looks a lot better, right?
Besides regular everyday use:
- Where this gives a lot of bang for the buck is when you’ve got a trial balance with every account, and it’s sorting by the department first, meaning the P&L accounts are mixed in with the balance sheet accounts. Go check this box, generate, and you’re good to go.
- Another place this pays is on the datamart version of Management Reporter on AX 2012. For some reason, the main account isn’t ever the first segment (except in AX) because it’s listed in alphabetical order (under M for main!) with all the other dimensions. Go figure. Go check this on and drilldowns make lots more sense.
Cheers—Jan
Hey Jan
I purchased your first MR book in 2011 having used FRx forever. I get lots of questions from clients about whether MR handles the limitations of Frx such as the number of rows. Do you know where Microsoft publishes that info? It isn’t in the fact sheets or on Partner Source.
Regards
Diane
Hi Diane…hmmmm I don’t know, but guessing since the back end is sql server, that number of rows is only limited by things like ram and hard drive space. But I’d maybe question their need for billions of rows (that will be a HIGH maintenance report) when sometimes a simpler design will give them what they want with far easier maintenance. A case in point is using the asterisk * to hold all accounts, drilldown on it and you’ve got a list of every account. Keeps you from maintaining a row definition with zillions of accounts. Food for thought!
And thanks for the purchase! Cheers…Jan
When i drill from my top level summary I’m getting too deep too quick. My top level summary has Revenue, Direct expense, G&A, etc. I have departments within each of those categories. So for direct expense I have nursing, dining, housekeeping, etc which is represented by the second segement of my COA. But when I drill on direct expense, it goes all the way to the full COA detail level – the lowest level of detail. I want to see the department total first, then the detail if I need to go deeper. It’ worked in FRX, but I’ve not figured it out in MR. Your help would be appreciated.
Alex
Hi Alex…yeah, know what you mean. I’m in the middle of a push to get FRx’s View Subtotals functionality added to MR. You can help tremendously by voting on the connect site. Every vote counts…I’m trying to get to 50. There’s more information and a link to the site here: BEER and View Subtotals. So glad you asked! Jan
Hi Jan,
At account Level detail, is there any way by which the Main account segment comes first instead of the default segment 1. Also, I have 9 segments in my database, but I want only 2-3 segment values in the report, can I hide unrequired segments? Your help would be appreciated.
Hi Vinod…what you’re looking for is Subtotals functionality, and unfortunately it doesn’t exist in MR at the moment. I have a post with more information on this feature as well as information on how to vote for it on Microsoft’s Connect site (their suggestion database). Please log in and vote; every vote helps in keeping this in sight. The post is called Beer and View Subtotals. Meanwhile, I also posted on an interim measure, how to create a pivot table from an MR report. The pivot may help in the meantime. Hope this helps…Jan
Hi Jan,
I marked the checkbox, however, my report is still sorting by first segment instead of the main account number. Do you know why that would be?
Thanks.
Hmmm…sounds suspiciously like a bug! I’ve not seen a problem with AX or GP even with an update that’s not terribly new, but haven’t tested on SL. Hoping it might be fixed with an update. Jan
Update…I just saw this exact problem on MR with CU8 installed with a GP back end. Sigh.
Hi Jan,
I have a trended trial balance with the beginning values in the first column, then a column for each month. When I sort on the natural or main segment, all accounts with a value in the first column are ordered properly, but anything that has a zero beginning balance falls to the bottom…..any ideas why and if there’s anything I can do to correct this?
Hi Don…YIKES, I don’t know, sounds a lot like a bug. I usually monkey with stuff like this for a little bit, trying not to burn too much time, because instinct says it’s not fixable. Other than with an export to excel. Sigh. Sorry. Jan
Hello Jan: In the past I have bought both FRX books and your first Management reporter book. Big fan.
I am having trouble with MR displaying the incorrect description on lines when I add an full account to a report.
I add account 61100-02-800 and it is displaying the description for 61100-02-640 on the designer and in the report. The total is correct on the report (61100-02-800) just the description is wrong.
All the other accounts I added to the report are fine, just a few out of 200.
Any ideas as to where I might look would be appreciated.
OK I know what it is doing not sure why.
It is picking up the first instance for the main account for the description and ignoring segment 2 and 3. Can I not get it to consider the whole account to come up with the description?
Hi Tom…thank you and glad you liked the books! I believe the latest hotfix CU10 streamlines the account description, but haven’t tested it yet. You might try toggling View > Dimension Descriptions on in the Report Viewer and see if that helps. Good luck…Jan