Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to handle optional parameter in SSRS with oracle Expand / Collapse
Author
Message
Posted Sunday, March 22, 2009 10:50 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 19, 2012 8:06 AM
Points: 218, Visits: 10,576
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.
Post #681231
Posted Monday, March 23, 2009 11:55 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 16, 2014 7:20 PM
Points: 2,897, Visits: 5,978
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
Post #681701
Posted Monday, March 23, 2009 4:19 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, May 11, 2009 5:26 PM
Points: 15, 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
Post #681943
Posted Wednesday, March 25, 2009 12:52 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, June 19, 2012 8:06 AM
Points: 218, Visits: 10,576
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
Post #683080
Posted Sunday, December 20, 2009 4:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 25, 2014 9:39 PM
Points: 2, Visits: 68
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!
Post #836866
Posted Monday, December 21, 2009 8:34 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 4, 2010 10:17 AM
Points: 191, 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
Post #837223
Posted Tuesday, May 25, 2010 10:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, April 9, 2013 10:52 AM
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.
Post #927627
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse