must declare the scalar variable in SQL Reporting Services 2005

apitech
apitech used Ask the Experts™
on
Below are my three datasets (DYNAMICS, Company, ItemNumber) followed by the SQL view that I created for my report.  

If I choose more than one ItemNumber in the drop-down list of my parameter in the Preview tab of the report, I get the "must declare the scalar variable "ItemNumber"".  If I only choose one Item Number, though, I do not get an error.

So, this error is preventing me from choosing more than one item in my parameter when I preview my report.

Please help!
DYNAMICS dataset:
select * from BPINKTONER
where Company = @Company and
ItemNumber = @ItemNumber
 
Company dataset:
select DISTINCT Company from BPINKTONER ORDER BY Company
 
ItemNumber dataset:
select DISTINCT ItemNumber from BPINKTONER ORDER BY ItemNumber
 
BPINKTONER view that is in the DYNAMICS dataset:
select 'Galaxy Media' As [Company],
IV30300.ITEMNMBR As [ItemNumber], IV00101.ITEMDESC As [ItemDescription], 
CASE WHEN IV30300.HSTMODUL = 'CL' THEN 'Y' ELSE 'N' END As [In-Transit?(Y/N)],
CASE WHEN IV00101.ITMTRKOP = '1' THEN 'Lot Numbers' ELSE 'None' END As [LotTracking],
IV30300.TRXLOCTN As [StockingLocation],
IV30300.TRNSTLOC As [TransferToLocation], 
CASE WHEN IV30300.DOCTYPE = '1' THEN 'Adjustment'
WHEN IV30300.DOCTYPE = '2' THEN 'Variance'
WHEN IV30300.DOCTYPE = '3' THEN 'Transfer'
WHEN IV30300.DOCTYPE = '4' THEN 'Purchasing Receipt'
WHEN IV30300.DOCTYPE = '5' THEN 'Sales Return'
WHEN IV30300.DOCTYPE = '6' THEN 'Sales'
WHEN IV30300.DOCTYPE = '7' THEN 'Assembly'
END
AS [DocumentType],
IV30300.DOCDATE As [DocumentDate], COALESCE (SY03900.TXTFIELD, '') As [TrackingNumber],
COALESCE (SOP30200.CUSTNMBR, '') As [CustomerNumber], 
COALESCE (SOP30200.CUSTNAME, '') As [CustomerName],
COALESCE (SOP30200.ShipToName, '') As [ShipToName],
IV30300.TRXQTY As [Quantity], IV30300.UOFM As [UOfM], IV30300.UNITCOST As [UnitCost],
IV30300.EXTDCOST As [ExtendedCost], 
CASE WHEN IV30300.TRFQTYTY = '1' Then 'On Hand'
WHEN IV30300.TRFQTYTY = '2' Then 'Returned'
WHEN IV30300.TRFQTYTY = '3' Then 'In Use'
WHEN IV30300.TRFQTYTY = '4' Then 'In Service'
WHEN IV30300.TRFQTYTY = '5' Then 'Damaged'
Else 'N/A'
End
As [TransferFromReason],
CASE WHEN IV30300.TRTQTYTY = '1' Then 'On Hand'
WHEN IV30300.TRTQTYTY = '2' Then 'Returned'
WHEN IV30300.TRTQTYTY = '3' Then 'In Use'
WHEN IV30300.TRTQTYTY = '4' Then 'In Service'
WHEN IV30300.TRTQTYTY = '5' Then 'Damaged'
Else 'N/A'
End
As [TransferToReason]
 from UNIV..IV30300
 INNER JOIN UNIV..IV00101 
 on 
UNIV..IV30300.ITEMNMBR = UNIV..IV00101.ITEMNMBR
 LEFT JOIN UNIV..IV40400 
 on 
 UNIV..IV00101.ITMCLSCD =  UNIV..IV40400.ITMCLSCD
 LEFT JOIN UNIV..SOP30200
 on 
 UNIV..SOP30200.SOPNUMBE = UNIV..IV30300.DOCNUMBR
 LEFT JOIN UNIV..SY03900
 on 
 UNIV..SOP30200.NOTEINDX =  UNIV..SY03900.NOTEINDX
 WHERE IV30300.TRXLOCTN IN ('TU', 'P', 'H')
 and IV40400.ITMCLSCD IN ('TONER', 'LOT', 'DEF', 'DEFAULT')
 and IV30300.DOCTYPE IN (1, 2, 3, 4, 5, 6, 7)
--and IV30300.DOCDATE BETWEEN '2009-06-01 00:00:00.000' and '2009-06-14 00:00:00.000'
and (IV00101.ITEMDESC LIKE '%BTL%' or IV00101.ITEMDESC LIKE '%BOTTLE%'
or IV00101.ITEMDESC LIKE '%TONER%'
or IV00101.ITEMDESC LIKE '%TNR%')
and IV30300.ITEMNMBR <> 'SUP8000103' and IV30300.ITEMNMBR <> 'OT9800' AND
IV30300.ITEMNMBR <> 'SUP6000103' AND IV30300.ITEMNMBR <> 'TONER9600' AND
IV30300.ITEMNMBR <> 'SUP5000103' AND IV30300.ITEMNMBR <> 'TONER9800' AND
IV30300.ITEMNMBR <> 'SUP3000103'
UNION ALL
select 'PRECISE CABINET CO.' As [Company],
IV30300.ITEMNMBR As [ItemNumber], IV00101.ITEMDESC As [ItemDescription], 
CASE WHEN IV30300.HSTMODUL = 'CL' THEN 'Y' ELSE 'N' END As [In-Transit?(Y/N)],
CASE WHEN IV00101.ITMTRKOP = '1' THEN 'Lot Numbers' ELSE 'None' END As [LotTracking],
IV30300.TRXLOCTN As [StockingLocation],
IV30300.TRNSTLOC As [TransferToLocation], 
CASE WHEN IV30300.DOCTYPE = '1' THEN 'Adjustment'
WHEN IV30300.DOCTYPE = '2' THEN 'Variance'
WHEN IV30300.DOCTYPE = '3' THEN 'Transfer'
WHEN IV30300.DOCTYPE = '4' THEN 'Purchasing Receipt'
WHEN IV30300.DOCTYPE = '5' THEN 'Sales Return'
WHEN IV30300.DOCTYPE = '6' THEN 'Sales'
WHEN IV30300.DOCTYPE = '7' THEN 'Assembly'
END
AS [DocumentType],
IV30300.DOCDATE As [DocumentDate], COALESCE (SY03900.TXTFIELD, '') As [TrackingNumber],
COALESCE (SOP30200.CUSTNMBR, '') As [CustomerNumber], 
COALESCE (SOP30200.CUSTNAME, '') As [CustomerName],
COALESCE (SOP30200.ShipToName, '') As [ShipToName],
IV30300.TRXQTY As [Quantity], IV30300.UOFM As [UOfM], IV30300.UNITCOST As [UnitCost],
IV30300.EXTDCOST As [ExtendedCost],
CASE WHEN IV30300.TRFQTYTY = '1' Then 'On Hand'
WHEN IV30300.TRFQTYTY = '2' Then 'Returned'
WHEN IV30300.TRFQTYTY = '3' Then 'In Use'
WHEN IV30300.TRFQTYTY = '4' Then 'In Service'
WHEN IV30300.TRFQTYTY = '5' Then 'Damaged'
Else 'N/A'
End
As [TransferFromReason],
CASE WHEN IV30300.TRTQTYTY = '1' Then 'On Hand'
WHEN IV30300.TRTQTYTY = '2' Then 'Returned'
WHEN IV30300.TRTQTYTY = '3' Then 'In Use'
WHEN IV30300.TRTQTYTY = '4' Then 'In Service'
WHEN IV30300.TRTQTYTY = '5' Then 'Damaged'
Else 'N/A'
End
As [TransferToReason]
 from PAPER..IV30300
 INNER JOIN PAPER..IV00101 
 on 
PAPER..IV30300.ITEMNMBR = PAPER..IV00101.ITEMNMBR
 LEFT JOIN PAPER..IV40400 
 on 
 PAPER..IV00101.ITMCLSCD =  PAPER..IV40400.ITMCLSCD
 LEFT JOIN PAPER..SOP30200
 on 
 PAPER..SOP30200.SOPNUMBE = PAPER..IV30300.DOCNUMBR
 LEFT JOIN PAPER..SY03900
 on 
 PAPER..SOP30200.NOTEINDX =  PAPER..SY03900.NOTEINDX
 WHERE IV30300.TRXLOCTN IN ('A', 'T')
 and IV40400.ITMCLSCD IN ('TONER', 'LOT', 'DEF', 'DEFAULT')
 and IV30300.DOCTYPE IN (1, 2, 3, 4, 5, 6, 7)
--and IV30300.DOCDATE BETWEEN '2009-06-01 00:00:00.000' and '2009-06-14 00:00:00.000'
and (IV00101.ITEMDESC LIKE '%BTL%' or IV00101.ITEMDESC LIKE '%BOTTLE%'
or IV00101.ITEMDESC LIKE '%TONER%'
or IV00101.ITEMDESC LIKE '%TNR%')
AND IV30300.ITEMNMBR <> 'TONER9400' AND IV30300.ITEMNMBR <> 'TONER9600'
AND IV30300.ITEMNMBR <> 'TONER9800'
UNION ALL
select 'CNV Inc.' As [Company],
IV30300.ITEMNMBR As [ItemNumber], IV00101.ITEMDESC As [ItemDescription], 
CASE WHEN IV30300.HSTMODUL = 'CL' THEN 'Y' ELSE 'N' END As [In-Transit?(Y/N)],
CASE WHEN IV00101.ITMTRKOP = '1' THEN 'Lot Numbers' ELSE 'None' END As [LotTracking],
IV30300.TRXLOCTN As [StockingLocation],
IV30300.TRNSTLOC As [TransferToLocation], 
CASE WHEN IV30300.DOCTYPE = '1' THEN 'Adjustment'
WHEN IV30300.DOCTYPE = '2' THEN 'Variance'
WHEN IV30300.DOCTYPE = '3' THEN 'Transfer'
WHEN IV30300.DOCTYPE = '4' THEN 'Purchasing Receipt'
WHEN IV30300.DOCTYPE = '5' THEN 'Sales Return'
WHEN IV30300.DOCTYPE = '6' THEN 'Sales'
WHEN IV30300.DOCTYPE = '7' THEN 'Assembly'
END
AS [DocumentType],
IV30300.DOCDATE As [DocumentDate], COALESCE (SY03900.TXTFIELD, '') As [TrackingNumber],
COALESCE (SOP30200.CUSTNMBR, '') As [CustomerNumber], 
COALESCE (SOP30200.CUSTNAME, '') As [CustomerName],
COALESCE (SOP30200.ShipToName, '') As [ShipToName],
IV30300.TRXQTY As [Quantity], IV30300.UOFM As [UOfM], IV30300.UNITCOST As [UnitCost],
IV30300.EXTDCOST As [ExtendedCost],
CASE WHEN IV30300.TRFQTYTY = '1' Then 'On Hand'
WHEN IV30300.TRFQTYTY = '2' Then 'Returned'
WHEN IV30300.TRFQTYTY = '3' Then 'In Use'
WHEN IV30300.TRFQTYTY = '4' Then 'In Service'
WHEN IV30300.TRFQTYTY = '5' Then 'Damaged'
Else 'N/A'
End
As [TransferFromReason],
CASE WHEN IV30300.TRTQTYTY = '1' Then 'On Hand'
WHEN IV30300.TRTQTYTY = '2' Then 'Returned'
WHEN IV30300.TRTQTYTY = '3' Then 'In Use'
WHEN IV30300.TRTQTYTY = '4' Then 'In Service'
WHEN IV30300.TRTQTYTY = '5' Then 'Damaged'
Else 'N/A'
End
As [TransferToReason]
 from NEWCO..IV30300
 INNER JOIN NEWCO..IV00101 
 on 
NEWCO..IV30300.ITEMNMBR = NEWCO..IV00101.ITEMNMBR
 LEFT JOIN NEWCO..IV40400 
 on 
 NEWCO..IV00101.ITMCLSCD =  NEWCO..IV40400.ITMCLSCD
 LEFT JOIN NEWCO..SOP30200
 on 
 NEWCO..SOP30200.SOPNUMBE = NEWCO..IV30300.DOCNUMBR
 LEFT JOIN NEWCO..SY03900
 on 
 NEWCO..SOP30200.NOTEINDX =  NEWCO..SY03900.NOTEINDX
 WHERE IV30300.TRXLOCTN IN ('G', 'CH', 'CO', 'D', 'S')
 and IV40400.ITMCLSCD IN ('TONER', 'LOT', 'DEF', 'DEFAULT')
 and IV30300.DOCTYPE IN (1, 2, 3, 4, 5, 6)
--and IV30300.DOCDATE BETWEEN '2009-06-01 00:00:00.000' and '2009-06-14 00:00:00.000'
and (IV00101.ITEMDESC LIKE '%BTL%' or IV00101.ITEMDESC LIKE '%BOTTLE%'
or IV00101.ITEMDESC LIKE '%TONER%'
or IV00101.ITEMDESC LIKE '%TNR%'
or IV00101.ITEMDESC LIKE '%WIPE%'
or IV00101.ITEMDESC LIKE '%SWAB%'
or IV00101.ITEMDESC LIKE '%CLOTH%'
or IV00101.ITEMDESC LIKE '%BLACK%'
or IV00101.ITEMDESC LIKE '%MAGENTA%'
or IV00101.ITEMDESC LIKE '%CYAN%'
or IV00101.ITEMDESC LIKE '%YELLOW%'
OR IV00101.ITEMDESC LIKE '%YEL%ML%'
OR IV00101.ITEMDESC LIKE '%PRINTHEAD%'
OR IV00101.ITEMDESC LIKE '%CARTRIDGE'
OR IV00101.ITEMDESC LIKE '%CLEANING%'
OR IV00101.ITEMDESC LIKE '%ECO_USER_KIT%'
OR IV00101.ITEMDESC LIKE '%MAINT_KIT%'
OR IV00101.ITEMDESC LIKE '%CORE_ADAPTER%'
OR IV00101.ITEMDESC LIKE '%BLK_3M%'
OR IV00101.ITEMDESC LIKE '%CY_3M%'
OR IV00101.ITEMDESC LIKE '%MAG_3M%'
OR IV00101.ITEMDESC LIKE '%STATION_PAD%'
OR IV00101.ITEMDESC LIKE '%FILTER%'
OR IV00101.ITEMDESC LIKE '%FLUSH%'
OR IV00101.ITEMDESC LIKE '%ADHESION_MASTER%'
OR IV00101.ITEMDESC LIKE '%CALIBRATION_SHEET%'
OR IV00101.ITEMDESC LIKE '%CWAVE%'
OR IV00101.ITEMDESC LIKE '%ABSORBENT%'
OR IV00101.ITEMDESC LIKE '%CUTTER_BLADE%')

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
You need create a function that will parse the vaule for itemnumber into multiple values when you select more than one item from the list.
Create the below fucntion in your database and call your view like this:
select * from BPINKTONER
where Company IN (Selet Param from  dbo.ParseVal (@Company, ',') and
ItemNumber IN (Selet Param from  dbo.ParseVal (@ItemNumber, ',')
 

CREATE FUNCTION [dbo].[ParseVal]
   (@RepParam nvarchar(4000), @Delim char(1)= ',')
RETURNS @Values TABLE (RecordID int, Param nvarchar(4000))AS
  BEGIN
  DECLARE @chrind INT
  DECLARE @id INT	
  DECLARE @Piece nvarchar(50)
  SELECT @chrind = 1
  SELECT @id = 0 
  WHILE @chrind > 0
    BEGIN
      SET @id = @id + 1	
      SELECT @chrind = CHARINDEX(@Delim,@RepParam)
      IF @chrind  > 0
        SELECT @Piece = LEFT(@RepParam,@chrind - 1)
      ELSE
        SELECT @Piece = @RepParam
      INSERT  @Values(RecordID, Param) VALUES(@id, @Piece)
      SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)
      IF LEN(@RepParam) = 0 BREAK
    END
  RETURN
END
 
 
	

Open in new window

Author

Commented:
I created this function and placed it in my database along with the syntax in the Data tab that you suggested in calling my view.

But, when I select more than one item, I get the following error:

ParseVal has too many arguments specified.

Help!
Commented:
Disregard.....I figured it out.  No function was necessary.

Here is what I did:

select * from BPINKTONER
where Company IN (@Company, ',') and
ItemNumber IN (@ItemNumber, ',')

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial