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: Generate
Category: Set
Description: The Generate function applies one set to each member of another set, then unions the resulting sets. This function also returns a concatenated string created by evaluating a string expression over a set.
Syntax: GENERATE( «Set1», «Set2»[, ALL] )
GENERATE( «Set», «String Expression»[, «Delimiter»] )
Technet Link: MDX Generate Function




The following query returns the top 5 products that have the highest Internet Order Count for each Country in the Customer Geography hierarchy in the Customer dimension.
 
WITH SET [5MostInternetOrderedProductsByCountry] AS
      GENERATE([Customer].[Customer Geography].[Country].Members, CROSSJOIN([Customer].[Customer Geography].CurrentMember, HEAD(ORDER([Product].[Product].[Product].Members, [Measures].[Internet Order Count], BDESC),5)))
SELECT
      {[Measures].[Internet Order Count]} ON COLUMNS,
      {[5MostInternetOrderedProductsByCountry]} ON ROWS
FROM
      [Adventure Works]
 
 

 
The query below uses the Generate function to create a string of all Sales Reasons in a set format.
 
WITH
      MEMBER [Measures].[GenSet] AS
      "{" + GENERATE([Sales Reason].[Sales Reasons].Members, MEMBERTOSTR([Sales Reason].[Sales Reasons].CurrentMember), ", ") + "}"
SELECT
      {[Measures].[GenSet]} ON COLUMNS
     
FROM
      [Adventure Works]
 
 

 
The following query uses the Generate function to create a string of all Sales Reasons (in a set format) which is then converted to an MDX set expression.
 
WITH SET [GenSet2] AS
      STRTOSET("{" + GENERATE([Sales Reason].[Sales Reasons].Members, MEMBERTOSTR([Sales Reason].[Sales Reasons].CurrentMember), ", ") + "}")
SELECT
      {} ON COLUMNS,
      {[GenSet2]} ON ROWS
FROM
      [Adventure Works]
 
The above query is for illustration purposes only since there are more efficient ways to arrive with the desired set. The main goal of the query was to show how to use the Generate function along with converting members to strings and strings to sets.
 

 
 

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