[Drew Rosen]

I have created a stored procedure (which is attached) with a parameter for vendor id.  I also have a stored procedure to query for the available vendor id from the pm00200 table.  The SSRS report uses these 2 stored procedures as the data sets.  When I choose multiple vendor id from the parameter it will only print the first vendor’s data.  I changed the stored procedure and commented out the vendorid parameter and ran the ssrs report without a parameter and all the vendor’s data printed.  When I added the parameter back in the where clause only the first vendor prints.  The SSRS report works if you choose 1 vendor at a time.  You cannot choose more than 1 vendor, if you do the first vendor on the list is the only that prints.  Thanks in advance for your help.  I have also included a list of the data of all vendors.  Drew Rosen 910-221-3561

——————————
Drew Rosen
Carolina Services of Fayetteville, Inc
Fayetteville NC
——————————

[Tom Schroder]

I believe the issue may be with your ParseMultiValuedParameter function.  I used all your code, removing just the non-GP stuff and the transaction description from the WHERE clause, and changed the function from ParseMultiValuedParameter to seeSplitString, and I get multiple vendors.  You should have the seeSplitString function ion your GP database.

——————————
Tom Schroder
Heartland Business Systems
Little Chute WI
——————————
——————————————-

[Blair Christensen]

Typically, dealing with muti-pick drop-downs in SSRS is substantially more difficult than just single-pick drop-downs.  Of course you have to make the filter a multi-select, but the tricky part is usually in modifying your WHERE statement to parse out the string of individual conditions from the filter.  Here’s one example of what I used:

where (d.RegionId in (select intValue from dbo.SplitDelimitedIntegers(',',@Region)) or ISNULL(@Region,'0') = '0')

dbo.SplitDelimitedIntegers is a function that will take whatever gets passed in from the SSRS report filter and split it into individual arguments based on the separator (the comma) and then use those to construct the necessary list for the WHERE IN statement.  The rest is a catchall which returns everything if the user selects the “ALL” option (configured in the drop-down list to return 0).

Hope that helps!

——————————
Blair Christensen
Database Administrator
Oppenheimer Companies, Inc.
Boise ID
——————————
——————————————-

[Drew Rosen]

Solution

On the stored procedure that is used to select the values for the vendorid parameter.  I needed to add the following function:

LTRIM(RTRIM(vendorid)) AS vendorid

so the stored procedure is the following:

USE [ScribeData]
GO
/****** Object: StoredProcedure [dbo].[rpt_vendor_repair_sel_par] Script Date: 4/29/2019 9:51:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[rpt_vendor_repair_sel_par]

as

select
LTRIM(RTRIM(vendorid)) AS vendorid
,vendname
from carol.dbo.pm00200
where ((VNDCLSID not in (‘ap trade’,’claims’,’noname’,’rent’,’sales’,’utilities’,’ ‘,
‘jwilliams’,’comdata’,’TATESTEVEN’,’handy man’,’thomas’) or VNDCLSID is null) and
VENDSTTS =1)
order by VENDNAME

——————————
Drew Rosen
Carolina Services of Fayetteville, Inc
Fayetteville NC
——————————
——————————————-