canvasplanning:userguide:reporttype

Row/Col Type

Several Row/Col Type selections have the ability to be modified using other options such as Sorting and Ranking, Add Totals and Suppress details, as well as the Filter options. The options that allow for further enhancement are:

  • Expand – Dynamically expand report members based on a dimension hierarchy.
  • List – Dynamically expand report members based on a fixed or dynamic list of dimension members.
  • Member – Display one report member based for one member in a dimension.
  • Range Name – Dynamically expand report members based on a list of dimension members contained in a named range.
  • Property – Display one report member containing member property values.
  • Heading – Display member descriptions.
  • Blank – Display an empty report member.


Dynamically expand report members based on a dimension hierarchy.


  • Number of levels to expand – The number of levels down a hierarchy to expand. To show immediate dependents of a member set this to one. This can also define over number of level down reporting. If this entry is empty, all descendants of the current member are included in the expansion
  • Suppress if zero – Use to exclude columns or rows from an expansion when the entire column or row contains zeros.
  • Exclude current member – Use to exclude the current member from an expansion. Normally the current member is included in an expansion.
  • Include Parent level members – Select this option to include non-leaf members in the expansion. A non-leaf member is any member with no lower level children or any member at the lowest level of an expansion.
  • Only include Base level member – Select this option to only include leaf level members in the expansion. A leaf level member is any member with no lower level children or any member at the lowest level of an expansion.
  • Display location for parents – Use this option to display parents before or after children.
    • Before children
    • After children
  • User add suppressed items – For input schedules this allows a user to select suppressed rows for first time entry of data. This option is only available on Rows when the Input Schedule option is checked. When this option is selected an additional row is added to the expansion with the heading “<double click to add suppressed items>”. When a user double clicks on any report column within the row, a selection dialog box opens for the user to select any suppressed members they want to enter data for. The selected members will be temporarily added to the bottom of the expansion, just about the selection row containing the double click heading. When nested expansions are defined this option can only be set on the inner most expansion, but each section of the expansion will include a selection row with the double click heading, so that a user can add suppressed rows to any section of the nested expansion.


Enter a list of member names. The member names must be valid names in the selected dimension. Any member name that is not valid will be skipped and will not be included as a column or row in the report. Member names can be specified as a short name, qualified name, or application variable substitution.


A list of member names in the item dimension. The list can be any combination of Fixed and Dynamic names. Application Variables combined with Dynamic names provide powerful capabilities for things such as rolling Forecasts, reporting from the beginning of the year through a current period, etc.

(Caution: If the same member name occurs in more than one hierarchy, the specific member selected may be on any hierarchy. To insure a specific entity in a specific hierarchy is retrieved use the fully qualified form of the name “[Dimension].[Hierarchy].[MemberName]”. A partially qualified form is “[Dimension].[MemberName]” is also supported, however there is no assurance which hierarchy the member may be retrieved from.)

  • Fixed names – A list of member names typed in or selected by the user.
  • Dynamic names - A dynamic range of members specifying one member to another member with a syntax of [start_member]:[end_member] where [start_member] and [end_member] are resolved to member names separated by a colon.. The beginning member or ending member of a dynamic list can be excluded by adding a ~ to the beginning of the list member. (Note: Both members must be on the same Level of the same Hierarchy. Otherwise the list will only return the two members used in the definition.)
    • [member1]:[member2] – Will dynamically produce a list of members starting with member1 and ending with member2. By default member1 and member2 are included in the list of members.
    • [member1]:~[member2] – All members from member1 to member2, but not including member2
    • ~[member1]:[member2] – All members from member1 to member2, but not including member1
    • ~[member1]:~[member2] – All members from member1 to member2, but not including member1 or member2.
  • Dynamic member names - List members can use Application Variables in the definition. This allows dynamic reports that automatically respond to values such as a current period, that is maintained by the Administrator. (Note: You must get the list of available Application Variables from your Administrator.) The Application Variables are evaluated and then the values of the Application Variables make up the List definition. See the Application Variables section for more info and examples.


Enter a dimension map and member names for each dimension.
Each row in the Multi-dimension Member List generates one row/column in the report with the data for the combination of all dimension members specified.
The member names must be valid names in the appropriate dimension by column. Any member name that is not valid will be skipped and a member for the appropriate dimension will be selected by the normal overrides of row/column slicer, report slicer and current view.

  • Dimension Map
  • Multi-dimension Member List

Below example using dimensions Account, Entity & Scenario :

  • Set dimensions in Dimension Map.
  • Green highlighted button is for adding more rows.
  • Make sure you put the dimension you want to show in the report column as last dimension, in this example, it's account dimension.

Result in the report:


Define a report member consisting of one dimension member. Select the dimension the member is from. Fix to a specific member in the Slicer.


Enter the name of the range to retrieve the list of member names from. Blank cells will be included in the member list without any error. (Note: The first cell and the last cell in the range will just be skipped when they are blank and not included in the member list for the expansion. This allows for easy dynamic creation of ranges by other reports or macros without breaking defined range definitions.)

Example of Range Name


Enter the name of the range to retrieve the list of member names from, and the dimension map. For a range used in a row definition each column of the range must contain member names from the appropriate dimension in the map. For a range used in a column definition each row of the range must contain member names from the appropriate dimension in the map. For any invalid member names or blank cells, the member for the appropriate dimension will be selected by the normal overrides of row/column slicer, report slicer, and current view. Each row (or column) in the range will generate a row (or column) in the report.

  • Range Name
  • Dimension Map

Example:

  1. setup a range (account & entity)
  2. use multi-dimension range name
  3. setup Dimension Map according to the range


Define a report member which is calculated from other rows or columns with a formula.


Defines a calculated column or row in a report.

  • Heading – Text to be displayed as the heading for this item.
  • Formula – A formula to calculate the numeric values for this item. A formula consists of a mathematical expression using the normal math operators + - * / as well as ( … ) to control the order of operations and [n] to refer to other rows or columns. A formula defined on a row may only refer to other rows. A formula defined on a column may only refer to other columns. A formula may refer to other value items of Expand, Member, List, Multi-Dimension List, Range, Multi-Dimension Range, Property, and other Formula nodes. When the node referred to generates multiple report rows (or columns), the value returned is the sum of all the report rows (or columns) generated for the item. When the node referred to includes the Add Totals option, the totals generated are not included in the value. If a Property item does not contain a number, a zero is retrieved. Row and column items are referred to by item number in the report definition. (Caution: A formula may NOT refer to itself. If a formula item refers to itself the result of the calculation is undefined.) Use the Lookup button to select row or column items. Following are some examples:
    • [1] – [2] will calculate a difference between columns (or rows) 1 and 2
    • [2] * 50 + [3] will multiple the value in column (or row) 2 by 50 and add the value in column (or row) 3
    • ( [1] + [2] ) * 50 will add the value in column (or row) 1 to the value in column (or row) 2 and then multiply the result by 50


  • Display property values only on Heading row/column – Property is only displayed on the Heading row or column. This is very useful with nested expansions to include a special heading at each break point.
  • Display property values on all rows/columns – Property values will be displayed in every row or column, including blanks
  • Offset property values display by a number of rows/columns – This is useful in dynamic expansions to provide a way to keep values referenced by formulas in the same row/column to avoid errors that might occur in formulas referencing other rows/columns within an expansion range as dynamic expansions or contractions take place.


Define member heading as the report member. The heading will be the description of the dimension member on the cross axis. For instance, a heading defined as a row will display member descriptions for the column members, and a heading defined as a column will display member descriptions for the row members.


Include a blank column or row in the report. This is good for spacing in a report.

  • Cell Format Options
  • Other Options


  • canvasplanning/userguide/reporttype.txt
  • Last modified: 2020/05/12 10:08
  • by pcevli