MDX in Analysis Services: Calculated Members: Further Considerations and Perspectives - Page 3

Now let's build the first of two calculated members. We need to generate the total Warehouse Margin for each state, and so need a calculated measure that gives us total Warehouse Sales less total Warehouse Cost.

WITH 
MEMBER [Measures].[Warehouse Margin] AS
'[Measures].[Warehouse Sales] - [Measures].[Warehouse Cost]'

	,[Measures].[Warehouse Margin] 

The intent here is simply to add the new calculated member, Warehouse Margin, ([Measures].[Warehouse Margin]), into the SELECT statement so as to display it.

The complete, modified query, Step 3, should appear in the Query pane as shown in Illustration 5 below:

The results dataset appears in Illustration 6.

We will handle the formatting later. The important thing at present is that we appear to have achieved the desired result, and have derived and displayed the needed Warehouse Margin amounts with our new calculated measurer. Let's add calculation of the next part of the requirement, the Percent Change in margin between Quarter 2 (Q2) and Quarter 1 (Q1) of 1997, to our query next.

	MEMBER [Time].[% Change] AS
	  '([Time].[1997].[Q2] - [Time].[1997].[Q1])/[Time].[1997].[Q1]'

>[Time].[% Change]

The complete, modified query, Step 4, should appear in the Query pane as shown in Illustration 7.

The insertion we have made within the WITH clause (put in place for a calculated member in our last step) creates a calculated member called % Change; the addition of the newly defined calculated member in the SELECT clause instructs the application to return it as an additional column in our result set.

The results dataset appears in Illustration 8.

As we see above, formatting aside, we approach even more closely the objectives of the targeted audience. We now have a column that presents the % Change calculation between the quarters.

I make it a practice to save formatting for last when creating queries, writing reports, or anything related to the extraction / derivation, and subsequent presentation of, information, because much rework can be saved by avoiding the inevitable changes associated with adding to or modifying a report. While I may find it hard to resist the urge to "go ahead and clear up the picture," years of experience have made it clear that this is a key kind of discipline to have, especially with elaborate queries and complex reporting.

That being said, let's take a look at a basic formatting techniques. We can begin by making the % Change cells more compact for ease of use in the design.

	'100  *

The modified query should appear in the Query pane as shown in Illustration 9.

The result set appears as shown in Illustration 10.

We see the % Change column for the first two rows more closely resembling a percent figure, to which most of us can relate, with the presentation of the percent sign a matter for later resolution. This was easily accomplished by the simple addition of the multiplier - one of numerous ways (and not necessarily the most optimal in a Production setting) to accomplish the same task.

We will practice the addition of a couple of different strings, to format the percent and dollar value fields, in our remaining section below. For the time being, however, we need to focus on a more substantive consideration that arises in a case similar to the one we have traversed in our example. Our creation of two calculated measures, via the WITH statement in our last few steps, and the resulting value that appears at the intersects of the calculated measures - that is, in each of the cells formed by the % Change / Warehouse Margin calculated members' intersection - brings to light a consideration that is somewhat weightier than mere formatting concerns.