Using Custom MDX in App Studio

I was working on a fairly simple report for one of the clients I'm doing consulting few days back. At first it looked quite simple. But then suddenly client changed the requirement and I got caught to a Red light. Grrrrrr why do clients always have to change their requirements.

Well .... This is not something new to me , actually this happens quite frequently in the IT world :).

In this instance I had to use a Custom MDX to overcome this problem.

Report Layout

The Site combo drives the work centers in the report layout. The initial requirement was to show the selected Site and all of it's base elements which was a simple structure selection in the hyperblock.

Later they changed the requirement to show All base elements of the selected Site as well as the parent for each base element.

Note : Site in the combo box is in Level 3 where as the base elements can be in different levels in the hierarchy.

So to over come this problem I had to write a custom MDX. What is MDX ??

Multidimensional Expressions (MDX) is a query language for online analytical processing (OLAP) .

It's like using SQL in SQL Server.... But the syntax is different and could be difficult to understand. But with some research on the internet I was able to figure this out.

Below is my MDX

="Generate  (   Descendants (  StrToSet('{"&ReportVariables.rv_Site.Text&"}'), 10,LEAVES  ) ,

                                    UNION ({ [Work Center].CurrentMember.Parent}, Descendants  ( StrToSet('{"&ReportVariables.rv_Site.Text&"}') ,10,LEAVES ) )

                        ) "

Wooooah what the hell is this ???

Ok so let me break this down in to small pieces and explain what I have done here.


I have used the below MDX functions to achieve my goal.

1. Descendants
2. Generate
3. StrtoSet
4. UNION

Let me first explain what these functions do..

Descendants
Returns the set of descendants of a member at a specified level or distance, optionally including or excluding descendants in other levels.

Descendants (  StrToSet('{"&ReportVariables.rv_Site.Text&"}'), 10,LEAVES  )

The report varbale rv_Site contains the selection which the user did. I need to get the base elements of the particular site.  The key word here is "LEAVES" . This means the lowest level members of the hierarchy. The number 10 here means how many levels you need to go down on the hierarchy. Make sure you set a number greater than the lowest level in the hierarchy so that it picks the lowest level members.

StrToSet
Returns the set specified by a Multidimensional Expressions (MDX)– string.

StrToSet('{"&ReportVariables.rv_Site.Text&"}')

What this does is convert the Site value in to a readable MDX format


UNION

Returns a set that is generated by combining two sets.

UNION ({ [Work Center].CurrentMember.Parent}, Descendants  ( StrToSet('{"&ReportVariables.rv_Site.Text&"}') ,10,LEAVES ) )

Here I am combining  [Work Center].CurrentMember.Parent and Descendants  ( StrToSet('{"&ReportVariables.rv_Site.Text&"}') ,10,LEAVES )

What I am actually doing here is getting the parent of each base element and combining it with the base elements of rv_Site.

Generate

Generate function gives you the ability to loop through a set and peform a function on each of the items in the particular set.

So in our example I am selecting all the base elements of rv_site as the Set for the Generate function.

Then i loop through using the [Work Center].CurrentMember.Parent option to get the Parent of each base element.

I hope my explanation makes sense :D. Well if it did not at least I tried lol.

For a for information on MDX you can visit here.

I will post few more MDX related examples in the future. Cheers...