Tip 4: "Filter, Wherefore Art Thou?"
So how do you use MDX to filter based on specific conditions? For example, you may want to filter on all products where the gross profit per unit is greater than $500.00, where the product contains the word 'BIKE' in the description.
Use FILTER inside a CROSSJOIN—not the other way around.
|
|
MDX provides a
FILTER function that takes on two parameters: the
SET definition, and the filter expression. Here is an example:
SET [FilteredOrderedSales] AS FILTER(
ORDER(
([Customer].[State-Province].CHILDREN,
[Product].[Subcategory].CHILDREN ),
[GrossProfitPerUnit],BDESC),
[GrossProfitPerUnit] > 500
AND INSTR(
[Product].[SubCategory].
CURRENTMEMBER.Name,'BIKE') > 0)
Notice that the code uses the
INSTR function and must evaluate each member using the
CurrentMember.Name convention.
Tip 5: Name, "RANK", and Serial Number
SQL Server 2005 provides a terrific new function to
RANK result sets (i.e., the new
ROW_NUMBER OVER syntax). MDX provides functionality to
RANK result sets as well, with the
RANK function.
Suppose you want to rank sales by product, according the sales amount descending (so that the top-selling product is ranked #1, etc.) In the simplest form, the
RANK function takes on two parameters: which member you are ranking on (
SubCategory.CurrentMember) and the ordered set against which each subcategory
CurrentMember is evaluated:
If you want to rank a result set based on an order, first create an ordered set and then create a calculated member that applies the RANK function against the ordered set. This will usually provide maximum performance.
|
|
WITH SET [SalesRank] AS
ORDER(
[Product].[Subcategory].CHILDREN ,
[Measures].[Internet Sales Amount], BDESC )
MEMBER [Measures].[SalesRank] AS
RANK([Product].[SubCategory].CurrentMember,
[SalesRank])
SELECT
{[Measures].[SalesRank],
[Measures].[Internet Sales Amount]} on 0,
[SalesRank] on 1
FROM [Adventure Works]
That covers a basic example. But suppose you want to rank product sales within customers? Or suppose you want to rank product sales across all products and customers? Tip 7 will cover this in more detail.
Tip 6: Can You "TOP" This?
You can easily perform the equivalent of a
SELECT TOP 10 by using the
TOPCOUNT function.
TOPCOUNT takes three parameters: the set definition, the number, and the measure on which to base the top order logic:
-- Get the top 10 states based on sales
SELECT
[Measures].[Internet Sales Amount] on 0,
TOPCOUNT( [Customer].[State-Province].CHILDREN,
10,[Internet Sales Amount]) ON 1
FROM [Adventure Works]
If you want to retrieve the states that make up the top ten percent of sales, you can use the
TOPPERCENT function:
-- Get the States that make up
-- the top 10% of total sales
SELECT
[Measures].[Internet Sales Amount] on 0,
TOPERCENT( [Customer].[State-Province].CHILDREN,
10,[Internet Sales Amount]) ON 1
FROM [Adventure Works]
Finally, if you want to retrieve the states that make up the top ten million dollars in sales, you can use the
TOPSUM function:
-- Get the States that represent
-- the top 10 million dollars
SELECT
[Measures].[Internet Sales Amount] on 0,
TOPSUM( [Customer].[State-Province].CHILDREN,
10000000,[Internet Sales Amount]) ON 1
FROM [Adventure Works]
Previous Page
