I am trying to filter a query by two (multi select) parameters.
It works fine when doing this for the first one, but complains when I add the the second one.
Is my syntax wrong is there a better way to achieve what I want?
A:
MDX WHERE has very little in common with SQL WHERE. MDX WHERE does not effect the number of rows that return, just which cube slice the cells are to be retrieved from.
I would use the FILTER function since a MDX WHERE clause must be a tuple (cell address), no more no less, i.e.,
(Dim1.Member, Dim2.Member, etc.)
Hope this helps.
ajdams
2010-06-07 14:33:28
but it works fine for one parameter as is
adolf garlic
2010-06-07 14:49:25
@adolf: Right but you are treating it like a classic WHERE and attempting to limit what is returned. So you'd need to FILTER to do this.
ajdams
2010-06-07 19:59:19
Isn't a FILTER only necessary when the item in question already appears on one of the axes? also, if using FILTER, do you use it only with the column you want to filter by or do you have to put all the columns from that dimension in there and have the filter expression appearing against the column of interest? I have 3 multi select (in RS) params that I ultimately want to filter by
adolf garlic
2010-06-09 04:38:20
A:
Tried subqueries?
SELECT
[Measures].[YourMeasure]
ON COLUMNS,
[Dimensions].[YourDimension]
ON ROWS
FROM
(SELECT STRTOSET(@p1) ON COLUMNS FROM
(SELECT STRTOSET(@p2) ON COLUMNS FROM
[YourCube] ) )
Meff
2010-06-09 08:31:26
Note that this is how SSRS generates parameters, so try making some queries in the graphic designer then change to MDX view.
Meff
2010-06-09 08:33:05