Selective Parameters

Question

Post reply

Selective Parameters

RS Newbie

SSC Journeyman

Points: 79
More actions
September 13, 2012 at 9:28 am

#393172

Good Afternoon All,

I am very new to RS but I'm at a point now that I want to get creative with parameters and not sure if if is possible to control parameters in the way I wish.

What I would like to to do is have a parameter to which a user can select either Month, Week, Day. Depending on what they selected the next parameter would give them a multi selection range of either months, startweek or a date. Obviously that is dependent on the fist parameter option.

Is this something that is possible is RS or is it quite restricted in this way?

Regards

RS Newbie.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Spiff SSCertifiable Points: 7766 More actions · Answer 1

Hi,

What you are looking for is cascading parameters.

There are stacks of tutorials about for this that explain it better than I ever could, here's a couple to start.

http://msdn.microsoft.com/en-us/library/aa337498%28v=sql.105%29.aspx

http://sql-bi-dev.blogspot.co.uk/2010/08/cascading-parameters-in-ssrs-2008.html

Your example sounds a little trickier, you'd have to return all your results as text so the user couldn't use the datepicker. You'd want a dataset to populate your second parameter (@Parameter2) that looked something like this:

SELECT DISTINCT Month as [Value]

FROM [DateTbl]

WHERE @Parameter1 = 'Month'

UNION ALL

SELECT DISTINCT Week

FROM [DateTbl]

WHERE @Parameter1 = 'Weeks'

UNION ALL

SELECT CONVERT(VARCHAR,[Dates],103)

FROM [DateTbl]

WHERE @Parameter1 = 'Dates'

Hope this helps.

Spiff SSCertifiable Points: 7766 More actions · Answer 2

The shard eyed among you might have spotted you don't need to use DISTINCT and UNION ALL, just use UNION instead. 😛



SELECT Month as [Value]

FROM [DateTbl]

WHERE @Parameter1 = 'Month'

UNION

SELECT Week

FROM [DateTbl]

WHERE @Parameter1 = 'Weeks'

UNION

SELECT CONVERT(VARCHAR,[Dates],103)

FROM [DateTbl]

WHERE @Parameter1 = 'Dates'