Creating a custom crosstab with R in SAP Analytics...

ashutosh_rastogi · ‎11-05-2018

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