How to leverage Excel formulas in Bots

[Jawanth]

The objective of this blog is to share some ideas about leveraging Excel’s computation power in SAP IRPA bots to automate the generation of Excel-based reports.

Background: A common business use case of robotics process automation is in generating a consolidated report, drawing upon data from several reports.

To illustrate a sample use case, let us take a company ABC that has several compliance training courses that employees must complete within a certain date. ABC would like to see a consolidated report, at a given time, showing which employees have been assigned specific courses, those who have completed those courses, and so on. The input reports here are individual course status reports. The consolidated report is a result of pulling together information, for each employee id, from these individual reports.

Another use case is a manufacturing company, which is trying to compare Master Data settings across several SAP Instances. In this case, it may pull several individual Master Data reports and have logic to compare across these instances in the consolidated report.

Yet another use case is in Planning & Logistics, to evaluate safety stock settings, by pulling together out of stock reports from various distribution centers.

SAP IRPA is an excellent tool to develop bots to provide an end to end, zero-touch solution in such cases. We will illustrate one method that can be employed to derive advantages from MS Excel’s capabilities.

The starting point is that the individual reports are already in CSV or .xlsx format. These reports could be the result of downloads from systems such as SAP ECC, SAP Success Factors, just as an example. This is typically the most common usage in businesses.

A couple of key points: First, an introduction to the fundamental nature of such a report. As those in databases or business intelligence area may be familiar with, this kind of consolidation represents a ‘star’ schema. The consolidated report forms the fact table, the center, or ‘star’ in star schema; the individual reports are the ‘dimension’ tables, which are the ‘points’ surrounding the star, as shown in the figure below.

Star Schema report. Courtesy: https://en.wikipedia.org/wiki/Star_schema

A second key point: a primary purpose of robotics process automation is to orchestrate the interaction between disparate applications and information sources, automating the transfer of information between them, based on some process flow and logic. The idea is to keep computation and coding to a minimum. Observe that the use cases above involve ‘joins’ and perhaps even some data filtering. Programming custom logic to do these actions inside the bot can be very time consuming, despite SAP Intelligent RPA being arguably the best development environment among RPA tools. It is because a developer must invest a lot of time in designing, debugging and error checking custom joins and data filters. In order to overcome this, we can leverage the Excel computation engine instead, with judicious use of “VLOOKUP’s” and other formulae that Excel has to offer. It will save substantial time in the development of such lookups inside the bot. Furthermore, it is quite likely that the ‘as-is’ process, has already created such formulae in Excel for existing reports.

I will illustrate using a sample use case: It involves two reports (dimension tables): ‘Ethics_course_report’ and ‘Secuirty_course_report’. Imagine these are some courses that Human Resources want employees to complete. The fact table report, EXEC_SUMMARY_REPORT, will ‘join’ these two, based on the ‘EMPLOYEE_ID’, along with some more columns, as required, optionally, containing employee info. The objective is to add to this table, columns drawn from each of the course reports. It is all shown in the following figure.

Schematic view of the reports

I will now proceed to show steps to build this bot. The key step is the use of Excel formulae to do the heavy lifting. As a starting point, we will assume ETHICS_course_Report, SECURITY_course_Report and EXEC_SUMMARY_REPORT (that has a tab “All_Employee_Report” tab with EMPLOYEE_ID information plus any other optional fields) have been downloaded from a system of record (e.g. SAP SuccessFactors), perhaps using another IRPA bot, as part if an end to end solution.

First Step:  bring in the ‘Ethics_Course_Report’ and ‘Security_Course_Report’ files into respective Excel sheets (i.e. tabs) in the EXEC_SUMMARY_REPORT. Note that optionally this step can be modified to bring in only the columns of interest if desired. Note that at a minimum one needs to bring in the key field column and of course one or more data fields.

In our example, EMPLOYEE_ID is the key field, and completion status is the data field.   We will bring in the completion status into the last two columns of the EXEC_SUMMARY_REPORT.  For this, we will write headers on those two columns and then populate the columns with VLOOKUP formulae. We are just recreating how someone using Excel to build this report would have built the formula. The code is as follows with the comments.

GLOBAL.step({ st_get_dimension_tabl: function(ev, sc, st) {

	var rootData = sc.data;

	ctx.workflow('Create_Report', '01df4ebd-93af-4dfb-aa10-44eb0b7f8ae2') ;

	//<<<<<<<<<STEP 1>>>>>>>>>

	// st_get_dimension_tables

	//Step 1. Copy the dimension tables into the consolidated summary result report

		//COPY Ethics_Course_Report

	ctx.excel.file.open("C:\\JV\\irpa\\blog\\Ethics_Course_Report.xlsx");

	ctx.excel.sheet.copySheet("C:\\JV\\irpa\\blog\\Ethics_Course_Report.xlsx",

		"C:\\JV\\irpa\\blog\\Exec_Summary_Report.xlsx",

		"Ethics_Course_Report","Ethics_Course_Report"); //Saving it with same name

	ctx.excel.file.save();

	//ctx.excel.file.close("C:\\JV\\irpa\\blog\\Ethics_Course_Report.xlsx");

	ctx.excel.file.closeAll(true);

	 	//COPY Security_Course_Report

	ctx.excel.file.open("C:\\JV\\irpa\\blog\\Security_Course_Report.xlsx");

		ctx.excel.sheet.copySheet("C:\\JV\\irpa\\blog\\Security_Course_Report.xlsx",

		"C:\\JV\\irpa\\blog\\Exec_Summary_Report.xlsx",

		"Security_Course_Report","Security_Course_Report");//Saving it with same name

	ctx.excel.file.save();



	//ctx.excel.file.close("C:\\JV\\irpa\\blog\\Security_Course_Report.xlsx");

	ctx.excel.file.closeAll(true);

	ctx.log("Done copying as");

Second Step: Next, we get the number of rows of EMPLOYEE_ID there are in EXCEC_SUMMARY_REPORT.

	//Step 2. Find the number of rows in Exec_Summary_Report in the "All_Employee_Report" tab

	// This assumes the very first Column (A) is the column of all unique employees_id in the company

	ctx.excel.file.open("C:\\JV\\irpa\\blog\\Exec_Summary_Report.xlsx");

	ctx.excel.sheet.activate("All_Employee_Report"); //contains the Empployee-ID in column A

	

	var lastrow=ctx.excel.sheet.getLastRow2("A1"); //Gets the last non-blank row/(i.e. populated row in col A)

	ctx.log("last blank row is:"+lastrow);

	var lastcol=ctx.excel.sheet.getLastColumn("A1");//Gets the last non-blank row/(i.e. populated col in row 1)

	ctx.log("last blank col is:"+lastcol);//

Third Step: Formula construction: We create the formula which will be put into the cells(s), understanding that as we populate the column down, the cells must reference correctly the EMPLOYEE_ID cell of that row (just as Excel increments cell references inside the formula when one copies and extends cells). Once can also enhance this formula in various ways: for example, to replace “#NA” (which will occur in Vlookup if an employee_id does not exist in the source reports), one can use a formula such as: IFNA(VLOOKUP(A11,A1:D8,2,FALSE),"EmployeeNonExistent")

	//Step 3: Create the formulas to insert

	// And insert the formula into cells in col: lastcol+1, rows 2 to numrows

	var vlookup_security,vlookup_ethics;

	//Here A1 will naturally be a header. So we write headers for the columns we are populating as well.

	ctx.excel.sheet.setCell(1,lastcol+1,"Ethics_Course_Status");//Ethics report

	ctx.excel.sheet.setCell(1,lastcol+2,"Security_Course_Status");//Ethics report

	for(var idx=2; idx<= lastrow;idx++){

		vlookup_security= "=VLOOKUP(A"+idx+",Security_Course_Report!A:B,2,FALSE) ";

		vlookup_ethics= "=VLOOKUP(A"+idx+",Ethics_Course_Report!A:B,2,FALSE) ";

		ctx.excel.sheet.setCell(idx,lastcol+1,vlookup_ethics);//Ethics report

		ctx.excel.sheet.setCell(idx,lastcol+2,vlookup_security);//Security report

	}

	ctx.log("finished creating report");

	ctx.excel.file.save();

	ctx.excel.file.closeAll(true);//Save and close

	ctx.excel.end();

Fourth Step: We then enclose in a loop going down the rows, populating the cells in the two columns in EXEC_SUMMARY_REPORT (please see code above). Note how the loop index is referenced inside the Vlookup formula.

Following this we save and close the files. The EXECC_SUMMARY_REPORT is ready for use (can be distributed via email if that is desired using Microsoft Outlook steps ref. links).

To summarize, we have presented a way to leverage the Excel engine to do computations for a common class of reports used in businesses. As you can see, the steps are extensible and the only development time required is in constructing these steps and then using Excel to do a lot of the calculations. We have presented code using a simple use case to illustrate this. One can explore injecting various formulae in Excel to enrich functionality as required. Usually, existing Excel reports in the ‘as-is’ case may already have such formulae. Or one can construct formula in Excel, try out, and then create the formula string  in the bot code.