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: 7826 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: 7826 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'

RS Newbie SSC Journeyman Points: 79 More actions · Answer 3

Excellent, thank you Grasshopper.

I will have a play with it today.

I'm sure I will have more questions regarding this but I will read those articles you have mentioned.

Thanks for you help.

RS Newbie SSC Journeyman Points: 79 More actions · Answer 4

Well it would appear that we are having to write our code in MDX and not SQL, so not 100% sure on how to go about this. (My MDX sucks).

I'm sure I will find a solution at some point though.