Creating a formula report with MDX.EXECUTE

We will build a simple Formula Report with MDX.EXCUTE in which we will use three MDX components: SELECT, WHERE and WITH.

Click New Formula Report in the Office Plus group on the ribbon.
Click Save and save the report.
Drag the database alias from the Database Structure into cell H1.
We do this because the MDX.EXECUTE formula will refer to the database alias. In the formula, you can specify the database alias in quotation marks (for example: "Best Practices OLAP") or you can specify the reference of a cell which contains the name of the database alias. It helps to prevent errors if you drag the database alias into a cell and refer to that cell in the formula.
To create the SELECT part of the statement, copy this MDX statement and paste it into cell B3.
```
SELECT
{[PRODUCT].[All Tires].children} on columns,
{[Period].[All Years].children} on rows
FROM
ANALYSIS
```
The statement occupies cells B3 to B7.
The statement says: from the Analysis cube, get the children of the All Tires (the product groups) element and the children of the All Years element (the years) and display them in the columns and rows of the report.
To execute the statement, specify this MDX statement in cell B12:=MDX.EXECUTE(H1, B3:B7,H4,G5,H5)

The MDX statement in cells B3 to B7 is executed. It displays the columns in H4, the rows in G5 and the data in H5.

Because the Measure dimension has not been used, the values of the report are based on the default element, Gross Margin.

To see the values for Units instead of Gross Margin, you can add a WHERE clause to the MDX query.

Paste this additional code into cell B8:

WHERE
(
[MEASURE].[Units]
)

The full statement is:

SELECT
{[PRODUCT].[All Tires].children} on columns,
{[TIME].[All Years].children} on rows
FROM
ANALYSIS
WHERE
(
[MEASURE].[Units]
)

In B12 change the Query argument of the MDX.EXECUTE formula to B3:B11.
An MDX query often contains a WITH clause. It defines elements of the report which are used in the SELECT statement.
We will define the set ProductSubGroup as [Product].[All Tires].children and use it in the SELECT statement
Paste this code into cell B1:
```
WITH
Set ProductSubGroup AS'{[PRODUCT].[All Tires].children}'
```
If an error occurs, delete and reenter the apostrophe (') after AS and at the end of the line.

Change the SELECT statement to:

SELECT
ProductSubGroup on columns,
{[Period].[All Years].children} on rows
FROM
ANALYSIS

Now the full MDX query is:

WITH
Set ProductSubGroup AS '{[PRODUCT].[All Tires].children}'
SELECT
ProductSubGroup on columns,
{[Period].[All Years].children} on rows
FROM
ANALYSIS
WHERE
(
[MEASURE].[Units]
)

The statement occupies cells B1 to B11.

Change the Query argument of the MDX.EXECUTE formula to B1:B11
If you receive this error message:Syntax error: WITH Set ProductSubGroup AS'(<<< delete and reenter the apostrophes in B2.
Save and close the report.