none
SSRS 2008 R2 is chopping off decimal places?

    Question

  • Within Management Studio, I have a stored procedure that returns values from a table. The values have four decimal places to the right of the zero.
    For example:   0.0095

    If I run the procedure within SQL Management Studio, I get the value without doing any conversion. I get 0.0095

    In SSRS 2008, I have a table that is populated by running the same procedure.  When the value is displayed in
    the table, the value is now:  .0090   (I made a quick table to show the values that SSRS is receiving).

    Why did SSRS drop my decimal place?

    Really, I'm using this value to dynamically create striplines within a chart, but the chart will only show the 0.0095 value as the "Strip Width" if it's hard-coded.
    If I want the chart to adjust the striplines dynamically by calculating it or pulling it from a table, I only get the 0.0090 value. Why? How do I fix this behavior?

    Stumped!

    Wednesday, October 02, 2013 9:06 PM

Answers

  • I cant reproduce your problem.  For example if i make this my report query:

    Select Convert(Decimal(7,6),0.0095) as number

    And then drop that value into my report in a table, it displays "0.009500"

    When you run your report, try running sql profiler against your database to pick up the EXACT query that the report is using.

    When run that query in the database and see what resultset you get.

    • Marked as answer by Amy G Smith Friday, October 18, 2013 5:56 PM
    Tuesday, October 08, 2013 4:24 AM
  • Can I make a suggestion?

    Add a new dataset to your report like so :-

    SELECT 0.0095 as [NumericColumn]

    Now change the dataset binding of your table to this new dataset and change the value displayed to NumericColumn 

    What value is returned in the report?

    Even better you could copy and paste the table so you have both datasets being displayed.


    Thanks! Josh


    • Edited by Josh Ashwood Tuesday, October 08, 2013 5:00 AM
    • Marked as answer by Amy G Smith Friday, October 18, 2013 5:56 PM
    Tuesday, October 08, 2013 4:59 AM
  • I think it may be because you've applied some formatting in SSRS textbox using one of standard formats or a custom expression. First check if any format settings exists for textbox

    Secondly check the data type of the field returned to the ssrs report from query

    • Marked as answer by Amy G Smith Friday, October 18, 2013 5:56 PM
    Tuesday, October 08, 2013 5:38 AM

All replies

  • Can you check the format on the table column (right click -> properties -> Number

    and tell me what the settings are?

    Thursday, October 03, 2013 12:51 AM
  • And can you please share your query
    Thursday, October 03, 2013 12:51 AM
  • In the Cell Properties, under Number and Format, I have:  #.0000    Previously, I left it alone.

    For the query:

    The table cell Expression is:  "=max(Fields!Red1End.Value)"

    The procedure itself pivots some values for a PartID.  If I run the procedure from Mgmt Studio, I get 0.0095, which is what I expect.  I have tried changing the SELECT statement to CONVERT to a decimal (see first two columns selected), and I've tried leaving it as is, like the rest of the columns. All return expected values from Mgmt Studio, but when i run it from BIDS, I get 0.0090.  (I haven't deployed it and tested it, but assuming it would do the same thing.

    Here's the Procedure:

    CREATE PROCEDURE [dbo].[usp_GetMeasurementRangeP]
    (
        @varPartID varchar(50)
    )

    AS
    BEGIN

        SET NOCOUNT ON;

    -- Get MeasurementRanges per Bag

    SELECT DISTINCT MStart.PartID
            , MStart.MeasurementID
            , CONVERT(DECIMAL(7,6),MStart.Red1Start) AS Red1Start
            , CONVERT(DECIMAL(7,6),MEnd.Red1End) AS Red1End
            , MStart.Yellow2Start
            , MEnd.Yellow2End
            , MStart.Green3Start
            , MEnd.Green3End
            , MStart.Yellow4Start
            , MEnd.Yellow4End
            , MStart.Red5Start
            , MEnd.Red5End
    FROM    
            (
            SELECT PartID, MeasurementID, MAX([1]) AS Red1Start , MAX([2]) AS Yellow2Start, MAX([3]) AS Green3Start, MAX([4]) AS Yellow4Start, MAX([5]) AS Red5Start
            FROM
                (
                  SELECT *
                FROM dbo.MeasurementRange
                PIVOT (
                       MAX(RangeStart)
                       FOR RangeOrder IN ([1],[2],[3],[4],[5])
                       )
                AS PivotTable
                ) AS StartTable
            GROUP BY PartID, MeasurementID
            ) AS MStart
    JOIN
            (
            SELECT PartID, MeasurementID, MAX([1]) AS Red1End , MAX([2]) AS Yellow2End, MAX([3]) AS Green3End, MAX([4]) AS Yellow4End, MAX([5]) AS Red5End
            FROM
                (
                  SELECT *
                FROM dbo.MeasurementRange
                PIVOT (
                       MAX(RangeEnd)
                       FOR RangeOrder IN ([1],[2],[3],[4],[5])
                       )
                AS PivotTable
                ) AS StartTable
            GROUP BY PartID, MeasurementID
            ) AS MEnd
    ON MStart.PartID = MEnd.PartID
    AND MStart.MeasurementID = MEnd.MeasurementID
    WHERE MStart.PartID = @varPartID
      AND MStart.MeasurementID IN ('P1','P2','P3','P4','P5')


        SET NOCOUNT OFF;

    END

    Thursday, October 03, 2013 5:14 AM
  • I cant reproduce your problem.  For example if i make this my report query:

    Select Convert(Decimal(7,6),0.0095) as number

    And then drop that value into my report in a table, it displays "0.009500"

    When you run your report, try running sql profiler against your database to pick up the EXACT query that the report is using.

    When run that query in the database and see what resultset you get.

    • Marked as answer by Amy G Smith Friday, October 18, 2013 5:56 PM
    Tuesday, October 08, 2013 4:24 AM
  • Can I make a suggestion?

    Add a new dataset to your report like so :-

    SELECT 0.0095 as [NumericColumn]

    Now change the dataset binding of your table to this new dataset and change the value displayed to NumericColumn 

    What value is returned in the report?

    Even better you could copy and paste the table so you have both datasets being displayed.


    Thanks! Josh


    • Edited by Josh Ashwood Tuesday, October 08, 2013 5:00 AM
    • Marked as answer by Amy G Smith Friday, October 18, 2013 5:56 PM
    Tuesday, October 08, 2013 4:59 AM
  • I think it may be because you've applied some formatting in SSRS textbox using one of standard formats or a custom expression. First check if any format settings exists for textbox

    Secondly check the data type of the field returned to the ssrs report from query

    • Marked as answer by Amy G Smith Friday, October 18, 2013 5:56 PM
    Tuesday, October 08, 2013 5:38 AM
  • Hello everyone,

    I did the simplest thing - saving the Project, shutting down and reopening, and I believe one of the formatting options I had must have done the trick - it just didn't take if I didn't restart BIDS.  It appears to be working properly now, with the correct amount of decimal places and no rounding.  Thank you for all the suggestions - I'm not sure which one worked, but probably a combination of all of them!

    Friday, October 18, 2013 5:56 PM