locked
When selecting one from a parameter, it worked. When selecting multiple from a parameter, it creashed RRS feed

  • Question

  • Thank you in advance for your help.

    I have an integer parameter called @Employees.

    When I select one value from a parameter, it worked.  When I select multiple values from a parameter, the report failed.

    I am using a function called fnParseString_udf to separate my parameter by ",".

    My Query is in the stored procedure.

    Example stored procedure:

    CREATE PROCEDURE [cu].[spBICLDI]
     	@Employees INT
    
    AS
    
    SELECT 
    	
    'LoanNumberAccountNumber' = CONCAT(LoanNoteNumber,' - ', AccountNumber)
    	
    FROM TableA
    	
    WHERE @InsuranceType = CASE WHEN @InsuranceType IN ('CLDI') THEN CLDIIndicator
    			    WHEN @InsuranceType IN ('GAPIWS') THEN GAPIWSIndicator
    			END
    	
    AND LoanCreatedByUser IN (SELECT * FROM cu.fnBIparseString_udf(@Employees,','))


    Sample of Employees parameter:

    Name: Employees

    Prompt: Employees

    Data Type: Integer

    Allow Multiple Values = Yes

    Visible = Yes

    =====================================

    Available Values: Get Values from a query

    Dataset: Employees

    Value Field: TellerNumber

    Label Field: Name

    ======================================

    Default Values: Get Values from a query

    Dataset: Employees

    Value Field: TellerNumber

    Sample of data in Employees Dataset:

    Thursday, October 18, 2018 1:43 PM

Answers

  • Hi,

    How do you pass the parameter to the dataset?

    It has to be passed like this :

    =Join(Parameters!EmployeeParameter.Value, ",")

    And you have to change the datatype of @Employees in the stored procedure to VARCHAR(MAX).


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by GarinTiger Thursday, October 18, 2018 2:23 PM
    Thursday, October 18, 2018 1:59 PM

All replies

  • Hi,

    How do you pass the parameter to the dataset?

    It has to be passed like this :

    =Join(Parameters!EmployeeParameter.Value, ",")

    And you have to change the datatype of @Employees in the stored procedure to VARCHAR(MAX).


    Ousama EL HOR

    [If a post helps to resolve your issue, please click the "Mark as Answer"  of that post or click Answered "Vote as helpful"  button of that post. By marking a post as Answered or Helpful, you help others find the answer faster. ]


     [User Page]     [MSDN Page]     [Blog]     [Linkedin]

    • Marked as answer by GarinTiger Thursday, October 18, 2018 2:23 PM
    Thursday, October 18, 2018 1:59 PM
  • Thank you.

    Changing the @Employees datatype to VARCHAR(MAX) works perfectly.

    Thank you.

    Thursday, October 18, 2018 2:24 PM