How to assign either a value or a wildcard to a report parameter

Question

How to assign either a value or a wildcard to a report parameter

Ade_1

SSC Enthusiast

Points: 175
More actions
October 12, 2015 at 8:37 am

#308791

Hi
I have two simple reporting services reports: One is a summary table showing upcoming sales calls by sales area and day. In this table/report the first column lists areas and the following columns showing the total number of sales calls scheduled for the next 14 days. The other table shows details of these calls and has two parameters @Area, and @Days. The detail table acts as a drill-down - A TextBox expression in the 'Summary Report' has an action which 'Goes To' the Detail Report passing in the appropriate values for @Area and @Days based on what values the user clicked on.
My problem is that I want to provide users with the option of just selecting an area (the first column in the summary table), and so where my dataset query for the detail report looks like this: 'WHERE SalesArea = @pmArea AND Days = @pmDays'.
I want it to accept the @pmArea value but use a wildcard for the Days value i.e. 'AND Days like '%'.
What is the best way to achieve this please? I've tried lots of things such as using '*' as a default value for the @pmDays parameter, and I've tried expressions in the parameter values for the dataset parameters e.g. =IIF(ISNOTHING(Parameters!pmSales.Value),'*',Parameters!pmSales.Value). But I'm not getting anywhere. Am I approaching this in the right way and does anyone have any suggestions for how I can achieve this?
Thanks in advance
Adrian

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

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

Ade_1 SSC Enthusiast Points: 175 More actions · Answer 1

Hi

I've managed to get it working by allowing the report parameter to accept nulls and using COALESCE within the query code e.g.

WHERE SalesArea = @pmArea AND Days LIKE COALESCE(@pmDays,'%')

If there's a better way I'd be glad to hear it.

Thanks

Luis Cazares SSC Guru Points: 183706 More actions · Answer 2

When building your parameter list, you can add an "All" row with value '%'.

Just remember the following:

- If the column is nullable, you won't return rows where days are null.

- If the column is not a string data type, the column will be implicitly converted to string avoiding the possibility of index seeks.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Ade_1 SSC Enthusiast Points: 175 More actions · Answer 3

Hi Luis

Thanks for the suggestion. I may misunderstand you but I do have a parameter list - the parameter values are made by the user selecting a cell within the table, from that selection the expression can determine the Area and Days to Visit values.

I hope that makes sense?

Thanks

Adrian