CODEWITHSUNDEEP
reporting-servicesssrs-2008-r2
BartMao
BartMao

Reputation: 681

Create a dataset which is a subset of another dataset in SSRS

In my SSRS report, I already have a dataset A(by running a SQL script), and parameter P1 use all the records in A. Now I want to get a subset of A, and use another parameter P2 to refer to it.

Is it possible that get the whole and the subset of the dataset at the sametime and only run the script once?

I guess creating a shared dataset is a possible way, but the dataset A is just for locally use and shouldn't be shared.

Upvotes: 4

Views: 9922

Answers (2)

codeMonkey
codeMonkey

Reputation: 4815

One way I've achieved this is with grouping. If Dataset A already has all the stuff you want, you can group that dataset with criterion P2 = TRUE. This splits Dataset A into two groups -- one where P2 condition is true, and the other where P2 condition is false.

For instance consider a dataset with two columns, Label and Amount. I want to subset my data where Label = "LabelNameOne". I create a group around my dataset with expression =Fields!Label.Value = "LabelNameOne", which then automatically creates a subset for me. Assuming you wanted it to filter on a user-chosen parameter at run time, you just sub in that parameter in your grouping expression: =Fields!Label.Value = Parameters!P2.Value.

Upvotes: 0

alejandro zuleta
alejandro zuleta

Reputation: 14108

Short answer

No, it is not possible.

Alternative

You can modify your query in order to it returns one column for populate the P1 parameter and other column to populate P2. Example:

select 'Foo' P1, 'Foo' P2
union all
select 'Bar', 'Bar'
union all
select 'Foobar', null

Returns:

P1       P2 
Foo      Foo
Bar      Bar
Foobar   null

Use P1 column for populate the P1 parameter and P2 for populate P2 parameter.

Note the subset column (P2 in my case) has less values than P1, if your parameter is set to allow NULL values, it will show the NULL option in select list otherwise it won't.

This solution could work for you but if you need the dataset runs only once I am unsure of that, I think SSRS will run the query for every parameter even if both parameters are being populated from one dataset.

Let me know if this helps.

Upvotes: 3

Related Questions