Rounding decimals for numbers AFTER the AVG func. Please take a l

My query returns numbers to the report, these are already rounded (this is
made to happen in query as they are cast to int).
After I get these integers to my report, I tend to group them, and AVG a
group set. If there is a lot of the numbers in the equation, the total tends
to decimalise to a really ridiculous number (x.xxxxxxxxxxxx). So, I try
putting d or n in the custom format properties for this textbox, I also try a
number of the default formatting options. Even I try to use the <format> tag
in the XML. All to no effect.
The only thing I can think is that I tend to write out a '%' after the
number (to show it's a percentage, it's purely cosmetic). Here is a line of
the AVG function, and how I am using it . I would really appreciate it if
anyone knew what I might be goofing up on here!
=iif(Avg(Fields!DoneIt.Value) = 0, "0%", Avg(Fields!DoneIt.Value) & "%")Did you try just using the following expression:
=Avg(Fields!DoneIt.Value)
and then just use formatcodes like P, P0, P1, etc.?
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matt Swift" <MattSwift@.discussions.microsoft.com> wrote in message
news:EA1C086B-CA02-4314-8CED-970B2D72B5EB@.microsoft.com...
> My query returns numbers to the report, these are already rounded (this is
> made to happen in query as they are cast to int).
> After I get these integers to my report, I tend to group them, and AVG a
> group set. If there is a lot of the numbers in the equation, the total
> tends
> to decimalise to a really ridiculous number (x.xxxxxxxxxxxx). So, I try
> putting d or n in the custom format properties for this textbox, I also
> try a
> number of the default formatting options. Even I try to use the <format>
> tag
> in the XML. All to no effect.
> The only thing I can think is that I tend to write out a '%' after the
> number (to show it's a percentage, it's purely cosmetic). Here is a line
> of
> the AVG function, and how I am using it . I would really appreciate it if
> anyone knew what I might be goofing up on here!
> =iif(Avg(Fields!DoneIt.Value) = 0, "0%", Avg(Fields!DoneIt.Value) & "%")|||In your report, go to [ Report Properties ] from your [ Report ] menu.
On the [ Code ] tab, paste this in...
--[ BEGIN CODE ]--
Public Function ConvertToInt(ByVal x_obj As Object) As String
Return String.Format("{0:#}", x_obj)
End Function
--[ END CODE ]--
Now, change your textbox's formula to:
=ConvertToInt(Avg(Fields!DoneIt.Value)) & "%"
Andrew Bruderer
"Robert Bruckner [MSFT]" wrote:
> Did you try just using the following expression:
> =Avg(Fields!DoneIt.Value)
> and then just use formatcodes like P, P0, P1, etc.?
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Matt Swift" <MattSwift@.discussions.microsoft.com> wrote in message
> news:EA1C086B-CA02-4314-8CED-970B2D72B5EB@.microsoft.com...
> > My query returns numbers to the report, these are already rounded (this is
> > made to happen in query as they are cast to int).
> >
> > After I get these integers to my report, I tend to group them, and AVG a
> > group set. If there is a lot of the numbers in the equation, the total
> > tends
> > to decimalise to a really ridiculous number (x.xxxxxxxxxxxx). So, I try
> > putting d or n in the custom format properties for this textbox, I also
> > try a
> > number of the default formatting options. Even I try to use the <format>
> > tag
> > in the XML. All to no effect.
> >
> > The only thing I can think is that I tend to write out a '%' after the
> > number (to show it's a percentage, it's purely cosmetic). Here is a line
> > of
> > the AVG function, and how I am using it . I would really appreciate it if
> > anyone knew what I might be goofing up on here!
> >
> > =iif(Avg(Fields!DoneIt.Value) = 0, "0%", Avg(Fields!DoneIt.Value) & "%")
>
>

Rounding a number

Hi,

I need to round a number in a table to either 2 or 4 decimals:

=((Sum(Fields!Total.Value) -Sum( Fields!Total_used.Value))*100)/(Sum(Fields!Total.Value)) & " %"

This results in a number like 91,1243656438345%

I would like it to be rounded to 91,1244% or 91,12%

Is there any builtin round-function, that does not just turn the number into an integer?

Thanks for your help

You should be able to do this using the Format function

=format(Sum(Fields!value.Value),"#,###.0000%")

or

=format(Sum(Fields!value.Value),"#,###.00%")

|||

Thanx - I wasn't familiar with that function. It works perfectly :-)

-- Heidi --

|||You might just need to change your format settings to conform to your use of a comma (,) as opposed to a UK full stop (.) for the decimal separator...|||Yup.... but my reports are running in various contries, so I already use different formatting depending on the users language settings. Not a problem :-)|||I've discovered that the use of comma or dot as seperator is automatic depending og language settings... So in this case I don't need to do anything... finally something is simple and straightforward ;-)

Round funtion on entire columns in MSSQL?

Hi,

I'd like to round all amounts in a certain column to 2 decimals.
I tried the following query, but eventhough the syntax is correct, it
doesn't give any result:

update gbkmut
set bdr_hfl = round(bdr_hfl,2)

can anyone help me?

cheers,

steveOn 5 May 2004 02:40:15 -0700, steve wrote:

>Hi,
>I'd like to round all amounts in a certain column to 2 decimals.
>I tried the following query, but eventhough the syntax is correct, it
>doesn't give any result:
>update gbkmut
>set bdr_hfl = round(bdr_hfl,2)
>can anyone help me?
>cheers,
>steve

Hi Steve,

What do you mean with "doesn't give any result"?

If you mean that no rows were returned by the update statement, then
this is expected behaviour. An UPDATE-statement will update the data,
nothing more nothing less. Use SELECT if you want to see anything.

If you mean that after executing the above UPDATE, you still have data
with more than two non-zero digits after the decimal point, I'd ask
you to post more details (table definition in the form of CREATE TABLE
statements, sample data in the form of INSERT statements, expected
output and the output you got) so that others can try if they can
reproduce this apparantly erroneous behaviour.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||stefan.van.den.steen@.exact.be (steve) wrote in message news:<79d8a1b0.0405050140.6f442694@.posting.google.com>...
> Hi,
> I'd like to round all amounts in a certain column to 2 decimals.
> I tried the following query, but eventhough the syntax is correct, it
> doesn't give any result:
> update gbkmut
> set bdr_hfl = round(bdr_hfl,2)
> can anyone help me?
> cheers,
> steve

At first glance, your UPDATE statement seems to be OK. Can you give
some more information? In particular, what is the data type of the
bdr_hfl column, and can you give some sample values, as well as your
expected result? And what does "doesn't give any result" mean?

Simon|||steve (stefan.van.den.steen@.exact.be) writes:
> I'd like to round all amounts in a certain column to 2 decimals.
> I tried the following query, but eventhough the syntax is correct, it
> doesn't give any result:
> update gbkmut
> set bdr_hfl = round(bdr_hfl,2)

There is very little information, but I would guess that your column is
of datatype float. Float is an approxamite datatype, which means that
far from all values can be stored exactly in a float. For instance,
try this:

select convert(float, 1.89)

In Query Analyzer this displays as 1.8899999999999999.

In many situations, it is possible to cope with these extra decimals at
the end; you only need some care. If you need exact numbers, you must
use the decimal type instad. The drawback is that with decimal, you
must decide from the beginning which range you handle.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp