Report Sets Summary

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 (aka MDX) 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 MDX 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

• Tuples — Mutli-dimensional MDX.

One limitation with Static Report Sets is that they don't support Set Value Expressions where the MDX 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

1. Navigate to Reports.

2. Click Add icon and select Report Set.

3. Select a Report Set type, either Static or Dynamic.

4. Enter a unique alpha-numeric code and a name for the Report Set.

5. 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.)

6. Select a Dimension for which the report set will be based.

7. Select the folder in the File Cabinet where the report set will be stored.

8. Click Save .

9. Define the Report Set through the various fields and functions.

How to Add and Delete Rows in Static Report Sets

How to add rows:

1. Access the Report Set from the File Cabinet.

2. 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:

1. Access the Report Set from the File Cabinet.

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

Data

Conditional

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

1. Edit the Column Set. Format % column with Apply on Header on the Line tab.

2. Set Precedence to Column on the Data tab.

3. Edit the Row Set. Format row as Apply on: Separate Header & Data on the Line tab. Select double underline.

4. On the Data tab, set Precedence to Row.

5. Include the Report Set on the row or column of a Dynamic Report.

6. Run the Dynamic Report with the Edited Column and Row Sets.

Input and Configuration for Static Report Sets

In Practice:

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

   
   

2. Click on a blank cell to enter information for the item.

3. Right-click any cell to open the Copy/Paste/Insert Copied Rows dialog.

How to Add Rules to Static Report Sets

In Practice

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

2. 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:

• Create a row set for an account dimension which excludes one member

• How to write MDX for a row set that pulls accounts for one product code

• How to write MDX/Rules to have substitution variables in a column set and a summation of the last 12 months of Actuals data

• How to write MDX on a row set where if the column set is 2017 Actuals, then perform an addition, else do nothing

• How to write MDX on a row set to pull the children, or Level0 members of a specific dimension

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.

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.

Report Set Use Case - How to write MDX for a row set that pulls accounts for one product code

SUM({[Product].&[123]}*{[Account].&[456],[Account].&[789]})

Report Set Use Case - How to write MDX/Rules to have substitution variables in a column set and a summation of the last 12 months of Actuals data

SUM({[Scenario].&[1]}*{@CURMTH@.lag(11):@CURMTH@})

The MDX example retrieves a summation of the last twelve months of data available against the Actual scenario. If you use this expression, make sure you are using substitution variables and the variable '@CURMTH@' is set to the current month period in your report.

Report Set Use Case - How to write MDX on a row set where if the column set is 2017 Actuals, then perform an addition, else do nothing

iif(Scenario.CurrentMember is [Scenario].&[1] and rank(Ancestor([Time].CurrentMember, Year), [Time].[All Time].Children) = 17,   SUM({[CostCenter_BE].&[4182]}*{[Account].&[3818], [Account].&[2320], [Account].&[2314] ), NULL )

Interpreting this formula:

if

(Scenario is Actuals and in the Time hierarchy at the Year level for the 15th member (time hierarchy starts at 2001 and year 2017 will be the 17th member),

then do the sum,

otherwise return null

Report Set Use Case - How to write MDX on a row set to pull the children, or Level0 members of a specific dimension

Use Descendents(,,LEAVES) where leaves are the children.

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.

1. In Reports, click the More menu and select Single Dimension under the Add Lines & Rules section.

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