locked
MULTIPLE VALUES PASSING TO STORED PROCEDURE RRS feed

  • Question

  • Hi, 

    I have a following stored procedure, I am passing two parameters from the drop down list by using a different dataset. User is selecting multiple values from drop down list in reporting services from code a drop down and code b drop down.

    I have put the JOIN(Parameters!qual_codea.Value,",") in dataset property for both parameters. But it is only displaying first row in the report. In report I have used simple table and Drag Total, Qualification Code A and Qualification Code B in three columns respectively.

    Any Idea why it is not displaying the remaining data for other codes selectedfrom drop down. If i run a query in management studio by using in operation with multiple codes, it display multiple rows which is correct but it is not happening in reporting services.

    Any Idea?

    ALTER PROCEDURE [dbo].[Multiple_Qualification_Summary]
    (
    	 @qual_codea nvarchar(10),
    	 @qual_codeb nvarchar(10)
    )
    AS
    
    select DISTINCT 
    	 COUNT(b.PRN) as Total
    	,A.UKCC_Qualification_Code AS 'Qualification Code A'
    	,b.UKCC_Qualification_Code as 'Qualification Code B'
    from dbo.Practitioner_Register_Part_Status as a, dbo.Practitioner_Register_Part_Status as b, dbo.Qualification_Register_Part as c
    where a.PRN = b.PRN 
      and a.UKCC_Qualification_Code = @qual_codea
      and b.UKCC_Qualification_Code = @qual_codeb
      AND A.IsLatest = 1 AND B.IsLatest = 1
      AND A.UKCC_Qualification_Code = C.UKCC_Qualification_Code
      AND C.QUALIFICATION_ACTIVE_SWITCH = 1
     group by A.UKCC_Qualification_Code 
    	,b.UKCC_Qualification_Code 
    
    order by 1



    MH

    Monday, December 30, 2013 3:51 PM

Answers

All replies

  • Hi,

    Try like this - http://social.technet.microsoft.com/wiki/contents/articles/19621.ssrs-multi-valued-parameter-as-stored-procedure-input.aspx


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    • Proposed as answer by Katherine Xiong Tuesday, December 31, 2013 6:21 AM
    • Marked as answer by MustafaH Tuesday, December 31, 2013 2:39 PM
    Monday, December 30, 2013 4:14 PM
  • Hi Sathya,

    Thanks for your reply, do i need to change the stored procedure? According to your link it suggest i have to use XML?


    MH

    Monday, December 30, 2013 4:19 PM
  • Hi,

    CTE with XML part is one of the splitting technique , splitting delimited values into separate rows .

    For example : Multivalued parameter comes as '1,2,3' , we can split this string into rows and join with rows of destination table .


    sathya - www.allaboutmssql.com ** Mark as answered if my post solved your problem and Vote as helpful if my post was useful **.

    Monday, December 30, 2013 4:30 PM
  • Hi Sathya,

    Thanks for your reply, do i need to change the stored procedure? According to your link it suggest i have to use XML?


    MH

    Try this - Passing multi-value parameter in stored procedure 

    Shahfaisal Muhammed http://shahfaisalmuhammed.blogspot.com

    Monday, December 30, 2013 5:33 PM