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 ««123»»

How to Ignore Report Parameter Expand / Collapse
Author
Message
Posted Tuesday, January 24, 2012 10:13 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 4,185, Visits: 4,264
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/

Post #1241019
Posted Tuesday, January 24, 2012 10:44 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:19 PM
Points: 1,221, Visits: 2,543
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
Post #1241041
Posted Tuesday, January 24, 2012 11:13 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:19 PM
Points: 1,221, Visits: 2,543
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
Post #1241060
Posted Tuesday, January 24, 2012 12:00 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 4,185, Visits: 4,264
The delimiter is not working.

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/

Post #1241110
Posted Tuesday, January 24, 2012 1:36 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 4,185, Visits: 4,264
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/

Post #1241177
Posted Tuesday, January 24, 2012 2:14 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:19 PM
Points: 1,221, Visits: 2,543
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
Post #1241212
Posted Tuesday, January 24, 2012 3:11 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 4,185, Visits: 4,264
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/

Post #1241252
Posted Tuesday, January 24, 2012 3:58 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 4,185, Visits: 4,264
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/

Post #1241265
Posted Wednesday, January 25, 2012 12:40 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 3:19 PM
Points: 1,221, Visits: 2,543
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
Post #1241721
Posted Wednesday, January 25, 2012 12:50 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 3:55 PM
Points: 4,185, Visits: 4,264
The function is wrong.

Do you have a tested solution with that function?


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 #1241726
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse