Example of Building a Dynamic Report

Creating a Dynamic Report

In this use case, the consolidated income statement shown below will be created. A report set is created for the row and another for the column axis. The information below walks you through the steps to recreate this Dynamic Report. It does not include information on how to create the report sets used on the row and column axis. For information on how to create a report set, see Creating a Variance Report with Forecast and Prior Year to Report Operating Expenses for a Selected Company, Scenario and Time.

1. Go to Reports and access the File Cabinet.
2. Click the Add button.
3. Select Dynamic Report.
4. Select the Financial Reporting Area.
5. Drag the Company dimension to the Page axis and click on it to display the members.
   
   
6. Select the Everest Holding Group.
7. Click Report Sets.
   
   
   
8. Drag the Account Report Set to the Row. Select New Income Statement.
   
   
   
9. Drag the Scenario Report Set to the Column and select Actuals PY CY.
   
   
   
10. Click the forward arrow to run the report. You're done!

Creating a Variance Report with Forecast and Prior Year to Report Operating Expenses for Company, Scenario and Time

1. Create a report set on an account dimension.
   • From the File Cabinet, click Add and select Report Set.
   • Select Static for Type.
   • Enter Operating Expenses for Code and Name
   • For Reporting Area, select Financial.
   • For Dimension, select Account
   • For the target report set Folder selection, select Report Sets.
   • Click Save.
     Selecting a reporting area determines the dimensionality for multidimensional reporting.
2. Build the Row Set.
   • Enter 7 and click Add to insert 7 rows.
   • Set each line Type to Reference Account.
   • 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.
   • Select a reference account line and click Add under the Rule column.
   • From the Rule tab, select the account dimension members from the account hierarchy for each line.
   • As the account dimension members are selected, the rule is built. An example is shown below.
     
   • Click Add and Next to move from one row to the next.
     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.
4. Create a report set on a measures dimension.
   • From the File Cabinet, select the Add list box and click Report Set.
   • In the Code text box, enter Variance Reporting (Vs Fcst & PY).
   • For the target report set Folder selection, select Report Sets.
   • Select the Financial Reporting Area.
   • Select Measures for the Dimension.
   • Select a Static report set type.
   • Click Save.
5. Build the Column Set.
   • Insert 8 lines.
   • Select Excel for two lines and set up formulas as follows.
     1. Enter Var to Forecastas the name for line 1. 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 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. Enter Var to PYas the name for line 2. Enter the following in the Frml field:

        = (CY Actual – PY Actual) * Variance

   • Set up line formats. Select the Percent format for Var% lines.
   • Select the number of decimal positions for the lines. Select 1 decimal position for Var% lines.
   • Hide the Variance type line by selecting the Hidden property.
   • Click the background fill bucket and select White Smoke to set the background color for header lines.
   • Click the Borders list box and select Top and Bottom Border for each report set line.
     For report column formatting, each report line is derived based on the formatting from row set 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, Percent, and Currency.
     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 3 rows to Reference Account for Line Type. Set up Rule expressions for each row.
   • Select a Reference Account type line and click Add under the Rule column.
   • Click the Advanced Rule tab.
   • Add the rules below for Reference Account lines.
     1. Rule Line: CY Actual
        Rule Formula:
        • Select Scenario. Drag and drop Actual to the Rule Builder pane.
        • Click the Add Member link.
        • Insert a comma(,) after the Actual member in the Rule Definition pane.
        • Select Measures. Drag and drop MTD to the Rule Builder pane.
        • Enclose the members in the Rule Definition pane 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.
     2. Rule Line: CY Forecast
        Rule Formula: Select MTD from the measures dimension and drag and drop it to the Rule Builder pane.
        
        Note:

        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.
        
        
     3. Rule Line: PY Actual

        Rule Formula:

        • Select the Actual scenario from the Scenario dimension and drag and drop it to the Rule Builder pane.
        • Insert a comma(,) after the actual member in the Rule Definition pane.
        • Select MTD from the Measures dimension and drag and drop it to the Rule Builder pane.
        • Insert a comma after the measure member in the formula editor.
        • Click Functions.
        • Select ParallelPeriod.
          
        • Drag and drop it to the Rule Builder pane.
        • Click the Rule Builder pane.

          

        • The resulting expression will look as follows:

          (Parallelperiod(year,1,Time.CurrentMember)

        • Enclose the members in parenthesis.
     4. 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 may 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 Dynamic Report.
   • From the File Cabinet click the Add list box and select Dynamic Report.
   • Select the Financial Reporting Area and click OK.
   • Drag and drop the report set created for the Account on the Row axis.
   • Drag and drop the report set created for Measures on the Column axis.
   • Drag and drop Company, Time, and Scenario dimensions on to the Page axis.
8. Setup header and footer details.
   • Click Header & Footer.
   • Drag and drop Company, Scenario and Time into the header section.
   • Drag and drop the Current Date on the left column under the report Footer section.
9. Click Run to view the results.