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.

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:

Use this workflow to write MDX scripts in a text editor and upload them to Essbase.

1. Write the MDX script in a text editor, and save it with an .mdx extension.

2. Upload the MDX script to the application or cube directory under Files in the Essbase web interface.

3. Run the MDX script from Jobs or from Smart View, using Calculate on the Essbase ribbon.

Use this workflow to write MDX scripts in a script editor on the cube, and run them from Jobs.

Use this workflow to create MDX scripts using an application workbook, and run them from Jobs.

1. In an application workbook, create an MDX worksheet. See Work with MDX Worksheets in Cube Designer.
2. Add a file name in the File Name field.
3. Indicate, in the Execute MDX field, whether to execute the MDX at the time the cube is created. Valid entries are Yes and No.
4. Add the MDX script below the Script line.
5. Save the application workbook.
6. Build the cube. See Create an Application and Cube in Cube Designer.
7. Run the MDX script from Jobs, or if using Smart View, using Calculate on the Essbase ribbon.

Use the following guidelines when working with MDX scripts.

• Use MDX scripts to perform Insert or Export data operations.

• For grid analysis, use MDX reports instead of MDX scripts.

• MDX scripts can optionally include runtime substitution variables.

  • To be usable within Smart View, MDX scripts with runtime substitution variables must use the XML syntax within the SET RUNTIMESUBVARS calculation command, including <RTSV_HINT>.

  • To set a runtime substitution variable so that it calculates only the visible slice of data in Smart View, set the value of the runtime substitution variable to POV, and set the data type to member.

  • When run from the Essbase web interface, your MDX scripts may use substitution variables, but not runtime substitution variables. To use runtime substitution variables in MDX scripts, you must run the scripts from Smart View, using Calculate on the Essbase ribbon.

Use Substitution Variables

The following are examples of MDX scripts you can run on the Sample Basic cube, either from Jobs or in Smart View.

MDX Insert

You can save this .mdx script and run it from Jobs or from the Calculate dialog in Smart View.

INSERT "([Measures].[Payroll])" TO "([Measures].[Revised_Payroll])"
INTO [Sample].[Basic]
FROM (
       SELECT 
            {[Measures].[Payroll]} ON COLUMNS,
                {Crossjoin
                  (Crossjoin(Descendants([Year]), 
                   Crossjoin(Descendants([Scenario]),
                  Descendants([Product]))),
                  Descendants([Market]))} ON ROWS
       FROM [Sample].[Basic]
);

The above example assumes you have previously added a Revised_Payroll measure to Sample Basic.

MDX Export

You can save this .mdx script and run it from Jobs or from the Calculate dialog in Smart View.

EXPORT INTO FILE "sample01" OVERWRITE
SELECT 
 {[Mar],[Apr]} 
ON COLUMNS,
 Crossjoin({[New York]},
  Crossjoin({[Actual],[Budget]},
     {[Opening Inventory],[Ending Inventory]})) 
ON ROWS
FROM [Sample].[Basic]
WHERE ([100-10])

After you run the script, the following export file, sample01.txt, is saved in the cube directory of the file catalog:

Market,Scenario,Measures,Mar,Apr
New York,Actual,Opening Inventory,2041,2108
New York,Actual,Ending Inventory,2108,2250
New York,Budget,Opening Inventory,1980,2040
New York,Budget,Ending Inventory,2040,2170

MDX Export Using Runtime Substitution Variable

You can save this .mdx script and run it from the Calculate dialog in Smart View.

SET RUNTIMESUBVARS
{
 States = "Massachusetts"<RTSV_HINT><svLaunch>
                    <description>US States</description>
                    <type>member</type>
                    <allowMissing>false</allowMissing>
                    <dimension>Market</dimension>
                    <choice>multiple</choice>
                    </svLaunch></RTSV_HINT>;
};
EXPORT INTO FILE "sample002" OVERWRITE
SELECT
 {[Mar],[Apr]}
ON COLUMNS,
 Crossjoin({&States}, Crossjoin({[Actual],[Budget]},
 {[Opening Inventory],[Ending Inventory]}))
ON ROWS
FROM [Sample].[Basic]
WHERE ([100-10])

After you run the script, the following export file, sample002.txt, is saved in the cube directory of the file catalog:

Market,Scenario,Measures,Mar,Apr
Massachusetts,Actual,Opening Inventory,-54,-348
Massachusetts,Actual,Ending Inventory,-348,-663
Massachusetts,Budget,Opening Inventory,-160,-520
Massachusetts,Budget,Ending Inventory,-520,-910