none
Currency Conversion Wizard MDX Script error RRS feed

  • Question

  • Hi guys,

    I am hoping someone can help me, I am trying to do exchange rates in my multidimensional model.

    The Fact Table contains data in different currencies, I have an exchange rate measure set to AverageOfChildren which is connected to the date table.

    I also have a Currency Dimension which just contains the currency name "EUR" etc. The Currency attribute has isAgrettable to False.

    Also the Exchange rate table rates is all EUR (Pivot Currency).

    I then used the Business Intelligence Wizard for exchange rates, everything worked correctly and I created a Many to Many as the Fact Data is stored in multiple currencies and I want to be able to report in multiple dimensions.

    I then try to process but it fails because of the MDX script here is what my script looks like:

    /*
    The CALCULATE command controls the aggregation of leaf cells in the cube.
    If deleted or modified, the data within the cube will be affected.
    This command should only be edited if you intend on manually specifying how the cube will be aggregated.
    */
    CALCULATE; 
    
    // <Currency conversion>
    		
    		// Currency conversion wizard generated script.
    		// Currency conversion generated on: 10 November 2018 17:57:32	
    		// by user: js	
    		// Currency conversion type: ManyToMany	
    		// Selected members to be converted: Sales - Local Currency	
    		// 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.[Sales - Local Currency]} );
    			Scope( Leaves([Date]) ,
    				[Reporting Currency].[EUR],	
    				Leaves([Currency]));
    		
    				
    		      
    		      // Convert Local value into Pivot currency for selected Measures that must be converted with Measure rate [Rate] 
    		      Scope( { Measures.[Sales - Local Currency]} );
    			         
    		       This = [Reporting Currency].[Local] / Measures.[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([Date]) , 	
    				Except([Reporting Currency].[Currency].[Currency].Members, 
    				{[Reporting Currency].[Currency].[Currency].[EUR],
    				[Reporting Currency].[Currency].[Currency].[Local]}));
    		
    				
    		    
    		    // This section overrides the local values with the Converted value for each selected measures needing to be converted with Measure rate [Rate]…	
    		    // LinkMember is used to reference the currency from the source currency dimension in the rate cube. 
    	        Scope( { Measures.[Sales - Local Currency]} );
    		           This = [Reporting Currency].[Currency].[EUR] * (Measures.[Rate], LinkMember([Reporting Currency].[Currency].CurrentMember, [Currency].[Currency])) ;
    		    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>

    The errors I get are in the attached 

    I have tried messing around with the script but I cannot get it to work, can anyone help please?

    Thanks,

    Joe

    Saturday, November 10, 2018 6:37 PM

Answers

  • Ignore this I had some naming violations for example I had named my Date Table "Date" and the key "Date" so the MDX script didn't know what it was looking at.

    Maybe someone else will be as silly as me :-)

    • Marked as answer by JoeyStyles Sunday, November 11, 2018 4:26 PM
    Sunday, November 11, 2018 4:26 PM