Reports_Report Sets

Using Rules and Single Dimension Features in Report Sets

Purpose: Provide instructions to use Add Rules and Single Dimension features to pull dimension members into a Report Set.

Add/Edit Rule Box — Single tab

The Single tab adds one or more dimension members to the selected line.

Steps

1. In the Report Set, add the desired number of lines.
   Note: At least one line is required before adding a Rule.

2. In the Reference Account line, under the Rule column, select Add to open Add Rule.

3. Under Single, in Select Member, choose the desired dimension member(s).

4. Select Add and Close.

Notes:

• With this method, the Report Set and the resulting Dynamic Report display the amount/sum for the selected member(s) only.

• After mapping, use Edit to update.

Single tab (Display leaf‑level members)

You can pull data based on the hierarchy level and display individual lines for each dimension member in the Dynamic Report.

Steps

1. In the Report Set, add the desired number of lines.
   Note: At least one line is required before adding a Rule.

2. In the Reference Account line, under Rule, select Add to open Add Rule.

3. Under Single > Select Member, choose the desired roll‑up level member.

4. In Options (bottom‑right), choose Leaves to display all leaf‑level members in the Dynamic Report when this Report Set is used.

5. Select Add and Close.

Notes

• The Report Set line will display only the selected roll‑up member.

• The Dynamic Report will display one line per leaf member.

• If members are added under the selected roll‑up later, they are automatically captured in future reports.

• After mapping, use Edit to update.

Add/Edit Rule Box — Multiple tab

The Multiple tab maps intersections across multiple dimensions to the same line.

Steps

1. In the Report Set, add the desired number of lines.
   Note: At least one line is required before adding a Rule.

2. In the Reference Account line, under Rule, select Add to open Add Rule.

3. Under Multiple, in Dimension, pick a dimension from the drop‑down.

4. In Select Members, choose the required roll‑up or leaf member.

5. Repeat to add other dimensions and members to the same line.

6. Select Add and Close.

   Note:

   After mapping, use Edit to update.

   Add/Edit Rule Box — Advanced tab

Use dimension‑based MDX to build more complex rules.

Steps

1. In the Report Set, add the desired number of rows.
   Note: At least one row is required before adding a Rule.

2. In the Reference Account line, under Rule, select Add to open Add Rule.

3. Under Advanced, choose the target dimension from the drop‑down.

4. In Select Member, either drag & drop roll‑up/leaf members or enter MDX functions to compose the rule.
   Tip: Switch between Member and Function tabs as needed.

5. Repeat to complete the rule syntax.
   Note: If the syntax includes more than one dimension, wrap the expression with parentheses and separate items with commas.

6. Select Add and Close.
   

   Note:

   After mapping, use Edit to update.

Single Dimension feature

Select any hierarchy level and pull members at that level into the Report Set, automatically creating one line per member.

Steps

1. In the Report Set, open More (three dots) > Add Lines & Rules and choose Single Dimension.

2. In Add Lines & Rule, under Select Members, pick the desired roll‑up or leaf member(s).
   Note: Selections appear in Selected Members.

3. Set Options as needed and select Add. You can pull data at any level.

4. After mapping, use Edit to update.

Using Reverse Sign and Variance Column

Purpose: Explain how to use Reverse Sign and Variance columns in Report Sets, including how they work together.

Reverse Sign column

Reverses the sign for a specific line in a Report Set.

Options (select in the Reverse Sign column on a line):

• None — No sign change.

• Reverse Sign — Flip all balances to the opposite sign.

• Negative to Positive — Convert only negatives to positives.

• Positive to Negative — Convert only positives to negatives.

Steps

1. In the Report Set, locate the target line.

2. In Reverse Sign, choose one of the options above.

3. Save the Report Set and run the Dynamic Report.

Variance column

Converts unfavorable balances to favorable values and adds a column indicating that variance exists for the line.

Settings for the Variance column

• -1 — Multiply the row value by -1 (flips sign).

• 0 — Variance not calculated.

• 1 — Multiply the row value by 1 (keep sign).

Important: To display variance for a line, you must update both the Row Set and the Column Set.

Steps

1. In the Row Set, set Variance = -1 for the desired line and Save.

2. In the Column Set, add a new line and set Line Type = Variance Factor.

3. For each required Excel Line in the column set, multiply the formula by the Variance Factor line.
   Example: In the Formula/Rule column, =(Actual - Plan) * Variance Factor.

4. Save and then run the Dynamic Report using the modified Row and Column Sets.

Reverse Sign + Variance — behavior

Using both options together changes the way data is presented. The PDF shows example combinations (e.g., Reverse Sign alongside Variance = -1) and their report outcomes.