MicroStrategy ONE
Hierarchies on MDX Cube Reports
You can include hierarchies of an MDX cube on the templates of MDX cube reports. Templates define the columns of data and data formatting displayed on reports and MDX cube reports. Hierarchies can be added to report templates using the same standard techniques to add attributes to a report template. At report run-time, any hierarchies included on an MDX cube report display the attributes that are part of that hierarchy.
When an MDX cube report includes a hierarchy, the attributes that are displayed for that hierarchy are determined by two factors:
- The default number of attributes to display for a hierarchy. This is defined for a hierarchy when a project designer maps data from MDX cube sources to an MDX cube. For a detailed explanation of how to define the default number of attributes to display for a hierarchy, see Displaying Hierarchies on MDX Cube Reports.
-
The attribute level defined in the report filter of the report. You can include an attribute in a report filter that is at a lower level than is set for its hierarchy. For example, you have a Geography hierarchy with Country, Region, and City attribute levels.
The lower attribute level of the hierarchy default and the report filter is used as the attribute level displayed on the report. In this hierarchy example, the hierarchy is defined to display one attribute on reports. If the Geography hierarchy is included on a report with no report filter qualifications on attributes of the Geography hierarchy, only Country is displayed for the hierarchy on the report. However, a report that includes a report filter qualification on City requires all attributes down to the level of the report filter qualification to be displayed for the hierarchy. (A qualification is the condition in a filter that limits the data to be included on a report.)
The two example reports shown below illustrate the other scenario in which the Geography hierarchy has been defined to display all of its attributes on a report with a report filter at a higher attribute level. Even though the second report has a report filter on the Region attribute, all of the Geography attributes are displayed because the hierarchy is defined to display all attributes down to City.
When using hierarchies on MDX cube reports:
- Only the default report display forms for each attribute are shown when attributes are displayed as part of a hierarchy on a report. To modify the attribute forms that are displayed for each attribute when displayed as part of a hierarchy, you must modify the report display forms for the attributes using the Attribute Editor. For details on defining report display forms, see the Project Design Help.
- Attributes displayed as part of a hierarchy on a report act as one unit on the grid or graph. This means that all attributes displayed as a hierarchy are pivoted or moved together. However, you can sort each attribute of a hierarchy individually by using the advanced sorting options for a report.
- Hierarchies must display attributes sequentially from the highest level attribute down to the lowest level defined for the hierarchy. This means that to see the lowest level attribute for a hierarchy displayed on the report, the hierarchy must display every attribute for the hierarchy. To see lower level attributes in the hierarchy without displaying the higher level attributes, add the attributes to the report one-by-one rather than including the hierarchy on the report.
- In SAP BW systems, dimensions can include multiple hierarchies for data display purposes. In MicroStrategy, only one hierarchy per dimension can be represented on an MDX cube report.
- You can drill on hierarchies included on your MDX cube reports using most of the standard techniques available for drilling on attributes on reports. For more information and best practices on drilling on hierarchies, see Drilling on MDX Cube Reports.
SAP BW dimensions with multiple hierarchies on reports
When data from an SAP BW MDX cube source is mapped to MicroStrategy objects, dimensions in SAP BW are mapped to a hierarchy object in MicroStrategy. However, some SAP BW dimensions are mapped to multiple hierarchy objects in MicroStrategy. This occurs because an SAP BW dimension can be broken up into separate MicroStrategy hierarchies for the purposes of formatting and structuring the display of data. For example, in the image shown below, the Sub Category dimension is broken up into four distinct MicroStrategy hierarchies; each hierarchy structures the same data in different ways.
When including hierarchies or attributes from these hierarchies on MicroStrategy reports, you are restricted to including only one hierarchy per dimension. This means that once you add a hierarchy itself or attributes from a hierarchy on a report, all other hierarchies and their related attributes within the same dimension cannot be added to the report or report filter. This restriction also applies to prompts that contain objects from a different hierarchy within a dimension already included on the report. However, you can include other attributes, hierarchies, and prompts on a report or report filter as long as the hierarchies associated with these objects are not from the same dimension of the MDX cube.
For example, you have an SAP BW MDX cube with a Geography hierarchy within the Call Center dimension and a Products hierarchy within the Sub Category dimension.
You include the Region attribute from the Geography hierarchy and the Category attribute from the Products hierarchy on the report.
In the report below that once you include the Category attribute from the Products hierarchy, the rest of the hierarchies within the same dimension are made unavailable. This is because for each dimension you can only include a single hierarchy on a MicroStrategy report's template and filter. The report below also shows that attributes (in this example, Region and Category) from different hierarchies can be included on the same report as long as the hierarchies are in different dimensions.
You could also include the Geography and Products hierarchy objects on the same report together rather than choosing individual attributes. This is possible because the hierarchies are from different dimensions.