From my earlier posts, you know that I really like using the E-Sourcing / CLM reporting tool. It was actually the very first thing that I did with E-Sourcing when I started working with the application over 5 years ago.

A couple years ago, SAP added the ability to export the report results to a pre-formatted Excel spreadsheet. The Excel spreadsheet can include formatting, formulas, and even visual basic macros.

Due to the fact that the E-Sourcing / CLM reporting tool provides only very basic formatting, the Excel template functionality is quite useful for those reports that really benefit from 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 E-Sourcing / CLM 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 an E-Sourcing / CLM 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.
3. In the E-Sourcing / CLM 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 E-Sourcing / CLM 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; 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 simple query called Project Portfolio. The query pulls data from the E-Sourcing / CLM projects. I 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 2 sheets:

The first sheet (Project Portfolio) is the name of the query that I created. There is nothing special in this sheet. The second sheet (Project Portfolio Report), which has the focus when I save the workbook, includes the desired formatting and “pulls” the data from the Project Portfolio sheet. The first image shows the formatting I wanted:

For each project that is part of my results set, I wanted three rows of display data. Each gray bar in the Excel template will be used to show a different project. The way this is done is by pulling the data from the source Project Portfolio worksheet. For example, the formula used in the first gray bar, first column is as follows:

Notice that the formula for cell A3 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 reformat them into the block style on my template sheet. The next two images show the raw data and the formatted sheet.

This example shows how you can use simple Excel formulas to create visually appealing Excel exports for E-Sourcing / CLM 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 exciting 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.

Please let me know what you think about this posting and whether you would like more details about the use of Excel templates with E-Sourcing / CLM reports. I hope it helped you understand the capabilities and perhaps inspired you to try them out. Good luck and Happy New Year.

This entry was posted on Friday, January 2nd, 2009 at 2:54 pm and is filed under Uncategorized. You can follow any responses to this entry through the RSS 2.0 feed. Responses are currently closed, but you can trackback from your own site.