FR Studio: How to Select Level 0 Descendants of a Specific Member

This post looks at the “Allow Expansion” functionality in Financial Reporting Studio. “Allow Expansion” is a great feature, as it gives the user more flexibility when running and displaying reports. However, it can also require more setup work as it makes the report more complex. Two situations that I encountered recently:

Adding a custom heading using conditional formatting
Calculating formulas on expanded members

1) Adding a Custom Heading Using Conditional Formatting

For this example, I want to rename the account “PROMO”, to “Promotions” in the report. Typically, this setting can be changed by clicking on the “PROMO” cell and then editing the Custom heading in the properties box. However, with expansion enabled, the result will be that the member and its children will be renamed to “Promotions” with expansion enabled.

To demonstrate this, first we must enable expansion. Highlight the entire row by clicking on the desired row number. Check the box next to “Allow Expansion” in the “Heading Row Properties” box. When expansion is enabled we will see that the custom heading will appear for all descendants of “PROMO”.

Run the report in the HTML preview, and click the arrow next to “Promotions” to see the children of the account. Notice in the screenshot below that both children now display as “Promotions”. How can we edit this so that the Custom heading only appears for “PROMO”, and not its children? This is where conditional formatting comes into play

The desired outcome is that both children will display their unique name as is stored in the dimension library. In order to accomplish this, some additional conditional formatting needs to be set up within the report:

The report needs to be given instructions on when to apply the custom heading. To apply this setting, we need to know what level or generation the account is, so that the rule will tell FR only to apply the custom heading on that specific account. For example, looking at the account structure below, “PROMO” is a Generation 7/Level 1 member of the hierarchy. We want the custom heading to only display for this level (i.e. “PROMO”) and allow the Generation 8/Level 0 members (i.e. children of “PROMO”) to display as usual.

The following 2 screenshots are examples of possible conditional formats for the member (based off the hierarchy above). Either of the 2 will work.

Select “Format Cells” and select the “Replace” tab. Enter in “Promotions” and click OK. After setting the conditional format, go ahead and run the report in HTML Preview again (Allow Expansion only works in HTML Preview, not in PDF Preview)

Expand on “Promotions” and notice the difference in formatting from before. The children will be displaying their unique member names as expected:

That took care of our first problem – How to set Custom Headings on Allow Expansion members using conditional formatting. Next up…

2) Dynamically Calculating using Allow Expansion

Another issue I encountered was that calculations didn’t update upon expansion of members. We’ll focus on the highlighted cell below to solve this issue:

The formula is a simple calculation, “PROMO”/”GROSS_SALES” or [A,3]/[A,2]:

When expanded, I want the calculation to update to show the relevant values for the children of “PROMO” i.e. the children will display as a percentage of “Gross Sales”. However, the values are static and do not update as expected. Notice that the 19.7% repeats itself for “Promotions” and its 2 children in the screenshot below. It seems that by hardcoding the formula to specific cells ([A,3] & [A,2]), the report only calculates “Promotions” / “Gross Sales”, instead of recalculating for the 2 children upon expansion (“425000/”Gross Sales” & “425040”/”Gross Sales”):

Rather than locking the numerator in place by selecting [A,3], we can improve the report by switching this value to reference the entire column [A]. Edit the formula by highlighting column B and typing the new formula into the formula box at the top of the report.

The new formula should look like this:

Instead of hardcoding the formula to one cell, the formula now has more flexibility for expanding members. Notice that the values calculate dynamically upon expansion this time around:

Overall, “Allow expansion” is a beneficial feature that allows users more flexibility with their reporting needs, but it sometimes takes a little more maneuvering to get the reports to display as expected.