DataLayer.MDX
The DataLayer.MDX element allows specification of an OLAP MDX query command that returns a cell set and populates an OLAP Table or OLAP Grid element.
- About DataLayer.MDX
- Attributes
- Enter an MDX Query
- Use MDX Query Elements
For additional information see Logi XOLAP.
This element is not available for Logi Java applications.
About DataLayer.MDX
The DataLayer.MDX element is used as a child of either an OLAP Table or an OLAP Grid element.
When used with an OLAP Table only, the text of an MDX query can be entered directly into the DataLayer.MDX element's MDX Source attribute, or generated using the special MDX Query Builder tool, which can be accessed by clicking the browse button at the end of the MDX Source attribute. This tool allows developers to create a correctly-formatted MDX query using a graphical interface, similar to the SQL Query Builder found in DataLayer.SQL.
An alternative approach, when DataLayer.MDX is used with an OLAP Table or OLAP Grid, is to skip entering the query text directly and instead add an MDX Query child element. You can use it and its child elements to specify the dimensions, measures, and filters needed and the Logi Engine will generate a correct MDX query at runtime based on them.
They appear on the top and left axes. The order in which dimensions are defined for an axis (the element order) determines the order in which they are displayed. For example, if a dimension showing "customer city" is provided first, followed by a dimension showing "customer marital status", the user will be able to drill down to a particular city and then drill down on marital status to see measures for each marital status in that city.
Attributes
The DataLayer.MDX element has the following attributes:
| Attribute | Description |
|---|---|
Connection ID | (Required) Specifies a connection to a data source, which has been defined in the _Settings definition. |
| ID | (Required) A unique element name. |
| Handle Quotes Inside Tokens | Enables special token processing, allowing use of token values that contain single quotes. When set to True, the single quotes in the values of any tokens in the MDX Source attribute will be "doubled" so that they work within a SQL statement. The default for value is False. |
| MDX Source | Specifies an optional developer-coded MDX command to be sent to the OLAP server. Only applicable when used with an OLAP Table parent element; OLAP Grids must use the MDX Query element instead. The Browse button for this attribute can be used to launch the MDX Query Builder tool. |
Enter an MDX Query
When working with an OLAP Table element, you can enter an MDX query directly into the DataLayer.MDX element's MDX Source attribute. This query is executed at runtime against the datasource associated with the element's Connection ID attribute.
The example above shows a query in the MDX Source attribute value. As you can see, this type of query is relatively complex and prone to typos during entry. In order to help you formulate a correct query, instead of typing in the query you can use the MDX Query Builder tool, which is accessed by clicking the Browse button at the end of the MDX Source attribute value.
Based on the selected (1) Cube, the MDX Query Builder presents lists of (2) Dimensions and Members, and (3) Measures. These can be moved to an axis or added and removed by selecting and right-clicking them, as shown above. The (4) results of these actions can be seen in the Results panel, in tabular or (5) source code form.
Clicking (6) OK will cause the query constructed in the Results panel to be copied into the datalayer's MDX Source attribute, if the MDX Query Builder was called by clicking the attribute's browse button. Otherwise, the query text can be selected, copied, and pasted elsewhere as needed.
When a Query Was Entered Manually
Note that if you have already entered a query manually in the MDX Source attribute and you then open it in the MDX Query Builder, the tool will try to "reverse-engineer" the query. It succeeds at this most of the time but, if the query includes tokens or is created using a non-standard SQL language, the MDX Query Builder may fail and display an error message. This is especially likely when Logi tokens have been used outside of a SELECT statement.
Use MDX Query Elements
When working with an OLAP Table or OLAP Grid element, you can have the Logi Engine create the MDX query for you by using DataLayer.MDX and the MDX Query element.
The example above shows the datalayer with its MDX Query child element. The name of the target data cube must be provided, as shown.
Beneath the MDX Query element, you add child elements to define the dimensions, measures, and filters needed, as shown above. When the application runs, the Logi Engine will use these elements to create the appropriate query.
The available child elements, and their child elements, include:
Element | Description |
| MDX Axis Dimension | Specifies a dimension that will be shown in the OLAP Grid or OLAP Table, across the top or left side. You may have any number of MDX Axis Dimension elements for either axis. Generally, the OLAP Table requires these elements, because the user has no way to add them at runtime. However, since the OLAP Grid allows user selection of dimensions, MDX Axis Dimensions need only be specified for "default" dimensions. There must be at least one dimension defined for the left axis to see any results. You may specify a hierarchy in the Dimension Name attribute, e.g. [MyDimension].[MyHierarchy]. |
| Child: MDX Drill-Down | Specifies a single member of the dimension to be drilled/expanded.
You may add any number of MDX Drill-Down elements. Their Value attribute must be a member of the
parent element's dimension. Separate each level with the period
character, and when a member has spaces in the name, it must be enclosed in
brackets. Begin the Value with the dimension name, e.g.
[Customers].[All
Customers]. When there are multiple dimensions on a single axis, the members of each axis must be separated by the asterisk character. For example: [Customers].[All Customers].[USA].[CA]*[Education Level].[All Education Level].[Graduate Degree]*[Gender].[All Gender].[M] Specify a Value of AllMembers to expand all members of the dimension. |
| Child: MDX Drill-Up | Used to collapse a dimension member that has been drilled-down. This may be used to undo drill-downs caused by setting MDX Drill-Down to AllMembers. |
| Child: MDX Find | When present, automatically drills down and filters to show all members that contain the specified Value. Available only when using the OLAP Grid element. |
| Child: MDX Member Property | Specifies a single member property that will be displayed with the appropriate member values. Each member property is listed in its own column to the right of the Dimension Members. The column only appears when there is at least one member that has the property. Add an MDX Member Property element for each property to be listed. At runtime, users can change the properties when the OLAP Grid element's Pick Member Properties attribute is True. Available only when using the OLAP Grid element. |
MDX Calculated Measure | Adds an additional measure based on an MDX formula. MDX formulae basically
follow VBScript syntax and typically references other measures. For
example: VAL((Measures.[Store Sales] - Measures.[Store Cost]) / Measures.[Store Sales]) Add an MDX Measure element to display the calculated measure. |
| MDX Filter Dimension | Filters or slices values out of the OLAP cell set. Each dimension to be filtered should have an MDX Filter Dimension element. Dimensions cannot be used in both this element and MDX Axis Dimension elements at the same time. |
| Child: MDX Filter Value | Specifies a single member to be included in, or excluded from, the cell calculations. You may have any number of MDX Filter Values for each dimension. The Value attribute must be a member of the MDX Filter Dimension element's dimension. Separate each level with the period character. When a member has spaces in the name, it must be enclosed in brackets. Example: [USA].[CA] |
MDX Measure | Specifies a measure that will be shown in the OLAP Grid or OLAP Table cells. You may have any number of MDX Measure elements. If none of these elements are included, the cube's default measures will be shown. Cells are formatted according to the format specified in the cube definition. Localization is applied to these formats according to the browsers language setting. |
The data retrieved into the datalayer can be viewed by turning on the Debugging Link in the _Settings definition (General element) and using the resulting link at the bottom of the report page to view the Application Trace page. A link on the Trace page will display the retrieved data.