I have these two lines
,SUM (CASE WHEN (DATEDIFF(DAY,Shopify_Ordered,Confirmed) >= 1)THEN 1 ELSE 0 END) OVER () Orders1DayNotShipped
,dbo.GROUP_CONCAT( (CASE WHEN (DATEDIFF(DAY,Shopify_Ordered,Confirmed) >= 1)THEN Customer_Purchase_Order_Number END)) OVER () Orders1DayNotShippedString
The first line counts the number of orders that are One Day Not shipped. The second line uses a Group_Concat function (found here: https://archive.codeplex.com/?p=groupconcat) to return a list of the order numbers of those late orders.
Here is a sample result: 91149220,91155318,91155319, etc
Now what I want to do is take that result/field and then pass it to another report through SSRS. I can pass this parameter to the other report easily, I just need some help making it actually work. I want the second report to take those order numbers and show me all those orders.
So for my second report, I set up the parameter and this is what I tried to do for my where clause
WHERE 1=1
AND (@LocalOrderList IS NULL OR (Customer_Purchase_Order_Number in dbo.[Z_N_CSVToList](@LocalOrderList)))
Z_N_CSVToList is a simpe function that breaks apart a string into a list. This is it:
ALTER FUNCTION [dbo].[Z_N_CSVToList] (@CSV varchar(MAX))
RETURNS @Result TABLE (Value varchar(300))
AS
BEGIN
DECLARE @List TABLE
(
Value varchar(300)
)
DECLARE
@Value varchar(300),
@Pos int
SET @CSV = LTRIM(RTRIM(@CSV))+ ','
SET @Pos = CHARINDEX(',', @CSV, 1)
IF REPLACE(@CSV, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @Value = LTRIM(RTRIM(LEFT(@CSV, @Pos - 1)))
IF @Value <> ''
INSERT INTO @List (Value) VALUES (@Value)
SET @CSV = RIGHT(@CSV, LEN(@CSV) - @Pos)
SET @Pos = CHARINDEX(',', @CSV, 1)
END
END
INSERT @Result
SELECT
Value
FROM
@List
RETURN
END
How do I make this work? Running it as it is right now gives me the error “Incorrect syntax near ‘dbo’.”
Do I have to add quotes around the order numbers in the group concat line?
EDIT: I figured it out. If I change my where clause to this, it works.
AND (@LocalOrderList IS NULL OR (Customer_Purchase_Order_Number in (Select * from dbo.[Z_N_CSVToList](@LocalOrderList))))
But now my report only returns the first order from the list, not all of them. Any idea why?
Advertisement
Answer
should be something like this using exists
AND (@LocalOrderList IS NULL OR
(exists (select 1 from dbo.[Z_N_CSVToList](@LocalOrderList) v
where Customer_Purchase_Order_Number = v.value
)))