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?
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.