DR Sets

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.

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

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.

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.

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

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.

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.

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.