Mastering Variable-Driven Currency Conversion in SAP Analytics Cloud Planning

Hello,
I’m Filippo Naggi, an Enterprise Architect with a deep passion for enterprise technologies, specifically SAP SAC, SAP BPC, Onestream, and BigQuery. Today, I am excited to take you through a detailed guide on how to build a variable-driven measure for currency conversion within SAP Analytics Cloud (SAC). This technique will enable you to select the desired destination currency dynamically and apply it seamlessly across various reports and analyses within your model.

Currency conversion is a critical aspect of financial modeling, especially when dealing with multi-currency datasets. Having the flexibility to switch between different currencies while maintaining consistency across measures is vital for making informed business decisions. In this blog, we will cover step-by-step how to create such a solution using a variable-driven approach in SAC.

Step 1: Model Setup

First, we’ll begin by setting up the model. Our model includes two dimensions, and we start with a source measure in USD (U.S. dollars), which will be our default currency for this exercise. As shown in Fig 1.x, this setup is foundational to ensure that our measures are properly aligned with the currency variable we will be adding later.

Fig. 1.1 The Multidimensional Model

Fig. 1.2 The Source measure in USD – Measure name: Translated

Step 2: Activating Currency Conversion

Next, we activate the built-in feature for currency conversion within SAC. SAC provides a robust framework to handle currency conversions dynamically, and by activating this feature, we can streamline the process. In Fig 2, you’ll see how the currency conversion option is enabled within the model, making it ready to support multiple currencies.

Fig.2 Currency Conversion Model Setup

Step 3: Building a Variable for Currency Conversion

The core of our approach lies in creating a variable that drives the currency conversion. This variable allows users to select the desired target currency, which can be applied universally throughout the system. In Fig 3, I will walk you through how to build and configure this variable step-by-step.

Fig.3 Currency Variable Settings

Step 4: Currency Rate Table

Of course, the actual conversion rates must be managed, and SAC simplifies this process through the use of a currency rate table. This table stores all the exchange rates that will be used in the conversion calculations. Fig 4 shows an example of how this table is configured and how the system pulls the relevant rates for each currency conversion.

Fig 4. Rates setup

Step 5: Applying the Variable in a Story

Once the variable is ready, it’s time to integrate it into a story. In this example, we’ll build a table in the story that leverages the currency conversion variable. SAC will automatically prompt users to select the currency at runtime. As shown in Fig 5, the system can even provide a default currency, which can be changed based on the user’s preferences.

Fig.5 Variable Story Input

Step 6: Runtime Selection of Different Currencies

The beauty of this solution is the flexibility it offers at runtime. Users can easily select different currencies based on the available options in the variable. As demonstrated in Fig 6.x, users can toggle between different currency selections, and the entire dataset will automatically adjust to reflect the newly selected currency.

Fig 6.1 USD Toggle

Fig 6.2 EUR Toggle

Step 7: Using the Feature in the Excel Plug-In

Lastly, it’s worth noting that this functionality isn’t limited to just the web interface of SAC. The variable-driven currency conversion can also be utilized through the SAC Excel plug-in. This allows users to work with currency conversions directly within their Excel environment, providing additional flexibility for analysis and reporting. Fig 7.x shows an example of how the feature operates within the plug-in.

Fig 7.1 Variable Selector – USD

Fig 7.2 Rate: USD

Fig 7.3 Variable Selector – EUR

Fig 7.4 Rate: EUR

Conclusion

In conclusion, variable-driven currency measures are a powerful feature within SAP Analytics Cloud, providing unmatched flexibility and control over financial reporting and analysis in multi-currency environments. This functionality highlights the multidimensional capabilities of SAC, making it an ideal tool for enterprise-scale financial planning and analysis.

If you have any questions or require further assistance in implementing this feature, feel free to reach out to me directly. I’d be happy to assist you in exploring the full potential of variable-driven currency conversion in your SAC environment.

Thank you for following along and Stefano for kicking off the interest in deep diving into this.

I hope you all found this guide helpful!

Best regards,
Filippo Naggi

​ Hello,I’m Filippo Naggi, an Enterprise Architect with a deep passion for enterprise technologies, specifically SAP SAC, SAP BPC, Onestream, and BigQuery. Today, I am excited to take you through a detailed guide on how to build a variable-driven measure for currency conversion within SAP Analytics Cloud (SAC). This technique will enable you to select the desired destination currency dynamically and apply it seamlessly across various reports and analyses within your model.Currency conversion is a critical aspect of financial modeling, especially when dealing with multi-currency datasets. Having the flexibility to switch between different currencies while maintaining consistency across measures is vital for making informed business decisions. In this blog, we will cover step-by-step how to create such a solution using a variable-driven approach in SAC.Step 1: Model SetupFirst, we’ll begin by setting up the model. Our model includes two dimensions, and we start with a source measure in USD (U.S. dollars), which will be our default currency for this exercise. As shown in Fig 1.x, this setup is foundational to ensure that our measures are properly aligned with the currency variable we will be adding later.Fig. 1.1 The Multidimensional ModelFig. 1.2 The Source measure in USD – Measure name: Translated Step 2: Activating Currency ConversionNext, we activate the built-in feature for currency conversion within SAC. SAC provides a robust framework to handle currency conversions dynamically, and by activating this feature, we can streamline the process. In Fig 2, you’ll see how the currency conversion option is enabled within the model, making it ready to support multiple currencies.Fig.2 Currency Conversion Model Setup  Step 3: Building a Variable for Currency ConversionThe core of our approach lies in creating a variable that drives the currency conversion. This variable allows users to select the desired target currency, which can be applied universally throughout the system. In Fig 3, I will walk you through how to build and configure this variable step-by-step.Fig.3 Currency Variable SettingsStep 4: Currency Rate TableOf course, the actual conversion rates must be managed, and SAC simplifies this process through the use of a currency rate table. This table stores all the exchange rates that will be used in the conversion calculations. Fig 4 shows an example of how this table is configured and how the system pulls the relevant rates for each currency conversion.Fig 4. Rates setup  Step 5: Applying the Variable in a StoryOnce the variable is ready, it’s time to integrate it into a story. In this example, we’ll build a table in the story that leverages the currency conversion variable. SAC will automatically prompt users to select the currency at runtime. As shown in Fig 5, the system can even provide a default currency, which can be changed based on the user’s preferences.Fig.5 Variable Story Input    Step 6: Runtime Selection of Different CurrenciesThe beauty of this solution is the flexibility it offers at runtime. Users can easily select different currencies based on the available options in the variable. As demonstrated in Fig 6.x, users can toggle between different currency selections, and the entire dataset will automatically adjust to reflect the newly selected currency.Fig 6.1 USD ToggleFig 6.2 EUR Toggle   Step 7: Using the Feature in the Excel Plug-InLastly, it’s worth noting that this functionality isn’t limited to just the web interface of SAC. The variable-driven currency conversion can also be utilized through the SAC Excel plug-in. This allows users to work with currency conversions directly within their Excel environment, providing additional flexibility for analysis and reporting. Fig 7.x shows an example of how the feature operates within the plug-in.Fig 7.1 Variable Selector – USDFig 7.2 Rate: USDFig 7.3 Variable Selector – EURFig 7.4 Rate: EUR  ConclusionIn conclusion, variable-driven currency measures are a powerful feature within SAP Analytics Cloud, providing unmatched flexibility and control over financial reporting and analysis in multi-currency environments. This functionality highlights the multidimensional capabilities of SAC, making it an ideal tool for enterprise-scale financial planning and analysis.If you have any questions or require further assistance in implementing this feature, feel free to reach out to me directly. I’d be happy to assist you in exploring the full potential of variable-driven currency conversion in your SAC environment.Thank you for following along and Stefano for kicking off the interest in deep diving into this.I hope you all found this guide helpful!Best regards,Filippo Naggi   Read More Technology Blogs by Members articles 

#SAP

#SAPTechnologyblog