This is the third in a series of posts on simplifying FRx.
Once upon a time many years ago, I was helping a large publicly held client with some FRx reports. One of their reports had an incredibly long row format (thousands of rows), and it took FOREVER to run. Upon closer examination, I found that they were using the row format to code lots of accounts for every department. It was like having the entire chart of accounts in there. There was LOTS of room for human error, and heaven help you if you needed to add a department. It wasn’t wrong, but it sure needed some help. In today’s post I’m going to share a technique I used to shorten their row format to about 25 rows total. And performance—you just wouldn’t believe how much faster it was!
So this post is all about departmental reporting, showing a P&L by department (which should be one of the segments or dimensions in the account structure). As you probably know, in FRx you add a departmental tree and come up with a separate report for each unit in the tree.
But what if you want to see every department on the same page? You might be looking for summary information as shown below:
So—you MIGHT be thinking that the following row format is how you would go about coding this report. And you wouldn’t exactly be wrong because it does give accurate results. BTW, this is just a portion; it does have many more rows (not shown here) in order to include every department. And this is the approach that my client mentioned above had taken. But this is not the best design for this job:
There is a much faster, easier, simpler way to create the above report. Use the following row format with the tree shown below with some specific catalog settings, and you’re good to go. It’s a total of 8 rows! See how much simpler this makes life with FRx?
BTW, the @unit code above pulls the Title/Description from column D of the tree.
Use this new streamlined row format with this departmental tree:
There’s just one thing about this FRx report, though—when you generate the report, it doesn’t show up all on one page in the drilldown viewer. So you either have to print it or export it to Excel to see it on one page. I think it’s a small price to pay. I usually set the catalog to export to Formatted Excel instead of exporting from the drilldown viewer. This just saves several clicks. Oh and be sure to check Activate Workbook too:
Finally, two more catalog settings and you’re done:
‘Include all units’ is optional but advised—it just runs the report for every unit in the tree without your having to Mark All.
‘No page breaks between units’ is essential. It’s what puts each department on the same page.
So there you have it—a summary report with departments on the same page. Another benefit to this report design is that maintenance is greatly simplified. When you add a department, all you have to do it add it to the tree! SWEET.
{ 4 comments… read them below or add one }
Can I just say I love you for this!! I wish I had known about this a long time ago! My one question for you though – If we wanted to do a report similar to this but by product line and each product line contains 5 or 6 departments, is there a simple way to get each product line’s summary at the top of the page rather than having it go in the order of the tree? Not sure if that makes sense.. but it would be pretty nifty!
You are too funny!
It would be a totally different design from this, but you can always restrict a row to product line using column D in the row format. Drop it down and choose the product line rollup from the tree. It’s not nearly as automated as the above design and you have to be very careful with accuracy to boot, making sure that your bottom line ties out to something. Good luck!
Hi Jan,
Quick question…Since it appears Management Reporter has not option for ‘No page break between units’, how are you overcoming this in producing Management Reporter summary reports? Thanks!
Ugh I have really missed the no page break between units option. It’s easy if you just want one line for each department—just list the department dimension in the row and filter the columns with the P&L main accounts. Not so easy if you want more than one line for each department. Then you have to combine the department dimension with the main account dimension on each row…and it can be a bear to maintain depending on how many departments there are.