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.
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|
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.
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
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 - LM, CTM - LM, CTM - LM)
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
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