No Longer Valid As of Q4 QRC 2022
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. This also transcends any pure Finance-driven function, at the end of the day any LoB or Function calls for the same requirement.
In this blog post, we demonstrated the ability to create a table/report for dynamic Version over Ver...
Now, how do we create a report in SAP Analytics Cloud that allows a user to dynamically select the Versions and also show a year over year comparison dynamically based off of the System Date (Calendar Date), in addition to allowing a user with the ability to drill into the year to see Month/Period values in addition to the total Annual value?
Simply put, say in 2020 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).
I'll walk through a scenario where you would like a user to select a Version and compare to either the same Version or another Version and see how the values have changed (trend) year over year. In this scenario they would like to do a comparison between the current calendar Year and a prior year. This is a very common scenario for forecasts and the key here is that neither the user nor a report designer will need to maintain the dynamic year references. The report/table will always reference the system date (calendar date) and via the time filter for the table and ‘constant selection’ in the restricted measures it will allow an offset (in our case minus 1, or previous period).
First, you'll need to add Account Story Calculations into your column definition.
Next, you will need to create two ‘Restricted Measures such as below:
The underlined blue relates to the ‘user selection’ which refer to the input control for the user to select:
The yellow highlighted sections are critical for the dynamic time reference. The ‘Previous Period’ will dynamically select the current year – 1 (e.g., if the current date is 2020, this will result in 2019). The ‘Show Values for All Time Levels’ will ensure when a user drills into the months the values show the month values instead of an aggregated annual value in each month. And lastly the ‘Enable Constant Selection’ will ensure the Date selection will override the Time filter value in the table itself.
In the ‘Date’ filter within the table it is imperative to select the ‘Dynamic Time Filter’ to select ‘Current Year’, this will ensure that when the report is run it is always using the current year as the time reference instead of hardcoding a time value which will need to be maintained within each cycle:
The ‘Base Version’ Restricted Measure will have a similar definition as the ‘Comparison Version’, however, there will be no time definition which means it will pick up the Time filter in the table itself (‘Current Year’):
The underlined orange relates to the ‘user selection’ which refer to the input control for the user to select:
The defined Restricted Measures would then be placed in the Columns as calculations:
And the final result would be the following table being driven based off of user selections:
This will also allow the user to drill into the months as well as seeing the total annual values:
This allows for a side-by-side comparison and because of our Dynamic Time Filters AND ‘Previous Period’ definition the value in the ‘Comparison Version’ will actually be Prior Year (in our case 2019).
It is key here that due to current limitations in SAP Analytics Cloud you cannot use Group Filters or Story Filters for the Time dimension and similarly for now you need to have Time derived based off of the System Date (for ultimate flexibility it would be opportune to allow the user to select the years as needed vs. being anchored in this manner). The ability to have this ultimate flexibility with regards to time reference would be a welcome enhancement to the product.
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.
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. This also transcends any pure Finance-driven function, at the end of the day any LoB or Function calls for the same requirement.
In this blog post, we demonstrated the ability to create a table/report for dynamic Version over Ver...
Now, how do we create a report in SAP Analytics Cloud that allows a user to dynamically select the Versions and also show a year over year comparison dynamically based off of the System Date (Calendar Date), in addition to allowing a user with the ability to drill into the year to see Month/Period values in addition to the total Annual value?
Simply put, say in 2020 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).
I'll walk through a scenario where you would like a user to select a Version and compare to either the same Version or another Version and see how the values have changed (trend) year over year. In this scenario they would like to do a comparison between the current calendar Year and a prior year. This is a very common scenario for forecasts and the key here is that neither the user nor a report designer will need to maintain the dynamic year references. The report/table will always reference the system date (calendar date) and via the time filter for the table and ‘constant selection’ in the restricted measures it will allow an offset (in our case minus 1, or previous period).
First, you'll need to add Account Story Calculations into your column definition.
Next, you will need to create two ‘Restricted Measures such as below:
Restricted Measure (Current Year minus 1) Example (click image to expand)
The underlined blue relates to the ‘user selection’ which refer to the input control for the user to select:
User Input Control (Restricted Measure 1) (click image to expand)
The yellow highlighted sections are critical for the dynamic time reference. The ‘Previous Period’ will dynamically select the current year – 1 (e.g., if the current date is 2020, this will result in 2019). The ‘Show Values for All Time Levels’ will ensure when a user drills into the months the values show the month values instead of an aggregated annual value in each month. And lastly the ‘Enable Constant Selection’ will ensure the Date selection will override the Time filter value in the table itself.
In the ‘Date’ filter within the table it is imperative to select the ‘Dynamic Time Filter’ to select ‘Current Year’, this will ensure that when the report is run it is always using the current year as the time reference instead of hardcoding a time value which will need to be maintained within each cycle:
Table Date Filter (click image to expand)
The ‘Base Version’ Restricted Measure will have a similar definition as the ‘Comparison Version’, however, there will be no time definition which means it will pick up the Time filter in the table itself (‘Current Year’):
Restricted Measure Example (click image to expand)
The underlined orange relates to the ‘user selection’ which refer to the input control for the user to select:
User Input Control (Restricted Measure 2) (click image to expand)
The defined Restricted Measures would then be placed in the Columns as calculations:
Table Definition (click image to expand)
And the final result would be the following table being driven based off of user selections:
Final Table Result (click image to expand)
This will also allow the user to drill into the months as well as seeing the total annual values:
Drill Into Months (click image to expand)
This allows for a side-by-side comparison and because of our Dynamic Time Filters AND ‘Previous Period’ definition the value in the ‘Comparison Version’ will actually be Prior Year (in our case 2019).
It is key here that due to current limitations in SAP Analytics Cloud you cannot use Group Filters or Story Filters for the Time dimension and similarly for now you need to have Time derived based off of the System Date (for ultimate flexibility it would be opportune to allow the user to select the years as needed vs. being anchored in this manner). The ability to have this ultimate flexibility with regards to time reference would be a welcome enhancement to the product.
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.
- SAP Managed Tags:
3 Comments
