none
How to use decimal(7,3) as Value field in a SSRS parameter?

    Question

  • Hi 

    I have a parameter which have decimal(7,3) as Value field. (I have put Integer as the datatype)
    The values can look like this:

    4.030
    20.005
    4.010

    I am using these values to filter a dataset. By using the IN Operator in a filter on the dataset.

    My problem is that the values I get in the to filter the dataset all looks like this:

    4
    2

    It seems like my decimals get truncated.
    The decimals get lost, and thus my query returns a wrong result :(

    Any solutions how I can use decimal(7,3) as Value field in my parameter and still get the correct values passed on to the dataset filter?


    • Edited by rune0071 Saturday, June 29, 2013 6:01 AM edit
    Friday, June 28, 2013 8:13 PM

Answers

  • Hi rune,

    Instead of the selecting the parameter type as Integer, you need to set to float(Float can only handle numeric datatypes), Integer just truncate the part after decimal.

    ParameterType


    Regards Harsh

    • Marked as answer by rune0071 Saturday, June 29, 2013 1:52 PM
    Saturday, June 29, 2013 10:48 AM
  • Hi Rune,

    Did you write passing those value as IN Query to your dataset.

    For Example : 

    SELECT * FROM <<TABLENAME>> WHERE VALUE IN (@ReportParameter1)

    you do not required to have set any filter expression, just pass the parameter as it is.


    Regards Harsh

    • Marked as answer by rune0071 Saturday, June 29, 2013 1:52 PM
    Saturday, June 29, 2013 1:31 PM

All replies

  • Hi rune,

    Instead of the selecting the parameter type as Integer, you need to set to float(Float can only handle numeric datatypes), Integer just truncate the part after decimal.

    ParameterType


    Regards Harsh

    • Marked as answer by rune0071 Saturday, June 29, 2013 1:52 PM
    Saturday, June 29, 2013 10:48 AM
  • Hi Harsh Kumar

    Thank you for your reply.

    It helps but it still does not work for me.

    When I write my values in my report they now look like this:

    5
    23,043
    4,004

    I can now filter my dataset by the expression:

    =CDec(Parameters!ReportParameter1.Value(0))

    The only thing I need to do to complete is to filter on multiple values instead of just one. My parameter returns multiple values, I just don't know how to pass these multiple values to the filter. Any suggestions?


    • Edited by rune0071 Saturday, June 29, 2013 1:19 PM I was editing
    Saturday, June 29, 2013 12:58 PM
  • Hi Rune,

    Did you write passing those value as IN Query to your dataset.

    For Example : 

    SELECT * FROM <<TABLENAME>> WHERE VALUE IN (@ReportParameter1)

    you do not required to have set any filter expression, just pass the parameter as it is.


    Regards Harsh

    • Marked as answer by rune0071 Saturday, June 29, 2013 1:52 PM
    Saturday, June 29, 2013 1:31 PM
  • Thank Mr. Harsh

    You are great :D

    When I wrote:

    SELECT        ID, MyName, MyNumber
    FROM            MyTable
    WHERE        MyNumber IN (@ReportParameter1)

    It worked :)

    Saturday, June 29, 2013 1:51 PM