I need to round a specific way

[muffntuf]

I need to round a number a specific way, here's an example of the number, its in the Group Summary area.

I am using CRXI:

25,187,680.50

should look like:

25187.7

I don't know how to get here, but I know it probably invovles a formula.

Thanks,
muffntuf

 

[lbass]

Try:

round({table.number}/1000,1)

Use the decrease decimal icon to display only one decimal.

-LB

 

[muffntuf]

Thanks for your help!

That rounds up the next major increment, the result is

28,190.0 Not quite what I am after. I need to reflect the 100's as rounded up to .7

If I divide the field by 100 then I get 182,880.0 which isn't what I am after either.

More help?

 

[muffntuf]

lbass,

my mistake I had -1 for the remainder, I changed that and now have the correct display.

Thanks much!

 

[muffntuf]

Further explanation of what I am looking for:

The customer wants the report values displayed to the nearest to the nearest $1,000 with the hundredths displayed as one digit to the right of the decimal place in preview mode. This is a common format used in published financial statements.



For example, the group footer summary total field value 28,187,681.32 is currently displayed as 28,187,681 using the customize number format settings. The required display value is 28,187.7. However, the “round to the nearest thousand” display shall not apply to the group summary total field when the drilldown group level > 0. A formula such as “Round(Sum ({@nCPERAmt}, {@sAcctClass})/1000,1)“ used in the customize, rounding formula editor produces a “Bad Formula Result error.

 

[lbass]

This is not a formula for the rounding format area. It replaces the summary itself in the report display. Try the following, assuming you have a version higher than 8.5:

if drilldowngrouplevel = 0 then
round(Sum ({@nCPERAmt}, {@sAcctClass})/1000,1) else
Sum ({@nCPERAmt}, {@sAcctClass})

Place this on your report instead of your summary.

-LB

 

[muffntuf]

Okay the obstacle here now, the formula syntax is right, is that I have 130 fields I need to display the format for, I was hoping not to have to create 130 new fields to do the display of the results. Recommendations?

 

[synapsevampire]

Understand that what you're asking for isn't rounding a number, it's changing the value. You can round to whatever precision you like, but it will still display as it's value.

Nor does Crystal allow for dynamically creating field names within formulas, so a generic formula can't handle this by passing fields through (I know, weeeeak!).

It might be easier to create a Add Command (pasting in your own SQL) for the data since what you want isn't the value in the field, and in the SQL do the proper math on the database side.

Something like:

select
table.field1/1000 val1,
table.field2/1000 val2,
...etc...
from table

Then you can lassoo all of the fields and use the round function on all of them at once.

You might also consider creating a View on the database if this type of massaging of the data is commonplace. that way it's available to any process and is more readily maintained.

-k

 

[muffntuf]

Don't know if I can yet, it is an odd database and had to set up a star schema with command objects to get data out of database with some sense to it. Unusual, but creative and effective use of command objects.

 

[synapsevampire]

If they've created a star schema, it should be childs play to add in another fact of the values divided by 1000 as well.

Of course this depends on the frequency of returning the data in this format. You might even consider the reverse, store it ONLY in 1000's if that's the only precision required to be returned from the data mart.

-k

 

[muffntuf]

No they don't have a star schema in the database, I made one out of their database with the sql command object, so there is one central table and about 6 other tables joined to that.

The database I’m working against doesn’t like anything but a select or sum field construct in the SQL pass-through ODBC too which is making it more difficult.

 

[synapsevampire]

Ahhh, I see, that does mean a bit more thought needs to be put into it.

If you've already created the "star schema", then it should be fairly simple to search and replace all of the values within the Add Command objects to divide them by 1000, there's probably already a set of commas in the select that's very handy for this...

-k

 

[lbass]

SV,

Note that a further complication is that the requirement is also for the full value during drilldown, with the rounding only for the display before drilldown.

-LB

 

[muffntuf]

well because they want the to the dollar in the detail section and on the group summary that particular format.