How to Ignore Report Parameter

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services 2005 Development » How to Ignore Report Parameter

24 posts, Page 1 of 3

1 2 3 »»»

How to Ignore Report Parameter

Rate Topic

Display Mode

Topic Options

Author

Message

Welsh Corgi

Posted Wednesday, January 11, 2012 10:35 AM

Hall of Fame

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:19 AM
Points: 3,833, Visits: 4,052

I modified a Stored Procedure to allow the parameter to have a Null Value.

I checked the Allow Nulls in the Parameters Dialogue Box but I get an error that I must select the parameter. Unsure

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1234188

Welsh Corgi

Posted Wednesday, January 11, 2012 11:11 AM

Hall of Fame

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:19 AM
Points: 3,833, Visits: 4,052

I have specified to allow NULL in the parameters dialogue box. Unsure

Post #1234224

Gazareth

Posted Wednesday, January 11, 2012 12:25 PM

UDP Broadcaster

Group: General Forum Members
Last Login: Monday, June 10, 2013 4:11 AM
Points: 1,474, Visits: 2,344

I bypassed SQL 2005 but 2008 also has an allow blank value checkbox, have you ticked that?

Post #1234288

Welsh Corgi

Posted Wednesday, January 11, 2012 12:34 PM

Hall of Fame

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:19 AM
Points: 3,833, Visits: 4,052

I added a Null Value to the table that is used to populate the list-box, I select Null but I get no results.

The Stored Procedure has the following criteria:

AND  ((@State IS NULL) OR (State = @State))

I tried the following so I could get a multi-value drop-down checkbox but that did not work either:

AND  ((@State IS NULL) OR (State IN (@State)))

Post #1234295

Steven.Howes

Posted Friday, January 13, 2012 6:51 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 374, Visits: 1,682

Have you tried a profile trace to see what is actually being passed to your SP?

Post #1235590

Welsh Corgi

Posted Friday, January 13, 2012 7:54 AM

Hall of Fame

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 6:19 AM
Points: 3,833, Visits: 4,052

Steven.Howes (1/13/2012)

Have you tried a profile trace to see what is actually being passed to your SP?

I need to use a function but I had to switch to some DBA tasks and it is on hold.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1235657

wolfkillj

Posted Friday, January 13, 2012 8:35 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 2:04 PM
Points: 774, Visits: 1,503

Welsh Corgi (1/11/2012)

I added a Null Value to the table that is used to populate the list-box, I select Null but I get no results.

The Stored Procedure has the following criteria:

AND  ((@State IS NULL) OR (State = @State))

In the past, I have avoided this issue by creating a dummy value for the SSRS parameter and making it the default value. Usually, I would display this to the user (i.e., set the label for the value) as "-ALL-" or something like that and set the value to something that would never be an actual value in the database. For example, for a State parameter that accepts two-letter abbreviations for U.S. states, I would set the value to 'XX' and the label to "-ANY-" or "-ALL-". Then, the query syntax in the stored procedure would look like this:

WHERE @State = 'XX' OR @State = State

This makes it certain that the value you pass to the stored proc parameter from SSRS will yield the desired result. Be sure to comment the stored proc code to explain the significance and use of the dummy value.

I also have used a little trick to make sure the dummy value appears at the top of the drop-down list. I wrote the query to populate the drop-down list like this:

select
StateName as paramLabel
,StateAbbr as paramValue
,2 as ordinal

from dbo.States

union all

select '-ALL-', 'XX', 1

order by ordinal, paramLabel

I used the artificial ordinal column to force the dummy value to the top of the list.

A friendly suggestion: If you haven't read Gail Shaw's article on "catch-all" queries, it's worth the time. She outlines the performance pitfalls of query syntax like that you cite above. Dynamic SQL most likely will improve the performance of your query when you want to allow some input parameters to be NULL. Of course, dynamic SQL may not be possible in your environment, so if not, please ignore this suggestion!

I tried the following so I could get a multi-value drop-down checkbox but that did not work either:

AND  ((@State IS NULL) OR (State IN (@State)))

Unfortunately, SSRS 2005 passes the selected values from a drop-down list for a multi-valued parameter to SQL Server as a comma-delimited string. That's why your "State in (@State)" syntax doesn't work - to SQL Server, it looks like "State in ('TN, AR, MS')", which of course is not the desired syntax of "State in ('TN', 'AR', 'MS')". You have to parse the comma-delimited string to get the individual values. Jeff Moden has developed a very efficient way to do this:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

After you have created the "splitter" function, your query syntax would look like this:

WHERE State in (select Item from dbo.DelimitedSplit8K(@State, ','))

If you need to refer to the values contained in @State several times in the stored proc, you could also insert the results of the subselect above into a table variable or temporary table so that you only have to call the splitter function once.

Hope that helps!

Post #1235695

mjbriggs03

Posted Tuesday, January 24, 2012 9:11 AM

Valued Member

Group: General Forum Members
Last Login: Saturday, January 26, 2013 11:29 AM
Points: 57, Visits: 93

I literally just wrote a parameter that I wanted to allow the user to use OR not use, their choice. It was a range parameter for population figures where the user types in the range (non-queried). So, here's the syntax..

((A."Population" BETWEEN (:MinPopulation) AND (:MaxPopulation)) OR ((:MinPopulation IS NULL) AND (:MaxPopulation IS NULL)))

Now, I checked off ALLOW NULL VALUES and the set the default values to NULL and it seems to be working just fine.

Oh, I'm working out of an Oracle database hence the reason you're seeing ':' instead of '@.'

Hope this is somewhat helpful

Mike

Post #1240966

wolfkillj

Posted Tuesday, January 24, 2012 9:39 AM

Right there with Babe

Group: General Forum Members
Last Login: Today @ 2:04 PM
Points: 774, Visits: 1,503

mjbriggs03 (1/24/2012)

Hi Mike,

I noted that you are executing that query against an Oracle DB, but wanted to point out for others' sake that, while this syntax yields the desired results, it has some significant performance drawbacks in SQL Server queries. See this seminal blog post on the topic:

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

Does Oracle handle this kind of query syntax differently?

Regards,

Jason

I noted your comment that you are executing that query against an Oracal

Post #1240989

mjbriggs03

Posted Tuesday, January 24, 2012 9:54 AM

Valued Member

Group: General Forum Members
Last Login: Saturday, January 26, 2013 11:29 AM
Points: 57, Visits: 93

Good question and I'm not sure haha. My query is about 32,000 characters long with nested selects, joins, etc. A real doozy. I've added two parameters thus far and run is only about 2-3 seconds. That's acceptable to me. Do you think it could run faster? I'm all ears haha.

Post #1240999

« Prev Topic | Next Topic »

24 posts, Page 1 of 3

1 2 3 »»»

Permissions