Hello all,
I am trying to schedule an MDX report to run once a week and it only works if I pass one parameter per field.It would fail every time I try to pass multiple parameters.Do you know if there is way to pass multiple MDX parameters?
The parameter field is set as multi-value field.
This worked –
'[Dim Travel Product].[Dim Travel Product].&[67]'
This failed –
'[Dim Travel Product].[Dim Travel Product].&[67], [Dim Travel Product].[Dim Travel Product].&[70], [Dim Travel Product].[Dim Travel Product].&[69]'
This failed -
'[Dim Travel Product].[Dim Travel Product].&[67]&[69]&[70]'
Could you post the MDX statement into which the parameters are being passed?
Thanks,
Bryan
The parameter is passed to @.TravelProduct:
SET [Dim Travel Product Set] AS StrToSet(@.TravelProduct)
MEMBER [Dim Travel Product].[Dim Travel Product].[Travel Product Subset] AS 'Aggregate([Dim Travel Product Set])'
Thanks in advance!
|||It also works if i were to run this manually and select multiple parameters. The problem is I don't know the proper syntax of what's being passed when there are multiple selections.
|||
Enclose the list of members inside { } when passing multiple members.
|||None of the below syntax works:
'{[Dim Travel Product].[Dim Travel Product].&[4]&[5]&[7]}'
'{[Dim Travel Product].[Dim Travel Product].&[4], [Dim Travel Product].[Dim Travel Product].&[5], [Dim Travel Product].[Dim Travel Product].&[7]}'
This is the method that I am using to pass the parameter:
So this is still working:
SELECT CONVERT(char(10),DateAdd(dd, -1 - (DatePart(dw, getdate()) - 2), getdate()),101) as Sunday,
'[Dim Travel Product].[Dim Travel Product].&[4]' as TravProd,
'[Dim Car Vendor].[Car Vendor Desc].[All]' as Vendor
This does not work:
SELECT CONVERT(char(10),DateAdd(dd, -1 - (DatePart(dw, getdate()) - 2), getdate()),101) as Sunday,
'{[Dim Travel Product].[Dim Travel Product].&[4], [Dim Travel Product].[Dim Travel Product].&[5], [Dim Travel Product].[Dim Travel Product].&[7]}' as TravProd,
'[Dim Car Vendor].[Car Vendor Desc].[All]' as Vendor
This does not work:
SELECT CONVERT(char(10),DateAdd(dd, -1 - (DatePart(dw, getdate()) - 2), getdate()),101) as Sunday,
'{[Dim Travel Product].[Dim Travel Product].&[4]&[5]&[7]}' as TravProd,
'[Dim Car Vendor].[Car Vendor Desc].[All]' as Vendor
Take a look at this. It works against Adventure Works:
Code Snippet
select
[Date].[Date].[July 1, 2003] on 0,
strtoset("{[Product].[Category].[Bikes],[Product].[Category].[Clothing]}") on 1
from [Adventure Works]
B.|||
What I am trying to do is pass the @.TravelProduct from a Data Driven Subscription in Reporting Services and schedule it to run once a week. The select statement that I built contains all my filters, but when I add more than one filter for a particular field, it errors.
Thanks!
|||What my code illustrates is how the MDX must be constructed to support multiple values from a parameter. You need to use STRTOSET, you need to wrap that comma delimited list of set members in curly braces, and that set string needs to be wrapped in double-quotes. If you've got all that in place, you can use a parameter in SSRS supply the set of members. The trick is getting your MDX in order to be able to work with the value from the parameter.
B.
|||Thanks! I'll try that later today and report back.
|||I tried what Bryan did above but it is still not working. ;(
sql
