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

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.

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

• Using R with SAP Analytics Cloud
• Adding R Visualizations to Stories
• Connecting to an R Environment
• Best practices for connecting and running your R environment
• Setup R server for use with SAP Analytics Cloud