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.

Using Row Sets and Column Sets in a Dynamic Report

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.