Author – Charles Braybrook, Consultant – Analytics, itelligence UK
Picture the scene; you are building a story in SAP Analytics Cloud, you have your page nicely setup with selection charts, tables and numeric points in exactly the way you want it. To complete the visual, you want to add some titles, however not just any titles. You want to add titles that will change dynamically to reflect the data you are reporting and just to make it more complicated, the data you want to show in the titles is not available within any of the data sources you are connecting to.
I can hear you saying to yourself, “this is not possible”. Wrong! This is possible in SAP Analytics Cloud and let me tell you how.
Before we start, let’s just talk about dynamic text and how the content in this blog differs. I’m sure some of the more SAC savvy amongst you might point out that we can use the dynamic text option, well so did I. Sadly this is not the case. Whilst the dynamic text does allow us to bring in dimensions used in page filters, story filters and dimensions amongst other options; it does not have the option to be filtered itself. The idea here is that we have multiple titles (or plain text depending on your needs) across a single page that can each be filtered and influenced in a customised and dynamic fashion.
In the example above we are analysing the volumes of product being transported between various locations by time.
We have a page with two-time series charts that show the volume of shipments by both inward and outward transport routes (e.g. Dublin to Cork and Cork to Dublin), and these will differ depending on the selection in the country filter. Selecting IE in the country filter will filter the two charts by Ireland transport routes and Great Britain transport routes when GB is selected.
What is not obvious from the example above is which routes these charts are currently reporting, and this is where we need titles to add some context. What we are looking to include is a dynamic title that will change depending on selection of the country filter. So in this example, the left-hand chart would show “From London to Manchester” when the country is filtered on GB and “From Dublin to Cork” when filtered on IE and the reverse journey will be shown on the right-hand chart.
To achieve this, we need to create a SAC model (for the purpose of this example we will call the model ‘TransportRoutes’), and this model will contain the transport routes that relate to the various countries, remembering this is the data that did not exist in our original data source. This can be achieved by creating a straightforward Excel file like the one shown below:
The “Measure” column is irrelevant and is only there due to SAP Analytics Cloud models requiring at least one measure.
Now we have the data, we now need the titles and to get a dynamic custom title we do not use the text object, but instead, we use the table object.
The first thing we need to do is insert a table into our story and use the ‘TransportRoutes’ model created above as the data source. Under columns add the ‘Route’ dimension and then hide everything else, (Table Title, Subtitle, Grid etc. Your report should end up like the example below:
Next we need to filter the table on the two titles that we want to switch between. (From Dublin to Cork and From London to Manchester for the left-hand chart) And vice versa for the right-hand chart. This can be achieved in the “Designer” panel (make sure that Builder is selected) and by adding a filter on the ‘Route’ dimension for the routes we want.
Once we have created the filters shown above, we need to link the models which contain the transactional data and the ‘TransportRoutes’, and we do this by linking the country dimensions in both models as shown below.
The model linking will ensure that by changing the value in the country filter, the transport routes in the title will dynamically change. We should only be able to see the transport route appropriate to the country we are filtering.
Now that we have the mechanics of the titles working, the final step is to edit the style of the table in such a way that we hide the fact that it is a table we are using. To do this we select the Styling option on the Designer panel. From here we can create new styles, and for this example, we have created two styles one called ‘Title’ and one called ‘Empty’.
Let’s go off on a brief tangent on Styling in SAC for a moment. Be all technical if you want, but part of the reason we’re doing all of this is to “pretty up” SAC which to be honest is fairly pretty to start with, but there is no harm in “dolling” it up a bit more.
In SAC we can use Styling at a very detailed level and in the example of a Table widget, whilst the Styling panel is open we can highlight anything from the entire table to a single cell depending on your selection.
When you have created your table selecting the title of the table will focus the Styling to the Header Region, also at this point, we have the option of creating and choosing new Styles. Styles are reusable across your entire story, not just a single widget, and this means you can keep the formatting consistent without having to remember precise font sizes and colours etc.
When you add a new ‘Style’ (clicking the ‘+’ icon) we get a default style panel:
In my example, I have changed this new ‘Style1’ to the “Title” style by changing it to the following:
Obviously this can be pretty much whatever you want.
The “Title” style controls the content, font and colour of the title to be displayed. The Empty style, on the other hand, is there to make everything invisible and to do this we set the font opacity to 0, the cell fill to transparent and hide all lines. There are no other requirements for the “Empty” Style.
Once we have completed the styling for the left-hand chart, we repeat the process for the right-hand chart.
Finally, we end up with two charts that have dynamically changing titles based on the value selected on the country filter and below is the final output.
For Great Britain:
This method can be applied in multiple areas across your Story. Whether it is a full on title, or if you just want to make a small subsection pop out a bit more. We have found this to be an effective way to make our Stories just that little bit more fluid and interactive.
One of the many benefits of SAP Analytics Cloud tends to be that whilst at first it might seem there isn’t a way of doing something, if you explore enough and think outside the box a little there is usually a way around the problem.
I would highly recommend anyone who is heavily invested in SAP Analytics Cloud actively checks on the Customer Influence page as this has an active role in how this product will be developed in the coming years so have your say in what you would like to see.
If you would like to discuss any of the content covered in the blog please do not hesitate to contact us.