DR Sets

Overview

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. You can add a Dynamic Report Set from the File Cabinet page.

The Dynamic Report Set includes two tabs:

• Single - Allows you to create a Report Set with selected members.

• Advanced - Allows you to select members and functions to create a Report Set.

The image below displays the screen that appears when you create a new Dynamic Report Set.

The image below displays the Advanced tab.

Field Descriptions

The following fields are available on the Dynamic Report Set toolbar:

• Reload - Click the Reload icon to refresh the page.

• Save - Click the Save icon to save the Report Set.

• Save As - Click the Save As icon to save the Report Set with a different name.

• Save as Static - Click the Save as Static button to save the Report Set as static.

• Preview - Click te Preview button 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 or without indents based on the status of Suppress Line Spacing.

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 which 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, and so on.

Classic and 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.

Build Dynamic Reports Using Report Sets

A Dynamic Report consists of a report grid. For example, an income statement report might be shown on the column axis and income statement accounts along the rows axis. At the intersection of each member along each axis, a cell displays the measure data for the selection of company and department.

You can use Report Sets as rows or column axis members in a Dynamic Report. Report Sets are individually designed on a specific dimension. Once created they can be referenced in multiple reports. Report Sets on the row of a Dynamic Report are referred to as Row Sets. Report Sets on the column axis of a Dynamic Report are called Column Sets.

A few examples of Report Sets are; Income Statement, Balance Sheet, and Cash Flow statements on an Account dimension or yearly, quarterly, and monthly on a Time dimension.

When a Static or Dynamic Report Set is used on the row axis of a Dynamic Report, the primary dimension which is used in the Report Set is reflected as the column header in the report. This is also applicable to the Dynamic Report Set used in combination with other Report Sets and dimensions.

Report Precedence

The following table provides information on report precedence such as how and when rows override columns or vice versa.

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.

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.

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 Workforce Attribute and the value of each compensation item, you will not be able to save the Report Set. This is because Workforce Attributes are created as measures in the Workforce Planning Cube.