While we are in the midst of our Essbase and BI EE connectivity article series, i got an email from Christian Berg (who used to frequent the BI EE forums when i was active before) asking whether a specific report was possible using MDX without using complex and complicated UNION requests. I thought it was worth publishing here just to emphasize the point that i made in the previous 4 articles. MDX is very powerful. Let us take a look at the report. We shall start with Demo Basic outline. I would not paste the exact outline and report screenshots that he sent. Rather i would rephrase that question so that everyone can try this out on the Demo Basic cube. In this case, we shall be using Scenario as our Measure Dimension.

The report specification goes like this “A report showing value of Actual, Budget and Variance (the measure dimension members) against all the products in Gen3, Products. Actual should show the value of Sales against each product (Accounts dimension member). Budget and Variance should show the value of Sales and Marketing (2 Accounts Members).” In a nutshell the report should look like this

Without Using MDX Functions this report is not possible out of the box. The only way is to use UNION requests and even then it would not produce the data in the correct format. So, lets use MDX to generate this report. As a first step pull in the Gen3, Product in our report. Once that is done, pull in the Actuals measure. In the column formula use the below EVALUATE function.

EVALUATE('(%1.dimension.currentmember,[Actual],[Sales]).value',Product."Gen3,Product")

This will ensure that we are getting the Actual for all the products with Sales as the Account.

Now, the next step is pretty tricky. All we need is a SUM([Product].currentmember,{[Sales],[Marketing]},[Actual]). But unfortunately, MDX would not accept the currentmember function within SUM. And we need to pass a column into EVALUATE. Else it would throw an error. In order to overcome this we shall be using the below evaluate function.

EVALUATE('(%1.dimension.currentmember,[Budget],[Sales]).value - (%2.dimension.currentmember,[Budget],[Sales]).value + SUM({[Sales],[Marketing]},[Budget])',Product."Gen3,Product",Product."Gen3,Product")

So, basically what this does is, we are passing Gen3, Product just to make the EVALUATE function work. The first minus will always result in a zero and that is our intention. The actual function should have the been the SUM part along. Similarly, use the below function for Variance

EVALUATE('(%1.dimension.currentmember,[Variance],[Sales]).value - (%2.dimension.currentmember,[Variance],[Sales]).value + SUM({[Sales],[Marketing]},[Variance])',Product."Gen3,Product",Product."Gen3,Product")

Now, if we look at the report, we would get the desired scenario values without any complicated UNION requests. But remember to disable the drills.