Professional InfoPath 2003 [Electronic resources] نسخه متنی

Payments Analysis

InfoPath provides a standard UI method for exporting forms to Excel. This will meet many basic requirements.

Excel’s default behavior is to load the XML file into a new workbook, in a single table with the top left at A1. This is definitely not what we want for the NewsLine data. We need a well-designed template, and we want to map our values rather than getting the denormalization Excel would otherwise impose.

Export Schema

For the Excel export schema, you’ll want to select a subset of the information contained in a resource listing. NewsLine managers are probably not going to be interested in the details of contributors, titles, and most of the other meta data. However, they might want to do some simple analysis of bureau performance against budget and be able to pin down the contribution that different story categories make to costs.

Our proposed solution allows the editor-in-chief at each bureau to export daily payments listings that can be cumulated by management to view against a weekly budget. This arrangement also has potential for interbureau comparisons at a company-wide level.

Here’s the schema. Figure 17-8 shows the content model.

<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
elementFormDefault="qualified" attributeFormDefault="unqualified">
   <xsd:element name="paymentsReport">
      <xsd:complexType>
         <xsd:sequence>
            <xsd:element ref="bureau"/>
            <xsd:element ref="period"/>
            <xsd:element ref="stories"/>
         </xsd:sequence>
      </xsd:complexType>
   </xsd:element>
   <xsd:element name="bureau" type="xsd:string"/>
   <xsd:element name="period" type="xsd:date"/>
   <xsd:element name="stories">
      <xsd:complexType>
         <xsd:sequence maxOccurs="unbounded">
            <xsd:element ref="story"/>
         </xsd:sequence>
      </xsd:complexType>
   </xsd:element>
   <xsd:element name="story">
      <xsd:complexType>
         <xsd:sequence>
            <xsd:element ref="identifier"/>
            <xsd:element ref="date"/>
            <xsd:element ref="payment"/>
            <xsd:element ref="status"/>
            <xsd:element ref="category"/>
         </xsd:sequence>
      </xsd:complexType>
   </xsd:element>
   <xsd:element name="identifier">
      <xsd:simpleType>
         <xsd:restriction base="xsd:string">
            <xsd:length value="6"/>
         </xsd:restriction>
      </xsd:simpleType>
   </xsd:element>
   <xsd:element name="date" type="xsd:date"/>
   <xsd:element name="payment">
      <xsd:simpleType>
         <xsd:restriction base="xsd:decimal">
            <xsd:fractionDigits value="2"/>
         </xsd:restriction>
      </xsd:simpleType>
   </xsd:element>
   <xsd:element name="status" type="xsd:string"/>
   <xsd:element name="category" type="xsd:string"/>
</xsd:schema>

Figure 17-8: The paymentsReport content model.

Export Transform

The payments report export transform loops through the daily resource list, selecting the data subset needed to populate new XML files. Here we truncate the values for identifier and publicationTime elements to clarify the output. The status element is there so that payments on spiked and embargoed stories can be identified, and category allows managers to analyze costs by story type.

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
   <xsl:output method="xml"/>
   <xsl:template match="resourceList">
      <paymentsReport>
         <bureau>
            <xsl:value-of select="formHeader/@deskName"/>
         </bureau>
         <period>
            <xsl:value-of select="formHeader/@period"/>
         </period>
         <stories>
         <xsl:for-each select="meta">
               <xsl:apply-templates select="."/>
               <xsl:sort select="publicationTime"/>
         </xsl:for-each>
         </stories>
      </paymentsReport>
   </xsl:template>
   <xsl:template match="meta">
      <xsl:variable name="date" select="substring(publicationTime,1,10)"/>
      <xsl:variable name="id" select="substring(identifier,10,6)"/>
      <story>
         <date>
            <xsl:value-of select="$date"/>
         </date>
         <identifier><xsl:value-of select="$id"/></identifier>
         <payment><xsl:value-of select="payment/@amount"/></payment>
         <status><xsl:value-of select="status"/></status>
         <category><xsl:value-of select="category"/></category>      
      </story>
   </xsl:template>
</xsl:stylesheet>

In this transform and in those for RSS and archive output, you’ll probably note that there’s something missing: a method of expressing the output result tree as a document URL. This is because there is no standard method of doing so in XSLT 1.0. Several XSLT processors have extension elements. For example, the Saxon processor uses the saxon:output element (or xsl:document if you specify XSLT1.1 in the namespace declaration).

XSLT 2.0 is now an advanced Working Draft, and we’ll soon have the xsl:result-document element to use consistently on all processors supporting the standard. In the meantime you’ll need to use the function provided by your favorite XSLT tool.

You saw an XML export instance earlier. Here’s a fragment again:

<paymentsReport>
<bureau>New York</bureau>
<period>2003-11-05</period>
<stories>
<story>
<identifier>ny1234</identifier>
<date>2003-10-10</date>
<payment>800.00</payment>
<status>Filed</status>
<category>Feature</category>
</story>
.
.
</stories>
</paymentsReport>

Importing the Data

Now it’s time to map the schema onto a cell in a spreadsheet and import the data. First, you’ll step through the process manually using the Excel interface. Then you’ll consider the options for using VBA and the Excel object model to automate the import actions.

Manual Import

You’ll need the following files from the folder for this chapter:

The first step in mapping is to import the schema.

Open the template ny_unmapped.xls.

Choose File→Save As ny_test.xls to preserve the template in its original state.

Choose Data→XML→XML Source to display the XML Source pane, and click the XML Maps button.

In the XML Maps dialog box, click Add and select paymentsReport.xsd. Excel opens the Multiple Roots dialog box. Select the paymentsReport element and click OK.

Back in the XML Maps dialog box, click Rename, and rename the map as paymentsReport_ny. Now close the dialog box. The schema is displayed in the Source pane, and the List and XML toolbar pops up.

Select the story element and drag it onto the cell marked Identifier. The nested elements are dragged with their parent and form a set of XML lists.

Now drag the bureau and period elements next to the labels at the top of the sheet. These are now mapped cells.

At the bottom of the XML Source pane, click Verify Map for Export. You should get an alert confirming that the map is exportable. Your sheet should look something like ny_mapped.xls.

Before you import any data, you’ll need to set up some map properties to allow your user to append data to the sheet (as distinct from replacing it):

Choose Data→XML→Map Properties to show the Properties dialog box.

In the options section at the bottom of the dialog box, choose Append new data to existing XML lists.

Check all the other options and click OK.

Now you can import the two data files:

Choose Data→XML→Import, and import day1.xml. This is an example of one day’s input for a bureau. The data appears in the XML lists and mapped cells.

Click anywhere in an XML list and click the Toggle Total Row button in the List toolbar. This adds a total row to the lists.

In the drop-down list in the totals cell at the bottom of the Payments column, choose Sum to show the total.

Now you can use any available Excel features to work with the figures. We’ve chosen to do a simple comparison between payments and budget, with a figure to show an average for any category.

To add power to the spreadsheet, you might choose to create additional maps for data from the other bureaus. Using similar layouts on separate tabs, you could map to additional XML lists and graph interbureau comparisons. As a final touch you might define a new schema to export the comparative information to the company intranet.

Using the Excel Object Model

The Excel OM provides three import methods from the Workbook class to get XML data into a spreadsheet:

OpenXML loads data from a file into a workbook and is equivalent to using File→Open to open an XML file, with its attendant disadvantages.

XMLImportXML accepts an XML data string value and imports it.

XMLImport is closest to the approach we took with the manual process, and we’ve chosen it for our example.

XMLImport accepts four parameters, detailed in the following table, and returns XlXmlImportResult.

To start the import process, you might place a button associated with a VBA macro on a sheet.

This example imports a data file into a sheet that already contains a map and appends the data. Excel places it in the mapped XML list. Because you specified the existing XML map when calling ImportXML, you do not need to worry about where the data is dropped. The value xmlData is an absolute path, so you will need to modify it to suit your file location. The sample file is ny_import_om.xls.

Private Sub importPayments_Click()
   Dim xmlData As String
   Dim paymentMap As XmlMap
   Set paymentMap = ThisWorkbook.XmlMaps("paymentsReport_ny")
   On Error Resume Next
   xmlData = "D:\infopath\project2\code\excel\day1.xml"
   ThisWorkbook.XmlImport xmlData, paymentMap, False
   If Err.Number = 0 Then
      MsgBox "Data from " & xmlData & " was successfully imported"
   Else
      MsgBox "There was an error importing" & xmlData
   End If     
End Sub

There is an interesting range of XML-related objects and methods in the OM, which we haven’t space to explore here. We encourage you to use them to improve on this simple example and to learn about the structure of the Excel mapping interface.