canvasplanning:userguide:reportfeatures

Report Features



  • Auto indent items based on indent counts for cross axis items – Use to turn automatic indentations on Heading or Property columns or rows.
  • Indent per hierarchy level of item – The number of space characters to indent for each member level within an Expand node, so that children will be indented more than parents. Enter a negative number for parent members to be indented more than children. When using nested row or column definitions, only the lowest level definition found will be used. Multiple Indent per level definitions are not added together.
  • Fixed indent count for items – The number of characters to indent heading or properties for Expand, List, Range Name, or Member nodes. Only positive numbers can be entered. When using nested row or column definitions, only the lowest level definition found will be used. Multiple Fixed indent definitions are not added together.


  • Add totals – Select this option to include a row/column at the end of the section containing the total of all numbers within the section. The default heading for the totals is comma separated list of the detail headings. No properties are displayed for totals. (Note: Totals defined in nested nodes are not included in the totals so totals do not get double counted.)
  • Suppress details – Select this option to suppress details and only display totals.
  • Heading


A time period offset to be used for this report member. This is used to define things like a column that is one year or one month prior to the column next to it.

  • Number – positive or negative number of periods to offset
  • OPE – offset to the opening period. For example the opening period based on the Year level is the last period of the prior year.
  • BEG – offset to the beginning period.
  • END – offset to the ending period.
  • Period, Year, Quarter, Month


Use to Sort and Rank rows or columns of Expand and List report nodes. These options are only available to the lowest node in nested nodes.

  • Sort options
    • <None> - No sorting or ranking
    • Ascending – Values sorted in ascending order
    • Descending – Values sorted in descending order
  • Sort Row/Col – Select the row or column to sort on. When the selected row or column is a List, Range Name, or Expand with Parent before children, the sort takes place based on values in the first row or column of the selection. When the selected row or column is an Expand with Parent after children, the sort takes place based on values in the last row or column of the selection.
  • Rank options
    • Count – Enter count as a positive number
    • Options
      • Top Count – Only include the top number of rows or columns
      • Top Percent – Only include the top rows or columns that sum up to the entered percentage of the total
      • Bottom Count – Only include the bottom number of rows or columns
      • Bottom Percent – Only include the bottom number of rows or columns that sum up to the entered percentage of the total
      • Significant Count – Display the first number of rows or columns based the sorted results of the absolute values.
      • Significant Percent – Display the first number of rows or columns based the count entered as a percentage of the sum of all absolute values.
      • Standard Deviation – Calculates the mean and the standard deviation of all values, and displays only those rows or columns that exceed the number of standard deviations entered from the mean of all values.


  • Clear All – Click this button to clear all filter settings.
  • Property name – Select a property from the drop down list.
  • LIKE / NOT LIKE – Select LIKE to include all members with property Like Value. Select NOT LIKE to include all members with property Not Like Value.
  • Value – The Value to determine members to include. The Value can end with a “*” for a wild card comparison.
  • AND / OR – Select AND if member must meet multiple conditions. Select OR if member can meet either condition.
  • Range override – Property filters can be controlled by a range within the workbook. To override filter settings with dynamic values specify a range name containing values for a Property Filter. This range must contain 4 columns named as follows. Each row in the range is one filter.
    • AndOr – Valid entries are blank, And, Or. This entry should be blank for first row.
    • Property – Valid property name for the node dimension
    • Like – Valid entries are Like, NotLike
    • Value – Filter value for the property


Use format options to format headings and data in reports. Formats for individual cells in a report are resolved by merging multiple format settings. The merge order is as follows:

  1. If “Use global formats to initialize all report formats” is checked in Report Options merge Global format options.
  2. Only on rows merge Report Options cell format.
  3. Merge Row cell format.
  4. Merge Row parent cell format for parent cells in Expand nodes on rows.
  5. Merge Column cell format.
  6. Merge Column parent cell format for parent cells in Expand nodes on columns.

Formats are defined with the following settings:

  • Reset all format options – Click this button to reset all format settings to default values.
  • Align – Select Left, Center, or Right to set cell alignment. Select Default to leave alignment as set in Excel.
  • Number scale – Select None, Thousands, or Millions. Select Default to not specify any scaling. None is handy as an override value. Scaling is selectively applied to numbers based on “NoScale” property for the Account type dimension. If an Account member has a NoScale property value of False, then the scale setting is not applied to values for that member.
  • Number format - Select one of the following:
    • Default – Select this to leave number format as set in Excel.
    • #,###
    • #,##0
    • #,##0.00
    • #,##0.000
    • #,##0.0000
    • #0%
    • #.00%
    • $ #,##0.00
  • Text color – Select Default to leave as set in Excel. Select from Black, Blue, Green, Purple, Red, Dark Red, Gray, Light Gray, or Yellow.
  • Background color – Select Default to leave as set in Excel. Select from White, Yellow, Green, Aqua, Blue, Purple, Brown, Red, Dark Red, or Gray.
  • Font settings
    • Font family – Select Default to leave as set in Excel. Select from Georgia, Times New Roman, Arial, Comic Sans MS, Lucina Sans Unicode, Verdana, or Courier New.
    • Font size – Select Default to leave as set in Excel. Select Small, Medium, or Large.
    • Font weight – Select Default to leave as set in Excel. Select Normal or Bold.
  • Cell border settings
    • Overline style – Select Default to leave as set in Excel. Select from None, Solid, Dotted, Dashed, or Double.
    • Underline style – Select Default to leave as set in Excel. Select from None, Solid, Dotted, Dashed, or Double.
    • Outline style – Select Default to leave as set in Excel. Select from None, Solid, Dotted, Dashed, or Double.
    • Line thickness – Select Default to leave as set in Excel. Select a number from 1 – 5.
    • Line color – Select Default to leave as set in Excel. Select from Black, Blue, Green, Purple, Red, Dark Red, Gray, Light Gray, or Yellow.
  • Visibility – Select to leave as set in Excel. Select from Show or Hide.


The data retrieved for a report item can be controlled by selecting the specific member on any dimension with the slicer. The data retrieved for each cell in the report is controlled by a fully qualified slicer for that cell. The slicer for each cell is determined by first taking the Current View setting, then applying the Report Slicer settings, and finally applying the row and column slicer settings. The Slicer dialog displays a list of dimensions and the currently selected member for each dimension. (Note: For the Report Slicer, the first entry in theDimension list shows Model and allows selecting a specific Model the report will run for. When the Model is changed, the list of dimensions for all Slicers of the report also changes to only show the dimensions available within the selected Model.)


  • Drill report name – Used to enable drill down reporting when the user double clicks on a cell in the column or row defined by this report member to either another report within Excel or to a web page in the browser. This entry can include Application Variables. See the section on Application Variables for more info and examples.
    • Excel report – To drill to an Excel report with additional detail, enter the name of a report in the current workbook. It is valid to enter the name of the same report for a drill down in place, however this should never be done on the initial drill. The first report should never be the destination of a drill so that it can remain unlocked and provide the user an easy way to return to their starting point. When a user double clicks on report member with a drill report defined, the report is selected and refreshed based on the CV of the cell clicked. The CV for the drill report will remain locked until the user drills to it again.
    • Web page – To drill to a Web page with additional detail, enter a URL that starts with “http:” or “https:”. The URL can include dynamic values based on the CV of the cell clicked allowing the web page to present a report tailored to exactly where the request originated within the report. To include dynamic member properties in a URL use the syntax of propertyname. Application Variables can be used to make it simple for users to define complex web page urls, and to keep reports portable between multiple environments such as production and development.
  • Excel range to override report options for this item – Report row / column items can be controlled by a range within the workbook. This allows for very sophisticated and very fine grained interactive customization of report definitions with users never knowing anything about report definitions. There is an entry in the report editor for Report Options range name. This range must contain 2 columns with column 1 contain the option name and column 2 containing the value for the option. The following option names are supported: .
    • Dimension – Name of the dimension
    • Property – Name of the property to display for a Property node
    • ExpandLevels – Number of levels to expand
    • SuppressIfZero – 0 = no suppress, 1 = suppress if zero
    • ExcludeCurrentMember – 0 = include current member, 1 = exclude current member
    • OnlyBaseMembers – 0 = include all members, 1 = only include base level members
    • ParentsAfterChildren – 0 = parents before children, 1 = parents after children
    • AutoIndent – 0 = no auto indent, 1 = auto indent on heading or property nodes
    • IndentPerLevel – A number of indent characters for each member level
    • FixedIndent – A fixed number of character
    • AddTotals – 0 = no Add totals, 1 = Add totals
    • SuppressDetails – 0 = no Suppress details, 1 = Suppress details
    • PropertyDisplayForAll – 0 = no Property Display For All, 1 = Property Display For All
    • PropertyDisplayAsHeading – 0 = no Property Display As Heading, 1 = Property Display As Heading
    • PropertyRowColOffset – A number for the Property Row or Column Offset
    • TimeOffset – A value to offset the time value. A number, OPE, BEG, or END.
    • TimeOffsetLevel – Valid entries are blank, Period, Year, Quarter, Month
    • DrillReport – Name of the Drill Report
    • SortOption – 0 = None, 1 = Ascending, 2 = Descending
    • SortItem – Index of the row or column definition to sort on
    • RankCount – A positive number
    • RankOption – 0 = None, 1 = Top Count, 2 = Top Percent, 3 = Bottom Count, 4 = Bottom Percent
  • Property Filters – Property filters can be controlled by a range within the workbook. At the bottom the property filter page, use the Range override field to specify a range name containing values for a Property Filter. This range must contain 4 columns as follows. Each row in the range is one filter.
    • AndOr – Valid entries are blank, And, Or. This entry should be blank for first row.
    • Property – Valid property name for the node dimension
    • Like – Valid entries are Like, NotLike
    • Value – Filter value for the property


  • canvasplanning/userguide/reportfeatures.txt
  • Last modified: 2020/05/12 10:09
  • by pcevli