Comment Copy Tool for SAP Analytics Cloud Planning

Recently, I fielded several requests from SAP Analytics Cloud Planning customers asking, “How do you copy the comments from one forecast version to a new one?” A common practice in Financial Planning & Analysis (FP&A) is to explain variances between forecasts and actuals using comments. However, at the time of writing, comments were not included when creating a new forecast as a copy of an old one. You might do this to archive a forecast from last year when you start working on this year. Below are the steps needed to implement an analytics designer based “comment copy” tool in your own SAP Analytics Cloud tenant.

This example utilizes the SAP Analytics Cloud for planning sample content you can download from the Content Network but it will work with any model:

Content Network

The resultant application has the following features and limitations:

1. Ability to copy all comments from the visible cells (i.e. selections) of a table into the equivalent visible cells of a second table.
   1. Table layouts should be identical, meaning the same columns, rows, and background filters.
   2. The only difference should be in the version filter, where the first table has the source of the comments and the target table the new version you want to copy the comments to.
2. Due to limitations with displaying large result sets, the sample can be enhanced to apply dimension filters to both tables and execute on slices of the data.
3. addComments() API does not accept a CommentInfo object, such as

   commentId: "42524185-6885-4378-8373-357994642734"
   createdAt: "Feb 3, 2021 20:01"
   createdBy:
     displayName: "James Rapp"
     id: "JAMESRAPP"
   numberOfLikes: 0
   text: "C900 BIKE / Apr 2019"

   It only accepts the text value. Thus, comments associated with the destination version will display under the username of the account used to run the application, and with a corresponding timestamp of when the comment was entered programmatically.

4. This sample is provided as-is and does not entail support from SAP. Please use at your own risk.

Application Structure

1. Table_1 – The table to copy comments from
2. Table_2 – The table to copy comments to. Configured identically to Table_1 but with a different background filter on Version
3. B_getComments – Button that executes helper functions for retrieving all selection/comment pairs from Table_1 and storing the results in arrays
4. Text_CommentCount – Populated by B_getComments with total number of comments found in Table_1. Acts as a notification for when the process is done as it can be computationally intensive with large tables
5. B_setComments – Button that inserts comments in script variables into Table_2
6. B_logValues – Shows an example of the stringified selection derived from Table_1. I had also wanted to show the effective filters on both tables but a limitation in the getDimensionFilters() API prevents me from retrieving the values from the object
7. TextAreaSelection – Populated by B_logValues with the dynamic selection of the first cell with a comment

Script Variables and Script Objects

The sample application uses the following script variables for storing comment and selection arrays:

The following script objects and functions are used to abstract the logic from the controls:

1. commentUtils – helper functions for converting result sets into an array of selections, using that array to get the comments, and filtering the selection array for just the selections mapped to a comment
   1. function convert(res: ResultSet[] : Selection[])
      input: array of result sets

      output: array of selections
      

   2. function getComments(sel: Selection[] : CommentInfo[])
      input: array of selections (see above)

      output: array of CommentInfo
      

   3. function getSelection(sel: Selection[] : Selection[])

2. stringConvert – helper function to convert an example dynamic selection into a stringified value for displaying on the UI
3. 1. function selString(sel: Selection) : string)

Component Layout

Table 1 Setup

Table 2 Setup

• Tip: set Date and Product dimensions to “Show only leaves in widget” for ease of display
• Tip: configure Table_1 and then duplicate it and change the Version filter to save some time

Code details and explanation

Script Objects

commentUtils.convert() //commented

//number of cells in the table
console.log("convert() ResultSet Length: " + ConvertUtils.numberToString(res.length));
//Generate an array with the cartesian product of the rows and columns
var all = Table_1.getDimensionsOnColumns().concat(Table_1.getDimensionsOnRows());
//console.log(all);
var ar = ArrayUtils.create(Type.Selection);
//Loop for each result set array
for(var i=0;i<res.length;i++) {
	//init selection JSON
	var obj = {};
	//dynamically set each dimension member
	for(var j=0;j<all.length;j++) {
		//each dimension
		var sel = all[j];
		//console.log(sel);
		//each dimension member
		obj[sel] = res[i][sel].id;
		//console.log(obj[sel]);
	}
	//each selection
	ar.push(obj);
	//console.log(obj);
}
return ar;

commentUtils.getComments() //commented

var ar = ArrayUtils.create(Type.CommentInfo);
//Loop for every cell in the table to check if there are comments > time consuming for large tables
for(var i=0;i<sel.length;i++) {
	//console.log(i);
	//Check if the cell has comments
	if(Table_1.getDataSource().getComments().getAllComments(sel[i])[0]){
	//console.log(Table_1.getDataSource().getComments().getAllComments(sel[i]).length);
	//for the ones with comments, get all comments
	for(var j=0;j<Table_1.getDataSource().getComments().getAllComments(sel[i]).length;j++) {
	//push the whole comment even though the addComment() only accepts text and not the CommentInfo object
	ar.push(Table_1.getDataSource().getComments().getAllComments(sel[i])[j]);
		}
	}
}
return ar;

commentUtils.getSelection() //commented

var ar = ArrayUtils.create(Type.Selection);
for(var i=0;i<sel.length;i++) {
	//console.log(i);
	//check if the table cell has comments
	if(Table_1.getDataSource().getComments().getAllComments(sel[i])[0]){
		//if it has comments, push the selection for each comment back into the array
		//this is a simple filter to reduce the array of selections to contain only selections with one or more comments
		//since we can't construct a nested array of selections and commentinfos we duplicate the selection for cells with comment threads where >1 comment are present
		for(var j=0;j<Table_1.getDataSource().getComments().getAllComments(sel[i]).length;j++) {
			ar.push(sel[i]);
		}
	}
}
return ar;

stringConvert.selString() //commented

//parser function to display a stringified selection
var string = "{";
var dims = Table_1.getDimensionsOnColumns().concat(Table_1.getDimensionsOnRows());
for(var i=0;i<dims.length;i++){
var dim = dims[i];
//construct a stringified JSON object out of a selection
string = string + "\"" + dim + "\"" + ":" + "\"" + sel[dim] + "\"";
	if(i<dims.length-1){
		//use escape character for new line
		string = string + ",\n";
	}
	//close the object on the last property
	else{string = string + "}";}
}

return string;

Button onClick events

B_getComments //commented

//console.log(Table_1.getDataSource().getResultSet());
// Call convert() function to change array of result sets to array of selections
selArray = commentUtils.convert(Table_1.getDataSource().getResultSet());
// Use the array of selections to get the comments for each cell
comArray = commentUtils.getComments(selArray);
// Filter the array of selections to contain only selections with comments
selArray = commentUtils.getSelection(selArray);
//console.log([selArray, comArray]);
//Log to the UI when complete – small notification for computationally intensive functions
Text_CommentCount.applyText("# Comments: " + ConvertUtils.numberToString(comArray.length));

B_setComments //commented

//Add the comment text to Table 2 for each selection/comment pair in the arrays 
for(var i=0;i<selArray.length;i++){
	Table_2.getDataSource().getComments().addComment(selArray[i], comArray[i].text);
}

B_logValues //commented

//Just pass the first selection with a comment into stringConvert.selString() to display in UI
// I also wanted to retrieve the effective dimension filters but FilterInfo object does not expose the values (only type)
TextAreaSelection.setValue("Dynamic selection:\n" + stringConvert.selString(selArray[0]));

I was excited to uncover a method for dynamically generating a selection for every visible cell in a table. The commentUtils.convert() function can probably be optimized, but the function itself could be used to build complex filter strings or apply fine-grained cell formats based on specific criteria. I would really like to hear from you on what other applications this could be used for.

I hope you enjoyed this post and happy building!