Technical Case Study

Published: February 1, 2006 | Updated: February 7, 2006

The corporate accounting organization at Microsoft needed a new tool to enable financial analysts and accountants to create and submit journal entries. Microsoft IT created a solution that may help other enterprise organizations to develop business solutions by using the new version of Visual Studio 2005 Tools for the Microsoft Office System. This case study is intended for chief information officers, technical decision makers, and business decision makers who need to build Office-based internal tools or line-of-business applications.

Situation

Financial analysts and accountants at Microsoft submit about 144,000 journal entries every year, and the number is increasing by about 20 percent per year. In the past, they used a Web-based application called WebJE to compose journal entries and submit the entries to SAP. Some journal entries have an auditing requirement for additional documentation; users accessed another Web-based application to submit this documentation when a journal entry required it.

The existing solution had a variety of problems, such as:

• Limited Excel functionality
• Separate SAP sign-in
• Inability of users to work offline
• Slow performance from worldwide locations
• Lack of enforcement of auditing requirement for justification documentation

WebJE was first deployed in 1998. It provided a Web-based user interface for creating journal entries and submitting them to SAP. The user interface included an Office Web Component embedded on the Web page that gave users access to a limited subset of Excel functionality. For example, users could compose journal entries in an Excel spreadsheet but then had to copy and paste them into the WebJE user interface; they could not link their local spreadsheet to the Web-based grid.

WebJE checked the user's network account and password to allow access to the application, but it did not transmit those network credentials to SAP. Therefore, a user had to sign in to SAP by using different credentials. A user sometimes forgot the SAP password and had to create a new password before submitting the journal entry. Some users solved this problem by writing down their passwords, which raised obvious security concerns. Users expressed a strong desire to eliminate this separate sign-in procedure.

Because WebJE was a Web-based application, a user could access it only when connected to the network; the user could not work offline.

As a Web-based application, WebJE required a network round-trip to and from the Web server when the user entered or edited data. The round-trip had to include the HTML required to render the page. WebJE resided on one server at Microsoft corporate headquarters. Analysts worldwide, located away from corporate headquarters, submit approximately 80 percent of the journal entries. These analysts experienced significant delays when opening, rendering, and refreshing the Web page.

The corporate accounting organization has an auditing requirement for justification documentation for journal entries that have expenditures above a threshold dollar amount; about 25 percent of journal entries require this additional documentation. WebJE did not include a feature that enforced the requirement. Users accessed a separate tool to assign justification documentation to a journal entry. Because WebJE could not enforce the requirement, users sometimes failed to submit the documentation.

In general, the WebJE solution had a very manual, poorly integrated workflow. Users had to sign in to SAP with separate credentials and had to remember data from one tool to another. One effect of the lack of software integration was an increased workload for users, especially at times such as the last few days of the month when many journal entries needed to be submitted.

Solution

To reduce development time, improve the user interface, improve the sign-in process, and improve compliance with auditing requirements, Microsoft IT decided to replace WebJE with a new system for journal entries. The group chose to build the new application by using VSTO to take advantage of the additional functionality and shorter development time that Visual Studio 2005 offers for building Office-based solutions. The new application, called Journal Entry in Excel (JEXL), consists of an Excel template with a robust set of data-entry and validation tools.

In the new solution, users can access all Excel functionality when preparing journal entries. Figure 1 shows a spreadsheet created from the JEXL template.

Figure 1. A JEXL spreadsheet

The decision to switch from a Web-based application to an Office-based application was largely driven by the features that needed to be improved or added to the journal-entry process. The Office-based application can use the components of the Microsoft Windows® operating system architecture and user interface, whereas the Web-based application was restricted to the capabilities and limitations of a browser.

The JEXL solution relies on Visual Studio 2005 Tools for the Microsoft Office System. VSTO enables the developer to write managed code in an Office document, taking advantage of the security and ease-of-development that Visual Studio provides. When a developer launches VSTO, the Visual Studio New Project dialog box displays a new category of projects called Office. In the Office category, the developer can choose from templates for project types called Excel Workbook, Word Document, Excel Template, Word Template, and Outlook Add-in.

Each Office-based project includes a visual designer that supports a set of managed-code controls called Windows Forms. A developer can use any Windows Forms control on the surface of the document that the developer is creating. VSTO includes an Actions Pane Control for programming the actions pane of an Excel or Microsoft Office Word 2003 window. (The actions pane is the region on the right side of the Excel or Word window that is separate from the body of the spreadsheet or document). The Actions Pane Control also has a visual designer and supports Windows Forms.

VSTO gives the developer a great deal of flexibility when building a solution in an Office document. In JEXL, functionality that depends on numbers and calculations is programmed into the spreadsheet part of the template. The developer can assign certain required fields and calculations to particular cells, for example. Within the code file for the actions pane, the developer can then write code that accesses the data that the user puts into the spreadsheet. The JEXL actions pane contains familiar text boxes, check boxes, and other controls where the user can enter data that does not require spreadsheet functionality (such as the currency of the journal entry) and take actions (such as validating the data in the journal entry and submitting the journal entry to SAP).

Much of the functionality of JEXL could have been developed as an Office add-in. However, such an add-in would have required more time to develop and would have required more resources to maintain and enhance. In addition, the add-in would have been written in unmanaged code.

Tabs in the Actions Pane

As shown earlier in Figure 1, the Excel window for JEXL is divided into a spreadsheet region and the actions pane. The actions pane contains three tabs. The most frequently used tab is the JE tab. The JE tab contains options that enable the user to interactively specify header and support data for a journal entry and to submit the entry. The Submit button is disabled until the user enters all required data and attaches justification documentation, if the journal entry requires it. Figure 2 shows the JE tab.

Figure 2. The JE tab in the JEXL actions pane

A user can check the journal entry for errors before submitting it. When the user selects the Validate Only check box on the JE tab and then clicks Submit, JEXL processes the data as if it were being submitted to SAP, but JEXL does not actually submit the data. This process includes data validation, and it includes checking business rules. For example, JEXL will reject a journal entry that attempts to post marketing expenses to a development account. JEXL lists any errors on the Errors tab. By fixing errors before submitting a journal entry, a user can avoid having SAP reject the journal entry. Prevalidation streamlines the overall workflow of submitting journal entries.

If the user does not prevalidate the journal entry by selecting the Validate Only check box and the entry contains errors, SAP rejects the transaction. In that case, JEXL sends an e‑mail message to the user that indicates that the submission to SAP was not successful. The user can then click the Errors tab of the actions pane and click Load Error File to display the errors. Figure 3 shows the Errors tab.

Figure 3. The Errors tab in the JEXL actions pane

The errors list is interactive; when the user selects an error in the list, the focus of the application moves to the spreadsheet row or actions pane field that caused the error. The user does not have to scroll through hundreds or even thousands of rows to locate the problem.

The BatchJE tab enables the user to specify a file that contains multiple journal entries. This feature supports users who copy multiple journal entries from their Microsoft SQL Server™ tables to a tab-delimited file. These users do not require the composition and editing features of JEXL; they only need to submit the journal entries to SAP. This tab does not include interactive controls; the user can only browse for the file. This tab does not use the spreadsheet portion of the JEXL user interface.

Architecture

In addition to the JEXL user interface, the other major component of the new solution is Microsoft BizTalk® Server 2004. BizTalk Server manages follow-up communications and any required justification documentation. When the user clicks Submit on the JE tab, JEXL sends the transaction to both BizTalk Server and SAP. After SAP processes the transaction, it sends a message to BizTalk Server indicating whether the transaction was successful. If BizTalk Server receives a success response message, it copies any justification documentation to a file server and associates the document with the transaction in a separate database. If BizTalk Server receives a failure response message, it sends an e-mail notification to the user.

Figure 4 shows the architecture of the new solution.

Figure 4. The architecture of the new solution

A major new feature in JEXL is a single sign-in. SAP can authenticate a user by using the user's network credentials. By using the Microsoft .NET Framework version 2.0, JEXL can query for the current user's network credentials and pass them to SAP. This functionality eliminates the separate sign-in to SAP that WebJE required. JEXL validates the user's credentials through a SAP Web service when the user opens a JEXL-based document. The credentials are passed from JEXL on the user's computer directly to SAP.

JEXL enforces the requirement for documentation to justify certain journal entries. When justification documentation is required, the user can attach a document to a journal entry through the JEXL user interface. As mentioned earlier in the "Tabs in the Actions Pane" section, the Submit button is disabled until the user provides all required data, including justification documentation if necessary.

Deployment

Benefits

The latest version of VSTO provides many benefits to the developer, such as the following:

• Uses the features of Visual Studio 2005 to improve the development experience and to reduce development time. For example, by using Visual Studio and the .NET Framework 2.0, writing code for Web services is much easier than writing unmanaged code to make network calls.
• Enables the developer to use Windows Forms controls, both in the main document and in the actions pane, which enables the developer to provide a well-known Windows-based user interface. In particular, VSTO now provides visual designers for documents and actions panes.
• Exposes properties, methods, and events from Excel that provide rich customization opportunities to the managed-code developer.
• Supports interaction between the main document and the actions pane, through new events that the document raises. For example, in an Excel-based application, the document causes an event when the user moves focus from one cell to another. In the Actions Pane Control, the developer can write code that responds to this event.
• Uses managed code for its functionality; developers can use the Microsoft .NET language of their choice.

Future releases of JEXL will be easier and faster to develop so that an IT team can deliver more requested features in a shorter amount of time. Also, the decision to completely rebuild the application led to the inclusion of various small features that would have cost too much in time and resources to implement in WebJE.

The architecture of the JEXL solution reduces network bandwidth by about 60 percent. There are fewer network calls because the streamlined rich client is completely local. The application accesses the network only to validate the user and to submit the transaction, whereas the Web-based architecture required a network round-trip for every change in the user interface.

The JEXL solution also has benefits for the end user:

• The single sign-in feature. This feature eliminates the time users formerly spent keeping track of and using separate sign-in credentials for SAP. Users report that this feature is very important to them. Not requiring a separate SAP password also eliminates the security risk of users writing down their passwords.
• The ability to work offline. For example, traveling users can accumulate journal entries on a business trip and then submit the entries when they are connected to the network.
• The ability to use all of the features of Excel. JEXL users are typically familiar with Excel, and they can capitalize on that familiarity when using the application. For example, they can customize the template with formulas or boilerplate text to reduce the time they take in preparing journal entries.
• Easy compliance with the requirement for justification documentation. Users attach and submit justification documentation on the same tab in the user interface where they created their journal entries.

Overall, JEXL provides a streamlined workflow solution for creating and submitting journal entries.

Conclusion

Developing an Excel-based application by using Visual Studio 2005 Tools for the Microsoft Office System was easier and faster than developing the Web-based applications that JEXL replaced. Users can benefit from the entire functionality of Excel, a single sign-in, and easy compliance with the business requirement for justification documentation.

For More Information

For more information about Microsoft products or services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada information Centre at (800) 563-9048. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information via the World Wide Web, go to:

http://www.microsoft.com

http://www.microsoft.com/itshowcase

http://www.microsoft.com/technet/itshowcase

© 2006 Microsoft Corporation. All rights reserved.

This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY. Microsoft, BizTalk, Excel, Visual Studio, and Windows are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners.