How do I set a report parameter based on another report parameter in SSRS?


I'm writing a couple of reports using SSRS that use parameters.
I would like to have a relationship between a couple of ther parameters
such that, whenever I change ParamA..., ParamB changes it's value based on ParamA.

It's straight forward to do this for the 1st time a parameter gets initialized.
The problem is I'd like to have this behavoiur in my report for when ever ParamA gets changed.

ParamA                                                     ParamB
StartDate             ====>                          EndDate
(DateTime)                                                (DateTime)

StartDate & EndDate are initially empty/null to start.
The Default Value of ParamB to equal (=) ParamA.
So, the 1st time I select a Date in ParamA, ParamB get's set.

I'd like to preserve this behaviour so that every time I select a date
in ParamA, ParamB gets updated.

Is this possible to do thru SSRS?

jxbmaSoftware ConsultantAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ValentinoVBI ConsultantCommented:
Not out of the box, you'll have to develop a custom application to get that behaviour  The ReportViewer control can then be used to render the report.

Also hava a look here for a similar Q:
This is one way you can do this.

1. Create a new dataset in your report and use the following SQL expression:

SELECT dateadd(day,4,@ParamA)

replacing the values in the dateadd function with the required values.

2. In the parameter properties of ParamB change it so that the values come 'From query' and select the new dataset and also select the output field as the value and label. Do the same in the default values section

Now every time you select a different value in ParamA, ParamB will automatically update. The only side effect with this is that you loose the date picker for ParamB

Hope this helps


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ValentinoVBI ConsultantCommented:
@shorak: I agree that your method would indeed update ParamB as expected.  But it also means that it can only be set to that one available value, right?  Which makes the parameter kind of obsolete in my opinion.
CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

You can past parameters to the next report if it is a subreport of  the main report.
ValentinoVBI ConsultantCommented:
Isn't that what I said in my initial post?
This is a problem in 2005. If you are able to limit the choices that are available for the EndDate then Shorak's idea of a dataset between the 2 parameters might work.
Otherwise it can only ever give you the initial default the first time Start Date is entered but not on any subsequent change.
And I guess this is what you are experiencing.

If you can use the dataset to provide you with a finite list of values for EndDate then Shorak's method will work, provide you place the desired default value as the first record. This works because every time the StartDate changes, the list that feeds the possible values in EndDate changes, so Reporting Services has to reprocess EndDate, thus giving you your desired affect.

To recap on the structure of this;
The dataset has to be dependant on parameter A
Parameter B's list of possible values as well as it's default value has to be dependant on the dataset.

So, no defaults in StartDate.
Dataset1 is something like;
    Select Label = ' ' + Convert(VarChar(11),DateAdd(yy, 1, @StartDate)), Value = DateAdd(yy, 1, @StartDate)
    Union All
    Select Label = Convert(VarChar(11), DateField), Value = DateField
        From DateTable
        Where DateField Between @StartDate And DateAdd(yy, 1, @StartDate)

The convert and the space added to the "default" value is to force the desired value to be first in the list. SSRS always sorts alphabetically.

In the dataset properties, go to parameters and set @StartDate to Parameters!StartDate.Value

That dataset will not run until the report parameter @StartDate has a value in it.
And everytime it changes, it should force the dataset to be refreshed.
The EndDate parameters extra dependancy on the dataset for it's list of valid values will force it to be refreshed whenever StartDate is changed.

I hope that's clear enough and does work for you!

ValentinoVBI ConsultantCommented:
Very nice explanation Chris!  Apparently I missed "the point" when Shorak posted his solution.

jxbma: if you want to avoid having to use a table containing a list of dates (not everyone has that present in their DBs), I'd like to mention another method of generating that list.  Try the query in attach.  That will generate a list of dates starting at your StartDate and ending one year later, just like in the solution by chrismc.

For an explanation on that spt_values table, have a look at this article by mark_wills:
select CONVERT(varchar(11), DATEADD(dd, number, @StartDate)) Label,
	DATEADD(dd, number, @StartDate) Value
from master..spt_values
where type = 'P' and number < 366

Open in new window

jxbmaSoftware ConsultantAuthor Commented:
I accept the moderator's recommendation.
Closing to accept the answers which seem to answer the question.

Experts Exchange Moderator
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.