Using Excel Templates in SAP Sourcing Reports

[Former Member]

For many releases, SAP Sourcing has had the ability to export the report results to a pre-formatted Excel spreadsheet. The Excel spreadsheet can include formatting, formulas, charts, and even visual basic macros. For those of you that cannot take immediate advantage of SAP Sourcing Wave 7's fantastic reporting enhancements with improved charting and integration with SAP BusinessObjects Xcelsius, you may benefit from using Excel and its wide range of formatting and charting capabilities.

The Excel template functionality is quite useful for those reports that benefit from visually appealing formatting. Recently, for example, I worked on a report for a customer that had multiple queries in it to generate the result sets. We decided to use the Excel template functionality to take the results from the queries and format the data into a easily presentable single Excel worksheet. This was particularly useful because the report users were only casual users of SAP Sourcing and much preferred to review and analyze the data in Excel.

If you are new to this functionality, I suggest starting with something very basic: change the column header and row data formatting in the template. This is done by performing the following steps (assuming you have already developed the query):

1. Create the Excel template. The template can be either an XLT (true Excel template) or XLS (an Excel worksheet). The Excel template should have one worksheet and the name of that worksheet should be the name of the query (not the internal name, but the internationalized display name). In the single sheet, you can apply formatting for the column headings and rows (search in the online help for “workbook templates” to see some shortcuts you can use in the template for the formatting).
2. Create a SAP Sourcing File Attachment Container with the Excel template. In the create screen of the file attachment container, the purpose should be set to workbook template. The path name should be the name of the file and you should upload the Excel template file into the file attachment. File Attachment Containers are access from setup > system setup > user interface > file attachment containers.
3. Associate the report with the template. In the SAP Sourcing report, set the workbook template to the file attachment container created in the previous step.

These steps will always be followed when you want to use Excel templates for reports. The real work will always be in creating, adjusting, and testing the Excel template.

A more advanced type of Excel template is to manipulate and/or format the raw data from the SAP Sourcing report in a separate worksheet in the Excel template. This type of template is particularly useful where you would like to take data from multiple queries and format it onto a single worksheet.

To create the Excel template that formats raw data from multiple queries, you must do the following:

1. Create worksheets in the template, one for each of the queries in the report and name the sheets the same name as the queries. These should be in the same order as the queries in the report.
2. Create a new worksheet in the Excel template as the last sheet. You can use formulas in this sheet to “pull” data from any of the other sheets. There are different techniques you can use in Excel to accomplish this; I typically will type “=” in the cell where I want the value, then click to the source sheet and select the source cell. The resulting value of the cell will be something like =Sheet1!a1.

For the posting, I created a new report called SAP Sourcing Momentum Dashboard. The report contains three queries that pull some basic metrics for Projects, RFx, and Auctions and is intended to show utilization of SAP Sourcing over the previous four years.

The Projects query included the following data:

Year	Started	Completed
2007	130	318
2008	388	423
2009	465	432
2010	456	418

The RFx query included the following data:

Year	Completed	Value
2007	73	2,280,885
2008	78	2,393,828
2009	91	4,328,708
2010	123	7,832,032

The Auction query included the following data:

Year	Completed	Value
2007	31	321,383
2008	17	174,831
2009	21	202,483
2010	19	198,381

I then created an Excel template for the report to format the results into something more visually appelling and easy to print. First, I created the Excel template with four sheets:

The first three sheets are the names of the three queries I developed, and in the order in which the queries exist in the report. There is nothing in these sheets. The fourth sheet (Sourcing Momentum), which has the focus when I save the workbook, includes the desired formatting and “pulls” the data from the previous three sheets.

Notice that the formula for cell B6 has a formula in it to pull data from the first sheet in the Excel workbook. This technique allows me to take the rows of raw data from the generated sheet and format it as desired on the target Sourcing Momentum sheet.

I also decided to include a chart in my generated report so that the users could view the information graphically. This is simply a standard Excel chart that uses the data from the corresponding table.

You can see my final product below. Each of the three formatted tables "pulls" data from the indiviudal queries developed for the report and the charts use that data. Additionally, I adjusted the sizing and spacing so that the report would print nicely.

This example shows how you can use simple Excel formulas to create visually appealing Excel exports from SAP Sourcing report data.

Due to the fact that most capabilities of Excel are supported through the use of templates, complex formatting, logic, and adjustments can also be implemented using Excel Visual Basic macros. In the past, I have used such approach to dynamically create pivot tables in the Excel workbook using the raw data from the query as the source data for the pivot table. You can do some really clever things if you want to invest the effort in programming Excel. One thing to keep in mind, however, is that once macros are included in the Excel template, Excel’s security comes into play - in certain cases, the users may not have the ability to execute the macros. You should review this topic with your IT staff before pursuing a complex Excel template of this sort.