Rounding to large no of dec places

[mart10]

I am using CR10

I have an exchange rate USD/GBP = 1.5937 held in a treasury database which I need to express in crystal as the inverse ie 0.627470665746376. I need crystal to show it to this no of decimal places because the system I am sending it to may then need to inverse back again to exactly 1.5937 - this is the way it has to be.

How can I get crystal to display or export this correct rate? At the moment when displaying to max no of dp and rounding it shows 0.6274706657 and when exported to Excel it gives the slightly wrong figure of 0.62747066574638 . Its the export bit that is absolutely critical !

 

[Turkbear]

Have you checked the formatting of the Excel cel that gets the exported data? Excel will round differently if not explicitly specified.

Try exporting data only or even to a CSV file then importing to Excel and specify the format for that cel.



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

If you convert the number to a string in a SQL expression, all digits will display:

{fn CONVERT(0.627470665746376, sql_varchar)}

...returns: 0.627470665746376

But the (potential) problem is that you would then have to convert this back to a number after export.

-LB

 

[mart10]

LBASS

Your solution is better and works on hardcoded figure I gave including sending to Excel and then doing the inverse.
Just one issue I have never used SQL expressions and all I did was paste your formula in to get it to work. However what I should have said is that this value 0.627470665746376 is the result of a formula:

@AdjustedRate(Number):

IF{HistoricFXRates.Currency}="EUR" THEN ROUND(1/{@Rate},6) ELSE

IF{@Rate}>99999.9999 then 1/({@Rate}/1000) else 1/{@Rate}


So how do I put @AdjustedRate into that expression you gave. If I can do that I think this will work , sorry but as I say SQL expressions are completely new to me

 

[lbass]

What is the content of {@Rate} and of any formulas nested within it? What kind of database is it? If you doubleclick on a field in the field list of the SQL Expression editor, how is it displayed in the formula area?

-LB

 

[mart10]

SQL Data base

If I double click on name filed in Currencies table I get:

Currencies . Name



Content of formulas:

@Rate :

If {HistoricFXRates.Currency}="GBP" then {@InverseGBP} ELSE

Round(({HistoricFXRates.CostToBuyDollar}*{@InverseGBP}),10)


@InverseGBP :
Maximum({@InverseUSD})


@InverseUSD}) :
IF {HistoricFXRates.Currency}="GBP" then 1/{HistoricFXRates.CostToBuyDollar} else 1

 

[lbass]

Sorry, but with all of these nested conditions, I don't think I can help.

-LB

 

[mart10]

I may be able to simplify it. How many nested conditions are allowed? How can I get @Rate into the SQL expression?

 

[lbass]

I don't ordinarily say I can't help unless I really cannot seem to solve an issue, therefore, as I said, I personally don't know how to do that. I only got so far, and then it became too complex.

You could also try doing this in a command (it will show the long numbers if converted to text (try using cstr()), and you could use a case when statement, but you can't reference formulas, so you would have to be able to nest the equivalent of these, while also building in a maximum subquery. And I can't take you there.

-LB

 

[pmax9999]

I just tried exporting a report that had that number (created by a formula). In Crystal, the maximum number of decimal places it would show was 10, and when it exported to XLS the rounding (for display purposes) was the same, but the actual data in that cell was the full 15 decimal places. I am using CR2008. It might be worth re-checking your results.

 

[mart10]

PMAX9999, yes but it in excel when you examine the data in excel it isnt 0.627470665746376 but 0.627470665746380. This is because of the rounding put on the amount in crystal. If I could take the rounding off or extend to 12dp it would work but you cant - this is the issue