Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to handle optional parameter in SSRS with oracle


How to handle optional parameter in SSRS with oracle

Author
Message
rock on dude
rock on dude
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 10576
I have three parameters in a SSRS report connected to an oracle database. I want to make all the parameters as optional, so that the user sees all the records if nothing has been selected. Though i give the parameters as 'Allow null' in the reports parameters, I am not getting back any records in the report. I have added the parameter in the where clause of the dataset as 'Field name in (:parametername)'. Please provide help asap.very urgent.
Luke L
Luke L
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2686 Visits: 6103
Couple of ways to do this, none of which are ideal, but try them and see what works in your environment. what you are currently doing is sending WHERE columnName in (NULL) when nothing is selected by the user. That's fine as long as you handle that in your logic. For instance if you had a stored procedure you were callign and passed a NULL value, you might handle that in the code with some IF syntax or something.

The best way for you would probably be this...Depending on how many values could be selected you could use of a multi select parameter and the SELECT ALL value. You could default it to SLEECT ALL by using the technique found here. That way when the report is run it automatically selects everything and returns a result set.

The only thing to watch out for is that if you default all 3 parameters to SELECT ALL when the report is run from the browser it will run automatically as soon as it's opened. Depending on the execution time and data you are bringing back this could be a problem as the user will have to wait for it to finish executing before they can narrow the results down.

Also if this is an expensive query from your db's perspective note how it may affect other users.

-Luke.

To help us help you read this

For better help with performance problems please read this
David Fremantle
David Fremantle
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 29
As Luke indicated, what you do will depend on the size of the datasets you're working with. If they're fairly small, here's my two bits.

Having had trouble loading sets of parameters into an Oracle query for a SSRS report before, I found that the easiest solution in some cases is to load the whole ("Select All") dataset into the report, then filter the display at the presentation level, i.e. in the report controls. For example, for a multi-value parameter, I could go to main table control's Properties > Filter tab, and then set the IN operator on the column to filter, using the parameter.

Once again, this is just an "easy" solution, letting me work around parameter issues in Oracle SQL, and it's only useful for relatively small datasets. For anything bigger, or where it'll make several seconds' difference to the end user, use a different solution.

David
rock on dude
rock on dude
SSC Veteran
SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)SSC Veteran (226 reputation)

Group: General Forum Members
Points: 226 Visits: 10576
Hi All,

Thank you so much ... that worked by passing a null value as well to the parameter when the check box was not selected...

Thanks for your help.

Regards,
Blah baby
jammy
jammy
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 69
Hi,
I am having same issue. Can you let me know details of what you modified in SSRS report and in SQL query so that the null value is handled.
My problem is that, if null is selected as a check box in report, the SQL query should pull all null values

e.g
Original query:
select a, b,c from xx where id =@id (select a,b,c from xx where id = 2)

if null value is passed it becomes: select a,b,c from xx where id = null which is wrong. Rather it should become where id is null

Thanks a lot!
Siva Gurusamy
Siva Gurusamy
SSC-Enthusiastic
SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)SSC-Enthusiastic (199 reputation)

Group: General Forum Members
Points: 199 Visits: 386
select a, b,c from xx where (@id is null or id =@id ) will solve your purpose.

Siva.

View Siva Gurusamy's profile on LinkedIn

"Software changes. This is a rather obvious statement, but it is a fact that must be ever present in the minds of developers and architects. Although we tend to think of software development as chiefly an engineering exercise, the analogy breaks down very quickly. When was the last time someone asked the designers of the Empire State building to add ten new floors at the bottom, put a pool on the top, and have all of this done before Monday morning? " : Doug Purdy, Microsoft Corporation
jaya.sharma684
jaya.sharma684
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 38
Hi
I have an issue. When you 'select all' from the drop down of the report, the parameter tab shows all the values being selected is there any way to display "SELECT ALL" instead of all the values being selected in the tab.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search