Dividing by Total Sales for Every Unit in the Tree

This is an alternate way to do a Percent of Sales report.

This post is also compliments of Rick Tenorio, moderator of the newsgroup at microsoft.public.frx. Normally in departmental reporting, a P&L calculates percentage of sales by using the sales for that department. For instance, the Welding department calculates percent of sales by using Welding revenue. In this report, however, Rick’s client wanted every department to calculate percent of total sales.

This is a “% of Sales” report.  Per Rick, “This report was requested by a client who wanted a report that would show the % of Sales amount exactly the same for each branch of the tree.  (So no rollup for the balance.)  I did not have this written up in detail, so I included the FRD file and the TDB file.  (The tdb file is from FRx 6.7 sp10.)”

The zip file below contains:

1. one .FRD file—this is the DrillDown Viewer file
2. one .TDB file—this is the row, column, tree and catalog that you can import. You should probably import into the FRxDemo spec set, then switch to company FW to see these.

Here’s the zip file:
Dividing by Total Sales

My comments:

This report doesn’t use total sales. Instead, it uses account 4100-1100-001, but the concepts are the same:

1. you override the tree by using the full account structure
2. you keep the account from rolling up multiple times by using XR in the row and a 2nd Link to GL
3. you specify link GL2 in the tree for the summary unit
4. you check ‘Use row formats…’ in the catalog

I haven’t devoted time to do a full writeup here, but note that there is much more information on this technique in my post How To Use Full Account Segments in the FRx Row.

My thanks to Rick for the files.

PS. One more item of interest: the drilldown viewer file came from SP10 and thus it’s a little confusing when you look at the % Sales column for both Net Sales and Gross Margin. As I mentioned in FRx SP10 The Good The Bad and the Ugly, the CS (currency sign) format in the row is overriding the column’s percentage special format mask. Please disregard since we’re not looking at those rows in this report.