none
SSRS Parameter should contain a operator

    Question

  • Hi

    I have a doubt regarding the parameters selection.

    Case 1: If i am working in bank industries i have check details report which having a checkAmount parameter actually what we do is

    SELECT * FROM Table1 WHERE CheckAmount = @CheckAmount

    In this condition we develop a report if user enters $100 then they will get check details having amount of 100$.

    What i need is if user want to get <100 or >100 Check amounts we have to change the quiry and design the report but what if we need to make user selecting the operation by himself and entering the value that means if want > amounts then he should be able to select > operator and then enter amount if he runs the report then details should be display according to their selection so can you please let me know is there any possibility and how?

    Quick replies are appriciated.... :)

    Thanks

    Hemanth


    hemanth

    Thursday, April 04, 2013 4:44 PM

Answers

  • Hi -

    Your stored proc would inlcude the ssrs parameters and the query should be similar, but you would probably need to use dynamic sql.

    Create Procedure dbo.GetCheckFact
     ( @CheckAmount int  
       , @Operator nvarchar(10)
    )
    AS
    Begin
        Declare @Sql nvarchar(4000)
        Set nocount on;
        
        Set @Sql = 'Select * From dbo.Check_Fact CF WHERE '
        Set @Sql = @Sql + @Operator + Ltrim(Rtrim(Str(@CheckAmount)))
         exec sp_executesql @sql  
    End 


    - will

    • Proposed as answer by ione721 Thursday, April 04, 2013 8:27 PM
    • Marked as answer by hemanth1618 Monday, April 08, 2013 10:35 PM
    Thursday, April 04, 2013 7:28 PM
  • Close but not quite what I said. Yours include =@CheckAmount within the string. Given you query, it should be:

    ="SELECT * FROM HPXR.dbo.CHECK_FACT CF WHERE CF.NET_CLM_CHK_AMT"+Parameters!Operator.Value+Parameters!CheckAmount.Value

    Now, for this to work, your new parameter that contains all of the operators must be named "Operator" and the check amount parameter must be named "CheckAmount". If either is named differently, change the parameter name in the formula to match the name you are using.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    • Marked as answer by hemanth1618 Thursday, April 04, 2013 7:11 PM
    Thursday, April 04, 2013 7:05 PM

All replies

  • Hi -

    yes, this is possible.  you could create a drop down list of operators that the user could choose from and then just have that as a parameter to your stored proc to build out the query.

     - will


    - will

    Thursday, April 04, 2013 5:30 PM
  • Can you please help me out how to keep that in query because I am not getting any idea how to fix this I will provide you an example can you please give idea for that.

    TableName: Check_Details (Check_Key, Claim_Key, Check_Num, MemName, IssueDate, PaidDate, CheckAmount)

    Can you please work on this

    Select * FROM Check_Details WHERE CheckAmount = @CheckAmount

    For operator selectuion what I have to do know can you please post the modified query.


    hemanth

    Thursday, April 04, 2013 5:48 PM
  • You can implement the change in the report dataset. Given that you create a second parameter with the operators you wish to use, double-click the dataset that contains your query:

    SELECT * FROM Table1 WHERE CheckAmount = @CheckAmount

    Click the fx (expression builder) button next to Query. Change your query from the above to:

    ="SELECT * FROM Table1 WHERE CheckAmount"+ Parameters!Operator.Value + Parameters!CheckAmount.Value

    This allows you to do dynamic operators as you need. The whole query is created from the string that the expression builder creates.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Thursday, April 04, 2013 6:10 PM
  • Hi Tim,

    As you said i changed the dataset as exp here is the exp

    This is error i am getting when i run the report and in the Operator parameter it is not taking '=' as a value how to specify that one.

    Thanks


    hemanth

    Thursday, April 04, 2013 6:45 PM
  • Close but not quite what I said. Yours include =@CheckAmount within the string. Given you query, it should be:

    ="SELECT * FROM HPXR.dbo.CHECK_FACT CF WHERE CF.NET_CLM_CHK_AMT"+Parameters!Operator.Value+Parameters!CheckAmount.Value

    Now, for this to work, your new parameter that contains all of the operators must be named "Operator" and the check amount parameter must be named "CheckAmount". If either is named differently, change the parameter name in the formula to match the name you are using.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    • Marked as answer by hemanth1618 Thursday, April 04, 2013 7:11 PM
    Thursday, April 04, 2013 7:05 PM
  • As Tim stated above, you need to remove the = operator after the CF.NET_CLM_CHK_AMT in your Where Clause.   Otherwise your query will be "....WHERE Chk_Amt = @ChkAmount > ....".  You need to replace the hard-coded operator with the Operator parameter.


    - will

    Thursday, April 04, 2013 7:08 PM
  • Thanks for the answer what if i use Stored proc insted of a query. How it works.

    Regards,

    Hemanth


    hemanth

    Thursday, April 04, 2013 7:11 PM
  • Much more complex. You would need to pass the operator into the stored proc. You would have to handle it in the stored proc. All I can think of to handle the dynamic operator in the stored proc is to wrap the query in an "IF/ELSE IF" clause that checks for @Operator = [whatever value] and runs a version of your query that uses the passed operator. You cannot do the same trick in TSQL as we did with the dataset query.

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Thursday, April 04, 2013 7:20 PM
  • Hi -

    Your stored proc would inlcude the ssrs parameters and the query should be similar, but you would probably need to use dynamic sql.

    Create Procedure dbo.GetCheckFact
     ( @CheckAmount int  
       , @Operator nvarchar(10)
    )
    AS
    Begin
        Declare @Sql nvarchar(4000)
        Set nocount on;
        
        Set @Sql = 'Select * From dbo.Check_Fact CF WHERE '
        Set @Sql = @Sql + @Operator + Ltrim(Rtrim(Str(@CheckAmount)))
         exec sp_executesql @sql  
    End 


    - will

    • Proposed as answer by ione721 Thursday, April 04, 2013 8:27 PM
    • Marked as answer by hemanth1618 Monday, April 08, 2013 10:35 PM
    Thursday, April 04, 2013 7:28 PM
  • Here is the SP i am using.

    I am not getting any value into the dataset u can see below

    Thanks

    Hemanth


    hemanth

    Thursday, April 04, 2013 7:50 PM
  • Looks like Will's suggestion has worked for you. You should mark his as answer as well so others will find there answers easier.

    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Thursday, April 04, 2013 8:10 PM
  • I couldnt get the solution when i use SP because if i use the abouve SP i am not getting any values you can see that in second image in my above reply.

    Thanks \

    Hemanth


    hemanth

    Thursday, April 04, 2013 8:25 PM
  • Hi Hemanth, When you use dynamic SQL in your dataset query sometimes the fields would not show up and you will have to add them (Dataset properties -> Fields -> Add (Query Fields)). Let me know should you have questions.

    Good luck..............

    Thursday, April 04, 2013 8:33 PM
  • Hemanth

    Yes, I forgot about that step in ssrs when you use dynamic sql.  it should work once you set up the columns.


    - will

    Thursday, April 04, 2013 8:44 PM