Skip to Content
Technical Articles
Author's profile photo Ashutosh Rastogi

Creating a custom crosstab with R in SAP Analytics Cloud

In this blog, I will talk about how R’s integration in SAP Analytics Cloud was leveraged to solve a problem of representation of data.

Problem Statement

A customer was using an SAP BusinessObjects dashboard (xcelsius), and they wanted to re-create the dashboard in SAC. The data source in this case was SAP BW and the BW query output looked like following

Project Date LY Revenue LY Margin LY Taxes LM Revenue LM Margin LM Taxes CTM Revenue CTM Margin CTM Taxes
PRJ002 Mar-17 23423 343 234
PRJ002 Jun-17 333 32 34
PRJ002 Jul-17 234 23 25

In the above query, you can note the following

  • Output data is for a given project (PRJ002) and for a given month (JUL-17)
  • The measure prefix LY=Last Year, LM= Last Month and CTM=Current Month

Now, they wanted to represent this data in crosstab in the following manner. Also while doing that they wanted to have a custom calculation which is simple: Current Month – Last Month of the measure.

LY LM CTM (CTM-LM)
Revenue 23423 333 234 -99
Margin 343 32 23 -9
Taxes 234 34 25 -9

Note, the above table is completely built of measures and they are arranged in a manner where the column header and rows are completely derived from the measure names.

Solution

This kind of representation can be easily done in R visualization in SAC. In case you would like to know how to create a basic R visualization in SAP Analytics Cloud, refer here

We need to add all the measures to the context of R visualization in order to create desired crosstab output. And then the script should go like this

Include the R packages required for drawing the table

library(gridExtra)
library(gtable)

Create the Vectors for the respective Columns

# MyModel is the dataset name for my query
Headers = c ("Revenue", "Margin", "Taxes")			 
LY = c(MyModel$`LY Revenue`, MyModel$`LY Margin`,MyModel$`LY Taxes`)
LM = c(MyModel$`LM Revenue`, MyModel$`LM Margin`,MyModel$`LM Taxes`)
CTM = c(MyModel$`CTM Revenue`, MyModel$`CTM Margin`,MyModel$`CTM Taxes`)		 
Delta = c(CTM[1] - LM[1], CTM[2] - LM[2], CTM[3] - LM[3])

Define the formatting and alignment of the text inside each vector

  • Numbers should have “.” as decimal separator and “,” and number separator.
  • Numbers should be aligned to right while text labels should be aligned to Left
  • Number of digits post decimal should be 2
Headers <- format(Headers, justify = "left")
LY <- format(LY, digits=5, nsmall=2, big.mark=",", justify = "right")
LM <- format(LM, digits=5, nsmall=2, big.mark=",", justify = "right")
CTM <- format(CTM, digits=5, nsmall=2, big.mark=",", justify = "right")
Delta <- format(Delta, digits=5, nsmall=2, big.mark=",", justify = "right")

Now create a data frame out of these vectors

df.data <- data.frame(LY, LM, CTM, Delta)

We will also define the look and feel of the table via editing an existing theme, defining the foreground and background parameters for row header, col header and core data

(Refer https://www.statmethods.net/advgraphs/parameters.html to know more about theme prameters)

tt2 <- ttheme_default(
  core=list(bg_params = list(fill = c("#fafafa", "white"), lwd=0.25, lty=1, col="#cecece"), 
            fg_params=list(hjust=1, x=0.95, cex=1.25)),
  colhead=list(bg_params = list(fill = "#4682b4", col=NA, lwd=5), 
               fg_params=list(col = "white", fontface="bold", cex=1.25)),
  rowhead=list(fg_params=list(hjust=0, x=0, cex=1.25, fontface="plain")))

Now, we are ready to display the data frame as a crosstab/table/xtable as you would like to call it.

tg <- tableGrob(df.data, rows = Headers, theme = tt2)
grid.arrange(tg)

And your desired output is displayed as

Summary

R integration in SAP Analytics Cloud provides interesting capabilities that customers/partners can leverage to address business needs.

More useful links w.r.t R and SAP Analytics Cloud

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Ingo Hilgefort
      Ingo Hilgefort

      Hello Ashutosh,

       

      why going for an R scenario when this is pretty easy to do in the BW Query or in SAC using the Restricted / Calculated Measures ?

       

      Regards

      Ingo Hilgefort, SAP

      Author's profile photo Ashutosh Rastogi
      Ashutosh Rastogi
      Blog Post Author

      Hi Ingo,

      They did not want to modify their BW query.

      Regards

      Ashutosh