SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Ignore Report Parameter


How to Ignore Report Parameter

Author
Message
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28082 Visits: 4895
wolfkillj (1/13/2012)
[quote]Welsh Corgi (1/11/2012)
After you have created the "splitter" function, your query syntax would look like this:

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



I tried that but I still get the same error in SSRS.

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/
wolfkillj
wolfkillj
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2772 Visits: 2582
mjbriggs03 (1/24/2012)
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.


Sorry, Mike - can't give you much help with tuning an Oracle query. It sounds like your query is doing a tremendous amount of work. It might be worth reviewing the business requirements and figuring out whether a "divide and conquer" strategy would help. In many situations, I've found that the costs of building, storing, and maintaining tables of precomputed results can be lower than doing a bunch of aggregations or computations on the fly. You know best your business requirements and available resources, but don't be afraid to consider creative ways to solve problems!

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
wolfkillj
wolfkillj
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2772 Visits: 2582
Welsh Corgi (1/24/2012)
wolfkillj (1/13/2012)
[quote]Welsh Corgi (1/11/2012)
After you have created the "splitter" function, your query syntax would look like this:

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



I tried that but I still get the same error in SSRS.


So if I understand your requirements correctly, you want the user to be able to execute the report without making a selection in the State parameter drop-down list, and if the user does this, the report should return results without regard to the value in the State column (i.e., the report will not be filtered by State), yes?

If so, you could set the default value of the State parameter to '-ALL-' in SSRS so that the user can view the report without making a selection in the drop-down box, then rewrite the WHERE clause in the query like this:

WHERE '-ALL-' IN (select Item from dbo.DelimitedSplit8K(@State, ',')) OR State in (select Item from dbo.DelimitedSplit8K(@State, ','))

You should have discovered that a multi-value parameter cannot have a NULL value (at least, not in SSRS 2008 - I'm pretty sure it's the same in SSRS 2005). Adding a NULL value to the drop-down list for the SSRS parameter will not help with this technique because the condition

WHERE State in (NULL)

resolves to UNKNOWN (neither TRUE nor FALSE) , and because all conditions in the WHERE clause must resolve to TRUE for any row to be returned, you will never get any rows.

If that doesn't help, could you post the full stored procedure code and more details about your SSRS parameter (is the drop-down list populated by a query, what do you have for the default value, etc.)?

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28082 Visits: 4895
The delimiter is not working.w00t

I have the following in my Stored Procedure:


AND State IN (SELECT State FROM dbo.Split_Multi_Value_Parameters (@State, ','))



I perform a DBCC INPUTBUFFER (SPID) and it parses as follows. I have not included all 50 states:


AND State = N'AL',N'AK'



The problem is that the delimiter function does not have it's intended behavior.

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/
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28082 Visits: 4895
This is the code for the function:


CREATE FUNCTION dbo.Split_Multi_Value_Parameters
/* This function is used to split up multi-value parameters */
(
@ItemList NVARCHAR(max),
@delimiter CHAR(1)
)
RETURNS @IDTable TABLE (Item NVARCHAR(100) collate database_default )
AS
BEGIN
DECLARE @tempItemList NVARCHAR(max)
SET @tempItemList = @ItemList

DECLARE @i INT
DECLARE @Item NVARCHAR(max)

SET @tempItemList = REPLACE (@tempItemList, @delimiter + ' ', @delimiter)
SET @i = CHARINDEX(@delimiter, @tempItemList)

WHILE (LEN(@tempItemList) > 0)
BEGIN
IF @i = 0
SET @Item = @tempItemList
ELSE
SET @Item = LEFT(@tempItemList, @i - 1)

INSERT INTO @IDTable(Item) VALUES(@Item)

IF @i = 0
SET @tempItemList = ''
ELSE
SET @tempItemList = RIGHT(@tempItemList, LEN(@tempItemList) - @i)

SET @i = CHARINDEX(@delimiter, @tempItemList)
END
RETURN
END



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/
wolfkillj
wolfkillj
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2772 Visits: 2582
Your splitter function returns the expected results, but the code in the stored proc should select the column Item from the function in the subselect in the WHERE clause, not State. Does that help?

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28082 Visits: 4895
Not really..

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/
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28082 Visits: 4895
wolfkillj (1/24/2012)
Your splitter function returns the expected results, but the code in the stored proc should select the column Item from the function in the subselect in the WHERE clause, not State. Does that help?


The function does not return the required results and there is a SELECT in the WHERE Clause.


WHERE Comp.Status = 'A'
AND State IN (SELECT State FROM dbo.Split(@State, ','))



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/
wolfkillj
wolfkillj
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2772 Visits: 2582
Welsh Corgi (1/24/2012)
wolfkillj (1/24/2012)
Your splitter function returns the expected results, but the code in the stored proc should select the column Item from the function in the subselect in the WHERE clause, not State. Does that help?


The function does not return the required results and there is a SELECT in the WHERE Clause.


WHERE Comp.Status = 'A'
AND State IN (SELECT State FROM dbo.Split(@State, ','))





Look at this line in the definition of your splitter function dbo.Split_Multi_Value_Params() that you posted above:

RETURNS @IDTable TABLE (Item NVARCHAR(100) collate database_default )

The function returns a table with a single column named Item. The code in your proc - WHERE State IN (SELECT State FROM dbo.Split_Multi_Value_Params(@State, ',')) - would generate error 207 - invalid column name 'State' (unless of course your function dbo.Split() in the code snippet you just posted is different from the function dbo.Split_Multi_Value_Params() that you previously posted). Have you ascertained that the code in your proc SELECTs the correct column name from the function?

Have you tested the function like this:
SELECT Item FROM dbo.Split_Multi_Value_Params('AZ,TN,NY', ',')
to see if it returns the expected output (as I did before I stated that it does)?

Do you get results when you EXECUTE the stored procedure directly in SSMS (or your preferred client software)?

If you confirm that you have corrected the small but significant mistake in the stored proc code, that the function returns a table with a column named Item populated the values from the delimited string, that you get results when you EXECUTE the stored procedure directly, and that you still do not get any results in your SSRS report, please post more information about how you are generating the report (i.e., in BIDS, with SSRS Report Manager, with the .NET Report Viewer control, by passing paramters to SSRS in the URL, etc.) and what happens when you try to view the report (e.g., an error message appears, the report appears to be running but never completes, etc.). Also, if you can SELECT @@ERROR on the same connection after you try to run the report, what is the output?

Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Welsh Corgi
Welsh Corgi
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28082 Visits: 4895
The function is wrong.

Do you have a tested solution with that function?w00t

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/
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