Data Driven Subscription Help

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

Data #ERROR in Report Manager but not Designer

Hi,

I'm running a report using MDX. In preview, the report displays correctly, but after deploying to Report Manager, where there were once numbers there is now '#Error'. The string fields display correctly, its just the numeric ones that are not working.

Any help is much appreciated.

Cheers,

Ali

Is anything coming back from the cube? Are dimension values being retrieved correctly?

It sounds as if the connectivity may not be working between the server and the cube.

|||Yes, a list of zones are coming back from the cube. It's their associated measures that are displaying the error.|||

Is there any security on the cube?

Also, is it a matrix or tabular layout?

|||

There's not really too much security. There is one user who has access to everything and this is the credentials used in the data source.

The report uses a table and it has previously worked on a 2000 server but has recently migrated to 2005 (referencing a 2000 cube with a 2005 dll). Other similar reports using the same dll have migrated fine, except for one which keeps asking for a user name and password.

I've copied the code from the dll and added into the dataset and am still getting the same errors.. so the problem isn't with the dlls..|||

Well, that exhausted my short list of possibilities.

Are numeric cells using any type of formula? Aggregate can be a bit flaky.

|||

I know.. its confusing ey!!

and get this.. when i try it on our production 2005 server .. it works! Something funky is going on.