Home      Functions     Common Solutions      Contact     






MDX Functions:
 
MDX Functions

 
Common Solutions

Common Solutions
 
Resource Links

MDX Books
MDX Related Software
MDX Related Blogs
 
Other Links

Contact Us
 
Microsoft SQL Server Analysis Services Function Page
Function Name: AddCalculatedMembers
Category: Set
Description: The AddCalculatedMembers function returns calculated members from the measures dimension when the calculated members have not been explicitly specified in the query.
Syntax: ADDCALCULATEDMEMBERS( «Set» )
Technet Link: MDX AddCalculatedMembers Function




The following query returns a set of all members of the measures dimension. Since calculated members are not materialized members of the measures dimension, they will not be returned to the rows axis. Note: This query does not use the AddCalculatedMembers function, it is used as a reference for the queries below
 

SELECT
      {} ON COLUMNS,
      {[Measures].Members} ON ROWS
FROM
      [Adventure Works]
 
 

 
The following query asks for all members of the measures dimension but now includes the AddCalculatedMembers function on the rows axis. The result will now include all measures from the measures dimension along with all calculated members.
 

SELECT
      {} ON COLUMNS,
      ADDCALCULATEDMEMBERS([Measures].Members) ON ROWS
FROM
      [Adventure Works]
 
 

 
The following query asks for all members of the measures dimension along with all calculated members (AddCalculatedMembers([Measures].Members)), then it removes the members of (-[Measures].Members). The row axis now contains only calculated members.
 

SELECT
      {} ON COLUMNS,
      ADDCALCULATEDMEMBERS([Measures].Members)-[Measures].Members ON ROWS
FROM
      [Adventure Works]
 
 

 
The following query shows: Column 1: Total Number of Measures Column 2: Total Number of Calculated Members
 
WITH
      MEMBER [Measures].[Total Number of Measures] AS
       COUNT([Measures].Members)
      MEMBER [Measures].[Total Number of Calculated Members] AS
      COUNT((ADDCALCULATEDMEMBERS([Measures].Members))-[Measures].Members)-2
SELECT
      {[Measures].[Total Number of Measures], [Measures].[Total Number of Calculated Members]} ON COLUMNS
     
FROM
      [Adventure Works]
 
The calculated member above, Total Number of Calculated Members, subtracts the two local calculated members from the count so it is not inflated.
 

 
 

 
Copyright 2008 by MDXpert.com.
 
Terms of Use                              Privacy Statement
 
Site Powered By - WinHost.Com