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 Nov 17, 2003

Introduction to MSSQL Server 2000 Analysis Services: Using Calculated Cells in Analysis Services, Part I - Page 3

By William Pearson





Let's focus now on the capabilities of the calculated cell to perform exception highlighting. For this example, we will create a calculated cell that highlights all USA warehouse locations with Warehouse Costs exceeding $11,000 for 1997. The first step of defining the new calculated cell is to define the range of cells in the cube, also known as the subcube (or cube region) upon which the calculated cell will act.



17.  Highlight/select the Warehouse dimension from the list on the left side of the Calculation Subcube dialog box.




18.  From the Members Set drop-down selector on the right side of the dialog box, select A Single Level.



19.  Expand the Warehouse dimension tree, and select Warehouse Name.




The dialog box appears as shown in Illustration 6.





Click for larger image

Illustration 6: Select A Single Level

20.  Click Next.

The Define the Calculation Condition dialog box appears.

At this stage, the wizard prompts for the calculated cell condition. We want to put highlighting in place for warehouse locations with Warehouse Costs exceeding $11,000 for 1997. To establish the condition for the application of highlighting, we will input the statement at this stage.

21.  Click the second radio button (Apply the Calculation Formula to Cells in the Calculation Subcube that Meet the Following Conditions) to select it.

22.  Enter the following into the MDX Expression text box:

CalculationPassValue((Warehouse.CurrentMember, 
	[Warehouse Cost]), 0) > 11000

Our condition formula uses the CalculationPassValue() function to ascertain the value of the cell before we apply the calculated cell. We are using this approach as a means of preventing the expression from being recursive, or referencing itself, as would normally be the case in a situation like ours (where the condition expression reads the value of the current cell, which itself references the calculation formula again). Because multiple passes are required for evaluation of MDX expressions, and because we know that evaluations of calculated cells will occur beyond pass 0, we tell Analysis Services to stop the evaluation at pass 0 (by placing 0 in the second argument position) and return the associated value. This mainly serves to prevent the expression from referencing itself ad infinitum, as if in a loop.

The completed Define the Calculation Condition dialog box appears, as shown in Illustration 7.


Illustration 7: The Completed Define the Calculation Condition Dialog Box

23.  Click Next.

The Define the Calculation Formula dialog box appears.

At this point, we are prompted to enter the expression for the calculation itself. The outcome of this formula will be the value that is assigned to the cells within the selected range, after evaluation of those cells via the condition we have assigned the calculated cell. Remember that the condition is a logical expression, and that it therefore evaluates to True or False; it is applied to every cell in the range defined in the calculation subcube. For any cell for which the condition evaluates to True, the result of the calculation formula applied to that cell is returned when the cell is queried. A False condition, by contrast, returns the actual value of the cell when it is queried. (Much of the power in calculated cells is that no real alteration of the data itself is taking place).

24.  Enter the following into the MDX Expression text box:

CalculationPassValue(Warehouse.CurrentMember, 0)

The completed Define the Calculation Formula dialog box appears, as shown in Illustration 8.


Illustration 8: The Completed Define the Calculation Formula Dialog Box



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