- 15 Minutes to read
- Print
- DarkLight
- PDF
Report Sets Summary
- 15 Minutes to read
- Print
- DarkLight
- PDF
Report Sets Overview
Customize the rows and columns of a Dynamic Report with Report Sets.
There are two types of Report Sets; Static and Dynamic.
See Static Versus Dynamic Report Sets
Report Sets are created on a specific dimension and typically contain members of other dimensions. They also contain the link from the report lines to the Account segments in a hierarchy, which are created using Rule formulas.
Typical examples of report sets are; income statements, balance sheets, and cash flow statements. For example, you might use a rule to map a Gross Margin line of an Income Statement to a Gross Margin member on an Account hierarchy.
Classic Versus Modern Report Sets
There are two versions of Report Sets; Classic and Modern. All Classic Report Sets work in modern browsers. The Version column differentiates between Classic and Modern report sets. A Classic Report Set is an existing Report Set that has not been migrated to the newer (Modern) version.
You can add, edit, save, save as, and copy Classic Static Report Sets from a modern browser. An existing Classic Static Report Set can be modified and saved from a modern browser, and the audit log is updated accordingly. Freeze On/Off, Grid Headers On/Off, and other toolbar options are saved and applied for a Classic Report Set when accessed from modern browsers.
All toolbar options are available when a Classic Static Report Set is accessed from a modern browser, as per user security and report access settings.
Available Data Formatting for Classic Report Sets
When a Classic Static Report Set is opened from a modern browser, and you click Format, Data (as shown below), the Currency Alignment, Negative Number, Thousand Separator, and Precedence are hidden, as these features are not available for Classic Report Sets. For all the other fields that are applicable to Classic report sets, the corresponding selections are displayed when opened within modern browsers.
Enabling Modern Features in Classic Report Sets
When you access a Classic report set in a modern browser, a toggle switch, Enable Modern Features, appears on the page. Flipping the switch ON converts the Classic Report Set to Modern, and all the new features can be accessed.
This conversion action cannot be reverted. New features are applied in place and immediately reflect (with no additional steps required) in the Dynamic Reports created using the Report Set where Modern Features are enabled.
The image below shows the Data Format options available for classic versus modern report sets.
The Absolute option is available for Number Format and Scale for Data Format, for all Classic Static Report Sets, accessed from modern browsers. If Absolute is selected for Number or Scale for existing Classic Report Sets, the checkbox appears as selected when accessed from modern browsers (and vice versa); it is not displayed for Modern Static Report Sets.
After enabling Modern Features on a Classic Report Set, all default formatting is applied, and column precedence is applied for absolute rows. All details can be modified and saved. The audit log is updated upon edit and save.
Dynamic Versus Static Report Sets
Static Report Sets offer better features, formatting and functionality compared to Dynamic Report Sets. The key difference is with the types of expressions that Static Report Sets support. These include:
Member Value Expressions — Parent, lead, lag, first child, last child, current member, previous member, next member, ancestor, first sibling, last sibling, etc.
Numeric Functions —Aggregate, sum, average, count, etc
Time Series Functions — Parallel period, opening period, closing period, periods to date, etc.
String Manipulations — MEMBERTOSTR, STRTOMEMBER
One limitation with Static Report Sets is that they don't support Set Value Expressions where it results in a set of elements or members. This is where Dynamic Report Sets can be used, using Set Value Expressions such as Hierarchies, Descendants/Ancestors/Children of a member, Filter, TopCount, Order, Except, etc.
How to Add a Report Set
The structure of a Report Set contains the underlying detail upon which the Report Set is built. When you create a new report set, you define the following:
Type—Either static or dynamic
Static—Create a static report by defining each report set line item individually. Static report sets often have header lines, Excel lines, and reference account lines. See Also: Using Excel Lines in a Report Set
Dynamic—Create a dynamic report by defining formulas or multi-dimensional expressions (rules). Rules are defined on segment hierarchies.
Reporting Area—The multidimensional cube upon which the report is built, either financial, sales, workforce, or scorecard.
Dimension—As per the dimensions and attribute dimensions created on the Attribute Setup and Common COA pages.
Once you define the Report Set, as described below, you then configure the fields and functions that you want to apply to the Report Set.
In Practice: Adding a Report Set
- Navigate to Reports.
- Click Add icon and select Report Set.
- Select a Report Set type, either Static or Dynamic.
- Enter a unique alpha-numeric code and a name for the Report Set.
- Select the Reporting Area. (Note that if you select Scorecard, attributes and attribute hierarchies will be displayed in the Dimension drop-down list. The display of attributes and attribute hierarchies is subject to the configuration on the Cube Settings and Dimension Security pages.)
- Select a Dimension for which the report set will be based.
- Select the folder in the File Cabinet where the report set will be stored.
- Click Save .
- Define the Report Set through the various fields and functions.
How to Add and Delete Rows in Static Report Sets
How to add rows:
- Access the Report Set from the File Cabinet.
- Enter the number of rows you want to add and click the Add Rows icon to add rows to the grid.
How to delete rows:
- Access the Report Set from the File Cabinet.
- Enter the number of rows you want to delete and click the Remove Rows icon.
How to Format Static Report Sets
Click Format to open a pane where you can format the lines and data in the grid, as well as add conditions (equal to, not equal to, etc.).
Line
Style | Apply bold, italic, underline or double underline. Select a font, font size, font color and background color. |
Alignment & Indentation | Select to justify right, center or left. Select right or left indent. Indentations and alignments apply to only Headers/Titles. They don't apply to Data/Numbers, which means, for example, that you will not see alignment or indentation when Apply On (under Miscellaneous) is set to Data. Numbers are always right aligned. |
Borders | Select border options and border color. |
Miscellaneous | Apply line selections to the report set header only, data only, the header and the data, or separately based on header and data selections. When Separate Header & Data is selected, you can apply formatting separately to report set headers and data to build more highly formatted report sets. The selections apply to the formatting on the Data tab in the formatting pane. When you run a Dynamic Report with the report set, the formatting is retained. |
Data
Display As | Display data in a number, percent, or currency format. |
Currency Alignment | Select to display a space between the currency symbol and that data or no space. |
Number Format | Decimal- Select the number of decimals you want displayed. Negative Numbers- Select how you want negative numbers displayed. Reverse Sign- Select any of the following to change the value of the data:
You can also change the value navigate using the Reverse Sign column available in the grid. If you select an option from the Reverse Sign column, the same option is displayed as selected in the Reverse Sign drop-down list. Number Scale - Select how you want the number scale displayed. Display the thousand separator - Select to display the thousand separator, For example, 5,888 or not 5888. |
Miscellaneous | Select row or column. See the Report Precedence topic. |
Conditional
Add Condition | Click to add conditional formatting to the Report Set. |
Reference Line | Select the reference line in the report set that you want to apply to condition to. Or click a row in the report set and it will display here. |
Condition 1 | Appears once you click Add Condition. Select a value / condition. For example, if the row is greater than zero then it will appear in green. |
Conditional Value | Values must be numerical with a maximum of 6 decimal places and 12 digits. |
Apply | Apply the condition to the report set row. |
How to Format Report Set Columns, Rows, and Headers
Let's say that you have reports that you distribute quarterly. These reports have one or more variance columns that need to be formatted as %. Additionally, you need borders across all columns. To make this work, you'll need to use a combination of Column Set/Column Precedence and Row Set Separate Header & Data. Then on Row Set, update both the Line and Data tabs in the Format menu.
In Practice
Edit the Column Set. Format % column with Apply on Header on the Line tab.
Set Precedence to Column on the Data tab.
Edit the Row Set. Format row as Apply on: Separate Header & Data on the Line tab. Select double underline.
On the Data tab, set Precedence to Row.
Include the Report Set on the row or column of a Dynamic Report.
Run the Dynamic Report with the Edited Column and Row Sets.
Input and Configuration for Static Report Sets
In Practice:
Click on any populated cell in the grid to view the options available. For example, under the Line Type column, options include Reference Account, Header, Row Title, Excel, and Variance.
Note:- Reference Account-The Reference Account organizes data in reports using defined mappings, providing a clear and structured way to understand and analyze information.
- Header- Header Lines are created to display the header in the reports.
- Row Title- In the column report set, row titles appear when you divide the report into sections.
- Excel- Use Excel in the report set to build formulas that seamlessly present and showcase the data in the reports.
- Variance- In the Report set, use the variance type to calculate A-B or B-A and multiply by -1 to determine the numbers as negative or positive.
Click on a blank cell to enter information for the item.
Right-click any cell to open the Copy/Paste/Insert Copied Rows dialog.
How to Add Rules to Static Report Sets
In Practice
Click Add or Edit in the Rule column to open the Add Rule or Edit Rule dialog. From here, you can add Single or Multiple members from the selected Dimension, as well as the actual rule itself.
Select Single, Multiple or Advanced. The Advanced page allows you to select Members and Functions for a particular scenario, and then build a rule for them by dragging them to the Rule Builder pane.
For examples on how to write rules and use cases, see the following topics:
Static Report Sets - More Menu
From the More menu, you can add lines and rules (simple-single dimension) and perform the following grid actions:
Turn gridlines on or off
Turn freeze on or off
Show/hide row header (modern browsers only)
Show/hide column header (modern browsers only)
Show/hide the formula bar (modern browsers only)
Show/hide the formula column (modern browsers only)
Show/hide all formulas (modern browsers only)
Adding a Dynamic Report Set
Dynamic Report Sets are created using Rules, which fetch members directly from the dimensional hierarchies. Dynamic Report Set performance is directly linked with the dimension hierarchy setup.
The image below shows the screen that appears when you create a new Dynamic Report Set.
Options for Rule Types
Add Single or Multiple Dimension, or Advanced Rules) by clicking the tab at the top of the Add or Edit Rule page.
Selected (default)—Members in the tree, as selected by user
Children—Immediate children of members, as selected by user
Selected + Children—Selected members, and their immediate children
Selected + All Children—Selected members, and all levels of children under them
Leaves—Leaf members at all levels under the selected members
Selected + Leaves—Selected members, and their leaf members, at all levels
Selected + Parents—Selected members, and their immediate parents
Exclude Selected—All members in the tree, excluding those selected
Preview
Click Preview to launch the Dynamic Report Set Preview page. Results are based on the Rule definition. All members selected on the Dynamic Report Set page are listed. Members are displayed with/without indents based on the status of Suppress Line Spacing.
Options for Single Dimension Rule in Report Set
All the options that are supported by Dynamic Reports are available in Single Dimension rule. The selected options in a Report Set are dynamically applied at Dynamic Report run time instead of adding members to Report Set lines one time at the setup. This ensures that any new members added in the hierarchy are pulled dynamically without making any changes to Report Set definitions. This is applicable to Main, Alternate, Attribute, and Attribute Hierarchies, Substitution (Standard and Derived) Variables when used within the Single Dimension rule in a Report Set. Additionally, this is available in all Reporting Areas (Financial, Workforce, Scorecard).
The following fields are added to the Edit Rule window:
Suppress Indents
You can select this check box to remove indents from the output for the selected member(s), children, all children, the selected member(s) plus the children, the selected member(s) plus all children, leaves, the selected member(s) plus leaves, or the selected member(s) plus the parents.
Options
The drop-down list is enabled only when a single rollup member is selected in the rule. The following options are available in the drop-down list:
Selected (default) - Members in the tree, as selected by the user:
Children - Immediate children of members, as selected by the user
Selected + Children - Selected members, and their immediate children
Selected + All Children - Selected members, and all levels of children under them
Leaves - Leaf members at all levels under the selected members
Selected + Leaves - Selected members, and their leaf members, at all levels
Selected + Parents - Selected members, and their immediate parents
The following additional options are available for the Time dimension:
Year
Quarter
Month
Trailing
Total - Trailing
The default value is Selected in the Options drop-down list for all the existing and new Report Sets. This is available across all Reporting Areas in both the Classic and Modern Report Sets.
Display Parents Last
The Display Parents Last check box is available for the Selected + Children, Selected + All Children, Selected + Leaves, and Selected + Parents options. If the check box is selected, the parent member will be displayed at the bottom of the hierarchy.
The following image displays the new fields added to the Edit Rule window.
The Excel Formulas, Insert Formulas, Formula exceptions are automatically adjusted when Options are dynamically applied at Dynamic Report run time.
The selected Options is displayed in the Report Set based Dynamic Report as shown in the following image.
Limitations
- When you copy and paste a rule line using the keyboard, the selected Options is not retained. If you edit the rule, the Edit Rule window opens in the Advanced tab.
- When you define Excel formula on a rule, the member is displayed in Dynamic Reports only when you select Selected + Children, Selected + All Children, Selected + Leaves and Selected + Parents from the Options drop-down list.
- When you add a conditional format on a line with reference to another line, the Report Set rule with the following Optionsis not supported:
Children
All Children
Leaves
Year
Quarter
Month
Trailing
Total - Trailing
Dynamic Report Set - Row and Column Precedence
The following row/column Report Set precedence is applied (as shown in the table below) when a Dynamic Report is run with Report Sets.
For example, if precedence on a row/line is set to column, and precedence on column is set to row, the row takes precedence for any line, data format, or formulas and calculations for the intersection.
Row Precedence | Column Precedence | Result |
---|---|---|
Row | Row | Row |
Column | Column | Column |
Row | Column | Column |
Column | Row | Row |
Row | NA (Hierarchy) | Row |
Column | NA (Hierarchy) | Default |
NA (Hierarchy) | Row | Default |
NA (Hierarchy) | Column | Column |
NA (Hierarchy) | NA (Hierarchy) | Default |
Report Sets and Measures
You cannot use more than one measure in a Report Set. For example, if you want to create a Report Set with an HR Attribute and the value of each compensation item, you will not be able to save the Report Set. This is because HR Attributes are created as measures in the Workforce Planning Cube.
Report Set Use Case - Create a row set for an account dimension which excludes one member
Sum(Except([Account].&[193].Children, [Account].&[194]))
In this example, 193 is an account parent named Operating Expenses and 194 is a child of that parent. The members of the set are summed. Other valid options might be to Average or Count the members in the set.
How to Mass Load Leaves for a Rollup in a Report Set
When building a new Report Set, use the mass load feature to select a roll-up node (parent member) and pull all of its leaf level members into the Report Set. which will create individual lines for each member. This way you will not need to type or copy and paste each member individually.
In Reports, click the More menu and select Single Dimension under the Add Lines & Rules section.
After selecting the parent member, set the option to Leaves and click on Add as shown in the screenshots below.
Using Excel Lines in a Report Set
The full range of Excel functions can be used on a report set (rowset or columnset) line. After selecting Excel as the linetype, start typing the function and a function menu will appear. Hover over each function for a description as shown below.
You might want to use Excel lines in Report Sets to round or sum cells or use if statements so that certain criteria is met. In the use case below, the round and sum functions are used.
In Practice
In this use case a row set is created that totals assets and liabilities. The sum and round Excel functions are used to total assets.
In the image below, line 21 is an Excel line that provides the total amount of assets. In the Formula/Rule column, the Excel function is:
=SUM(ROUND(F15,0)+ROUND(F19,0))
This means that the sum for cell F21 is cell F15 (Total Current Assets) rounded to 0 decimals plus cell F19 (Total Non-Current Assets) rounded to 0 decimals. If you entered F15,2 then the cell total would round to 2 decimals.
When this rowset is used on the row axis of a Dynamic Report, the results are Total Current Assets (526,697) plus Total Non-Current Assets (928,703), which equal Total Assets for 2017 of 1,455,400 as shown below.