SSRS: adding a Matrix based on 2 DataSets

0
votes

I just started working with SSRS, and figured out how to connect my MDX Query to a Cube and plug that into a Matrix

However, i need to have a matrix, and in each section, i need to call a different DataSet.

For example:

Matrix1 would have a repeating section based off DataSetA (let's say its customer centers).

in each section (customer center) of Matrix1 i would have a Expression, which calls DatasetB, using a value from DataSetA as parameter (let's say it would be a separate MDX query getting some other information based on customer center ID).

so my expression from within a tablix cell is something like this:

=First(Fields!MTD_Trades.Value, "DatasetB")

is it possible to pass a MDX query filter (or where clause) using a parameter from this point?

ideally it would be something like this (pseudo code):

=First(Fields!MTD_Trades.Value, "DatasetB") WHERE Fields!Where_Filter.Value

something along these lines.. where the MDX WHERE section is dynamic based off another query

UPDATE - solution explanation

here is an example of what i am doing: Let's say the application is based on air traffic and the business user here is some aviation authority person. I will give that person a UI that will let them define the slices they want to see. For example they can come in, and create n slices (i.e. International Flights, Flights leaving US, Flights Arriving After Midnight, Flights with 50+ passengers... n). As you can see, they will have the freedom of adding any filters they wish to each slice.

Then based on their actions (using GUI), i will generate the necessary MDX queries (one for each slice).

My challenge here is to create a SSRS report that will show a separate line for each one of those slices (1-n). Hope that helps in understanding my conundrum. The first part of the query will be the same for each slice (# of flights), but the WHERE section will be different and dynamic for the report.

1
sql-serversql-server-2005reporting-servicesssasmdx

1 Answers

1
votes

You can use the lookup() function to access data in another dataset from within a reportitem but you cannot run multiple sub-queries within the report. You can only do this within the parameters where one parameter can be an input for another query driven parameter.

The best course of action is to modify your original query to contain all the data you need for the Matrix.

EDIT: It sounds like you need to use a multivalue parameter.

  1. In the "Slices" parameter set it to allow multiple values
  2. In the dataset which accept this parameter, goto the parameters form and set the value to an expression which is =Join(Parameters!Slices.Value,",").
  3. Pull apart the string in you query and run each subquery then return as a set.