Report Format - Not rounding to 2 Decimal Places

Question

Report Format - Not rounding to 2 Decimal Places

2Tall

SSChampion

Points: 14644
More actions
January 26, 2022 at 8:13 am
Go to Answer
#3977396
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
2Tall

SSChampion

Points: 14644
More actions
January 27, 2022 at 4:43 pm
Answer
#3977980

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

Jeff Moden SSC Guru Points: 1004300 More actions · Answer 1

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)