Forcing '0' to set number of decimal places

Ok heres my problem.
Ive got a field that is rounded off to a set number of decimal places that
is defind in another field. Heres the code...
=ROUND (Fields!Transaction_Quan_.Value, Fields!Decimal_Places.Value)
my problem is that when the value is zero, it returns just 0, not 0.00 for
example. This is a requirement of the report.
Ive tried using the format feature, and dont have access to the SQL at the
moment to so some sort of CAST, it all needs to be done in RS for now.
Any ideas?
Cheers
Rob.Quick work around:
=IIF(Fields!Transaction_Quan_.Value >0, ROUND
(Fields!Transaction_Quan_.Value, Fields!Decimal_Places.Value), "0.00")
There's probably a more scientific way to do it, but that should make 0
appear as 0.00. ;)
Kaisa M. Lindahl Lervik
"MACNR" <MACNR@.discussions.microsoft.com> wrote in message
news:97D2A9F3-F623-4D3C-81B3-A06ECB41E8CB@.microsoft.com...
> Ok heres my problem.
> Ive got a field that is rounded off to a set number of decimal places that
> is defind in another field. Heres the code...
> =ROUND (Fields!Transaction_Quan_.Value, Fields!Decimal_Places.Value)
> my problem is that when the value is zero, it returns just 0, not 0.00 for
> example. This is a requirement of the report.
> Ive tried using the format feature, and dont have access to the SQL at the
> moment to so some sort of CAST, it all needs to be done in RS for now.
> Any ideas?
> Cheers
> Rob.
>|||Thanks for the reply kaisa, but the main problem is that 'the man' wants 0's
to be rounded to the correct number of decimal places aswell. so if the
decimal places amount is 4, then it needs to be 0.0000 etc. Irritating i know.
Cheers anyway though ;)
"Kaisa M. Lindahl Lervik" wrote:
> Quick work around:
> =IIF(Fields!Transaction_Quan_.Value >0, ROUND
> (Fields!Transaction_Quan_.Value, Fields!Decimal_Places.Value), "0.00")
> There's probably a more scientific way to do it, but that should make 0
> appear as 0.00. ;)
> Kaisa M. Lindahl Lervik
> "MACNR" <MACNR@.discussions.microsoft.com> wrote in message
> news:97D2A9F3-F623-4D3C-81B3-A06ECB41E8CB@.microsoft.com...
> > Ok heres my problem.
> >
> > Ive got a field that is rounded off to a set number of decimal places that
> > is defind in another field. Heres the code...
> >
> > =ROUND (Fields!Transaction_Quan_.Value, Fields!Decimal_Places.Value)
> >
> > my problem is that when the value is zero, it returns just 0, not 0.00 for
> > example. This is a requirement of the report.
> >
> > Ive tried using the format feature, and dont have access to the SQL at the
> > moment to so some sort of CAST, it all needs to be done in RS for now.
> >
> > Any ideas?
> >
> > Cheers
> >
> > Rob.
> >
> >
>
>

Force a round() when it's below a 5?

I have a sales tax function in my antiquated system. Someone decided to
play it safe and over collect on sales tax.
If your bill = 9.53 @. 9.25% your total would be 10.4115. My Tax in the
system shows 10.42
How do I force the round up for reporting to Auditors when they want to
query random samples of data?
TIA
__StephenTry:
select ceiling (10.4115 * 100) / 100
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:eKkPqOn%23FHA.4004@.TK2MSFTNGP14.phx.gbl...
>I have a sales tax function in my antiquated system. Someone decided to
>play it safe and over collect on sales tax.
> If your bill = 9.53 @. 9.25% your total would be 10.4115. My Tax in the
> system shows 10.42
> How do I force the round up for reporting to Auditors when they want to
> query random samples of data?
> TIA
> __Stephen
>|||Lookup CEILING in Books Online.
ML
http://milambda.blogspot.com/|||"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uvnkFSn%23FHA.532@.TK2MSFTNGP15.phx.gbl...
> Try:
> select ceiling (10.4115 * 100) / 100
Thanks. With your credentials I'll give it a whirl. :)
Will this work within a SUM() when I'm grouping by State, Client, City.
Some clients have different contracts with us and we compute tax on where
the HQ of company is that signed the contract, and not where the recipient
is located.
I'm processing 250,000 + detail rows a month creating a final result of 110
rows summarized today.|||Sure. I assume that this round-up has to happen on each line item before it
is summed. You can make it conditional, too. For example:
select
sum (case when State in ('CA', 'TX') then ceiling (Tax * 100) / 100 else
Tax end)
from
MyTable
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"__Stephen" <srussell@.transactiongraphics.com> wrote in message
news:OaaHuYn%23FHA.208@.tk2msftngp13.phx.gbl...
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uvnkFSn%23FHA.532@.TK2MSFTNGP15.phx.gbl...
> Thanks. With your credentials I'll give it a whirl. :)
> Will this work within a SUM() when I'm grouping by State, Client, City.
> Some clients have different contracts with us and we compute tax on where
> the HQ of company is that signed the contract, and not where the recipient
> is located.
> I'm processing 250,000 + detail rows a month creating a final result of
> 110 rows summarized today.
>
>|||Back in the dark ages, we rounded by adding and truncating. For
example, to always round up with 2 decimal places,
SELECT cast((9.53 * 1.0925 + .009999) * 100 as integer) / 100.
Not sure its any simpler.
Good luck.
Payson
Tom Moreau wrote:
> Try:
> select ceiling (10.4115 * 100) / 100
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada tom@.cips.ca
> www.pinpub.com
> "__Stephen" <srussell@.transactiongraphics.com> wrote in message
> news:eKkPqOn%23FHA.4004@.TK2MSFTNGP14.phx.gbl...