Formula for Record Calculation

I have created two commands in the database expert. And I want to do the calulation of these records.

My situation is:

In table A, I have fields "Country","Amount" and "Currency".
In table B, I have fields "Currency" and "Exchange".

I want get the sum of "Amount"*"Exchange" if table A "Currency" = table B "Currency" that group by "Country"

How to write this formula?

Thank you fro helping me.Have u linked these commands by currency?|||Please tell me how to linkand what I should do after linking?
Thanks very much|||I have linked the currency field of two commands.

My situation is tableB store the currency of all countries in the world. And some of the countries using same currency. If I use a sql statement to join two table, then the records will be duplicated and cannot get the correct exchange rate of each currency.
So that I want to write a formula that caluclate the tableA amount with correct currency. But I don't know how to get the exchange rate of tableB if tableA.currency=tableB.currency.

Do you have any idea to do that?
thanks alot~~~|||I am using mysql 4.0 which is not supported subquery.

If you have any idea other than my method. Please tell me. This problem spent me a week ago~

Really thanks alot~

formating string to time

Hello,

I have the following question.

I have a report where i do a lot of time calculation

in one colomn i have values in seconds.

for example i have a total of 246 seconds

how can i format this to view as 00:04:06 or even 4 mins 6 secs

Vincent

Hello Vincent,

Try putting this in your textbox's expression...

=cStr(Floor(Fields!TotalSeconds.Value / 60)) + " mins "
+ cStr(Fields!TotalSeconds.Value - Floor(Fields!TotalSeconds.Value / 60) * 60) + " secs"

Hope this helps.

Jarret

|||

Hello Jarret,

This works great.

Thank you.

Vincent

|||

Hi,

How can i add the hours also to it ?

I'm still really quite new to all this stuff, but i'm working on it.

Thanks

Vincent

|||

For hours, try this...

=cStr(Floor(Fields!TotalSeconds.Value / 3600)) + " hours "
+ cStr(Floor(Fields!TotalSeconds.Value / 60) - Floor(Fields!TotalSeconds.Value / 3600) * 60) + " mins "
+ cStr(Fields!TotalSeconds.Value - Floor(Fields!TotalSeconds.Value / 60) * 60) + " secs"

Jarret

FORMAT_STRING For Calculation

I have a calculation that gives the evolution of a certain measure over 1 month in percentage.

MAR2006 : 1000

APR2006: 1100

> calculation = 10%

MAR2006 : 1000

APR2006: 900

--> calculation = -10%

This works perfect ... but the users of my application ask me if I can put a '+' sign for positive evolutions.

In the first case they want to see '+10%' in stead of '10%'

This is the FORMAT_STRING I am using : "#0.00%"

Any idea ?

Hi Christophe,

You need to include two sections in your format string definition, the first for the positive values and the second for the negative values. Here's an example from Adventure Works which does what you want, I think:

with

member measures.test as (7000000 - [Measures].[Internet Sales Amount])/[Measures].[Internet Sales Amount]

, format_string='+#0.00%; -#0.00%'

select measures.test on 0,

[Date].[Calendar Year].members on 1

from [Adventure Works]

HTH,

Chris

|||

THANKS A LOT.

Just tested it and it works.

Format to 2 decimal places?

Hi, i need to display my data in 2 decimal places but now i'm getting results after some calculation (for eg. 2.336224). How can i round it off to 2.34?

Hi,

In the report designer, right click the cell where you want to display the above number then go to properties and then to the format tab and enter Format Code as "0.00". It should format the 2.336224 to 2.34.

Hope it helps.

Rajiv

|||hehe.. thanks so much! It's so helpful.. I came across this when i was exploring the program but didnt occur to me when i need it.. thanks!

Format a Percent, remove decimals.

I have the percent calculation for a pice chart in SRS, my chart has 4
values. it looks like:
=(Fields!Estimated_Value.Value)/Sum(Fields!Estimated_Value.Value)
My results look like
42.61%
25.63%
12.35%
19.41%
How do I format my result to remove the decimals and round the values
up?=Format((Fields!Estimated_Value.Value)/Sum(Fields!
Estimated_Value.Value),"###")|||Hi,
dineshasanka's answer was nearly right but it will round numbers up and down
i.e. 4.6% will round to 5% and 4.3% will round to 4%. To always round up
you need to add 0.5 to the calculated answer:-
=Format((Fields!Estimated_Value.Value)/Sum(Fields! Estimated_Value.Value) +
0.5,"###")
In the example above 4.6% + 0.5 becomes 5.1% and thus rounds down to 5%,
4.6% + 0.5 is 5.1% rounding down to 5%.
Hope it helps,
Dale
<dineshasanka@.gmail.com> wrote in message
news:ed19510b-9395-45a7-a055-4b62cba0e4df@.34g2000hsz.googlegroups.com...
> =Format((Fields!Estimated_Value.Value)/Sum(Fields!
> Estimated_Value.Value),"###")
>|||Thank you both, well my requirements changed a bit to needing to do
the same thing for a count, I opened another post for this issue but I
am not getting any feedback,
http://groups.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/2be09ac8fcf5e49a/28cc4cc0db0e38a2#28cc4cc0db0e38a2