Use Case

2 Minutes to read

Print
Dark
Light
PDF

Article summary

Did you find this summary helpful?

Thank you for your feedback

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.

Write MDX for a row set that pulls accounts for one product code

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

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

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.

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({[Cost

Interpreting this formula:

(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

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.

Using Row Sets and Column Sets in a Dynamic Repor

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.

Was this article helpful?

What's Next

Dynamic Reports