| 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.
|
| |
|
| |
|