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:

• Username: Bcuser

• Password: Minisap

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.

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.

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.

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.

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.