Analyze Data with MDX Reports

You can store and render queries in the Essbase web interface using MDX reports. The minimum permission required to create a report is Database Manager.

Defining Layouts using the Ad Hoc Analysis tab may not always be the most efficient way to create a sophisticated report. If you know exactly what you want to query, you can use MDX to create a query to populate the grid.

To create an MDX report:

1. Log in to the Essbase web interface as a Database Manager or higher role.
2. On the Applications page, expand an application and select a cube.
3. Click the Actions menu to the right of the cube name, and select Analyze Data.
4. In the Analyze view, select the Reports tab and click Create.
5. Enter a name for the report, and optionally, a description.
6. In the Query field, enter an MDX query relevant to the current cube. For example:

   SELECT
     {([West].children)}
   ON COLUMNS,
     {([Diet].children)}
   ON ROWS
   

   The query must contain both row and column axes specifications. In other words, the query syntax must include specifications for both ON COLUMNS and ON ROWS, even if only an empty set {} is specified for one axis.

   Because the context of Analyze Data is the active cube, we recommend that you omit the optional cube specification (the FROM clause) from MDX reports. Omitting the FROM clause allows for more flexibility—if the cube is copied or renamed, the report will work in the new cube.

   Substitution variables are supported in MDX reports, but not runtime substitution variables. To use runtime substitution variables, save the MDX query as a script, and run it from Smart View using Calculate on the Essbase ribbon.

7. Click Validate to verify your MDX syntax, and then click Save.
8. From the Reports panel on the left, select the saved report to render a grid.

To learn more about MDX, see MDX and Writing MDX Queries.

Access to MDX Reports

How you work with reports depends on your cube access.

Users with, at minimum, the application-level role of Database Access can render saved MDX reports created by others. The data a user sees displayed in the report depends on that user's filter access.

In addition to rendering saved reports, Database Access users can export result sets in various formats: HTML, CSV, Excel, and JSON.

Database Access users can also view the MDX query that defines the report, by clicking the Actions menu next to the report name and selecting View.

If you have at least Database Manager role, you can use reports in the same ways that Database Access users can. Additionally, you can edit and delete reports using the Actions menu.

If you are a Service Administrator, you can additionally use the Execute As button to impersonate other users and check their data access. This can be useful for testing filters assigned to various users.

Examples of MDX Reports

The MDX examples in this section demonstrate special types of analyses you can perform, using MDX reports, that are not easily accomplished in the Ad Hoc Analysis view.

The following examples are designed to work on the Sample Basic cube.

Metadata Report

The following example returns only metadata (member names, but no data):

SELECT 
  {[Product].Levels(1).Members} 
ON ROWS, 
  {} 
ON COLUMNS

returning the grid:

Attribute Report

The following example uses, on columns, members from an attribute dimension:

SELECT 
 [Product].Children 
ON ROWS, 
 [Ounces].Children 
ON COLUMNS 
WHERE {Sales}

returning the grid:

Filtered Report

The following example uses a slicer (WHERE clause) to limit the query to Cola. Additionally, the Filter function limits the level 0 markets in the query to those that have a negative profit.

SELECT
  { Profit } 
ON COLUMNS,
  Filter( [Market].levels(0).members, Profit < 0) 
ON ROWS
WHERE {Cola}

returning the grid:

UDA Report

The following example shows Product data for Market dimension members that have a user defined attribute (UDA) of "Major Market." A slicer (WHERE clause) limits the query to include only Sales data.

SELECT 
  [Product].Children 
ON ROWS, 
  {Intersect(UDA([Market], "Major Market"), [Market].Children)} 
ON COLUMNS 
WHERE {Sales}

returning the grid: