Show All

Hide All

By Mark Gillis

Two words that may make you run for cover: expense reports. You dread them, you defer them, and you despise them, especially when "petty cash" returns them for minute corrections in big red ink.

Put those fears aside and learn to positively love expense reports. Impossible, you say? Not if you team up with Excel 2003 and Extensible Markup Language (XML) to turn those expense reports around in no time flat. This article explains how you can automate expense reporting by using XML data in an Excel form. We even provide a sample expense report and XML schema file to get you started.

Download the Expense Report and XML Schema sample files.

Expense reports: the old-fashioned and new-fashioned way

Are you printing out an Excel template, writing in your expenses by hand, and then turning it in with crumpled receipts and crossed fingers? I sure hope not. But even if your Information Technology (IT) department has expense report automation at the bottom of its backlog, don't despair. You can quickly push expense report automation up that stack of requested applications.

How, you ask? Two words that may make you leap for joy: smart client. In a traditional client/server application, data is stored on a network server database (the back end), and forms are displayed on a client computer (the front end). A smart client adds built-in support for XML, leverages the full power of a personal computer, offers a familiar user interface, and has a rich set of features to analyze, publish, report, and share data.

Sounds like we just defined Excel, not to mention the Microsoft Office System.

Most users are already familiar with entering data into Excel. Besides, an Excel-based form is a natural for expense reports. You can take advantage of formulas (formula: A sequence of values, cell references, names, functions, or operators in a cell that together produce a new value. A formula always begins with an equal sign (=).), lists (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.), comments, data validation (data validation: An Excel feature that you can use to define restrictions on what data can or should be entered in a cell, and to display messages that prompt users for correct entries and notify users about incorrect entries.), cell and worksheet protection (protect: To make settings for a worksheet or workbook that prevent users from viewing or gaining access to the specified worksheet or workbook elements.), and even smart tags (smart tags: Data recognized and labeled as a particular type. For example, a person's name or the name of a recent Microsoft Outlook e-mail message recipient is a type of data that can be recognized and labeled with a smart tag.), to name a few features. And then, of course, there's XML: the open, industry standard that makes it easy to define, transmit, validate, and interpret structured data between applications and organizations.

Now toss the new custom XML Schema (XML Schema: A formal specification, written in XML, that defines the structure of an XML document, including element names and rich data types, which elements can appear in combination, and which attributes are available for each element.) feature into the mix. This makes it possible to map an XML schema file (.xsd) to specific cells in a worksheet, save mapped cell data from your worksheet into an XML data file, or import an XML data file and bind the data to these mapped cells. You can think of Excel 2003 as an XML data file generator, a powerful front end to new or even earlier applications. In many cases, you won't even need to write code, either Microsoft Visual Basic® for Applications (VBA) or Extensible Stylesheet Language Transformation (XSLT).

By combining the time-tested features of Excel with the new XML schema-mapping features in Excel 2003, you can fill out that expense report and submit it faster than you can say "Show me the money!" IT folks take notice: Custom XML schemas and XML data make it simple to retrofit an existing front end and connect with back-end databases. Your custom applications don't have to be continually redesigned from the ground up.

How expense reports get done from the top down

The following diagram illustrates a typical workflow for expense report processing. This workflow uses Excel as a smart client on the front end and XML to communicate with a back-end database on a server.

 You fill out the expense report by using an Excel worksheet that has been mapped to a custom XML schema and save the data as an XML data file.

 You submit the expense report data in the XML data file to the back-end database.

  Accounting scrupulously checks the expense report for accuracy and adherence to internal business rules and spending limits.

  If accounting approves, you get a gold star (and a reimbursement check).

  If accounting does not approve (that side-trip to Tijuana didn't pass muster), they send the XML data file back for you to fix and resubmit.

Let's look at the "Fill out expense report" step more closely.

 First, an IT developer creates a custom XML schema, defined in an XML schema file, and maps the appropriate cells in the Excel worksheet. Note that this is done only once. In practice, users obtain the expense report with the schema already mapped, often by downloading it from an internal Web site.

 Then, users enter identification information and line-by-line details into an Excel worksheet that looks just like an expense report.

  Finally, after completing the expense report, users save the data to an XML file.

Now, let's look at a real Excel workbook (.xls) and XML schema file (.xsd), just to show we're not pulling your leg. You can download these sample files from Downloads on Microsoft Office Online, and then use them to step through the instructions below. You can also use them as models for designing your own expense report system.

ExpenseReport.xls

The expense report is an Excel worksheet that takes advantage of built-in features of Excel to streamline filling out an expense report.

  Use comments to provide context-sensitive assistance for each cell.

  Link users to an internal Web site for detailed information about how to use the expense report.

  Visually highlight cells that contain formulas.

  Lock cells that contain formulas, to protect them from users who are accident-prone.

  Locally validate data, such as special codes.

In your application, make sure that IT developers protect the entire workbook and create a password before users obtain the workbook, so no one jimmies those locked cells.

Savvy IT developers can even add smart tags to the Excel template and turn a smart client into a bona-fide genius. For example, you can use a smart tag to pre-load the expense report with user information, validate cost center codes from an external database, or link to Web pages for detailed information about a cell.

ExpenseReportSchema.xsd

The following XML schema file defines the data that you want to capture in the expense report and send to accounting (the folks with the white shirts and green eyeshades). Usually, IT developers create this schema because they understand best what data needs to be defined and captured.

For detailed information about how to create an XML schema, see XML Schema Part 0: Primer, published by the World Wide Web Consortium (W3C).

ExpenseReportSchema.xsd

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
   <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:expenseReport"
	targetNamespace="urn:expenseReport" elementFormDefault="qualified">
      <xsd:element name="Root">
         <xsd:complexType mixed="false">
            <xsd:sequence minOccurs="0" maxOccurs="1">
               <xsd:element minOccurs="1" maxOccurs="1" name="Meta">
                  <xsd:complexType mixed="false">
                     <xsd:all>
                        <xsd:element minOccurs="1" maxOccurs="1" type="xsd:string" name="Name"/>
                        <xsd:element minOccurs="1" maxOccurs="1" type="xsd:string" name="Email"/>
                        <xsd:element minOccurs="1" maxOccurs="1" type="xsd:string" name="EmployeeNumber"/>
                        <xsd:element minOccurs="1" maxOccurs="1" type="xsd:string" name="CompanyCode"/>
                        <xsd:element minOccurs="1" maxOccurs="1" type="xsd:string" name="CostCenter"/>
                        <xsd:element minOccurs="1" maxOccurs="1" type="xsd:date" name="StartDate"/>
                        <xsd:element minOccurs="1" maxOccurs="1" type="xsd:date" name="EndDate"/>
                        <xsd:element minOccurs="1" maxOccurs="1" type="xsd:string" name="Purpose"/>
                     </xsd:all>
                  </xsd:complexType>
               </xsd:element>
               <xsd:element minOccurs="1" maxOccurs="1" name="Summary">
                  <xsd:complexType mixed="false">
                     <xsd:all>
		        <xsd:element type="xsd:decimal" name="TravelTotal"/>
		        <xsd:element type="xsd:decimal" name="MealsTotal"/>
		        <xsd:element type="xsd:decimal" name="ConferenceTotal"/>
                     </xsd:all>
                  </xsd:complexType>
               </xsd:element>
               <xsd:element minOccurs="0" maxOccurs="unbounded" name="ExpenseItem">
                  <xsd:complexType mixed="false">
 		     <xsd:sequence minOccurs="0" maxOccurs="1">
			<xsd:element type="xsd:date" name="Date"/>
			<xsd:element type="xsd:string" name="Description"/>
		        <xsd:element type="xsd:integer" name="Miles"/>
		        <xsd:element type="xsd:decimal" name="Mileage"/>
		        <xsd:element type="xsd:decimal" name="AirFare"/>
		        <xsd:element type="xsd:decimal" name="Other"/>
		        <xsd:element type="xsd:decimal" name="Meals"/>
		        <xsd:element type="xsd:decimal" name="Conference"/>
		        <xsd:element type="xsd:decimal" name="Misc"/>
		        <xsd:element type="xsd:string" name="MiscCode"/>
		        <xsd:element type="xsd:decimal" name="Amount"/>
		     </xsd:sequence>
                  </xsd:complexType>
               </xsd:element>
            </xsd:sequence>
         </xsd:complexType>
      </xsd:element>
   </xsd:schema>

Take a stroll through the process

Got motivation? Then put your walking shoes on and learn how to use the sample Excel workbook and XML schema file, step by step.

First, you'll add the schema to the Excel workbook and map specific data elements to corresponding cells in the expense report worksheet. Then, you can practice typing data into the expense report, and save the data to an XML file for further processing. Finally, you'll learn how you can submit an expense report, and how to revise it, if necessary.

Add the schema to the worksheet

1. Open ExpenseReport.xls.
2. On the Data menu, point to XML, and then click XML Source.

   The XML Source task pane opens.

3. Click XML Maps.
4. Click Add.
5. In the Look in list, click the drive, folder, or Internet location that contains ExpenseReport.xsd.
6. Click ExpenseReport.xsd, and then click Open.
7. Click OK to close the XML Maps dialog box.

The XML map is displayed in the XML Source task pane. The task pane displays a simple hierarchical view of the XML schema that makes it easy to view and map elements to cells.

Map the nonrepeating data to single-mapped cells

Nonrepeating data, such as information about the user or the purpose of the trip, is mapped to single cells. In an Excel worksheet, cells that contain nonrepeating data are called single-mapped cells.

1. On the Data menu, point to XML, and then click XML Source to open the XML Source task pane.
2. For each of the elements — user information (under ns1:Meta) and summary data (under ns1:Summary) — select the elements that you want in the XML Source task pane, and drag them to their corresponding cells.

   By default, a single-mapped cell is visually identified with a blue border.

   In general, when you drag a nonrepeating XML element onto the worksheet, you can choose to include the element name as a heading above or just to the left of the single-mapped cell. In the expense report, the existing cell value already has a heading or label.

Map the repeating data to an XML list

Repeating data elements, such as the line item information, are mapped to rows of data. In an Excel worksheet, rows that contain repeating data are XML lists.

• Select the ns1:ExpenseItem element in the XML Source task pane, and drag it to the B10 cell on the worksheet.

Congratulations! You just mapped all the line item columns in a single step, and you also created an XML list. An XML list has the added advantage of automatically expanding when users add new rows to the list in the worksheet.

 Note   Repeating data elements are mapped together into an XML list when the XML Map property Automatically Merge Elements When Mapping is selected (this is the default value). To modify this and other XML map properties, right-click on the XML list, click XML, and then click XML Map Properties.

Type data into the Excel expense report

Users of the expense report can now organize their crumpled receipts and type the data into the spreadsheet. Still got motivation? Type the following sample data into the expense report and feel the love.

Save expense report data as an XML data file

When you manually type (or import) data into a mapped workbook, that data becomes part of the workbook when it is saved. But it is not automatically saved as an XML data file. To create an XML data file, do the following:

1. On the Data menu, click XML, and then click Export .
2. If the Export XML dialog box appears, click the XML map that you want to use and then click OK.

   The Export XML dialog box appears only if an XML list is not selected and the workbook contains more than one XML map.

3. In the File name box, type ExpenseReportData.xml for the XML data file.
4. Click Export.

Here's what the XML data file looks like, just to show we're not pulling your other leg.

Submit expense report data

A simple approach to submitting your expense report is to send the XML data file to the accounting department, perhaps to a dedicated e-mail account that retrieves the XML data and stores it in an equally dedicated folder.

However, with just a few lines of VBA code, your IT developer can add a Submit button that would export the data for you and send it to a similarly dedicated database or even to a Web service.

For more information about automating XML, see XML and Microsoft Office Excel 2003: Creating an Expense Report Template.

Revise expense report data

If you need to make changes to your expense report later, you can revise it by doing the following:

1. On the Data menu, point to XML, and then click XML Source.

   The XML Source task pane opens.

2. For ns1:Summary, ns1:Mileage, and ns1:Amount, right-click each element, and then click Remove element.

    Note    The cells that these elements are mapped to contain formulas, so you don't want to overwrite the formulas when the XML data is imported.

3. On the Data menu, click XML, and then click Import .
4. Make your corrections. (For example, delete the value in cell D16.)
5. For ns1:TravelTotal (cell L20), ns1:MealsTotal (cell H20), ns1:ConferenceTotal (cell I20), ns1:Mileage (cell E10), and ns1:Amount (cell L10), remap the elements by dragging them back to their corresponding cells.
6. Export (save as XML) and resubmit the expense report.

Of course, with just a few more lines of VBA code, your IT developer could also add a Revise button.

The bottom line

Expense reports in your company are now a breeze. At that recent business conference in Chicago, you were a hero with your fellow road-warriors. Why, just last week, the entire IT department delivered flowers to your office and personally thanked you for lightening their load. The other day, you courted an important customer with an expensive dinner and finally closed that big sale. This morning, even your boss patted you on the back, complimented you on that paisley tie, and said…

"Don't forget to fill out those expense reports."