Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Oct 27, 2003

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

By William Pearson

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:

	,[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:


Illustration 5: Modified Query in the Query Pane

16.  Execute the query, by clicking the Run Query button.

The results dataset appears in Illustration 6.


Illustration 6: The Query - Step 3 Results Dataset

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].[1997].[Q2] - [Time].[1997].[Q1])/[Time].[1997].[Q1]'

21.  After the [Time].[1997].[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:

>[Time].[% Change]

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


Illustration 7: Modified Query in the Query Pane

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.


Illustration 8: The Query - Step 4 Results Dataset

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].[1997].[Q2]-[Time].[1997].[Q1])/[Time].[1997].[Q1]' part of the WITH clause (last line of the clause):

	'100  *

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.


Illustration 9: Modified Query in the Query Pane, Step 5

27.  Execute the query.

The result set appears as shown in Illustration 10.


Illustration 10: The Query - Step 5 Results Dataset

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.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM


















Thanks for your registration, follow us on our social networks to keep up-to-date