Filtered Detail

You can configure an embedded worksheet based on a Filtered Detail report. The embedded SCP grid control, with predefined layouts is used to display the reports, on the worksheets, which are also used to define the items, overriding the item data context setting (for the module).

To configure embedded worksheets:

  1. Select Configuration > Common > Worksheets.
  2. Click New and select Standard Worksheet.
  3. 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
    The type of the report. Select Filtered Detail.
    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
    If this option is set to On, you can access the worksheet offline, using the Infor Excel Add-in.
    Note: This option is enabled only if you set the Report Source to Embedded.
    Module
    Select the cycle or cycle and module in which the worksheet can be viewed.
    Workbook
    Select a workbook from the required module or specify a new workbook. The workbooks are used to group the worksheets defined for the 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 specified.
    Roles
    Specify the list of roles authorized to access the worksheet.
    Note: If users or roles are not selected, by default, the current user is authorized to access the worksheet.
  4. In the Data Parameters tab, specify this information:
    Parameter
    Select the parameters for the report. The parameters applicable for the Filtered Detail report:
    Field Name Mandatory Description
    rv_filter_measure Filter Measure Yes Used to filter the report using the items and the locations.
    rv_filter_condition Filter Operator Yes Used to filter the report using the condition operators >, <, =, >=, <=.
    rv_filter_value Filter Value Yes Used to filter items and locations. For example, [ISSUE_COUNT] > 0 returns the item and location combinations in the context where the ISSUE_COUNT measure is > 0 for the pass period.
    rv_measure_passed Measures Yes Used to display the list of measures in the worksheet. A single element [element1] or a set in the form: {[element1],[element2]} must be specified.
    rv_item_passed Items Passed No A single element [element1] or a set in the form: {[element1],[element2]} must be defined. Overrides the main item data context (for the module) for this worksheet.
    rv_location_passed Location Passed No A single element [element1] or a set in the form: {[element1],[element2]} must be defined. Overrides the main location data context (for the module) for this worksheet.
    rv_period_passed Period Passed No A single element [element1] or a set in the form: {[element1],[element2]} must be defined. Overrides the main period data context (for the module) for this worksheet.
    rv_period_drilldown Periods Show Children True Indicates if both the passed period element and the children are displayed in the worksheet when uploaded. The period selection is a single element or a set, and the period dimension exists for the pivot (not the slicers). For example, Passing [FY 2014] displays the period FY 2014 and the child level quarters: FY14 Qtr1, FY14 Qtr2, FY14 Qtr3, FY14 Qtr4 on the pivot. Default = True. When False, only the selected passed period is displayed. When a set of elements is passed for the period selection, the elements are displayed on the pivot, irrespective of the parameter value. When the period dimension is within the slicer (extension of a pivot table) area, only the passed element or default member in the selection is displayed, irrespective of the parameter value.
    rv_sort_options Sort Options No An XML table is used to define the process to sort the dimensions in an ascending or descending order. Each dimension exists as a row and the name of the dimension is specified. Each property is defined as a name and value pair. The Measures dimension is not applicable in the Sort options.

    Properties:

    • sorton: If the dimension must be sorted (TRUE) or not (FALSE).
    • hierarchized: If the dimension must be displayed in a hierarchy, so that each child level is displayed with an indent (TRUE) or flat (FALSE). You can expand or collapse the child elements of an aggregated element.
    • sorttype: If the dimension must be sorted in an ascending (ASC) or descending (DESC) order. Applicable only where sorton=TRUE. If not defined, the default sort option for the dimensions is used.
    rv_period_lock Periods Edit Lock No The list of periods that are locked. 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.
    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_spreadlike Measures Spread Like No 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_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_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 in the amber and red conditions, otherwise displays the normal (white) background color. Applicable when 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_column_width Column Widths No Specifies the width of columns (in pixels) for the filtered 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 the data columns, 90 for the left1 dimension, and 180 for the left2 dimension. A value of 90 is 90px for the data columns and the default width for any dimension columns.
    Description
    Specify a description.
    Value
    Specify the data value 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.