Report Format - Not rounding to 2 Decimal Places

  • Hi all. Strange issue (well to the untrained eye).

     

    --I have this line of code
    ROUND(SUM(CAST([FileSize] AS BIGINT)/1048576.0),2)) AS [FileSizeMB],
    ROUND(SUM(CAST([FileSize] AS BIGINT)/1073741824.0),2) AS [FileSizeGB]

    In my report I set text box properties for both fields to 'Number > Decimal Places = 0 > Negative numbers: (12345)

    The FileSizeGB displays correctly.

    The FileSizeMB displays all numbers after the period i.e. 1498.454108130

    --If I modify to the feilds display to 0 places
    ROUND(SUM(CAST([FileSize] AS BIGINT)/1048576),2)) AS [FileSizeMB],
    ROUND(SUM(CAST([FileSize] AS BIGINT)/1073741824),2) AS [FileSizeGB]

    --However the conversion appears incorrect:
    FileSizeMB = 1429
    FileSizeGB = 0

    ROUND(SUM(CAST([FileSize] AS BIGINT)/1048576),2)) AS [FileSizeMB],
    ROUND(SUM(CAST([FileSize] AS BIGINT)/1073741824.0),2) AS [FileSizeGB]
    FileSizeMB = 1429
    FileSizeGB = 146

    Kind Regards,

    Phil

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • I'm a bit confused.  Can you post the actual value of what FileSize is BEFORE your modifications, please?  Also, what are you using as a reporting tool?

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Hi Jeff. I changed my approach and formatted within the SSRS report rather than the code.

    SQL query now SUM([FileSize]) AS SumOfFileSize

    Many Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply