none
SSRS Rpeort Parameter issue.

    Question

  • Hi

    I am designging one report having around 6 parameters basically this SIX parameters are not required so we are just giving them user so that if they need they can use so they can be called as optional parameters.

    1. If they dont select any thing then it means all the values as per query should be displayed none of them should be filtered.

    2.Lets say the parameters are (EmpName, EmpID, Location, State) If they all three are blanks then all data according to the query should be displayed. If user selects data for EmpName and remaining blank then filter should be applied for only the EmpName remaing should be displayed

    Eg: Here is the Table

    If i select EmpName(Tom) and Keep remaing parameter blank then filter should be applied for only EmpName the remainig Parameters should take all the values and display

    Tom 3565042 mountainside NJ

    Tom 3565043 Winston         MO

    This values should be displayed

    My Query

    Select * FROm EmpTable WHERE EmpName IN (@EmapName) AND EmpID IN (@EmpID)

    for this query its asking for the both the parameters so it should not cpmpalsury ask for values it should take all the names including null values if it is kept blank so please help me on this issue it will be more help full for me if answered soon.

    Thanks

    Hemanth

     

    hemanth

    Tuesday, March 26, 2013 4:16 PM

Answers

  • Hi Finally i solved the issue. here is sol

    Select * FROM Table1

    Where column1 IN (Use split string function here ) OR @Param1 IS NULL

    In SSRS report data set properties in parameters we have to do like this 

    IFF(Parameter = "", NOTHING, Parameter)

    Parameter properties keep one available value as blank so that when they select blank automatically it takes all the values obtained for the query.

    thanks for your ans guys it was very helpfull i used different ways and finally solved the issue


    hemanth

    • Marked as answer by hemanth1618 Tuesday, April 02, 2013 3:01 AM
    Tuesday, April 02, 2013 3:01 AM

All replies

  • Hi,

    First, check "Allow Blank Value" under parameter properties, general .

    Second, modify your query to be an expression like this (include the = sign):

    ="select * from emptable where empname in ('" & Join(Parameters!EmapName.Value,"','") & "') AND EmpID IN ('" & Join(Parameters!EmpID.Value,"','") & "')"

    If employee number is numeric, remove all the single quotes (') after the second IN keyword above.


    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z

    Tuesday, March 26, 2013 4:24 PM
  • Hi Krootz

    I am not using a query in the SSRS report i am using a SP so according to that i couldn't do that can you please give me a detailed explanation on this parametrrs issue

    I tried doing that but i am getting so many errors in the query here is the error msg

    Msg 102, Level 15, State 1, Procedure usp-930-Dental_Utilization_Analysis_Report, Line 38

    Incorrect syntax near '='.

    Msg 103, Level 15, State 4, Procedure usp-930-Dental_Utilization_Analysis_Report, Line 38

    The identifier that starts with 'SELECT DISTINCT

    CLD.CLAIM_ID AS Claim_ID,

    CASE

    WHEN MD.RELAT_CD = 'M' THEN CAST(MD.SUB_ID AS CHAR(7))

    ELSE CAST(MD' is too long. Maximum length is 128.

    Msg 103, Level 15, State 4, Procedure usp-930-Dental_Utilization_Analysis_Report, Line 89

    The identifier that starts with '')

     


    hemanth

    Tuesday, March 26, 2013 6:51 PM
  • I am confused. You said you were using a query for your dataset?

    Select * FROm EmpTable WHERE EmpName IN (@EmapName) AND EmpID IN (@EmpID)

    Or are you using a stored proc for your dataset? are the stored proc parameters mandatory or optional?


    Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    k r o o t z

    Tuesday, March 26, 2013 8:33 PM
  • I am using the SP in the SSRS Report and they are not mandatory they are optional parameters. I am using about 6 parameters they all are optional they should allow blank values because if the users doesn't enter any values then the report should display all possible values based on the query.

    Thanks

    Hemanth


    hemanth

    Tuesday, March 26, 2013 9:34 PM
  • And have you set the parameters on your report to allow null values?
    Wednesday, March 27, 2013 4:00 AM
  • And also to allow blank values so that you dont have to select anything at all
    Wednesday, March 27, 2013 4:00 AM
  • In your stored proc then your where clause can be something like

    Select * from Table1 T

    Where @param1 is null or T.field in @param1

    Wednesday, March 27, 2013 4:01 AM
  • But for multiple valued parameters in the SP i am not getting values when i use 'IN' 

    It is just taking only the top most value of the selection.


    hemanth

    Wednesday, March 27, 2013 4:07 AM
  • Hello,

    When create a multiple value parameter, all selected values of the parameter will be passed as ‘,’ (comma) separated string when preview the report .If you want to pass multiple value in WHERE caluse of the Stored Procedure, we should create a UDF to split the string value.

    Please refer to the following blog about the split function:
    http://blog.summitcloud.com/2010/01/multivalue-parameters-with-stored-procedures-in-ssrs-sql

    The following thread is about the same issue,please refer to:

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/0ead7ceb-3fdd-4625-aa82-1d4195f984b1/

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Wednesday, March 27, 2013 9:46 AM
  • Hi, 

    Try This :

    SELECT *
    FROM EmpTable
    WHERE EmpName IN 
    (CASE WHEN  (@EmapName IS NULL  OR @EmapName ='' OR @EmapName = ' ') THEN EmpName  END )
    	AND EmpID IN (CASE WHEN  (@EmpID IS NULL  OR @EmpID ='' OR @EmpID = ' ') THEN EmpID  END )
    	
     

    Regards

    http://simplesqlserver.wordpress.com


    Dj's | Please mark as answered or vote helpful if this post help resolved your issue. Thanks!

    Wednesday, March 27, 2013 1:05 PM
  • The above format is not working actually i will give my full view here

    I am using a stored proc for the dataset in the report it has about 6 parameter values all are multiselection expect the date parameters

    I have 4 parameters which i have to enter multiple values and all 6 parameters should be optional parameters.

    my Stored Proc structure is

    Create Proc Example1

    (

    @Parameter1 AS VARCHAR(MAX),

    @Parameter2 AS VARCHAR(MAX),

    @Parameter3 AS VARCHAR(MAX),

    @Parameter4 AS VARCHAR(MAX),

    @ParameterDate1 DATETIME,

    @ParameterDate2 DATETIME

    )

    AS

    BEGIN

    CREATE TEMPTABLE1

    (

    Column1 INT,

    Column2 VARCHAR(MAX)

    )

    INSERT IN TEMPTABLE1

    SELECT * FROM SPLITSTRING(@Parameter1, ’,’ ,1 ,0)

    So on for 4 paramters which we need multiple values

    SET NCOUNT ON;

    SELECT Column1, Column2, Column3 ,Column4, Column5, Column6

    FROM Table1 JOIN Table2 <condition>

    JOIN Table3 <Condition>

    Join Table4 <Condition>

    WHERE Table3.Column1 IN (SELECT Column2 FROM TEMPTABLE1)

    AND table4.column IN (SELECT column2 FROM TEMPTABLE2)…………….up to 4 conditions including dates

    END

    Dropping Temp tables

    GO

    This is the structure of my stored procedure now I will give my spec

    For the above query when I select the data and run the report the data is validating according to the selection.

    But what I need is if I don’t select any parameter values that mean if I keep blank then it should display all the possible values for the query.

    If I select only one and keep blank remaining parameters then all the condition should satisfy FOR THE SELECTED PARAMETERS and it should take all possible values for the remaining parameters.

    I think we have some null values too for those columns used for parameters. When I am trying to keep allow nulls option in report level it is not letting me to do when the allow multiple values option is checked so this is my whole issue  

    Now can you guys please help me in query level and report level? Quick answers will be appreciated


    hemanth

    Wednesday, March 27, 2013 6:48 PM
  • Hi Finally i solved the issue. here is sol

    Select * FROM Table1

    Where column1 IN (Use split string function here ) OR @Param1 IS NULL

    In SSRS report data set properties in parameters we have to do like this 

    IFF(Parameter = "", NOTHING, Parameter)

    Parameter properties keep one available value as blank so that when they select blank automatically it takes all the values obtained for the query.

    thanks for your ans guys it was very helpfull i used different ways and finally solved the issue


    hemanth

    • Marked as answer by hemanth1618 Tuesday, April 02, 2013 3:01 AM
    Tuesday, April 02, 2013 3:01 AM