user2040021 user2040021 - 7 months ago 14
SQL Question

Twp multi select parameters in SSRS not displaying results for when selected more than one value

I have an issue with SSRS report not populating second multi value drop down list which is dependent upon first multi-valued drop down list and hence not populating report.

So, what I am trying to do here is populating SSRS report for construction contractors from selected cities from first drop down list and selected skills sets from second drop down list which gets populated once we select Cities drop down list.

But my issue is when I select one city the skill's drop-down list get populated without any problem, when I select more than one city , my skill's drop down show no value.

My code is as follows. I have 3 datasets.

Dataset 1 (Main)

ALTER PROCEDURE [dbo].[RPT_ADM_Total_Contractors]

@Office nvarchar(255),
@Start datetime,
@End datetime,
@Servicetype nvarchar(255)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT fs.skillname,fa.NEW_ContractorIDNAME AS ContractorName, fa.new_Contractorstatusname AS Contractorstatus,
fa.createdon AS CreatedDate, fa.new_addresscity AS City
FROM
Filterednew_contrator fa
join
FilteredService fs
on fa.new_service = fs.serviceid

WHERE
fa.new_sitename IN (@Office )
AND fs.skillname IN (@Servicetype)


END


Dataset 2

ALTER PROCEDURE [dbo].[RPT_ADM_Total_Contractors_Officelist]
@Start date,
@End date
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT DISTINCT fa.new_sitename
FROM
Filterednew_contrator fa
join
FilteredService fs
on fa.new_service = fs.serviceid
WHERE
new_admitdate between @Start and @End
AND new_sitename like 'Office%'
fa.new_service is not null

END


Dataset 3

ALTER PROCEDURE [dbo].[RPT_ADM_Total_Contractors_Servicetypelist]

@Office nvarchar(255)
--@Start datetime,
--@End datetime

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

SELECT DISTINCT fs.skillname
FROM
Filterednew_contrator fa
join
FilteredService fs
on fa.new_service = fs.serviceid
WHERE
fa.new_sitename in (@Office)
order by fs.skillname


END


I have 4 paramters

@Start date/time
@End date/time
@Office "Allow Multivalue" AND gets populated from dataset 2
@Servicetype "Allow Mutivalue" AND gets populated from dataset 3


I tried using =Join(Parameters!Office.Value,",") but didn't work.
I hope someone ca help me out with this.

Thanks.

Answer

You have 2 options.

Option 1. Make the SSRS dropdown a single value dropdown.

If that is not a viable option, then here is option 2:

You are going to have to make the data type for the @Office and @Servicetype parameters in the stored procedure something like an (n)varchar(1000); something long enough to handle a string with all possible values. Then you have to split the string into individual values so you can use them in the IN clause. Here is a split UDF I have used in the past (and posted on this site several times).

CREATE FUNCTION [dbo].[udf_Split] 
   (  @List      varchar(8000), 
      @Delimiter varchar(5)
   ) 
   RETURNS @TableOfValues table 
      (  RowID   smallint IDENTITY(1,1), 
         [Value] varchar(100) 
      ) 
AS 
   BEGIN

      DECLARE @LenString int 

      WHILE len( @List ) > 0 
         BEGIN 

            SELECT @LenString = 
               (CASE charindex( @Delimiter, @List ) 
                   WHEN 0 THEN len( @List ) 
                   ELSE ( charindex( @Delimiter, @List ) -1 )
                END
               ) 

            INSERT INTO @TableOfValues 
               SELECT substring( @List, 1, @LenString )

            SELECT @List = 
               (CASE ( len( @List ) - @LenString ) 
                   WHEN 0 THEN '' 
                   ELSE right( @List, len( @List ) - @LenString - 1 ) 
                END
               ) 
         END

      RETURN 

   END 

Once you have that in place (in your database), you can change your SP code to look like this in the WHERE clause.

WHERE 
    fa.new_sitename IN (SELECT value FROM dbo.udf_Split(@Office, ','))
    AND fs.skillname IN (SELECT value FROM dbo.udf_Split(@Servicetype, ','))

Enjoy!

Comments