Where clause in a SET

Can you use a WHERE clause in a set statement I want to limit the set I am building to a certain subset of data.

SET [Customers] as ORDER

(NONEMPTY([Dim Customer].[Name].[Name].members,[Measures].[Coil Weight]),

[Measures].[Coil Weight],BDESC)

MEMBER measures.[Ton Rank] AS

RANK([Dim Customer].[Name].CurrentMember,[Customers])

Hello! I am not sure about what you are trying to do but the where clause is only supported in MDX Selects.

You can CrossJoin [Dim SalesType].[SalesYype] = "Sales"(is it a member property ) if it is {[Dim SalesType].[SalesYype].&[Sales]} (a member turned into a set) in your set.

To achive the effect you are looking for you can use CrossJoin.

And when you have a set you can refer to it as [MySet] .

HTH

Thomas Ivarsson

I have a dimSalesType with a measure [Sales Type] which holds the type of sales transaction. I want to limit my data set for ranking to different sales types. I'll write a different report for each sales type (there are only 3 or 4 sales types).

SET [Ordered Customers] as ORDER

(NONEMPTY([Dim Customer].[Name].[Name].members,[Measures].[Sales] * [Dim SalesType].[SalesTYpe].&[Sales}),

[Measures].[Sales],BDESC)

Would that work

Thanks.

I see I chose a confusing example. I am using "Sales" in the fact table to mean dollar amount and "Sales" in the dimenstion table to mean type of Sale. I changed that termanology. I am already using a Year/Month parameter to pare down the result set. Perhaps it would be easier to make SalesType a parameter and set it to "Sales" and let SSAS handle it.

When I do that (and let SSRS build most of my query for me) I get the following query, which works pretty good:

WITH

SET [Ordered Customers] as ORDER

(NONEMPTY([Dim Customer].[Name].[Name].members,[Measures].[Amount]),

[Measures].[Amount],BDESC)

MEMBER measures.[Sales Rank] AS

RANK([Dim Customer].[Name].CurrentMember,[Ordered Customers])

SELECT NON EMPTY { [Measures].[Gross Profit], [Measures].[Coil Weight], [Measures].[Steel Margin], [Measures].[Gross Margin], [Measures].[Amount], [Measures].[Sales Rank] } ON COLUMNS, NON EMPTY { ([Dim Customer].[Account Code].[Account Code].ALLMEMBERS * [Dim Customer].[Name].[Name].ALLMEMBERS * [Date Shipped].[Date].[Date].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@DimSalesTypeSalesType, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@DateShippedYearMonth, CONSTRAINED) ) ON COLUMNS FROM [Heidtman DW])) WHERE ( IIF( STRTOSET(@DateShippedYearMonth, CONSTRAINED).Count = 1, STRTOSET(@DateShippedYearMonth, CONSTRAINED), [Date Shipped].[Year-Month].currentmember ), IIF( STRTOSET(@DimSalesTypeSalesType, CONSTRAINED).Count = 1, STRTOSET(@DimSalesTypeSalesType, CONSTRAINED), [Dim Sales Type].[Sales Type].currentmember ) ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

My only problem now is that the cross joins are producing several hundred rows that return null values. It doesn't seem to effect the results, it just causes the report to run very slowly. What I am eventually going to do is add five different rankings to the report. I think it will work but, again, there is going to be a speed issure. Do you see any way of excluding null results Like I said, I let SSRS build most of the query for my and I don't really understand all that it is doing.

It was cross joining everything to the date field. Once I made the sales type a parameter and got rid of the date field it produced a cleaner data set. The only thing it is doing now is returning a rank of 0 for customers who have no activity in the time period. But I can live with that.

Thanks!