CODEWITHSUNDEEP
sqlreporting-servicesreportbuilder3.0
Morten Laustsen
Morten Laustsen

Reputation: 527

Using a parameter to set the value of another parameter (Report Builder 3.0)

I'm creating a report (in report builder 3.0) containing 3 parameters @Name, @StartDate and @EndDate - pretty straight forward.

Now the report reader wants the possibility to use a fourth parameter, @Year. In theory this parameter should set @StartDate and @EndDate when chosen.

For instance, a reader chooses '2012' in @Year and then @StartDate and @EndDate are automatically populated with '01-01-2012' and '31-12-2012'

How is this possible?

Upvotes: 0

Views: 1503

Answers (2)

glh
glh

Reputation: 4972

To add to Pradeeshnarayan's answer.

The only way to achieve this you have to add two hidden parameters that decide if the year is entered then default to the whole year or if not then default to the selected start and end dates.

E.G.:

@h_Startdate = IIf(@year=Empty, @Startdate, convert(datetime, @year+'-01-01', 120))
@h_Enddate = IIf(@year=Empty, @Enddate, convert(datetime, @year+'-12-31', 120))

Good question.

Upvotes: 1

Pradeeshnarayan
Pradeeshnarayan

Reputation: 1235

Cant you create two new variables @Startdate and @Enddate in your SQL query from the @Year parameter. So it will work as you need.

@Startdate = convert(datetime, @year+'-01-01', 120)
@Etartdate = convert(datetime, @year+'-12-31', 120)

Upvotes: 2

Related Questions