Currency to text conversion

Does anyone have an easy way to convert currency to text ($50.00 - Fifty Dollars) for writing checks in reporting services

Thanks

You'll have to convert it from VBA to VB.NET or C#, but this looks like what you want:http://www.ozgrid.com/VBA/CurrencyToWords.htm|||

Thanks thats what I was looking for.

If it helps anyone else this code can be used "as is" (except it didn't like the REDIM statement) and embeded into the report itself. Reports support embedded code that can be entered through the report's property dialog on the Code tab. The function can then be accessed throughout the report as a member of the class called Code. In this case =Code.ConvertCurrencyToEnglish(First(Fields!Amount.Value)). Note that the expression editor showed a squigly line under the ConvertCurrencyToEnglish and a popup indicated "Unrecoginized Identifier" however the code did run. Note also that the code section only supports VB. If you prefer C# or another .NET language, you'll have to access a separate .NET assembly.

More info can be found here.

http://msdn.microsoft.com/msdnmag/issues/06/07/DataPoints/

Currency conversion wizard - performance

We have created a cube in which we want to do many-to-many currency conversion - i.e. we have a measure group with amounts measured in 4 different currencies, and we want to be able to display these amounts in any of these 4 currencies. The measure group is linked to a company dimension which in turn is linked to a currency dimension via a referenced relationship (there are 3-4 more dimensions in the cube). Running the currency conversion wizard yields the expected results and we can show our measures in any available currency using the "Reporting Currency" dimension... However - it is SLOW!

We realize that the calculations in the MDX script generated by the wizard scopes on the leaves on the time dimension, which causes a performance hit, but the query runs for 4-5 minutes when choosing a currency other than "Local". Choosing "Local" the query runs for 2-3 seconds. The measure group in question contains approximately 1.4 millions fact rows and we have exchange rates for a 10 year period (approximately 365 x 4 x 10 = 14.600 fact rows for the exchange rate measure group).

We are using the September CTP (Standard Edition). Is there any way to improve the response times? Has bugs that affect this been fixed for RTM?

Thanks...

We are really having problems with this one... Has anyone else had similar experiences? |||Hi Michael,

Although my experience is limited, I can think of some things it might be worth trying...

First of all, using measure expressions instead of pure MDX Script calculations should give you better performance. See the following entries on my blog for more information:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!260.entry
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!299.entry

Secondly (and this is only an educated guess), if you make sure that any aggregations you build are only at the granuarlity attribute of the Time dimension, then with a bit of luck the AS engine will be able to use them for your queries. You can do this by making sure that only the granuarity attribute has its AttributeHierarchyOptimizedState property set to FullyOptimized (the others should have it set to NotOptimized); if you then redesign your aggregations, hopefully you'll still get some built and see some improvement. If you don't get any aggregations built then you might have to design some manually by hacking the XMLA.

Thirdly, I wonder if the fact that the Currency dimension has a referenced relationship with the main fact table could be a factor here? Can you redesign the main fact table so that you have a Currency key as well as a Company key?

Fourthly, and I'm not sure that this will have much impact but it's worth trying, partitioning your Exchange Rate measure group by Month might be a good idea.

Let me know if any of this works..

Chris|||Thanks Chris... I will try your suggestions and report back my findings. My options with regard to your suggestions are limited, however, since the project is being built on a Standard Edition (thus no measure expressions and/or partitioning).

Currency conversion wizard - performance

We have created a cube in which we want to do many-to-many currency conversion - i.e. we have a measure group with amounts measured in 4 different currencies, and we want to be able to display these amounts in any of these 4 currencies. The measure group is linked to a company dimension which in turn is linked to a currency dimension via a referenced relationship (there are 3-4 more dimensions in the cube). Running the currency conversion wizard yields the expected results and we can show our measures in any available currency using the "Reporting Currency" dimension... However - it is SLOW!

We realize that the calculations in the MDX script generated by the wizard scopes on the leaves on the time dimension, which causes a performance hit, but the query runs for 4-5 minutes when choosing a currency other than "Local". Choosing "Local" the query runs for 2-3 seconds. The measure group in question contains approximately 1.4 millions fact rows and we have exchange rates for a 10 year period (approximately 365 x 4 x 10 = 14.600 fact rows for the exchange rate measure group).

We are using the September CTP (Standard Edition). Is there any way to improve the response times? Has bugs that affect this been fixed for RTM?

Thanks...

We are really having problems with this one... Has anyone else had similar experiences? |||Hi Michael,

Although my experience is limited, I can think of some things it might be worth trying...

First of all, using measure expressions instead of pure MDX Script calculations should give you better performance. See the following entries on my blog for more information:
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!260.entry
http://spaces.msn.com/members/cwebbbi/Blog/cns!1pi7ETChsJ1un_2s41jm9Iyg!299.entry

Secondly (and this is only an educated guess), if you make sure that any aggregations you build are only at the granuarlity attribute of the Time dimension, then with a bit of luck the AS engine will be able to use them for your queries. You can do this by making sure that only the granuarity attribute has its AttributeHierarchyOptimizedState property set to FullyOptimized (the others should have it set to NotOptimized); if you then redesign your aggregations, hopefully you'll still get some built and see some improvement. If you don't get any aggregations built then you might have to design some manually by hacking the XMLA.

Thirdly, I wonder if the fact that the Currency dimension has a referenced relationship with the main fact table could be a factor here? Can you redesign the main fact table so that you have a Currency key as well as a Company key?

Fourthly, and I'm not sure that this will have much impact but it's worth trying, partitioning your Exchange Rate measure group by Month might be a good idea.

Let me know if any of this works..

Chris|||Thanks Chris... I will try your suggestions and report back my findings. My options with regard to your suggestions are limited, however, since the project is being built on a Standard Edition (thus no measure expressions and/or partitioning).

Currency Conversion SSAS 2005

I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies.

In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary).

I have a fact table with with currency code and converstion rate. Since all of our measures are stored in USD, I do not have a currency key in the other fact tables. Is what I am trying to do possible without adding the currency key to all the fact tables?

Any suggestions are greatly appreciated.

Even in AS2000 with your setup, LookupCube wasn't the best solution. The best solution would've been to create virtual cube, and then multiply the USD measure by ValidMeasure(Rate). The same approach should work in AS2005 as well, only instead of virtual cube, you would have two measure groups in your cube.|||

Thank you for the reponse.

Can you expand upon what you mean by using the ValidMeasure function?

I have a measure group with the Converstion Rate. The only dimension related to this measure group is the currency dimension. I would like to be able to have one calculated measure per currency code that multiplies all related measures stored in USD by the corresponding conversion rate.

So that leaves me with two issues:

1) How to apply the conversion rate to the measures based on what currency is selected in the currency dimension.

2) How to filter the solution to only apply to financial measures (the count measures should not adjust)

|||

Here is an example of how MDX Script could look like to achieve both of your issues. This assumes that Currency attribte is non-aggregatable, as it makes no sense to aggregate across different currencies.

({Measures.FinancialMeasure1, Measures.FinancialMeasure2, Measures.FinancialMeasure3}, Currency.Currency.Currency.MEMBERS) = Measures.CurrentMember*ValidMeasure(Measures.ConversionRate);

(more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx)

|||Works perfectly! Thanks! |||

It seems that this solution has caused another issue. The measures that this logic was applied to are not rolling up correctly. When I filter a dimension on the rows other than at the all level, the totals do not reflect the current selection of data.

For example, if I want to display the units and amount by product for all products, my result set might look something like this:

Product Units Amount

A 1 10

B 1 10

C 1 10

Total 3 30

If I want to look at only Product A, my result looks like this:

Product Units Amount

A 1 10

Total 1 30

The Total for the Units measure is correct because it's value does not change based on the currency that the user has selected, however, the Total for the Amount measure is incorrect and does not take into account what dimension filters are being applied.

Do you have any suggestions?

|||You can apply the currency conversion only at Leaves() - then they will be rolled up to the higher levels, and then visual totals should work correctly. However, be aware that the performance might become worse. Yet another approach would be to use measure expressions to do currency conversion during processing. This solution is not as flexible, but I think it will fit your scenario too.|||

I cannot use ValidMeasure with the Measure Expressions thus the conversion is inaccurrate. Can you expand on the Leaves() suggestion?

|||

I was able to use Measure Expressions to solve this problem after adding a date key to the currency conversion fact table. So now the currency conversion fact table looks like this:

Currency Key

Time Key

Conversion Rate

I am able to link the currency fact table to the other fact tables via the Time dimension and perform the currency conversion calculations using Measure Expressions based on what Currency is selected in Currency Dimension. Not as dynamic as placing this logic in an MDX Script but it works and the performance is great! Thanks Mosha!

Currency Conversion SSAS 2005

I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies.

In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary).

I have a fact table with with currency code and converstion rate. Since all of our measures are stored in USD, I do not have a currency key in the other fact tables. Is what I am trying to do possible without adding the currency key to all the fact tables?

Any suggestions are greatly appreciated.

Even in AS2000 with your setup, LookupCube wasn't the best solution. The best solution would've been to create virtual cube, and then multiply the USD measure by ValidMeasure(Rate). The same approach should work in AS2005 as well, only instead of virtual cube, you would have two measure groups in your cube.|||

Thank you for the reponse.

Can you expand upon what you mean by using the ValidMeasure function?

I have a measure group with the Converstion Rate. The only dimension related to this measure group is the currency dimension. I would like to be able to have one calculated measure per currency code that multiplies all related measures stored in USD by the corresponding conversion rate.

So that leaves me with two issues:

1) How to apply the conversion rate to the measures based on what currency is selected in the currency dimension.

2) How to filter the solution to only apply to financial measures (the count measures should not adjust)

|||

Here is an example of how MDX Script could look like to achieve both of your issues. This assumes that Currency attribte is non-aggregatable, as it makes no sense to aggregate across different currencies.

({Measures.FinancialMeasure1, Measures.FinancialMeasure2, Measures.FinancialMeasure3}, Currency.Currency.Currency.MEMBERS) = Measures.CurrentMember*ValidMeasure(Measures.ConversionRate);

(more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx)

|||Works perfectly! Thanks! |||

It seems that this solution has caused another issue. The measures that this logic was applied to are not rolling up correctly. When I filter a dimension on the rows other than at the all level, the totals do not reflect the current selection of data.

For example, if I want to display the units and amount by product for all products, my result set might look something like this:

Product Units Amount

A 1 10

B 1 10

C 1 10

Total 3 30

If I want to look at only Product A, my result looks like this:

Product Units Amount

A 1 10

Total 1 30

The Total for the Units measure is correct because it's value does not change based on the currency that the user has selected, however, the Total for the Amount measure is incorrect and does not take into account what dimension filters are being applied.

Do you have any suggestions?

|||You can apply the currency conversion only at Leaves() - then they will be rolled up to the higher levels, and then visual totals should work correctly. However, be aware that the performance might become worse. Yet another approach would be to use measure expressions to do currency conversion during processing. This solution is not as flexible, but I think it will fit your scenario too.|||

I cannot use ValidMeasure with the Measure Expressions thus the conversion is inaccurrate. Can you expand on the Leaves() suggestion?

|||

I was able to use Measure Expressions to solve this problem after adding a date key to the currency conversion fact table. So now the currency conversion fact table looks like this:

Currency Key

Time Key

Conversion Rate

I am able to link the currency fact table to the other fact tables via the Time dimension and perform the currency conversion calculations using Measure Expressions based on what Currency is selected in Currency Dimension. Not as dynamic as placing this logic in an MDX Script but it works and the performance is great! Thanks Mosha!

Currency Conversion SSAS 2005

I would like to have calculated members in a currency dimension that will apply an exchange rate to all applicable measures based on what the user selects. All measures in the fact table are stored in one currency (USD) but need to be reported in a variety of currencies.

In AS 2000 this was accomplished via a lookup cube. I have read the documentation on books on line regarding currency conversion with the Business Intelligence Wizard in SSAS 2005 and this seems overly complicated and will not work based on our needs (since our data is stored in one currency and only the most recent exchange rate is important, no date dimension is necessary).

I have a fact table with with currency code and converstion rate. Since all of our measures are stored in USD, I do not have a currency key in the other fact tables. Is what I am trying to do possible without adding the currency key to all the fact tables?

Any suggestions are greatly appreciated.

Even in AS2000 with your setup, LookupCube wasn't the best solution. The best solution would've been to create virtual cube, and then multiply the USD measure by ValidMeasure(Rate). The same approach should work in AS2005 as well, only instead of virtual cube, you would have two measure groups in your cube.|||

Thank you for the reponse.

Can you expand upon what you mean by using the ValidMeasure function?

I have a measure group with the Converstion Rate. The only dimension related to this measure group is the currency dimension. I would like to be able to have one calculated measure per currency code that multiplies all related measures stored in USD by the corresponding conversion rate.

So that leaves me with two issues:

1) How to apply the conversion rate to the measures based on what currency is selected in the currency dimension.

2) How to filter the solution to only apply to financial measures (the count measures should not adjust)

|||

Here is an example of how MDX Script could look like to achieve both of your issues. This assumes that Currency attribte is non-aggregatable, as it makes no sense to aggregate across different currencies.

({Measures.FinancialMeasure1, Measures.FinancialMeasure2, Measures.FinancialMeasure3}, Currency.Currency.Currency.MEMBERS) = Measures.CurrentMember*ValidMeasure(Measures.ConversionRate);

(more details can be found here: http://www.sqljunkies.com/WebLog/mosha/archive/2005/12/06/multiplication_perf.aspx)

|||Works perfectly! Thanks! |||

It seems that this solution has caused another issue. The measures that this logic was applied to are not rolling up correctly. When I filter a dimension on the rows other than at the all level, the totals do not reflect the current selection of data.

For example, if I want to display the units and amount by product for all products, my result set might look something like this:

Product Units Amount

A 1 10

B 1 10

C 1 10

Total 3 30

If I want to look at only Product A, my result looks like this:

Product Units Amount

A 1 10

Total 1 30

The Total for the Units measure is correct because it's value does not change based on the currency that the user has selected, however, the Total for the Amount measure is incorrect and does not take into account what dimension filters are being applied.

Do you have any suggestions?

|||You can apply the currency conversion only at Leaves() - then they will be rolled up to the higher levels, and then visual totals should work correctly. However, be aware that the performance might become worse. Yet another approach would be to use measure expressions to do currency conversion during processing. This solution is not as flexible, but I think it will fit your scenario too.|||

I cannot use ValidMeasure with the Measure Expressions thus the conversion is inaccurrate. Can you expand on the Leaves() suggestion?

|||

I was able to use Measure Expressions to solve this problem after adding a date key to the currency conversion fact table. So now the currency conversion fact table looks like this:

Currency Key

Time Key

Conversion Rate

I am able to link the currency fact table to the other fact tables via the Time dimension and perform the currency conversion calculations using Measure Expressions based on what Currency is selected in Currency Dimension. Not as dynamic as placing this logic in an MDX Script but it works and the performance is great! Thanks Mosha!

Currency Conversion

I am using the cube intelligence wizard to add currency conversions. I have defined everything properly. At a certain moment, a query is launched against my Oracle backend. This is the query:

SELECT "Reporting Currency".*

FROM

(

SELECT "CCDM_IL"."CCDM_IL_CRRN_T"."CURRENCY_CODE","CCDM_IL"."CCDM_IL_CRRN_T"."CURRENCY_NAME"

FROM "CCDM_IL"."CCDM_IL_CRRN_T"

WHERE [CURRENCY_NAME] IN ('Euro') UNION

SELECT

DISTINCT

'Local' "Local",'Local' "Local 2"

FROM "CCDM_IL"."CCDM_IL_CRRN_T"

)

"Reporting Currency"

Now, this query returns the error "ORA-00936: Missing Expression". This error is caused by the square brackets around currency_name in the where clause. Once I remove these the query runs fine.

How can I make this query work?

Thanks

Joos

By the look of it this is coming from a named query in the DSV. Check if the wizard has actually inserted the square brackets into the named query in the DSV. If so, you should be able to edit the definition to remove the square brackets.|||

The query is generated by the BI wizard itself. In my DSV, I do not have a named query for the currency dimension. It is a real table...

The currency dimension has one attribute for which the key is set to CCDM_IL_CRRN_T.CURRENCY_CODE and the name is set to CCDM_IL_CRRN_T.CURRENCY_NAME.

|||Can you double check? Right click on the object in the DSV and tell me if the third menu item you see says either "Edit Named Query..." or "Replace Table with...". I would not normally ask, but the reason I am is becuase the BI wizard actually generates objects in your DSV and cubes, but it does not actually send any queries to the relational source.|||

For the table CCDM_IL_CRRN_T, it definitely says "Replace Table".

Joos

|||

Have you been able to isolate if this error is coming from a dimension or from a partition? (by processing objects associated with currencies one at a time)

Try going into SSMS, right clicking on the database and generating an alter script. This will generate a heap of xmla, search through this for the table name in square brackets or some other identifiable portion of this problem query. You can then either run the alter script and then import the database back into BIDS or hopefully this will help identify where exactly the problem query is coming from.

|||

In the script, the square brackets do not occur. Extracts where currency_name occurs:

<NameColumn>

<DataType>WChar</DataType>

<DataSize>255</DataSize>

<Source xsi:type="ColumnBinding">

<TableID>CCDM_CCDM_IL_CRRN_T</TableID>

<ColumnID>CURRENCY_NAME</ColumnID>

</Source>

</NameColumn>

<xs:element name="CURRENCY_NAME" msprop:FriendlyName="CURRENCY_NAME" mspropbColumnName="CURRENCY_NAME" minOccurs="0">

<xsimpleType>

<xs:restriction base="xstring">

<xs:maxLength value="255" />

</xs:restriction>

</xsimpleType>

</xs:element>

Joos

Currency conversion

Hi all,
I have an application based in Germany where the currency is the following
format: #.###,00
Users enter values in the above format but SQL Server requires the standard
format: #,###.00
Is there a way that I can tell SQL Server to convert formats automatically
or should I do the conversion in my code?
Regards,
IvanIvan Debono wrote:

> Hi all,
> I have an application based in Germany where the currency is the following
> format: #.###,00
> Users enter values in the above format but SQL Server requires the standar
d
> format: #,###.00
> Is there a way that I can tell SQL Server to convert formats automatically
> or should I do the conversion in my code?
> Regards,
> Ivan
Applications can do better formating of data than SQL Server.
Regards
Amish Shah