How to handle optional parameter in SSRS with oracle

Question

How to handle optional parameter in SSRS with oracle

rock on dude

Ten Centuries

Points: 1236
More actions
March 22, 2009 at 10:50 pm

#207707

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.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Luke L SSC-Dedicated Points: 35547 More actions · Answer 1

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[/url]For better help with performance problems please read this[/url]

David Fremantle SSC Journeyman Points: 95 More actions · Answer 2

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 Ten Centuries Points: 1236 More actions · Answer 3

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 SSC Journeyman Points: 80 More actions · Answer 4

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 SSCrazy Points: 2711 More actions · Answer 5

select a, b,c from xx where (@id is null or id =@id ) will solve your purpose.

Siva.

"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 SSC Journeyman Points: 77 More actions · Answer 6

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.