Skip to Content
Technical Articles
Author's profile photo Jay Gandhi

Creating Dynamic Version over Version and Time Reporting with SAP Analytics Cloud (Column over Column)

SAP Analytics Cloud like any Planning and Reporting tool has some of the same asks and requirements as other SAP legacy tools and competitor products. I’ve been fortunate to be in the Planning-space with SAP for over a decade with a good chunk of that in the Consulting and Services space.

What I’ve found is that despite many business trends emerging and surface level requirements changing there are foundational and timeless needs that all businesses will continue to have. What I’d like to walkthrough in this blog post is one of these basic business requirements and how native SAP Analytics Cloud capabilities can address these.

One of our tried-and-true basic reporting requirements in any Planning or Analytics solution continues to be Comparative or Variance reporting. Regardless of being purely Planning, purely Analytics related or if it’s a combination of both. but rather could be a combination of both. This also transcends any pure Finance-driven function, at the end of the day any LoB or Function calls for the same requirement. Simply put, I have a Version, actual/budget/forecast/plan for revenues/expenses/production/demand and would like to compare against another Version (fill in the underlined as the base requirement remains unchanged).

Now, how do we create a report in SAP Analytics Cloud that allows a user to dynamically select the Time-periods and Versions to compare against?

In this How-To I’ll walk through a scenario where you would like a user to select a version and time period in ‘column A’ to another user selection to select a version and time period in ‘column B’. The use of Story or Group filters typically override user selections (via Page Filters) and render this scenario null.

First, you’ll need to add Cross Calculations into your column definition (in my scenario, I’ve renamed mine into Story Calculations).

Next, you will need to create two ‘Restricted Measures such as:

Restricted%20Measure%20Example%20%28click%20image%20to%20enlarge%29

Restricted Measure Example (click image to expand)

 

The underlined red dimensions relate to the ‘user selections’ which refer to the input controls:

Corresponding%20User%20Input%20Controls%20%28Click%20Image%20to%20Enlarge%29

Corresponding User Input Controls (click image to expand)

 

The underlined green ‘enable constant selection’ can be used IF a Story/Group filter is used for these dimensions (e.g. Time) and you would like the user selection to override it. For example, if a user selects 2020 for other widgets (tables and charts) on the Story tab (via a Story filter), however, for this table you would like to select 2019, you would select this and enable constant selection for Time.

The defined Restricted Measures would then be placed in the Columns as calculations:

Table%20Definition%20%28click%20image%20to%20expand%29

Table Definition (click image to expand)

 

 

And the final result would be the following table being driven based off of user selections for ‘comparison versions/time’ in a column over column fashion:

Final%20Table%20Result%20%28click%20image%20to%20expand%29

Final Table Result (click image to expand)

 

In the blog post found here please see how to allow a user to perform Version over Version comparative reporting with the Time dimension also in the column axis allowing for drill capability (all for a Total Year or Monthly breakdown comparison).

This concludes our How-To, I hope you have found this blog post useful as I feel it’s important to understand some of the foundational pieces of SAP Analytics Cloud when it comes to planning and reporting.

As we progress into the new year I will continue writing on a host of topics as it pertains to SAP Analytics Cloud. My goal will be to continue to provide my experiences with creating various solutions to business problems and customer requirements. The underlying theme for these solutions will be focusing on best practices and where creativity is necessitated still having an eye on sustainability.

Thank-you and stay tuned for subsequent blog posts.

Assigned Tags

      5 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Jef Baeyens
      Jef Baeyens

      Hi Jay, great subject but can you add some more details on how you actually set this up?

      Some steps seem left out. Could it be that in columns you’ve added ‘Cross Calculations’ and renamed it to ‘Story Calculations’ and then added restricted measures?

      Author's profile photo Jay Gandhi
      Jay Gandhi
      Blog Post Author

      Great catch Jef Baeyens I’ve updated the post for posterity sake.

      The Story Calculations were indeed renamed from the default Cross Calculations name. To make it clear I’ve called out that step now, much appreciated.

      Author's profile photo Kavitha PAIPALLE
      Kavitha PAIPALLE

      Hi Jay,

      Thank you for the blog.

      Could you please help me understand if the below requirement can be achieved in SAC.

      In our SAC Planning project, forecasting for 24 months is done for each version. (so, version is concatenated with the year ex: 2022_FC01).

      The requirement is to compare forecasts of different Years and Versions. Eg: Compare 2021_FC01 (version), 2021(year) with 2022_FC02 (version), 2022 (year)

      Note: The comparison has to be at month level as below.

      Variance%20Reporting%20Mockup Variance Reporting Mockup

      Regards
      Kavitha

      Author's profile photo Jay Gandhi
      Jay Gandhi
      Blog Post Author

      Hi Kavitha,

      Yes you could still perform the above. You could take the approach I outlined in this blog post: Creating Dynamic Version over Version with System Date Dynamic Time Reporting with SAP Analytics Cloud (With Time Drill Capability) | SAP Blogs. Basically a user would make two version selections and a single date/year selection and the other date/year could be "previous year". The date drill would still display at the month level. In this scenario though, as shown in the blog post the data will all display in the primary year selection even though it is showing data from another time period. If you look at the blog post closely, you'll see what I mean.

      Jay

      Author's profile photo Vasileios Zarkadoulas
      Vasileios Zarkadoulas

      Hello,

      The above approach covers what we need, but its ugly to display in the description "Base Comparison" or "Previous Year Comparison" etc. The system should allow the dynamic text variable to be displayed in the description as is in the Bex Queries.

       

      Best Regards