We will look at how to implement multi currency using the Currency business function.
Before you can implement the business function, you have to insure a proper exchange rate table in your data warehouse. This table has to have the following fields: Company, Currency Code, Date, Exchange Rate. It also has to have a record for each day for each currency code you want to use later on in the cube. You can refer to our standard Jet Analytics NAV and Jet Analytics AX projects on how to implement this.
You will also need to have Company, Currency and Date dimensions already set up in your project.
First you need to add an Exchange Rate measure. To do that you will most probably need to add your Exchange Rate table as a fact table to the cube. Right click on your cube and select Edit. Tick your Exchange Rate table in the list of tables.
Once you have the table in the cube, you can go ahead and right click the Measures node and add a Standard Measure. Give it a name, like Exchange Rate, select the correct table and field and then make sure Type is set to Last Child and Visible checkbox is not checked.
Second step is to add a Script Command. Right click on Script Commands node, select Add Business Function > Scrip Commands > Currency.
If you do not have an option to Add Business Function or you can't find Currency business function, you will need to download the latest business function library from here .
Give the script command a name, for instance Currency. You will then need to populate parameters with values and members. The first parameter is a list of all the measure groups (each fact table in your cube creates one measure group with the same name as the table) you want this functionality to work for. Check the description field for an example and use it as a template. The next four parameters need to be populated with members. Please pay attention to the Parameter Type field, so you know, if you need to drag in a Dimension, Dimension Level or a Measure (sometimes Dimensions and Dimension Levels are named the same!). You will also need to select the correct Variant. Again, check the description field for instructions.
Once you have everything set up, deploy and execute the project.