How to Build Reports Using Rules

The typical structure of 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. In a report, both rows and column axis members may be a direct or pre-defined group of members called Report Sets. Report Sets are individually designed on a specific dimension. Once created they can be referenced in multiple reports. Typical 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.

In Practice

Create a Variance Report with Currency Year Forecast and CurrentYear Actual to Report Operating Expenses for a Company, Scenario and Time

Report Structure:

1. Create a report set on an account dimension.

   1. From the File Cabinet, click the Add list box and select Report Set.

   2. Enter Operating Expenses for Code.

   3. For the target report set Folder selection, select Report Sets.

   4. Select the Financial Reporting Area.

   5. Select Account for the Dimension.

   6. Select a Static report set Type.

   7. Click Save.

      

      Selecting a reporting area determines the dimensionality for multidimensional reporting.

2. Build the report set.

   1. Enter 7 and click the Add Rows button to insert 7 lines.

      

   2. Add line descriptions.

   3. Set each line Type to Reference Account.

   4. Set the Variance for each line to -1.

      The Variance property value is used in the calculation of a favorable budget variance. A favorable budget variance is a better actual result than the amount that was budgeted.

3. Set up rules for reference account lines.

   1. Select a reference account line and click Add under the Rule column.

   2. From the Rule tab, select the account dimension members from the account hierarchy for each line.

      When a reference member definition directly corresponds to a member selection on the dimension used for building the report set, the member definitions are treated as dynamic members because they yield better performance over member references to multiple dimensions and advanced functions and formulas. During report set setup it is highly recommended that you evaluate the definitions to minimize the multiple dimension references or using the .CurrentMember functions in the MDX definition.

   3. Save the report set.

      

4. Create a report set on a measures dimension.

   1. From the File Cabinet, select the Add list box and click Report Set.

   2. In the Code text box, enter Variance Reporting (Vs Fcst & PY).

   3. For the target report set Folder selection, click on the magnifying glass and select Report Sets.

   4. Select the Financial Reporting Area.

   5. Select Measures for the Dimension.

   6. Select a Static report set type.

   7. Click Save.

      

5. Build the Report Set.

   1. Insert 8 lines.

   2. Add line descriptions.

   3. Select line Types.

   4. Set up formulas for Excel type lines.

      1. For Var to Forecast, enter the following formula in the Frml (formula) field:

      = (CY Actual - CY Forecast) * Variance

      

      Note:

      To show a favorable budget variance, the variance property value for each line from the report set is placed on a row. To include this value, set up a line of type Variance (completed in step b) column set and use it for variance reporting. Favorable variance examples include: actual revenues are more than the budget or plan; actual expenses are less than the budget or plan; actual manufacturing costs are less than the budget for the period.

      2. For Var to PY enter the following in the Frml field:

      = (CY Actual – PY Actual) * Variance

   5. Set up line formats. Select the Percent(abs) format for Var% lines.

      

   6. Select the number of decimal positions for the lines. Select 1 decimal position for Var% lines.

   7. Hide the Variance type line by setting the Hidden property to Yes.

   8. Click the background fill bucket and select White Smoke to set the background color for header lines.

      

   9. Select Top and Bottom Border for each report set line.

      

      For report column formatting, each report line is derived based on the formatting from report row lines. To override this default behavior for specific columns, use special type formatting for the report set lines. Special type formatting options applicable for report sets are: Number(abs), Percent(abs), Currency(abs).

      To apply column borders (left and right) for reports, select the Top and Bottom Border option for report set lines used on the column axis of the report. The Top and Bottom Borders are transposed to left and right borders when used in the report set on the column axis of a financial report.

6. Set up Rule expressions for the line with a Reference Account type.

   1. Select a Reference Account type line and click the Add under the Rule column.

   2. Click the Advanced Rule tab.

   3. Add the rules below for Reference Account lines.

      Rule Line	Rule Formula	Desc./Notes
      CY Actual	Select the Actual member from the Scenario dimension. Drag and drop to Rule Builder. Insert a comma(,) after the Actual member in the formula editor. Select MTD from the measures dimension . Click on Add Member link. Enclose the members in the formula editor within a parenthesis ().	For this line to represent Current Year or Current Period actuals, refer to two dimensions such as the Scenario dimension to reference Actual data and the Measures dimension to represent the measure data such (MTD). When directly referencing members like this, add the references either from the Advanced Rule or Rule tab by selecting the Multiple Dimensions checkbox. Current Year or Current Period references is dynamically evaluated based on user selections at run time.
      CY Forecast	Select MTD from the measures dimension.	Current reference is dynamically evaluated based on user selections at run time. Similarly, a Current Year or Current Period reference is based on user selections.
      PY Actual	Select the Actual scenario from the Scenario dimension. Drag and drop to Rule Builder. Insert a comma(,) after the actual member in the formula editor. Select MTD from the measures dimension. Click the Add Member link. Insert a comma after the measure member in the formula editor. Reference prior year of same period prior year using the parallelperiod MDX function (Parallelperiod(year,1,Time.CurrentMember) Enclose the members in the formula editor in parenthesis.	The ParallelPeriod function involves the following three parameters: Level expression - Specifies the level at which the returned member will lag from the specified member. If you omit this, it uses the level immediately above the assumed member, Time.CurrentMember. Lag- Specifies the number of positions that the returned member will lag from the specified member, at the specified level. For example, if you specify the Month level and a lag of 2, the returned member will be from two months before the specified member. You can lag to get a member that lies in front of the specified member in the hierarchy. If you specify zero lag, the function will return the specified member. If you omit this parameter, the function uses a lag of one by default. Member expression - Specifies the member at which the returned member shares its relative position. If you omit this parameter, the function uses Time.CurrentMember by default.

      Rule definition syntax :

      A report set line can have reference of members from one or more dimensions. It is essentially a multidimensional member.

      When a line has a single member reference then it is a simple member reference on the dimension. For example: [Measures].[MTD]

      When referencing more than one dimension, there is only one member from each dimension. To compose a member definition with more than one dimension, you must wrap the members in parentheses. For example: ([Scenario].&[1],[Measures].[MTD])

      When a report is set built on a specific dimension, each report set line must have a reference to at least a single member of that dimension.

7. Create a financial report.

   1. From the File Cabinet click the Add list box and select Dynamic Report.

   2. Select the Financial Reporting Area and click OK.

   3. Drag and drop the report set created for Account on the Row axis.

   4. Drag and drop the report set created for Measures on the Column axis.

   5. Drag and drop Company, Time, and Scenario dimensions on to the Page axis.

      

8. Setup header and footer details (not shown in this example).

   1. Click the Header & Footer button.

   2. Drag and drop Company, Scenario and Time into the header section.

   3. Drag and drop Current Date on the left column under the report Footer section.

9. Click the Run button.