Working with Dynamic Reports
  • 25 Minutes to read
  • Dark
    Light
  • PDF

Working with Dynamic Reports

  • Dark
    Light
  • PDF

Article summary

When you open a report from the reports home page, depending on that report configuration, you can view the data in the report. You can refresh the report from the top menu using the Refresh option to view the latest data. Apart from refreshing the data, you can do the following activities in a Dynamic Report:

  • Update the header and footer of the report using the Show Header and Footer option in the top menu.
  • Expand the arrow near the Page field to view Page, Row, and Column dimension members.
  • Expand the arrow at the left side of the report, view the list of Dimensions or Report Sets, and update the Page, Row, and Column dimension members if required.
  • Run the report to get the latest report data from the top menu using the Run option if you have made changes to the report setup, like after changing any Page, Row, and Column dimension members or updating the header and footer.
  • Take a snapshot of your Dynamic Report using the Snapshot option in the top menu so you can export it to Excel and save it to your desktop or in a folder in the File Cabinet.
  • Use Predict Signals to analyze the report. Refer to the Predict Signals topic to know more.

Formatting a report at Cell Level

The Format functionality contains the following tabs:

Text

  • STYLE - Apply bold, italic, underline or double underline. Select a font, font size, font color, and background color.

  • ALIGNMENT & INDENTATION - Select to justify right, center or left. Select right or left indent.

  • BORDERS - Select border options and border color.

Data

NUMBER FORMAT

  • Display as - Select to display the cell contents in number, currency, or percentage format.

  • Show Zero As - Select how you want to display zero values.

  • Decimal Places - Choose up to 6 decimal places for numbers. Decimal places are displayed in Dynamic Reports based upon whether Report Sets are used or not. When Report Sets are not used in a Dynamic Report, decimal places displayed in quick functions are identical to those in the Dynamic Report. When Report Sets are used, decimal places in quick functions allow for the maximum number of decimal places allowed for a selected range.

  • Negative No. - Determines the format of the negative number. You can display negative numbers in parentheses, in color, or both. If you choose Absolute as the format, negative numbers will be displayed as positive numbers without any formatting.

  • Number Scale - Defines a value that the numbers displayed in the Dynamic Report must be multiplied by to get the actual number.

  • Display Thousand - Determines whether a comma is to be placed to separate thousands.

  • Preview - Displays the format for negative numbers based on your selections.

Other

SUPPRESS

  • Empty Rows - Select to exclude empty rows from the report .

  • Empty Columns - Select to exclude empty rows from the report.

DRILL THROUGH -

Select to drill through Translation and Transaction details. Expand (drill down) and collapse (drill up) report detail.

Transaction and Translation data is not stored in the OLAP Cube from which the Dynamic Reports pull their data, which is why drill-through is required. This means that you cannot run a Dynamic Report to output Transaction or Translation data. For example, lets say you have a travel line item in your report that shows up as a number. You have to drill down to see what transaction data makes up that number. You cannot generate a report to open and display at the transaction level.

Note:
Once you start drilling down, some functionality becomes disabled such as Pivot and Sort.

With Cell Level Formatting, you can control the look and feel of report output down to the cell level. Apply formatting to the entire report, a row/column, a range of cells, or an individual cell. Additionally, you can:

  • apply formatting when using the insert formula row/column in a report

  • format a formula exception value

  • vertically top/center/bottom align headers and numbers in a report

Note:
Header and Footer rows honor the formatting applied from the Header & Footer section

If you opt-in, this functionality applies to all existing and new Dynamic Reports across all browsers. It is available for all reporting areas (Financial, Sales, Workforce).

Cell Level Formatting is enabled for Dynamic Reports that are designed using dimensions or attributes and Report Sets on the Row or Column axes.

Note:
The Cell level formatting options in Dynamic Reports are enabled by default for new and existing tenants.

The default format for a Dynamic Report is:

  • Column headers are center aligned and bold

  • Row headers are left aligned and bold

  • Drill down members are shown with indents

  • Numbers are right aligned

  • Font = Arial, size = 8, color = black, fill color = no color

Formatting is applied to all cells within the body of the report and:

  • Applies to row and column headers, numbers/data values in the report

  • Applies to merged cells (when multiple dimensions are used on the column axis)

  • Notes, Insert Formula row/column, Formula Exceptions

Formatting is retained when exporting a Dynamic Report, when it is included as an email attachment, linked report, and when used in a Report Collection or FPP.

When the structure of the report is modified by adding or removing dimensions from the row or column axis, formatting has to be readjusted for accuracy.

How to Apply a Cell Level Formatting to a Dynamic report?
  1. Click Reports.
  2. Open the required report, and click Format (an example is shown below).
    Format-%20cell%20level%20editing
  3. The Format pane appears as shown below. There are 3 tabs; Text, Data, and Other. Ensure the Text tab is selected.
    Format%20box
  4. Select a cell and apply different styles. The same report shown above has been modified and is shown below.
    edited%20formattingTo select a range of cells as shown below, drag the cursor as you would in Excel.
    highlight%20selection
  5. Change the font type and size. In the report below, font size is increased for Apr-23 and May-23.
    changing%20styles-%20font%20size%20and%20font%20typePlanful supports a range of font types, which are enabled in Static Report Sets. Select the list-box to view all supported font types. Fonts size 8 through 32 px are supported. The default is set to 8 for all existing and new Dynamic Reports across all the lines. Based on the font size the row height automatically adjusts.

  6. Select alignment and indentation options. In the report shown below selected cells are vertically aligned to the bottom of the cell. Select horizontal alignment (left, center or right). Select vertical alignment (top, center or bottom). Select to indent left or right. Indents, horizontal, and vertical alignment also applies headers and data values.
    ALignment%20and%20indentation
  7. Apply borders to the report. Select a cell or cells, select a border color and click the border type to be applied. In the image below, a yellow border is applied to a cell.
    apply%20borders
    Here is the border color and border type selection pane. There are many options to choose from including, no border, left and right border, and top and bottom border.
  8. Click the Data tab.
    click%20data%20tab
  9. For Display As, select to display the cell contents in number, currency, or percentage format. When currency is applied to a cell or cells, the Currency Alignment options displays as shown below.
    currency%20display%20asSelect how you want the currency symbol displayed in the cell. There are two formats; Accounting and Currency. With the Accounting format, the currency symbol is displayed in the left-most position within the cell. With the Currencyformat, the currency symbol is displayed directly in front of the number in the cell.When multiple formats are applied to the report, only the most recently applied format gets applied to each cell in the report. When multiple cells with different formatting are selected, format of the first cell is displayed in the pane. Formatting is not supported when Properties are added to the report.
     The Currency symbol displayed is based on your location settings.

  10.  For Show Zero As, select how you want to display zero values (as dashes, empty, or as 0) for a selected cell, cell range, or the entire report. Selecting 0 applies to all data values in the report including, Excel calculations, insert formulas, and formula exceptions.
  11. Select the number of decimal places for a selected cell, cell range, or report. In the example below, 1 decimal places is selected for a cell. You can select 0 to 6 decimal places.
    decimal%20place%201
  12. Select a Thousand Separator option. Display of the thousand separator is driven based on user location settings.
  13. Finally, click the Other tab and select to hide or display empty rows and columns. Don’t forget to click Apply and Save!

Resetting Report Format

Let's say you made several formatting selections, but want to return to the default formatting, click the Reset icon. The Reset icon on the top of the Format pane resets the format values to the defaults.

Tip!
Use the Ctrl+A shortcut key to select the entire report and apply Text and Data formatting. Alternatively, use the Format button available on right click menu of a cell.

Situations When Formatting is Disabled or Can't be Applied

In certain situations, formatting might become disabled or has to be reapplied. 

Running Borders and Currency Format

Running borders and data formatting is inherited from the parent member and applied to the drill down rows and columns. The images below depicts this behavior.

Running Border and Currency Format Applied

Running Border and Currency Format Also Applies to Drill Down Members

Number Scale

Number Scale is disabled when you SELECT ALL or do a CTRL+A. In the image below, a CTRL+A was performed.

Font Color When On a Mac

The drop-down for negative numbers does not apply a Red font color in the drop down (shown below) on a Mac.

Graphical user interface, application

Description automatically generated

Using Formulas in a Report

Formula Exceptions 

Click Formula and select Formula Exceptions from within the Dynamic Report interface. Use formula exceptions to override the calculations performed on report results.

  • Add formula exceptions by clicking on the Add Row… link.

  • Once a new blank row is added, enter a cell or a range of cells and the formula that the exception will apply to. The range of cells must be entered as follows: E1:E26 . Formulas must start with an = (equal) symbol.

Formulas

Use the Quick Functions menu to select functions like Sum, Average, Count, Max, and Min. The resulting amounts are displayed when you select the range of cells in Excel mode.

Decimal places are displayed in Dynamic Reports based upon whether Report Sets are used or not. When Report Sets are not used in a Dynamic Report, decimal places displayed in quick functions are identical to those in the Dynamic Report. When Report Sets are used, decimal places in quick functions allow for the maximum number of decimal places allowed for a selected range.

A column or row with the Excel Formula function can be used to write advanced or complex Excel formulas in the report.

Each cell can have a different set of formulas.

From the top menu, click Formula and use the Insert Row/Column option to add a formula row or column.

image581.png

Available Functions 

% of Row – Select a row and column intersection as the basis for percentage of all other rows on the spreadsheet. For example, select Column B, Row 3 which contains 100 dollars. And you want to insert a new column with % of Row function. A new column will be added based on the selections made on the Insert Formula page. The new column will result in 100% for Row 3 with 100 dollars. But, if Row 2 only contains 50 dollars. A percentage of 50% will be displayed.

Average – Average the amount of two rows or columns.

Comments – Add a row or column where you can add comments.

Count – Add a row or column that totals the count of columns or rows.

Max Value – Find the maximum value of a column or row.

Min Value - Find the minimum value of a column or row.

Q&A – Add a row or column where you can add questions and enter answers.

Column Total – Add a row or column that provides the summed amount for the selected row or column.

Sum – Find the sum or two rows or columns.

Variance – Find the difference between two rows or columns.

Variance % - Find the percentage of the difference between two rows or columns.

Custom Formula – Enter a row or column where you can create a custom formula to apply.

  • Name – Enter a name for the formula you are inserting.

  • Insert – Select the area of the report where the formula will be inserted.

  • Function – For example, if you want the sum the total of a column, select this function.

Grid Actions

Grid

Show/Hide Gridlines

Select this option to show or hide the gridlines in the report.

Freeze Panes

Select this option for large reports for enhanced scrolling.

Sort Ascending

Select this option to sort a report in ascending order.

Sort Descending

Select this option to sort a report in descending order.

Default Sort Order

Select this option to sort the columns on the grid based on the member sequence selected while creating the Dynamic Report.

Note:
If you apply a sort order to a column and save the Dynamic Report, the selected sort order is retained when you navigate to another page or login to the application the next time.
Note:
When you apply a sort order and save the Dynamic Report, the sort order is retained in Report Collection and Financial Package Publisher.

Pivot

Pivot a column selection to row and a row selection to column.

Show/Hide Row Headers

Displays or hide the row headers.

Show/Hide Column Headers

Displays or hide the column headers.

Resize Column(s) Width

Resize and save the column width to prevent modification.

Default Column(s) Width

Reset the column width to the original setting when the report was created.

Resize Row(s) Height

Resize and save the row height to prevent modification.

Default Row(s) Height

Reset the row height to the original setting when the report was created.

In Practice : To resize the row height or column width

  1. Select the Resize Column(s) Width or Resize Row(s) Height from the Grid list box. A pop-up window is displayed.

  1. Update the width or height.

  1. Click Ok.

Additionally, you can drag and resize the row height or column width using the mouse.

Best Practice

  • Size of the column or row is specified in pixels.

  • Size applies to the entire row or column and cannot be changed by a cell.

  • Use shortcut keys CTRL or SHIFT to select multiple rows or columns and drag to resize them or use the toolbar menu option.

  • Use double-click to auto-fit the contents.

  • Set the size to 0 to hide a row or column. Use the Default Column(s) Width or Default Row(s) Height options from the Grid list box to reset the size.

  • Maximum size allowed is 999. By default, the row size is 20. The column size varies depending on the content.

  • Select the Show Row Headers or Show Column headers option from the Grid list box to use mouse for adjusting the size.

Limitations

  • Row height adjustments are not supported for header or footer rows.

  • When you drill down on a rollup member, row height adjustments are retained with the row where it was originally applied and does not automatically move with the member on the row.

  • Size cannot be copied from one row or column to another.

Other Settings

Comments Off

Select this option to enable the Comment option. This option is visible on the right-click menu. A visual indicator is displayed if a comment already exists on a cell. When Comments Off is enabled, the Comment option and visual indicators for comments are removed from the Dynamic Report. The setting is retained when you Save, Save As or Copy a Dynamic Report.

Note:
The default is set to Comments Off in all existing and new reports

Apply/Remove POV

Select this option to apply or remove Point of View (POV). You can modify the page level settings to display specific dimension members. If you apply the Point of View (POV) setting to a dimension and save the report, the applied POV setting is retained when you navigate to another page or login to the application the next time.

When you open a shared report with POV settings defined by another user, the report is displayed with the applied POV setting.

Note:
If you are a read-only user, you can change the POV setting while running the report, but when you navigate to another page or login to the application the next time the changes will not be saved.

The Apply/Remove POV setting on the report is retained:

  • When you Save As or Copy a report.

  • In a Dimension based report and reports with Substitution Variables on the Page axis.

Rank

Rank dimension members. Select Rank to open the Rank page. For each dimension, select a top or bottom ranking, a measure and count. Ranking methods include; Top, Bottom, Top %, and Bottom %.

Disable/Enable Drill Through

Enable or disable Drill Through functionality. Drill through functionality provides users with the ability to view and analyze all financial, operational, capital, workforce, and transactional data from a single place. For more information, see the Dynamic Reports topic.

Note:
By default, Drill Through is enabled.

Show/ Hide Notes

Show Notes provides you with the ability to view template notes in a Dynamic Reports. Click Show Notes, a Notes column is displayed on the report and notes are displayed for each line in the report. Notes are always vertically bottom aligned like the other cells in the report.

Repeat Labels On/Off -

Labels in report output are for informational purposes.

To turn Repeat Labels Off - In the Dynamic Report output screen, click the More list-box. Select Repeat Labels Off.

To turn Repeat Labels On - In the Dynamic Report output screen, click the More list-box. Select Repeat Labels On.

Show/ Hide Subtotals and Show/ Hide Grand Total

You can show or hide the subtotals and grand total functionality in the Dynamic report.

Note:
The subtotals and grand total functionality are enabled in the More list box if Dimensions are mapped to the Row and Column axis, and more than one dimension is mapped in the Row axis.

The subtotals are displayed at the end of each section and the grand total is displayed at the end of the report as shown in the image below.

How to Access the Subtotals and Grand total Functionality?

  1. Click Reports and open a Dynamic Report.
  1. Click the More list box.
  1. Select Show Subtotals or Show Grand Total.

You can change the format of subtotals and grand total if the Format button is enabled in the Dynamic Report toolbar.

The following options are disabled when Show Subtotals or Show Grand Total is enabled:

  • The Formula Exceptions and Insert Rows / Columns options in the Formula list box is disabled. Only the existing Formula Exceptions on the Dynamic report are applied on the adjusted cells and included in the subtotal or grand total calculations. The existing Insert Rows / Columns are not visible when Show Subtotals or Show Grand Total is enabled in the report.

  • The Pivot, Sort Ascending, and Sort Descending options in the Format list box are disabled.

Note:
When you drill down on the report, Show Subtotals and Show Grand Total are not displayed in the Dynamic Report.
Note:
When Rank is applied in a Dynamic Report, the subtotal and grand total is calculated on the result set after applying Rank.
Note:
When properties are added to the Row or Column axis, the subtotal and grand total is calculated only for dimensions and not for properties.

Subtotals and Grand Total are retained in the Report Collection, FPP, e-mail attachment, linked reports, PDF, and Google sheet.

When you export a report to excel:

  • Grouping of subtotal is retained as shown in the image below.

  • Pivot and Macros are not supported if the subtotal option is enabled.

Output

Export to Excel

You can export a report to excel using this functionality.

Export to PDF

You can export a report to PDF using this functionality.

Print Setup

You can print a Dynamic Report and apply the required settings from the Print Setup dialog page using this functionality.

Header and footer information will repeat on each page of the PDF or print output. When you print a Dynamic Report, the Print Setup dialog page is displayed. On the Print Setup dialog page, select the Delete all extra Rows & Columns checkbox to remove extra data rows and columns from your Excel export. If the checkbox is not selected, the Row IDs in the Excel output match with the Row IDs within the Excel view of the report. The Delete all extra Rows & Columns setting is carried forward to Report Collection Excel outputs. Extra rows and columns are deleted or retained in the Report Collection based on the source Dynamic Report configuration.

The row and column header are repeated in each page of a Dynamic Report by default. On the Print Setup page, you can deselect Repeat row header on each page and Repeat column header on each page.

Note:
Dynamic Reports exported as Google Sheets always repeat the headers in the print output irrespective of the Repeat Header configuration done in the Dynamic Report Print Setup.
Note:
The option to delete extra rows and columns is not applicable for Financial Package Publisher as hidden/suppressed/extra rows/columns are not displayed in Financial Package Publisher output. Your Excel output will be different if the Delete all extra Rows & Columns option is selected for existing Dynamic Reports. Macros used for Excel output need to be re-adjusted manually.
 
Google for Work must be configured by your System Administrator in order to enable export for Google Sheets.
Note:
You can export Dynamic Reports in unprotected view. See, Exporting Dynamic Reports Output in Unprotected View.
Note:
To avoid overlapping headers with report lines when printing, the default top margin in Print Setup is set to 3.5 inches.

Print

You can print a Dynamic Report using this functionality.

Send Email

You can send a report to the selected user through e-mail.

Ctrl+M

Access the Rule dialog page, which displays the rule(s) used to generate the report.

Drillthrough in Reports

Click the drill through icon on the top menu to view the drill through report.

Note:
The Drill Through icon is enabled only if Drill Through is enabled for the Dynamic report.

The icon is visible in the toolbar across all Dynamic Reports on Financial reporting area where Drill Through is enabled in the report. Additionally, the icon is enabled only for those cells where drill through is applicable. It is disabled for header, excel, row title, Insert Formula, formula exception, and advanced formula cells.

You can view the drill through report in the following ways:

  • Click the Drill Through icon.

  • Double click the cell on the grid.

  • Drill down – hierarchical drill - expand a line on the report to see data for lower level members in the main/alternate/attribute hierarchy

  • Drill through – drill on a number into the transactional data or translational details that were loaded with Data Load Rules

In Practice: Drill Down

Time dimension data - For example, if you expand 2017, you can view data for the 4 quarters associated with 2017. If you expand each quarter you can view data for each month.

Variable data - If you expand a variable, such as @CURYR@, you can view data for the 4 quarters associated with the current year variable. You can expand the quarters to view data for each month.

Simple Rule in a Report Set

- For example, you define a line as follows:

sum({4010 - Gross Sales , 4020 - Royalties , 4030 - Indirect Sales , 4100 - Intercompany Sales - Mfg})

You can drill down to view data for 4010, 4020, 4030, and 4100.

Or, let's say you define a line as follows:

sum({@CURMTH@ , @CURMTH-1@ , @CURMTH-2@ , @CURMTH-3@ , @CURMTH-4@})

You can drill down to view data for the current month and the previous 4 months.

Note:
A rule represents data elements in the cube similar to rules created in Excel (similar to cells, rows, and columns). Report Sets are built using rules.

In Practice: Drill Through

You have Net Income on the row and Aug-17 on the column. Click the number and drill to the daily transactional or translations data. This works only if the data was loaded with Data Load Rules.

You have Net Income on the row and @CURMTH@ on the column. Click the number and you can drill to the daily transactional or translations data if loaded via Data Load Rules.

You have Net Income on the row and sum({@CURMTH@ , @CURMTH-1@ , @CURMTH-2@ , @CURMTH-3@ , @CURMTH-4@}). When you drill through, you can view the transactional or translations data for the current month and the previous 4 months.

You have ([Account].&[1068],[Department]. &[101],[Project].&[565]) on the row defined using the Multiple dimension option in Report Sets and MTD on the column. You can drill to the daily transactional or translations data.

Drill-through displays values from the beginning of the year to the current period when YTD or QTD are applied.

Values displayed in the drill-through pop-up window are applicable for all periods, from either the beginning of the current fiscal year or the period selected during report design. For example, if current month is set to September, drill-through displays the values from January until September if YTD is the Measure applied.

The columns for transactional or translation drill through data can be sorted in the pop-up window.

Note:
Drill through will work if both the row and column use simple rule definitions. If multi-dimensional rules are defined using the Advanced Rule page, the system will not automatically translate the rule to a simple rule definition, and drill through is not opened until you manually convert the advanced rules into simple rules.

To know more about the drill through capabilities, refer to Enhanced Drill Through topic.<>

How to Set Up a Substitution Variables in Dynamic Reports?

  1. Navigate to Maintenance > Reports > Cube Settings and click the Substitution Variables tab.
  1. Review the list of substitution variables that have been defined /delivered.
  1. Click Add to set up a new substitution variable on the Add Substitution Variable page.
  1. Complete the fields on the Add Substitution Variable page. Note that substitution variables are defined for a reporting area and dimension.
  1. Select the newly added substitution variable on the list page and click Derived Variables to view the corresponding derived variables.
  1. Add new derived variables by clicking Add on the Derived Variables list page.
  1. The Add Derived Variable page is opened. Complete the fields on this page.

Using Substitution Variables in a Report

Substitution variables are used in the creation of Dynamic Reports. A substitution variable replaces a complex multi-dimensional expression (Rule) with a specified value. Substitution variables must be added to Report Sets as lines to be reflected in reports. Planful provides over 40 system-defined substitution variables.

Substitution variables are available for selection on the Members pane and can be applied to the page, row, or column.

Example: A dimension is added to the page axis and the @CURBDGT@ substitution variable is selected and added to the Mapped Members pane, which means that the substitution variable is applied as a page axis member

Note:
The Members tab is not available when a Report Set is selected and variables will not be available for selection.

This feature is available in Dynamic Reports only and is not available when a Dynamic Report is used in the Financial Package Publisher.

When substitution variables are selected in the member selector for a row or column axis, the values are displayed as row or column headers in the report output. When you run the Dynamic Report, you are prompted for variable values.

Substitution variables can be added to the Pick List. All report functionality (drill through, formatting, and grid actions) is available for substitution variables values when applicable.

The purpose of using a substitution variable is to:

  • Improve report performance

  • Improve Report Set maintainability

  • Dynamically display report column/row headers

Where to Use Substitution Variables

Efficiently Replace Formula Exceptions

In Combination with Static Text

Improve Report Performance

Instead of Reporting Scenario Functionality

Improve Efficiency Building Report Sets

Overcome Rule Limitations

Efficient Way to Replace Formula Exceptions

To display the current period in the column header of a report, replace the column set’s line name with a substitution variable, such as @CURMTH@. When the report is run, @CURMTH@ will be replaced with the actual month name Mar-18. 

Combine Substitution Variables with Static Text

The year is 2019

YTD~ @CURMTH@ will display YTD~ Jan-19

@CURYR@Actual will display 2019 Actual

Improve Report Performance by Avoiding the User of Calculated or Measured Dimensions when Building Column Sets

The following Report Set is commonly used:

YTD

MTD

Actual

Budget

Variance

Actual

Budget

Variance

Setting up this Report Set requires the usage of complex Rules that typically require a column set on a Calculated or Measured dimension and utilizes the Reporting scenario for the Budget column. The following table displays commonly used formulas to deliver report columns.

Line NameFormula Without Substitution Variables

YTD~Actual

([Measures].[YTD],[Scenario].&[1],[Calculated].&[1])

YTD~Budget

([Measures].[YTD],StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]")),[Calculated].&[1])

YTD~Variance

Custom Formula

MTD~Actual

([Measures].[MTD],[Scenario].&[1] ,[Calculated].&[1])

MTD~Budget

([Measures].[MTD],StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]")),[Calculated].&[1])

MTD~Variance

Custom Formula

 While the rules above deliver the expected result, they are complex. This complexity adversely impacts report performance. The same report can be set up more efficiently using Substitution Variables. The table below displays how Substitution Variables compare to the formulas in the table above.

Line NameFormula with Substitution Variables

YTD~Actual

([Scenario].&[1] ,[Measures].[YTD])

YTD~Budget

(@CURBGT@,[Measures].[YTD])

YTD~Variance

Custom Formula

MTD~Actual

([Scenario].&[1],[Measures].[MTD])

MTD~Budget

(@CURBGT@,[Measures].[MTD])

MTD~Variance

Custom Formula

 Performance for the Report Set depicted in the table above can be improved by moving the repeated member on the Page axis. The Report Set above uses [Measures].[MTD] and [Measures].[YTD] frequently. Move one of these members to the page level. For example, use the member [Measures].[YTD] on the Page axis rather than repeating [Measures].[YTD] on the first and second lines as shown in the table below.

Line NameFormula with Substitution Variables

YTD~Actual

[Scenario].&[1]

YTD~Budget

@CURBGT@

YTD~Variance

Custom Formula

MTD~Actual

([Scenario].&[1],[Measures].[MTD])

MTD~Budget

(@CURBGT@,[Measures].[MTD])

MTD~Variance

Custom Formula

Instead of Reporting Scenario Functionality

When using reporting scenario functionality to retrieve the budget and forecast for a selected year, you might use the following rule.

StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]"))

While the rule above delivers the expected result, it slows report performance. A performance improvement is to use Substitution Variables as described below.

Line NamePrevious Approach to FormulaFormula with Substitution Variables

Current Budget

StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]"))

@CURBGT@

Current Forecast

StrToMember ("Scenario.&["+Time.Currentmember.properties("forecast_Id")+"]"))

@CURFCST@

Current Month Budget

(Time.Currentmember,StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]")),[CalculatedDim].&[1])

(@CURMTH,@CURBGT)

Prior Year YTD

(Parallelperiod(Year,1,Time.currentmember),[Measures].[YTD])

(@PREVYEAR@,[Measures].[YTD])

Improve Efficiency in Building Report Sets

Substitution Variables aide in the setup and maintenance of time-based Report Sets, such as Trailing 12 months, along with Budget and Forecast columns. The following example displays a Trailing 12-month Report Set:

Line NamePrevious Approach to RuleRule with Substitution Variables

Current Month

(Time.Currentmember,[CalculatedDim].&[1])

@CURMTH@

Current Month - 1

(Time.Currentmember.Lag(1),[CalculatedDim].&[1])

@CURMTH-1@

Current Month - 2

(Time.Currentmember.Lag(2),[CalculatedDim].&[1])

@CURMTH-2@

Current Month - 3

(Time.Currentmember.Lag(3),[CalculatedDim].&[1])

@CURMTH-3@

Current Month - 4

(Time.Currentmember.Lag(4),[CalculatedDim].&[1])

@CURMTH-4@

Current Month - 5

(Time.Currentmember.Lag(5),[CalculatedDim].&[1])

@CURMTH-5@

Current Month - 6

(Time.Currentmember.Lag(6),[CalculatedDim].&[1])

@CURMTH-6@

Current Month - 7

(Time.Currentmember.Lag(7),[CalculatedDim].&[1])

@CURMTH-7@

Current Month - 8

(Time.Currentmember.Lag(8),[CalculatedDim].&[1])

@CURMTH-8@

Current Month - 9

(Time.Currentmember.Lag(9),[CalculatedDim].&[1])

@CURMTH-9@

Current Month - 10

(Time.Currentmember.Lag(10),[CalculatedDim].&[1])

@CURMTH-10@

Current Month - 11

(Time.Currentmember.Lag(11),[CalculatedDim].&[1])

@CURMTH-11@

Current Month - 12

(Time.Currentmember.Lag(12),[CalculatedDim].&[1])

@CURMTH-12@

Current Budget

(Ancestor(Time.Currentmember,1,Year), StrToMember ("Scenario.&["+Time.Currentmember.properties("Budget_Id")+"]")),[CalculatedDim].&[1])

(@CURYR@,@CURBGT)

Current Forecast

(Ancestor(Time.Currentmember,1,Year), StrToMember ("Scenario.&["+Time.Currentmember.properties("forecast_Id")+"]")),[CalculatedDim].&[1])

(@CURYR@,@CURFCST)

Overcome Rule Limitations

You cannot use the same dimension on two axes simultaneously. For example, a report that requires a Report Set on the Time dimension, will not allow selection of Time dimension members at the Page level. To set up a trailing 12-month report, define the column set on either the Calculated or Measure dimension. Time should be on the Page axis. This setup does not use substitution variables so performance will not be optimal. With Substitution Variables, the report column set can be set up on the Time dimension directly and the required selections can be made using Substitution Variables, which will result in improved performance.

Quick Functions

Use the Quick Functions to display Sum, Average, Count, Max, and Min. The resulting amounts are displayed when you select the range of cells in Excel mode.

Decimal places are displayed in Dynamic Reports based upon whether Report Sets are used or not. When Report Sets are not used in a Dynamic Report, decimal places displayed in quick functions are identical to those in the Dynamic Report. When Report Sets are used, decimal places in quick functions allow for the maximum number of decimal places allowed for a selected range.


Was this article helpful?