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: Aggregate
Category: Statistical
Description: The Aggregate function returns a value that is calculated based on the aggregation type of a measure or an optionally supplied numeric expression across tuples in a supplied set.
Syntax: AGGREGATE( «Set»[, «Numeric Expression»] )
Technet Link: MDX Aggregate Function




The query below creates a calculated member called PriceAndQuantityTotal for the Sales Reason hierarchy in the Sales Reason dimenions. The calculated member aggregates the Price and Quantity Sales Reasons into a single value.
 
WITH
      MEMBER [Sales Reason].[Sales Reason].[PriceAndQuantityTotal] AS
       AGGREGATE({[Sales Reason].[Sales Reason].[Price],[Sales Reason].[Sales Reason].[Quality]})
SELECT
      {[Sales Reason].[Sales Reason].[Price], [Sales Reason].[Sales Reason].[Quality], [Sales Reason].[Sales Reason].[PriceAndQuantityTotal]} ON COLUMNS,
      {[Measures].[Internet Sales Amount]} ON ROWS
FROM
      [Adventure Works]
 
 

 
This query aggregates the North America and Pacific Sales Territory Groups into a single value.
 
WITH
      MEMBER [Sales Territory].[Sales Territory Group].[NorthAmAndPacTot] AS
       AGGREGATE({[Sales Territory].[Sales Territory Group].[North America], [Sales Territory].[Sales Territory Group].[Pacific]})
SELECT
      {[Sales Territory].[Sales Territory Group].[North America], [Sales Territory].[Sales Territory Group].[Pacific], [Sales Territory].[Sales Territory Group].[NorthAmAndPacTot]} ON COLUMNS,
      {[Measures].[Internet Order Quantity]} ON ROWS
FROM
      [Adventure Works]
 
 

 
 

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