Initialization Queries Used in Variables to Override Selection Steps

For analyses that contain hierarchical columns, global variables or session variables can override selection steps.

Global and session variables intended for this purpose must use valid JSON syntax.

Using JSON, you must define type, column, and members with the following syntax.

{
  "type": "Hierarchy",
  "column": {
    "subject_area":"your_subject_area",
    "hier_id":"your_hier_id",
    "dim_id":"your_dim_id",
    "table_name":"your_table_name"
  },
  "members": [
    {
      "level_id":"your_level_id",
      "values": [
        your_value,
        your_value
      ]
    },
    {
      "level_id":"your_level_id",
      "values": [
        your_value
      ]
    }
  ]
}

Where:

"type" indicates hierarchy type.

"column" indicates the hierarchy column's information such as subject area and table name.

"dim_id" is the logical hierarchy name.

"members" indicates which hierarchy level and which member ID.

"level_id" is the presentation level name.

Example of Standard Hierarchy Syntax

{
  "type": "Hierarchy",
  "column": {
    "subject_area": "A - Sample Sales",
    "hier_id": "H2 Offices",
    "dim_id": "H3 Offices",
    "table_name": "Offices"
  },
  "members": [
    {
      "level_id": "Company",
      "values": [
        10001,
        10002
      ]
    },
    {
      "level_id": "Organization",
      "values": [
        1005
      ]
    }
  ]
}

Example of Parent-Child Hierarchy Syntax

{
  "type":"Hierarchy",
  "column":{
    "subject_area":"A - Sample Sales",
    "hier_id":"Sales Rep Hierarchy",
    "dim_id":"H5 Sales Rep",
    "table_name":"Sales Person"
  },
  "members":[
    {
      "level_id":"Grand Total",
      "values":[
        27,
        24,
        18,
        16
      ]
    }
  ]
}