Distributing Excel PivotTables With ReportCaster

By Susan Trommer

In Release 5.3, ReportCaster has added Excel PivotTable (EXL2K PIVOT) and two new graph types, Scalable Vector Graphics (SVG) and Portable Network Graphics (PNG) to the format options available for distribution with WebFOCUS procedures. However, there have been so many requests to distribute an Excel PivotTable with ReportCaster 5.2 that I decided it would be worth investigating how to do it.

I'm happy to say with a bit of "out of the box" thinking and assistance from the Information Builders experts for Excel PivotTable, I have successfully distributed an Excel PivotTable with ReportCaster 5.2.5. I have confidence that the sample code I provide in this article will work in any 5.2 Release but I recommend Release 5.2.2 or later.

Before I get started with the specifics of how to accomplish distributing an Excel PivotTable with ReportCaster, it is important to understand that an Excel PivotTable is actually two files – the Excel report and an XML file containing the PivotTable.

The WebFOCUS Reporting Server creates the Excel report with a file extension of .xht. When the WebFOCUS request to create an Excel PivotTable runs interactively, the application association is handled by the WebFOCUS Client in a mime table associating the .xht extension with Microsoft Excel. So the user is unaware of the extension of the Excel file and the .xht extension has no impact on expected behavior.

However, the .xht extension on the Excel report will produce an undesirable prompt for what application if distributed with ReportCaster. This is because an end user's desktop does not have a default association of the .xht extension for Microsoft Excel. In the code sample I provide in this article, the Excel report is directed to a file with the .xls extension that is associated with Microsoft Excel on virtually every Windows desktop.

The XML file for the PivotTable is created with the obvious .xml extension and this is no issue for ReportCaster but a bit tricky to code in the procedure (FEX) to store the two files created by the WebFOCUS Reporting Server to a permanent location, not in edatemp directory where they are deleted when the request completes processing.

The HOLD statement needs to include the ‘AS' phrase to assign a specific name to the HOLD files. Two FILEDEF commands are also necessary – one for the .xls file and one for the .xml file. It is extremely important that the physical files you create on disk be the same as the as name you specified in the HOLD statement because the Excel report will internally utilize this name to access the PivotTable (XML file).

Now that you know two files are associated with an Excel PivotTable, it is time to cover what to do with ReportCaster. The sample code in Figure 1 includes comments to explain each step of creating, storing, scheduling, and using the files sent through mail. It is coded for WebFOCUS on the Windows OS and with both the WebFOCUS Server and Distribution Server on the same machine.

-* Date: April 2, 2004 -* Distribution of Excel PivotTable with ReportCaster. -* Basic Instructions to accomplish this are: -* Schedule this FEX as the first FEX as the first task. -* Then schedule 2nd task to distribute .xls file specified in the -* first FILEDEF below. In the task specify the 'Save Report As' value -* to be the same name as in the FILEDEF below (this is IMPORTANT!) -* Then schedule 3rd task to distribute the .xml file specified in the -* second FILEDEF below. In the task specify the 'Save Report As' value -* to be the same name as in the FILEDEF below (this is IMPORTANT!) -* Distribute via email -* When receive email save both attachments to your local machine and -* then open the .xls to view the Exel PivotTable report. -* -*****Start of FEX Code **** -*Step 1: -*Verify if pivot table files already exist and if they do delete them -*so prior report output is not distributed. This assumes that the -*pivot tables are accessible to both the WF Server and Distribution -*Server. If not, you will have to code an FTP routine to check or -*them and delete them. -* DOS STATE D:\ibi\apps\reportcaster\carpivot.xls -RUN -IF &RETCODE NE 0 GOTO CHECKXML; DOS ERASE D:\ibi\apps\reportcaster\carpivo$.xml -RUN -CHECKXML DOS STATE D:\ibi\apps\reportcaster\carpivo$.xm -RUN -IF &RETCODE NE 0 GOTO STEP2; DOS ERASE D:\ibi\apps\reportcaster\carpivo$.xml -RUN -*Step 2: -*Create Excel Pivot table and HOLD to a directory outside of WF Server -*temporary directory and accessible to ReportCaster Distribution Server. -*If on two different machines you will have to FTP them to a location -*accessible (read access) by the Distribution Server. -* -STEP2 FILEDEF CARPIVOT DISK D:\ibi\apps\reportcaster\carpivot.xls FILEDEF CARPIVO$ DISK D:\ibi\apps\reportcaster\carpivo$.xml TABLE FILE CAR HEADING "CAR File PivotTable" "Sum of Sales by Car Across Model" PRINT SALES BY CAR ACROSS MODEL ON TABLE COLUMN-TOTAL ON TABLE SET STYLE * TYPE=HEADING, LINE=1, FONT='ARIAL', COLOR=PURPLE, SIZE=16, STYLE=BOLD,$ TYPE=HEADING, LINE=2, FONT='ARIAL', COLOR=PURPLE, SIZE=16, STYLE=BOLD,$ TYPE=DATA, FONT='ARIAL', COLOR=PURPLE,$ TYPE=GRANDTOTAL, FONT='ARIAL', COLOR=PURPLE, SIZE=16, STYLE=BOLD,$ ENDSTYLE ON TABLE HOLD AS CARPIVOT FORMAT EXL2K PIVOT PAGEFIELDS COUNTRY CACHEFIELDS RETAIL_COST TYPE END

You will need to modify site-specific locations in the DOS and FILEDEF commands. I created an application named ‘reportcaster' under the WebFOCUS \app directory and added it to the APP PATH of the WebFOCUS Server installation in D:\ibi\srv52\wfs\etc\edasprof.prf APP PATH IBISAMP REPORTCASTER. The procedure (FEX) to create the Excel PivotTable must be accessible from the WebFOCUS Server path when ReportCaster executes the scheduled job.

Although it is not required, it is important to note that when using ReportCaster file distribution, the Distribution Server requires read access to the location and file(s) to successfully obtain and distribute them. If your installation has the WebFOCUS Server and Distribution Server on separate machines without network access to obtain the files or unlike operating systems (such as Windows and Unix), you will need to speak with your system administrator on how to make the two files accessible to the Distribution Server (FTP is a possibility) or wait until Release 5.3 when the WF Server will send back a single file for an Excel PivotTable to the Distribution Server.

Before you schedule your Excel PivotTable request with ReportCaster make sure it works running it interactively through the WebFOCUS Client. You can quickly test the execution by using the WebFOCUS Demo page with the URL to the WebFOCUS demo page http://machine:port/ibi_html and select Execute a Procedure, which will display a form in which you can specify the procedure name, parameter values, the server and the application. Note: The /ibi_html alias can be customized, so check with your WebFOCUS Administrator if you have problems accessing it.

Note that the sample code includes deleting the .xls and .xml files you created. The first time you run the code you will get a message that the .xls and .xml files do not exist. This is a message the DOS STATE command produces when the file does not exist. The message cannot be suppressed but this has no impact on ReportCaster processing. It is important that the .xls and .xml files are deleted before they are recreated so that if they are not created by a current schedule execution, the old version of the report is not distributed. Do not delete them in a post-processing procedure, since post-processing runs immediately after the last scheduled task and before the ReportCaster Distribution Server does the report distribution.

Once you have tested your procedure and verified it is successfully creating the .xls and .xml files in the specified location, you are ready to schedule it. You will need to use ReportCaster Administrator and the Developer Interface Scheduler, which have the capability to create schedules that have multiple tasks (objects scheduled) that can be distributed using mail or printer distribution. We will use e-mail distribution because an Excel PivotTable is not a report type that can be distributed directly to a printer.

The default URL to access the ReportCaster Administrator and Developer Interface is http://machine:port/rcaster/main/reportcaster.jsp. You will see a prompt asking you to log on to ReportCaster. See the ReportCaster Administrator's Guide for information on creating ReportCaster users.

The ReportCaster Developer Guide contains information on Schedule Management to create a new schedule and distribution options (list, file, dynamic list). It isn't feasible to give you instructions on every option in the Scheduler and creation of distribution lists so I recommend keeping the initial testing simple by using the Run Once interval and the single e-mail address distribution option.

In the schedule you will need to create three tasks. The first task is a WebFOCUS Procedure that creates the Excel PivotTable and writes it to the specified destination. The second task is file distribution for the .xls file. The third task is file distribution for the .xml file. For the file distribution give the Save Report As value the same filename and extension as the file you are telling the Distribution Server to distribute. It is very important not to alter the filename and extension values.

In the Distribution tab select e-mail as the distribution option. You will need to include some information in the body of the e-mail to inform the recipient to save the files to disk and then open the .xls file. For instance:

Excel PivotTable for Car Sales is 
attached. Save both files to disk on your 
machine and then select carpivot.xls to 
open the Excel PivotTable.

This is necessary because from within the e-mail client the .xls file cannot access the .xml file for the PivotTable information.

The last selection is the notification option. By default it is distributed when there is an error, so you will need to supply the required values or change the selection to not send the notification e-mail.

Save your schedule and check the log after it executes for any problems that may have occurred (Figure 2). We all make typos from time to time so don't panic if you don't get it right the first time. (I didn't.) Once you have a successful log report, check your e-mail and successful use of the Excel PivotTable after the files are saved to your desktop. Then you are ready to expand your distribution to others and impress them with the capabilities of WebFOCUS Excel PivotTable creation and ReportCaster distribution.

-* Date: April 2, 2004 -* Distribution of Excel PivotTable with ReportCaster 53. -* Basic Instructions to accomplish this are: -* Schedule this FEX specifying format EXL2K PIVOT. -* Distribute using Mail, Library, or FTP option. -* -*****Start of FEX Code **** -*Step 1: -* Issue the SET WEBARCHIVE=ON command to tell WebFOCUS to create the -* Excel PivotTable as a single file. SET WEBARCHIVE=ON -* -*STEP2 -*Request to create the Excel PivotTable TABLE FILE CAR HEADING "CAR File PivotTable" "Sum of Sales by Car Across Model" PRINT SALES BY CAR ACROSS MODEL ON TABLE COLUMN-TOTAL ON TABLE SET STYLE * TYPE=HEADING, LINE=1, FONT='ARIAL', COLOR=PURPLE, SIZE=16, STYLE=BOLD,$ TYPE=HEADING, LINE=2, FONT='ARIAL', COLOR=PURPLE, SIZE=16, STYLE=BOLD,$ TYPE=DATA, FONT='ARIAL', COLOR=PURPLE,$ TYPE=GRANDTOTAL, FONT='ARIAL', COLOR=PURPLE, SIZE=16, STYLE=BOLD,$ ENDSTYLE ON TABLE PCHOLD FORMAT EXL2K PIVOT PAGEFIELDS COUNTRY CACHEFIELDS RETAIL_COST TYPE END

Keep an eye out for Release 5.3 later this year. In this release, we are simplifying the creation of an Excel PivotTable so that you no longer need to deal with the two files (.xls and .xml) The Excel Report and PivotTable are created as a single file that is sent back to the ReportCaster Distribution Server for distribution.

Note: Figure 2 is a sample of code for Release 5.3 that can be scheduled with ReportCaster to distribute on Excel PivotTable as a single file.