Professionally Format Reports by Hiding Empty Sections
Reports are cleaner and easier to read when empty sections don’t appear. For example, this report shows Revenue header and Total even though the section has no data.
- These sections can be hidden by using an expanding group. Add an outer expanding group that extends from the Header row to the empty row below the Total row.
- Group by something that is identical for all data in the section. This is most often the AccountType attribute of the Account dimension, but there may be cases when another attribute or another dimension is more appropriate.
Since this method removes the empty sections altogether, formulas that reference these sections (like net income) must be updated as SUM(OSRRef(cell reference)) or IFERROR(cell reference, 0) to prevent errors in those formulas.
This same idea can be leveraged for other use cases. The following procedures provide an example where a Projection and variance columns are displayed/not displayed using an expanding group and parameter.
- Create a dimension (name it Proj or No Proj) and add two codes Projection and No Projection (where Projection is the name of the Projection Scenario). I often add this Dimension to an empty Module named Lists to make it easily accessible in the Report Designer.
- In Report Designer, create a Proj or No Proj dimension parameter (to keep things clean, do not filter the report on this dimension).
- Create an expanding group over the Projection and Projection variance columns and group these columns by the Scenario dimension.
- Filter this expanding group by Scenario referencing the Proj or No Proj parameter in the filter (since this parameter isn’t for the Scenario dimension, it won’t be available to pick from the look-up window. It can be entered directly or copied and pasted).
When the user runs the report, if he/she chooses No Projection, the projection column and variance do not appear.
If the user chooses Projection, the Projection and variance columns appear (assuming they have data).
I prefer this method since it eliminates virtually duplicate reports and correspondingly reduces report maintenance.
