Passing parameters from MDX SSRS report to SQL SRRS report

Question

Passing parameters from MDX SSRS report to SQL SRRS report

Duncan Lawrence

SSCrazy

Points: 2047
More actions
February 7, 2013 at 9:34 am

#269907

Hi, I have a matrix report in SSRS 2008 r2 (using Report Builder 3) running against an SSAS data source
It is a simple matrix with a count by two dimensions with five parameters
This works fine, users can use the parameters to filter the result set
I have a second SSRS report running against a T-SQL relational data source that returns a detailed list of records. It takes six parameters
This second report is intended to act as a 'drill-to-detail' for the first report and I'm trying to get the parameters selected in the first report to pass to the second. But without much success
The values on the columns and rows of the report one matrix pass through fine to the second report, it is the values passed by the parameters that is causing the problem, well two problems
First, the value being passed is in the format [Dimension].[Hierarchy].[Member], how do I strip-out the [Member] value and pass only that to report two?
Second, the default for the report one parameters is the "All Member", this gets passed as [Dimension].[Hierarchy].[All Members]. How can I convert this to an array that can be recognized by the parameters of report two?
Report two is able to accept multiple value parameter values, so, for example, if I am able to convert the "All member" to an array containing all the hierarchy members the T-SQL will handle this successfully
I've asked Mr Google but without any joy, there's plenty on passing parameters to MDX and SQL queries but I have found nothing on passing an MDX parameter value to a T-SQL query so your assistance will be appreciated
Thank you
All the best,
Duncan

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mackers SSCrazy Points: 2935 More actions · Answer 1

Not sure this fully answers you question but could you create an extra column in you MDX statement with the name in such as

with member [Measures].[member name] as

[Dimension].[Hierarchy].CurrentMember.Name

Select [Measures].[member name] on columns,

{[Dimension].[Hierarchy].Members} on rows

from [Cube]

Mack

Mackers SSCrazy Points: 2935 More actions · Answer 2

This maybe more what you are after as it will give you a comma seperated list of the children for the All level

with member [Measures].[member name] as

IIF([Dimension].[Hierarchy].Level.Ordinal = 0

, Generate([Dimension].[Hierarchy].[All].Children

,[Dimension].[Hierarchy].CurrentMember

,',')

, [Dimension].[Hierarchy].CurrentMember.Name)

Select [Measures].[member name] on columns,

{[Dimension].[Hierarchy].Members} on rows

from [Cube]

Duncan Lawrence SSCrazy Points: 2047 More actions · Answer 3

Mack, thanks for taking the time to respond

In essence what I am trying to do is pass the parameter values from one report to another

The first report queries an OLAP cube and the parameters are dimension members ([dimension name].[hierarchy name].[member name])

The second report queries a relational database and will take as a parameter value the [member name] from the first report. So I've been looking at ways the strip-out the [member name]

This returns first member selected (I want to be able to pass multiple parameter values, the relational query can handle these)

=Mid(Parameters!PropertyPropertySubtype.Value(0),(InStrRev(Parameters!PropertyPropertySubtype.Value(0),"[")+1),((InStrRev(Parameters!PropertyPropertySubtype.Value(0),"]"))-(InStrRev(Parameters!PropertyPropertySubtype.Value(0),"[")+1)))

However if I remove the (0) from Value(0), nothing gets returned

Any ideas?

Thanks

All the best,

Duncan

Duncan Lawrence SSCrazy Points: 2047 More actions · Answer 4

Sorted

I have edited the stored procedure used by the detailed, relational report to strip out the values needed instead of relying on SSRS functions

Now the complete list of fully qualified parameter values are passed through to the procedure, it then uses a table-value function to provide a list of comma separated values for the Where ............ In clause

I'll need to remove the "All members" member from the drop-down list of paramter values provided in the first report as this is unrecognisable in the T-SQL end

All the best,

Duncan

Swallow Grasshopper Points: 23 More actions · Answer 5

Hi Duncan,

I would like to know how did you achieve this by using the stored procedure to pass the parameter from the MDX report to the T-sql Report.

Thanks a lot!

Swallow