Query (MDX) report

You can configure an embedded worksheet based on a Query (MDX) (MultiDimensional Expression) report. The embedded SCP grid control, with predefined layouts is used to display the reports on the worksheets, and is also used to define the items; overriding the item data context setting (for the module).

To configure embedded worksheets:

  1. Select Configuration > Common > Worksheet.
  2. Click New.
  3. Select Standard Worksheet. Specify this information:
    Name
    Specify the name of the worksheet.
    Display Name
    Specify the name to be displayed in the application. This is an optional field.
    Type
    The type of the worksheet. This value is defaulted based on the type of worksheet selected.
    ReportSource
    The source of the report based on which the worksheet is configured. Select Embedded.
    Report
    Select Query (MDX).
    System
    If this option is set to On, the worksheet is system-defined.
    Enabled
    If this option is set to On, you can view the worksheet in the specified module.
    Available Offline
    This option is disabled when the report is set to Query (MDX).
    Module
    Select the cycle or cycle and module in which the worksheet can be viewed.
    Workbook
    Select a workbook from the selected module or specify a new workbook. The workbooks are used to group the worksheets defined for the selected module.
    Tags
    Select the tags for the worksheets. These are used to group common configuration elements such as calculation rules or measures.
    Users
    Specify the users who can access the worksheet.
    Note: By default, the current user is selected.
    Roles
    Specify the list of roles authorized to access the worksheet.
    Note: The value of the Users field is defaulted to the current user, if no users or roles are selected.
  4. On the Data Parameters tab, specify this information:
    Parameter
    Select the parameters that must be set for the report. The parameters applicable for the Query (MDX) report:
    Field Name Mandatory Description
    rv_mdx_cols On Columns Yes Used to select and define the layout for the columns applicable to the SELECT statement (0 axis) for MDX (Multidimensional Expression).
    rv_mdx_rows On Rows Yes Used to select and define the layout for the columns applicable to the SELECT statement (1 axis) for MDX.
    rv_column_width Column Widths No The width of columns (in pixels) for the report. Specified in the format: data_cell_width[:left_dim1_width:left_dim2_width:…:left_dimN_width] (left1, left2 dim only initially). For example, 80:90:180 represents 80px for data columns, 90 for left1 dimension, 180 for left2 dimension. A value of 90 is 90px for data columns, and the default width for the dimension columns.
    rv_mdx_with With No The WITH clause of the MDX statement is used to build member sets that can be used in the MDX SELECT statement.
    rv_measure_calc Measures Edit Calculations No An XML table of rules, to edit the calculated measures in the worksheet. Each measure calculation exists as a row and the name of the measure code is specified. Each property is defined as a name and value pair.

    Properties:

    • targetmeasure: The measure used to receive the edited value.
    • weightmeasure_multiply: The measure used to multiply the edited difference (between the original and the new value).
    • weightmeasure_divide: the measure used to divide the edited difference (between the old and the new value).
    • measure_spread: The measure used to spread the weighted difference.
    • spread_type: Either 'delta' or 'value'. Indicates the spreading method to be used with the measure_spread; wherein 'value' is the new value and 'delta' is the edited difference between the existing value and the new value. Applicable when the previous value is not null otherwise, the behavior of the Delta and Value is the same, when existing value is 0 or #NA. Uses 'delta' if not specified. If not defined, you can edit the measure values; based on the calculated cells. The edits are rejected by OLAP.
    rv_measure_editable Editable Measures No The list of editable measures in the worksheet. A single element [element1] or a set in the form: {[element1],[element2]} is required. If not set, by default, all measures can be edited. To ensure that all measures in the worksheet are read-only, an empty set {} is passed.
    rv_measure_format Measures Format No An XML table of the format and color coding for the measures, defined in the worksheet. Each measure format exists as a row and the name of the measure code is specified. Each property is defined as a name and value pair.

    Properties:

    • format: The format for the measure values. The valid format values are "0", "0.00", "0%", "0.00%", "Date","Datetime", "KPI".
    • highamber: Sets the background to amber when the scenario values >= the value for the corresponding measure.
    • highred: Sets the background to red when the scenario values >= the value for the corresponding measure.
    • lowamber: Sets the background to amber when the scenario values <= the value for the corresponding measure.
    • lowred: Sets the background to red when the scenario values <= the value for the corresponding measure.
    • highlightgreen: If TRUE, sets the background to green when scenario values are within the amber and red conditions, otherwise displays the normal (white) background color. Applicable where the measure has at least one highamber, or highred, or lowamber, or lowred defined.
    • size: Used when format = KPI, allows the user to set the KPI indicators to a certain size. Valid size values are 'equal' or 'proportional'.
    • type: Used in conjunction with size, if the type is set to equal, the same can be set to either 'blob' or 'icon', if the type is set to proportional, the same can only be set to 'blob'.
    • total: If TRUE, sets the values to bold. If measure is not defined on the pivot, this condition is ignored.
    • insertblank: If TRUE, a blank row (if measure is on Left Dim) or blank column (if measure is on Top Dim) is inserted, in the worksheet after the selected measure. If the measure is not defined on the pivot, the same is ignored
    rv_measure_spreadlike Measures Spread Like No The measure used by default, when spreading the new values (cells = 0) to the base level elements. A single element [element1] is required. If no value is passed, the default period spread measure is used.
    rv_period_lock Periods Edit Lock No The list of periods that are locked for editing. The ascendants or descendants of the periods are also locked. This is a subset of the rv_period_passed parameter. A single element [element1] or a set in the form: {[element1],[element2]} is required. Supports the MDX definition for the set. For example, rv_period_lock = [FY 14 Qtr1] locks the Ascendants: FY 2014 and "Monthly Calendar" (calendar name / aggregate). Descendants: FY14 M01, FY14 M02, FY14 M03. If not defined, all the elements in the rv_period_passed parameter can be edited.
    Value
    Specify the data that must be included in the report for the selected parameter.
    Enabled
    If this check box is selected, the parameter is used in the worksheet.
  5. Click Submit and Back to List.