How do you control the format of the converted currencies? RRS feed

  • Question

  • I’m using Analysis Services 2008 (64bit), viewing through the BI browser. I have a measure which has the formatstring set to “#,##0.00;-#,##0.00”. I have used the currency wizard to generate the script for currency conversion. The conversions are all working fine. The problem is that the values do not honour the two decimal places that the measure formatstring is set to. I added the Format_string(this) = "#,##0.00;-#,##0.00"; within each of the two scopes to sort the problem out. Now I'm wondering what the performance cost is.





    // <Currency conversion>


                            // Currency conversion wizard generated script.

                            // Currency conversion generated on: 21 August 2009 10:12:05

                            // by user: keith.bruce   

                            // Currency conversion type: ManyToMany      

                            // Selected members to be converted: Amount LCY, Offshore Fixed LCY, Onshore LCY, Offshore Marine LCY, Refining LCY, Chemicals LCY, Conventional LCY, Nuclear LCY, Commercial LCY, Navy LCY, Pulp And Paper LCY, Mining And Metals LCY, Pharmaceutical LCY, Food And Beverages LCY, Utilities LCY, OG Unallocated LCY, PE Unallocated LCY, PO Unallocated LCY, SH Unallocated LCY, OT Unallocated LCY      

                            // Please be aware that any changes that you decide to make to it may be overridden the next time you run the Currency Conversion wizard again. 


                            // This is the Many to One section        

                            // All currency conversion formulas are calculated for the pivot currency and at leaf of the time dimension           

                            Scope ( { Measures.[Amount LCY] } );

                                        Scope( Leaves([Period]) ,

                                                    [Reporting Currency].[GBP],   





                                  // Convert Local value into Pivot currency for selected Measures that must be converted with Measure rate [Avg Rate]

                                  Scope( { Measures.[Amount LCY] } );


                                   This = [Reporting Currency].[LOCL] * Measures.[Avg Rate];



                                  End Scope;



                                        End Scope;     


                                        // This is the One to Many section

                                        // All currency conversion formulas are calculated for the non pivot currency and at leaf of the time dimension        

                Scope( Leaves([Period]) ,       

                                                    Except([Reporting Currency].[Currency Code].[Currency Code].Members,

                                                    {[Reporting Currency].[Currency Code].[Currency Code].[GBP],

                                                    [Reporting Currency].[Currency Code].[Currency Code].[LOCL]}));




                                // This section overrides the local values with the Converted value for each selected measures needing to be converted with Measure rate [Avg Rate]… 

                                // LinkMember is used to reference the currency from the source currency dimension in the rate cube.

                        Scope( { Measures.[Amount LCY] } );

                                       This = [Reporting Currency].[Currency Code].[GBP] / (Measures.[Avg Rate], LinkMember([Reporting Currency].[Currency Code].CurrentMember, [Currency].[Currency Code])) ;


                                End Scope; 



                                        End Scope; // Leaves of time, all reporting currencies but local and pivot currency         

                            End Scope; // Measures


                            // End of the currency conversion wizard generated script

                // </Currency conversion>




                                                    Format_string(this) = "#,##0.00;-#,##0.00";

                                             Format_string(this) = "#,##0.00;-#,##0.00";

    • Edited by KBR95 Monday, August 24, 2009 7:28 AM
    Friday, August 21, 2009 9:52 AM