Report Sets - How tos

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.

Use Case

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.

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.

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

Sum(Except([Account].&[193].Children, [Account].&[194]))

The following 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.

iif(Scenario.CurrentMember is [Scenario].&[1] and rank(Ancestor([Time].CurrentMember, Year), [Time].[All Time].Children) = 17,  SUM({[Cost

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

Use Descendents(,,LEAVES)

where leaves are the children.

Row Sets

Row Sets are Report Sets used on the Row axis of a Dynamic Report. In the image below, the New Income Statement Base Row Set is used on the Row axis of the Consolidated Income Statement Dynamic Report.

The New Income Statement Base Row Set is displayed below.

The Row Set lines translate to the rows of the Dynamic Report output.

Rules are used on lines in the Report Set to reference accounts; like the 4001-Gross Revenue account on line 11 of the Report Set.

Formulas are used to tally lines. For example, Net Sales equals F11-F14.

Column Sets

Column Sets are Report Sets used on the Column axis of a Dynamic Report. The Actuals PY CY column set is used on the Column axis of the Dynamic Report below.

When the Actuals PY CY column set is opened, it displays two lines. The first line has a substitution variable of currency year minus one. If the current year is 2017, then the year displayed is 2016 as shown in the Dynamic Report. The second line references the current year so 2017 is displayed in the second column in the Dynamic Report.

The advanced rule for line 10 in the column set has a description of [Actual][YTD]. If the rule is opened, the syntax is displayed (as shown below).

The syntax indicates that the Actual scenario (identifier is 1), the year to date measure (identifier is YTD), and the current month less the last 12 months substitution variable is being formulated for the first column.