Office Business Application Starter Kit: Integrating 2007 Office System with SAP Using Visual Studio 2005 Tools for the Office System SE (Part 2 of 2)
Summary: Get insight into the Office Business Application Starter Kit for SAP (OBA Starter Kit for SAP), and learn how to integrate line-of-business (LOB) systems such as SAP with Microsoft Office.
Jan Narkiewicz, Softagon
Steve Fox, Microsoft Corporation
Applies to: Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System, Microsoft Office Excel 2007, Microsoft Office PowerPoint 2007
This document introduces the application associated with the Office Business Application Starter Kit for SAP (OBA Starter Kit for SAP). This application enables the Contoso Tours travel agency (a fictitious company) to sell travel packages to its customers. A package is a multi-destination trip designed to let the traveler attend a series of events. Examples of these events include Xbox 360 tournaments, bicycling races, and football matches. At the same time a package is purchased, flight information can be found and flights can be booked. The overall application provides an example of an Office Business Application (OBA) by not only integrating parts of the 2007 Microsoft Office system, but also in extending on that system to integrate with a line-of-business (LOB) system – in this case SAP. Under the covers, this application is demonstrating integration among the Microsoft Office client, Microsoft Visual Studio 2005 Tools for the Microsoft Office System (VSTO), and SAP, and showcasing technologies related to Microsoft Visual Studio 2005 Tools for the 2007 Microsoft Office System (also known as Visual Studio 2005 Tools for Office Second Edition, or VSTO 2005 SE), Microsoft SQL Server 2005, and SAP.
The application runs as a Microsoft Office Excel 2007 add-in and facilitates the process of purchasing packages, maintaining packages and associated events, and booking flights. The application is also used to dynamically generate Microsoft Office PowerPoint 2007 presentations. These PowerPoint presentations are shown to potential customers. They display marketing information designed to entice customers to purchase the packages. Each presentation includes snazzy graphics and information about other customers who are attending the events in the package.
The OBA Starter Kit for SAP contains a comprehensive installation guide. Before you attempt to run the application and review the tutorial, use the installation guide to assist in installing the application. The installation guide presents the prerequisites required to run the application associated with this tutorial.
The Contoso Tours application’s technical goals are to demonstrate:
How the 2007 Microsoft Office system and VSTO 2005 SE can interface with SAP using a Web service exposed by SAP via generic Web Service Definition Language (WSDL) and a SOAP Handler. This SOAP Handler enables every Business Application Programming Interface (BAPI) Remote Function Call (RFC) in SAP to be accessed as a Web service.
New features of the Microsoft Office Fluent user interface, including customized Ribbons and Excel 2007 dashboards.
How Microsoft Office applications can both consume Web services and access data using SQL Server 2005.
How XML maps can be used to display data within Excel 2007.
How to use VSTO 2005 SE to develop task panes and dialogs using Windows Forms.
How PowerPoint 2007 applications can be customized and dynamically created using VSTO 2005 SE.
How one Microsoft Office application (Excel 2007) can launch another Office application (PowerPoint 2007).
How the native Microsoft Office XML format (a PowerPoint .pptx file) can be decompressed (unzipped) and parsed to customize and use features of Microsoft Office not presently exposed programmatically.
The Xbox 360 Tournament Package is a typical Contoso Tours package (suite of events). This package targets the avid video gaming demographic. The Xbox 360 Tournament Package, and all packages sold, include travel to multiple events in multiple cities. The events associated with the Xbox 360 Tournament Package are a Project Gotham Racing 3 tournament held in Frankfurt, a Ninety-Nine Nights tournament held in Singapore, a Fuzion Frenzy 2 tournament held in San Francisco, and a Gears of War tournament held in New York.
The Contoso Tours packages and events theme is designed to build on the sample data provided with NetWeaver 2004s, which is part of SAP. The SAP sample data (from the downloadable trial version of SAP) includes customers, flight information, and the flights booked by the customers. A goal of this application is to demonstrate accessing SAP and SQL Server 2005 data so additional travel agency-related information is placed in SQL Server 2005 to augment the SAP flight data. The information stored in SQL Server 2005 includes package and event-specific data not found in SAP.
Excel 2007 serves as a front end to administer packages and events (create, update, and delete via SQL Server 2005). Excel 2007 also displays flight information and customer lists retrieved from SAP. Excel 2007 also displays financial information about packages and events, which includes information retrieved from both SQL Server 2005 and SAP (ticket pricing).
Excel 2007 is also used by travel agents at Contoso Tours to sell packages. This includes signing customers up for these packages (SQL Server 2005 data), displaying flight information (SAP data), and booking the customers’ flights (SAP data). The personal information for customers who book a package is stored in SAP, along with the flights booked by the customers. The application is also used to market packages to potential customers. A travel agent at Contoso Tours can show a potential customer a dynamically generated PowerPoint 2007 presentation that includes package and event information retrieved from SQL Server 2005. The information retrieved includes graphics to enhance the presentation. This PowerPoint 2007 presentation, which is generated by VSTO 2005 SE, also retrieves data from SAP in order to show potential customers who else has purchased the package. This includes graphically displaying the age and geographic demographic information of customers. This data is retrieved from SAP. The idea here is to sell based on this demographic information: "see, lots of other families with kids from Beijing are flying around the world to take part in the Xbox360 Tournament package."
The Contoso Tours application does not provide a complete suite of administrative features for managing packages, events, venues (where events are held), flights, and customers. This is a starter kit, and the set of functions implemented reflects a feature set that shows key features of Microsoft Office, VSTO 2005 SE, and SAP. The starter kit is designed to be comprehensive with respect to the technical information presented rather than comprehensive with respect to the functionality of the sample application, and is designed to illustrate how you might create an OBA.
Modes of Operation
The Contoso Tours application has two modes of operation:
General Mode (default): Running under this mode, the application can be used to manage and sell packages. This includes maintaining the events associated with each package, listing customers, and querying flight information, including seats available. A travel agent would typically use this mode of operation.
Analysis Mode: Running under this mode, the user can access information about revenue and sales for packages, events, and flights. An accountant or business analyst would typically use this mode of operation.
Both modes of operation access SAP and SQL Server 2005 data, and both modes demonstrate the use of VSTO 2005 SE to expose Office features such as custom Ribbons. General Mode further demonstrates VSTO 2005 SE, including task panes and dialogs. This mode shows how Excel 2007 can launch PowerPoint 2007, how PowerPoint 2007 can be dynamically generated using VSTO 2005 SE, and how the native PowerPoint XML file format can be accessed to further customize the application. Analysis mode demonstrates features such as Excel 2007 dashboards and the use of XML maps to display data within Excel 2007.
SQL Server 2005 Data Access
SQL Server 2005 is accessed programmatically using ADO.NET to call SQL Server 2005 stored procedures. This access is enhanced using Enterprise Library 2.0, which can be found on the MSDN Patterns & Practices Web site at http://msdn.microsoft.com/practices/.
Patterns & Practices is a suite of developer best practices. These recommendations, including the Enterprise Library 2.0 data access layer, facilitate improved design, development, and deployment on the Microsoft application platform.
SAP Data Access
SAP exposes a generic Web service that can be used to extract WSDL information from the SAP Business Application Programming Interface (BAPI). WSDL is the standard means by which Web services are accessed, and in this case the SAP-specific BAPI Web services. Using this approach, you do not have to set up Web services directly in SAP via the SAP GUI application.
Visual Studio 2005 consumes WSDL and generates code that can be compiled. For the OBA Starter Kit for SAP, a dedicated C# project was created to handle all SAP Web service access. Accessing the WSDL is a matter of pointing Visual Studio 2005 (add Web reference) to an SAP-specific URL that exposes the desired Web service. To understand this, consider the BAPI called BAPI_FLTRIP_GETLIST. This BAPI retrieves flight lists. To expose this BAPI as a Web service, the following URL was accessed using Visual Studio 2005: http://localhost:8000/sap/bc/soap/wsdl11?services=BAPI_FLTRIP_GETLIST. This URL enables Visual Studio 2005 to access the SAP WSDL, and therefore Visual Studio 2005 could generate the corresponding C# code to access this Web service.
Before running the application, ensure that the SAP Service is running. For guidance on how to check whether the SAP Service is running and how to start it, check the OBA Starter Kit for SAP installation guide.
Starting the Application
The Contoso Tours application uses SAP security credentials, so before running through the application’s features, more mundane things like login need to be addressed. The basic steps for logging in are straightforward.
Run Excel 2007. The Contoso Tours application is accessible through the Contoso Tours custom Ribbon.
Click the Login button, which displays a dialog into which a user name and password can be specified.
It is important to note that the application is not technically logging into SAP. The user name and password are being used to test that SAP can be accessed (the login process). This access test is a matter of calling an SAP method and passing in the supplied credentials. This SAP method and all SAP methods are exposed via a Web service that is consumed by the Contoso Tours application. Future calls to SAP will use these validated credentials. The standard credentials for SAP NetWeaver 2004 trial version are:
If the application is running for the first time, it will show a dialog that provides the option to seed the application with data.
If this application is to be used for demonstration purposes, click the Seed the database link. This action creates flight and client data (information stored in SAP), and signs the clients up for packages and their corresponding events (information stored in SQL Server 2005). Seeding the database may take more than ten minutes, depending on the speed of the computer on which the application is run.
The button to the right of Login on the Contoso Tours Ribbon is Switch to Analysis Mode, which indicates that the present mode is General Mode (the default mode). The topics discussed in this section pertain to General Mode. These topics include buying packages, managing packages/events/venues, listing flights/clients, and displaying a PowerPoint 2007 presentation as part of the package sales process.
Buying a Package
An example scenario for the buy-a-package feature is the case of a travel agent purchasing a package on behalf of a client who is a cycling aficionado. The package oriented towards bicyclists is the Adventure Works package. This package is built around the SQL Server 2005 sample database, Adventure Works, named for a fictitious bicycle shop. The buy-a-package feature demonstrates VSTO 2005 SE and accessing both SAP and SQL Server 2005 data. To purchase a package, click the Buy a Package button on the Contoso Tours custom Ribbon. This displays the Buy Package task pane in Excel 2007.
The Buy Package task pane displays packages and the events for a package (SQL Server 2005 data). This task pane also displays the cost of the package purchased, based on a client selecting the bronze, silver, or gold version of each event. These event levels (bronze, silver, and gold) are used to make the analytical data more interesting. They correspond to flight classes, namely First Class, Business Class, and Economy, respectively.
Using the Buy Package task pane, travel agents can show the client the dynamically generated PowerPoint 2007 presentation marketing the package (View Presentation), view the details of a package including events and cities (View Package), and adjust the package price by selecting different classes of event (bronze, silver, and gold). Ultimately, the Buy Package button is used to allow a client to purchase the package.
The View Presentation button launches the visual extravaganza (the PowerPoint 2007 presentation) designed to convince the customer to purchase the package. The PowerPoint 2007 presentation contains a title screen, taken from the Xbox 360 Package, for the package.
This screen was invoked using the Process.Start method of the .NET Framework. The name of the PowerPoint 2007 presentation contains the PackageID (used to look up the package in SQL Server 2005), the SAP user name, and an encrypted form of the SAP password. This is not a recommended security practice for passing passwords between Office applications. This package, and the SAP login credentials, enable the PowerPoint 2007 VSTO 2005 SE add-in to access the package via SQL Server 2005 and allow the add-in to access customer demographic information from SAP.
In the previous screenshot, the image associated with the package is extracted from SQL Server 2005 and incorporated into the PowerPoint 2007 presentation. The second slide in the presentation is event-specific. An example of an event-specific slide is as follows.
This screen shows a per-event graphic extracted from SQL Server 2005. There is a corresponding slide for each event in the package.
The final screens of each PowerPoint 2007 presentation contain demographic information for the customers who have currently purchased the package. SAP is also used to retrieve demographic information, including a breakdown of the classes of travel purchased.
SAP also facilitates retrieving the city of origin for each customer who purchased the package.
The previous screenshots were generated with the SAP login credentials passed into the PowerPoint 2007 add-in, and were extracted from SAP.
The graphs shown in the last few screenshots merit an additional explanation. These graphs are actually Excel 2007 spreadsheets embedded in PowerPoint 2007. In order to update the values in these graphs, they need to be extracted from PowerPoint 2007. PowerPoint 2007 files, like all Office files, are compressed files that contain XML that corresponds to the various embedded objects, such as images and graphs. After extracting the embedded Excel 2007 sheet from the PowerPoint 2007 file, the add-in decompresses the embedded Excel 2007 sheet and extracts the XML file that represents the spreadsheet. Within this spreadsheet, the predefined cell values are replaced with values retrieved from SAP. After these changes are made, the Excel file is compressed and the PowerPoint file is reconstructed and compressed again. This approach was made possible because the native format of the 2007 Microsoft Office system is XML. This technique was deemed mandatory because VSTO 2005 SE did not offer the requisite functionality in support of this feature.
Buy Package Button
Clicking the Buy Package button displays a dialog that lets travel agents buy a package for their customers.
The package and event information in the previous screenshot is SQL Server 2005 data. All flight information is SAP data.
The City field in the previous screenshot is of particular importance. This is the city of origin from which a client will fly to an event. Events take place in major cities (such as San Francisco, Tokyo, and Berlin) because these cities are found in the SAP data repository as flight destinations. It is important to enter cities in the previous dialog that contain a matching flight origination location. For example, the data in SAP includes a flight from Frankfurt to Singapore. When a client attends an event in Singapore, Frankfurt is a practical choice to enter as the client’s home city.
Clearly SAP has only limited flight connections in its sample data, so when using the previous screen, refer to Appendix A: Flight Connections. This section of the document contains a list of available flights (origination and destination city) such that a city can be entered in the previous dialog that will result in a matching flight.
Clicking on the airplane icon next to an event in the previous dialog allows the flight to be booked using the methods exposed by SAP. When the airplane icon is clicked, the Flight List dialog is displayed.
This dialog is shown while querying SAP for flight information. To emphasize that the flight data comes from SAP rather than another source, the status dialog contains the text Querying SAP.
Before querying for a flight, pay particular attention to the default Travel Agency, Bavarian Castle. As discussed previously, SAP exposes a limited number of flights, and Bavarian Castle is the only travel agency with registered flight connections in SAP sample data. If a different travel agency is selected and the search button is clicked, no flights will be returned.
The Flight List screen is used to select a flight. This dialog must be used to book a flight for each event in the package. After the per-package flights have been booked and the customer information has been entered, click the Save button on the Book Package dialog.
Accessed from the custom Ribbon, the Package Outline option displays a hierarchical view of each Package, corresponding Events, and the Event’s corresponding flight connections, categorized into promo types - Gold, Silver, and Bronze. The Package Outline is filtered based on the specified agency in the list; Bavarian Castle is specified because it is the only agency available in the SAP sample data that has flight connections associated with it.
The travel agency from which the tickets are purchased is clearly SAP-specific information. Packages and events are retrieved from SQL Server 2005. The classes of travel - gold, silver, and bronze - are really First Class, Business Class, and Economy respectively. This information and the other flight information is retrieved from SAP.
The Package Outline is also able to display details of a Package or an Event. This functionality is accessed by clicking on the magnifying glass in the Package Viewer task pane. The information for the currently highlighted package or event is shown in Excel.
The magnifying glass button can also show event-specific information.
The events information and corresponding flights are displayed. This is a merging of the SQL Server 2005 and SAP data.
The data within Excel 2007 is displayed by creating a VSTO-supported XML map. The mapping is from an ADO.NET DataSet (an XML repository) to an Excel 2007 worksheet. This functionality maps each XML element to a corresponding cell in the spreadsheet.
The custom Ribbon Package Manager feature enables the creation, modification, and deletion of packages. By now it should be clear that Packages correspond to SQL Server 2005 data. Clicking the Package Manager button displays the Package Manager dialog.
The Package Manager dialog lists all available packages. The Edit and Delete buttons next to specific packages allow those packages to be modified and deleted. The left side of the dialog shows the per-package image displayed in the PowerPoint 2007 presentation. The per-package image displayed corresponds to the currently selected package. The New Package button in the upper right allows packages to be created.
Clicking the Edit button for a particular package launches the Package Editor dialog.
The Package Editor dialog allows Package information to be edited, if the package has not yet been sold to a customer. The previous screen is non-editable and the Save button is disabled. The Adventure Works package shown in the previous screenshot has already been sold to a customer, so its information is read-only. It is also not permissible to delete previously sold packages.
The Package Editor dialog contains a Poster tab. The PowerPoint 2007 add-in module of the application pulls this from the database and renders it as one of the backgrounds in the presentation slides. An example of the Package Editor’s Poster tab is as follows.
Notice in the previous screenshot that the poster cannot be edited, because the package has already been sold.
Clicking the Event manager button displays the Event Manager dialog.
This dialog allows events to be created, updated, and deleted. As with Package data, only events that have not been sold to customers can be edited.
The event administration functionality mirrors the Package administration functionality. The screen has a New Event button in the upper right. Each event has an Edit and Delete button. To the left side of the dialog is an image corresponding to the image displayed in the PowerPoint 2007 slide for the event. The image displayed corresponds to currently selected image.
Clicking the edit icon displays the Event window.
Notice that the information in the previous screen cannot be edited, because the package including this event has already been sold to a customer.
The Poster tab in the Event dialog allows the image displayed in the marketing PowerPoint 2007 presentation to be altered.
The Actor tab is not implemented. For a cycling race such as Adventure Works, the actors would be the headlining cyclists – the ones designed to encourage consumers to purchase the package containing the events. If the event were a musical performance, the actors would be the band members. A video game event might feature actors such as the game designers and the voice-over actors used in the game.
Venues are stored in SQL Server 2005 and correspond to the place where events take place. Just selling a plane ticket to New York does not automatically get a customer to the boxing match. The boxing match, or any other event, would take place in a specific venue, likely an arena.
The Venue Manager button displays the Venues tab.
Venues implement standard create, update, and delete functionality. Venues contain multiple images, including what the venue looks like (venue image), where the venue is located (venue location map), and seating chart (venue facility image).
The Customer List button on the custom Ribbon Listings group displays the list of customers.
This customer information is retrieved from SAP. The exclamation point on the left corresponds to customers who have not purchased at least one Package from Contoso Tours. A check indicates that they have purchased a package. Customers who have purchased packages exist in SAP and have a corresponding SQL Server 2005 record. The technical purpose of this screen is to demonstrate SAP data being displayed within an Excel worksheet using an XML map.
The Flight List, as its name suggests, lists all the flights available from SAP data. An example of the application when this option is selected is as follows.
This list uses an XML map to display data retrieved from SAP. The Flight List option also makes use of an Excel 2007 feature: conditional formatting. The icons within the First Class, Business Class, and Economy Class seats are generated using conditional formatting.
Analysis Mode is enabled by clicking the Analysis Mode button. This toggles the mode from General Mode to Analysis Mode. This causes the Ribbon to appear as follows.
In Analysis Mode, Contoso Tours can be used to show the travel agency’s financial information and to generate a report of package sales.
Actually, Analysis Mode is used to demonstrate that a custom Ribbon is not just buttons, but that it can also contain menus such as the following when Sales and Distribution is selected.
Using a combination of the Revenue and Forecast button and the Sales and Distribution menu, the feature of Analysis Mode can be demonstrated.
Revenue and Forecast
The Revenue and Forecast button on the custom Ribbon displays a spreadsheet that contains the company’s revenue for a particular year. Clicking this button prompts the user as to what year to display; selecting a year generates the revenue for that year.
After you select the year, the Revenue and the Forecast spreadsheet will be shown.
The previous information is all retrieved from SQL Server 2005.
The Package Sales menu displays the number and amount of sales per package.
The previous information is all retrieved from SQL Server 2005.
Package Sales per Promo Type
The Package Sales per Promo Type menu displays the number of sales per package categorized by promo types (Gold, Silver, and Bronze).
The previous information is all retrieved from SQL Server 2005 and SAP. As previously mentioned, this data makes use of conditional formatting.
The Ticket Sales menu displays the number and amount of airplane tickets sold per agency.
The data for the previous set of graphs is all retrieved from one data source, SAP.
To learn more about the products and technologies mentioned or used in this article, see these resources: