CODEWITHSUNDEEP
reporting-servicesreportingssrs-2012
Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

Setting 2 parameters with single dataset in SSRS

I've created a view that has 3 columns:

For example (if today is 2018-04-11 10:12:54.4770000):

And so on. So my goal is to provide user dropdown with the labels displayed and when he picks any, I would like to assign @start_date and @end_date parameters to use in other datasets. Any suggestions what can I do?

Upvotes: 1

Views: 41

Answers (2)

Dmitrij Kultasev
Dmitrij Kultasev

Reputation: 5745

I'll answer my own question. "Alan Schofield" version works, but I wanted to do it with single dataset. So, what I did:

  1. Added concatenated column to dataset that have start and end dates in it, for example: "2018-04-11 00:00:00.0000000|||2018-04-12 00:00:00.0000000"
  2. Create 2 internal params with default value as =Split(Parameters!date_selector.Value,"|||")(0) and =Split(Parameters!date_selector.Value,"|||")(1) accordingly

That's it

Upvotes: 0

Alan Schofield
Alan Schofield

Reputation: 21683

Add a dataset called say dsDropDown and set the query to something like

SELECT * FROM myView

Add a parameter myParameter to your report and set the available values to your dsDropDown dataset. As you don't have a key you'll need to set the Value and Label properties both to your label field.

Next, add your main dataset and join to your view, filtering by the view label, something like.

SELECT * 
    FROM myTable t
        JOIN myView v on t.SomeDate >= v.start_date AND t.SomeDate <= v.end_date
WHERE v.label = @myParameter

Make sure the parameter name in the dataset query matches the parameter name EXACTLY (they are case sensitive).

Next just add your tablix/matrix/chart etc and point it to your main dataset.

I think that should be it but this was off the top of my head so it may not be 100% correct. Hopefully enough for you to follow though.

Update after OP response: If you need these are parameters then you will need to add two new datasets, dsStart and dsEnd. The query for these datasets will be as follows..

SELECT start_date from myView WHERE label = @myParameter

and 

SELECT end_date from myView WHERE label = @myParameter

Then add two new parameters @start' and@end` and set the default values to the respective datasets.

When you select your first parameter, the seconds two will be updated automatically. You can set the @start and @end parameters to hidden once you confirm its working.

Then change the main dataset query to something like..

SELECT * 
        FROM myTable t
    WHERE t.Somedate between @start AND @end

Upvotes: 1

Related Questions