MDX in Analysis Services: Calculated Members: Further Considerations and Perspectives - Page 3
October 27, 2003
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.
11. Save the query with changes as MXAS08-1-2.
12. Modify the comment line to read -- MXAS08: Tutorial Query - Step 3.
13. Save the query with changes as MXAS08-1-3.
14. Insert the following statement into the Query pane, between the comment line and the SELECT keyword.
WITH MEMBER [Measures].[Warehouse Margin] AS '[Measures].[Warehouse Sales] - [Measures].[Warehouse Cost]'
15. Insert the following into the SELECT clause, between [Measures].[Warehouse Cost] and }) ON ROWS:
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:
16. Execute the query, by clicking the Run Query button.
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.
17. Save the query with changes as MXAS08-1-3.
18. Modify the comment line to read -- MXAS08: Tutorial Query - Step 4.
19. Save the query with changes as MXAS08-1-4.
20. Insert into the Query pane, between the WITH clause (do not place a comma after [Measures].[Warehouse Cost], a common error), added in query Step 3 above, and the SELECT clause, the following:
MEMBER [Time].[% Change] AS '([Time]..[Q2] - [Time]..[Q1])/[Time]..[Q1]'
21. After the [Time]..[Q1] portion of the SELECT clause, and within the braces, add a comma, then the following line below it, just before the close brace (}) and ON COLUMNS:
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.
22. Execute the query, by clicking the Run Query button.
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.
23. Save the query with changes as MXAS08-1-4, again leaving it open for the next steps.
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.
24. Insert the following just ahead of the ([Time]..[Q2]-[Time]..[Q1])/[Time]..[Q1]' part of the WITH clause (last line of the clause):
25. Change the comment line to read -- MXAS08: Tutorial Query - Step 5.
26. Save the query with changes as MXAS08-1-5.
The modified query should appear in the Query pane as shown in Illustration 9.
27. Execute the query.
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.
28. Save the query with changes as MXAS08-1-5, leaving it open for the next steps.