Building the Contoso Auto Sales Office Business Application Part 4 – Generate Reports

  • Article

Robert Green
 MCW Technologies

Download the code for this tutorial on Code Gallery

Read Part 1: Building the Contoso Auto Sales Office Business Application Part 1 - Scheduling Customer Appointments

Read Part 2: Building the Contoso Auto Sales Office Business Application Part 2 - Generating Automobile Quotes

Read Part 3: Building the Contoso Auto Sales Office Business Application Part 3 - Generate Financing Information

Introduction

Contoso Auto Sales is a fictitious automobile dealer specializing in high-end automobiles. Contoso has computer systems to talk to suppliers, to generate an invoice when a customer purchases an automobile, to bill for service visits and to pay employees. It wants a system to manage the day-to-day interaction with customers and improve its level of pre-sales customer service.

Contoso Auto would like a solution based on Office 2007 that addresses the following scenarios:

  • The sales manager needs to contact customers who have requested an appointment by calling Contoso or visiting the Web site. She wants to send a mail acknowledging the appointment request and then schedule appointments using Outlook.
  • Sales consultants want to use Word to create and print price quotes for automobiles, reflecting available options selected by a customer.
  • The financing manager wants to use Excel to generate financing information for customers with pending quotes. He wants the ability to generate this information interactively, but also wants it created automatically when the sales consultant generates a quote.
  • The sales manager wants to use Excel to run reports showing quotes by manufacturer, model, customer and employee. She wants to view reports offline on her laptop and wants the reports automatically updated with up to date information.

In the tutorials in the Building the Contoso Auto Sales Office Business Application series, you will use Visual Studio 2008 to create an Office Business Application that address each of the scenarios listed above, using Outlook 2007, Word 2007 and Excel 2007. The tutorials contain both Visual Basic and C# code.

Exercise 1: Create the Excel Reporting Add-in Project

Contoso’s sales manager wants to view “snapshots” of quote activity. She wants to see quotes by manufacturer, by model, by customer and by sales consultant and she wants to see what percentage options packages represent of the total quote price. Finally, she wants to be able to view these reports offline at home. Excel is the perfect tool for this.

In this tutorial, you will create an Excel 2007 reporting solution. The Excel solution will support the following:

  • The ability of the sales manager to retrieve information on quotes based on various criteria and display that information in a worksheet and in a chart.
  • The ability to view reports when offline.
  • The ability to retrieve the latest report data when online.

The solution you will build in this tutorial will be based on the Reports workbook, which starts out empty.

The add-in you will create will work with data that resides in the ContosoAuto SQL Server database. Refer to the first tutorial in this series for information on how to create this database.

To get started, in Visual Studio 2008 select File | New | Project to display the New Project dialog box. In the list of project types, expand the Office node. Select Version2007, displaying the list of templates. In the Templates pane, select Excel 2007 Workbook. Name your project Reports and select an appropriate folder for the project. Click OK to create the project. Visual Studio displays the Visual Studio Tools for Office Project Wizard (see Figure 1).

Figure 1. Visual Studio displays this wizard when you create the solution.

You can base the add-in on a new or existing workbook. Select Copy an existing document and click Browse. Navigate to the folder where you downloaded this tutorial’s sample project. Navigate to the Starter Documents and Workbooks folder. Select Reports.xlsx and click Open. This returns you to the wizard. Click OK. Click OK if Visual Studio displays the message shown in Figure 2.

Figure 2. Visual Studio displays this message when you create the solution for the first time.

In the Solution Explorer, expand the Data Connections node. If you do not have a connection to the ContosoAuto database, right-click on Data Connections and select Add Connection to display the Add Connection dialog box. If the Data source does not display Microsoft SQL Server (SqlClient), click Change. This displays the Change Data Source dialog box. Select Microsoft SQL Server (SqlClient) and click OK.

In the Add Connection dialog box, enter your computer name (if you are using SQL Server) or .\SQLEXPRESS (if you are using SQL Server Express) in the Server name text box. Select ContosoAuto from the database drop-down list. Click OK to close the Add Connection dialog box.

In the Server Explorer, expand the ContosoAuto node. Expand the Tables node to see the tables in the database (see Figure 3).

Figure 3. The ContosoAuto database contains these tables.

The Manufacturers table contains the automobile makers. The Models table contains the automobiles. The Quotes and QuotePackages tables contain the information that comprises existing quotes. The solution will use this data when generating reports.

The Excel solution you are building in this tutorial needs a connection to the ContosoAuto database. Select Project | Reports Properties. Select the Settings tab. To add a new setting, enter ContosoAutoConnectionString in the Name column. Select (Connection string) from the Type drop-down list.

Click the ellipsis in the Value text box. This displays the Connection Properties dialog box. Create a connection to the ContosoAuto database. Click OK to close the Connection Properties dialog box. Close the Project Designer, saving your changes.

You will use a DataSet to communicate with the ContosoAuto database. Rather than create the DataSet from scratch, you will use an existing version of it. Select Project | Add Existing Item to display the Open File dialog box. Navigate to the folder where you downloaded this tutorial’s sample project. Navigate to the DataSets\Reports folder. Then navigate to the VB or CS folder depending on the language you are using. Select ContosoAutoDataSet.xsd and click OK to add the DataSet to the project.

In the Solution Explorer, double-click ContosoAutoDataSet.xsd to open the DataSet Designer (see Figure 4). Take a few minutes to familiarize yourself with the tables and TableAdapters.

Figure 4. The add-in uses this DataSet to work with the data in the ContosoAuto database.

Close the DataSet Designer. Save your changes. Select Build | Build Solution and verify that the project compiles correctly.

In the next step, you will add data bound controls to the report worksheet. These controls will contain and display the information for the various queries the sales manager can run.

In the previous tutorial, you saw that you could create data bound controls on spreadsheets by dragging from the Data Sources window. You will use the same technique in this tutorial. By default, dragging a column from the Data Sources window creates a NamedRange control and dragging a table creates a ListObject control.

Double-click Sheet1 in the Solution Explorer to display the Reports worksheet. Select Data | Show Data Sources to display the Data Sources window. Expand the ReportData node.

Expand the drop-down list next to the QuoteDate column. Select NamedRange from the drop-down list.

Drag ReportData from the Data Sources window to the Excel worksheet. Drop the table in cell B9 (see Figure 5). This creates a List object containing each of the columns in ReportData (see Figure 6). Visual Studio also adds data controls, which you can see in the Component Tray.

Figure 5. Drag the ReportsData table from the Data Sources window to the Excel worksheet.

Figure 6. The List object is bound to the ReportsData table.

Change the Name property of the List object to ReportDataList if you are using Visual Basic and reportDataList if you are using C#.

Place the cursor in cell B9 and change the column heading text to Quote.

Place the cursor in cell C9 and change the column heading text to Date.

Place the cursor in cell H9 and change the column heading text to Vehicle Price.

Place the cursor in cell I9 and change the column heading text to Options Price.

Place the cursor in cell J9 and change the column heading text to Total Price.

Select cells K9 through N9. Right-click and select Delete | Table Columns.

Select cells H10 through J10 Right-click and select Format Cells from the menu. In the Number tab of the Format Cells dialog select Currency from the Category list. Select 2 from the decimal places list. In the Alignment tab, select Right (indent) from the Horizontal drop-down list. Click OK.

Set the ShowTotals property of ReportDataList to True. Click the arrow next to the Date column in the total row (cell C11). Select Count (see Figure 7).

Figure 7. Select Count to display the number of quotes.

Click the arrow next to the Vehicle Price column in the total row (cell H11). Select Average. Click the arrow next to the Options Price column in the total row (cell I11). Select Average. Click the arrow next to the Total Price column in the total row (cell J11). Select Average. The worksheet should look like Figure 8.

Figure 8. The worksheet should look like this.

Save your changes. Select Build | Build Solution and verify that the project compiles correctly. Press F5 to run the application. You should see the Reports worksheet filled in with information for each quote in the database that you entered in the previous tutorials (see Figure 9). Exit Excel, without saving your changes, and return to Visual Studio.

Figure 9. The worksheet displays all quotes.

Use an Actions Pane to Select Report Criteria

You will next add a user control that enables the sales manager to see quotes for a particular manufacturer, model, customer or sales representative.

Select Project | Add Existing Item to display the Open File dialog box. Navigate to the folder where you downloaded this tutorial’s sample project. Navigate to the UI Components folder. Then navigate to the VB or CS folder depending on the language you are using. Select ReportCriteria. Click Add to add the user control to the project.

The user control includes four grids (see Figure 10). The first grid in the control displays manufacturers and the number of quotes for each. The second grid displays models and the number of quotes for each. The third grid displays customers who have quotes and the number of quotes for each customer. The fourth grid displays sales representatives and the number of quotes for each.

Figure 10. The ReportCriteria control displays manufactures, models, customers and employees and the number of quotes for each.

Save your changes. Do not build the solution. It will not compile yet.

Exercise 2: Cache Data in the Workbook

Typically, you would add code to the Load method of the user control to call the Fill methods of the four TableAdapters and fill the grids when the user control appears. However, to do this, the sales manager must be online. If the sales manager is offline, the calls to the Fill methods would each throw an exception.

The sales manager wants to be able to run reports while offline, as well as online. There are several options for a disconnected scenario. You could populate the dataset with the needed information and then prior to disconnecting copy the dataset to XML using the WriteXML method of the dataset. When the sales manager opens the workbook, you could use the ReadXML method to repopulate the dataset from the XML rather than from the SQL Server database.

You could also use SQL Server Compact Edition on the sales manager’s computer and synchronize data via either queries or sync services.

The solution with the lowest overhead is to cache the data inside the workbook. The dataset will be stored as XML in a data island when the user saves the workbook. At startup, the Visual Studio Tools for Office runtime checks if the data island contains cached data. If so, it fills the dataset from the cache rather than the database.

Using caching as your offline technique eliminates the overhead of the XML and SQL Server scenarios. Everything needed to run reports online and offline is contained in the spreadsheet file. The sales manager can copy the file to another computer and continue to run the same reports using the same data. This works well for read-only report data.

You will next configure the workbook to cache the data. If the worksheet is not visible in Visual Studio, double-click Sheet1 in the Solution Explorer.

Select ContosoAutoDataSet in the Component Tray. Set the CacheInDocument property to True and set the Modifiers property to Public (see Figure 11).

Figure 11. Set the Modifiers and CacheInDocument properties to cache the dataset in the workbook.

Currently, this instance of the DataSet only contains quotes, not the data that appears in the four grids. The solution is to add the appropriate TableAdapters to the worksheet and to then retrieve the data when the worksheet loads. That way, all of the data needed to run reports will be cached in the worksheet’s instance of the dataset.

From the Reports Components tab of the Toolbox, drag ManufacturersListTableAdapter onto the worksheet. This adds the TableAdapter to the Component Tray. Remove the 1 at the end of the TableAdapter’s name.

Next, one at a time, drag ModelsListTableAdapter, CustomersWithQuotesTableAdapter and EmployeesListTableAdapter onto the worksheet. Remove the 1 at the end of each TableAdapter’s name.

In the Solution Explorer, right click on Sheet1 and select View|Code. Add the following declaration.

 

' Visual Basic

Private criteriaControl As ReportCriteria = Nothing

// C#

private ReportCriteria criteriaControl = null;

Replace the code in the Sheet1_Startup method with the following code:

' Visual Basic

If Me.NeedsFill("ContosoAutoDataSet") Then

  Me.ManufacturersListTableAdapter.Fill( _

    Me.ContosoAutoDataSet.ManufacturersList)

  Me.ModelsListTableAdapter.Fill( _

    Me.ContosoAutoDataSet.ModelsList)

  Me.CustomersWithQuotesTableAdapter.Fill( _

    Me.ContosoAutoDataSet.CustomersWithQuotes)

  Me.EmployeesListTableAdapter.Fill( _

    Me.ContosoAutoDataSet.EmployeesList)

  Me.ReportDataTableAdapter.Fill( _

    Me.ContosoAutoDataSet.ReportData)

End If

// C#

if (this.NeedsFill("contosoAutoDataSet"))

{

  this.manufacturersListTableAdapter.Fill(

    this.contosoAutoDataSet.ManufacturersList);

  this.modelsListTableAdapter.Fill(

    this.contosoAutoDataSet.ModelsList);

  this.customersWithQuotesTableAdapter.Fill(

    this.contosoAutoDataSet.CustomersWithQuotes);

  this.employeesListTableAdapter.Fill(

    this.contosoAutoDataSet.EmployeesList);

  this.reportDataTableAdapter.Fill(

    this.contosoAutoDataSet.ReportData);

}

NeedsFill will be true if the Visual Studio Tools for Office runtime did not find data in the cache. The code above will then connect to the database and retrieve the data. If the runtime finds data in the cache, NeedsFill will be false and the Fill methods will not run.

The code to retrieve the data in the user control’s grids is in the Sheet1 class. What code is in the user control? To see, right-click ReportCriteria in the Solution Explorer and select View Code. The ReportCriteria_Load method contains the following code:

' Visual Basic

Me.ContosoAutoDataSet = _

  CType(Globals.Sheet1.ContosoAutoDataSet.Copy, ContosoAutoDataSet)



Me.ManufacturersListBindingSource.DataSource = Me.ContosoAutoDataSet

Me.ManufacturersListBindingSource.ResetBindings(False)



Me.ModelsListBindingSource.DataSource = Me.ContosoAutoDataSet

Me.ModelsListBindingSource.ResetBindings(False)



Me.CustomersWithQuotesBindingSource.DataSource = Me.ContosoAutoDataSet

Me.CustomersWithQuotesBindingSource.ResetBindings(False)



Me.EmployeesListBindingSource.DataSource = Me.ContosoAutoDataSet

Me.EmployeesListBindingSource.ResetBindings(False)

// C#

this.contosoAutoDataSet = ((ContosoAutoDataSet)

  (Globals.Sheet1.contosoAutoDataSet.Copy()));



this.manufacturersListBindingSource.DataSource =

  this.contosoAutoDataSet;

this.manufacturersListBindingSource.ResetBindings(false);



this.modelsListBindingSource.DataSource =

  this.contosoAutoDataSet;

this.modelsListBindingSource.ResetBindings(false);



this.customersWithQuotesBindingSource.DataSource =

  this.contosoAutoDataSet;

this.customersWithQuotesBindingSource.ResetBindings(false);



this.employeesListBindingSource.DataSource =

  this.contosoAutoDataSet;

this.employeesListBindingSource.ResetBindings(false);

The code above first sets the user control’s instance of the dataset a copy of the worksheet’s dataset instance. That instance contains the data needed for the grids. The code then binds the four grids to the user control’s dataset.

Return to the Sheet1 code file. Add the following code to the end of the Sheet1_Startup method to add the ReportCriteria user control to the Actions Pane.

' Visual Basic

criteriaControl = New ReportCriteria

Globals.ThisWorkbook.ActionsPane.Controls.Add(criteriaControl)

Globals.ThisWorkbook.ActionsPane.AutoSize = True

// C#

criteriaControl = new ReportCriteria();

Globals.ThisWorkbook.ActionsPane.Controls.Add(criteriaControl);

Globals.ThisWorkbook.ActionsPane.AutoSize = true;

Add the following method as a placeholder.

' Visual Basic

Public Sub ShowQuotes(ByVal criteria As String, ByVal idValue As Short)



End Sub

// C#

public void ShowQuotes(string criteria, short idValue)

{



}

Save your changes and select Build | Build Solution and verify that the project compiles correctly. Press F5 to run the application. You should see the quotes in the worksheet. The Actions Pane should look like Figure 12.

Figure 12. The worksheet should look like this.

Exit Excel, saving your changes. If you do not save your changes, the workbook will not cache the data in the dataset.

Stop the instance of SQL Server you are using. You can use any of the following techniques:

  • In SQL Server Management Studio, connect to the instance of SQL Server you are using. Then right-click on the instance in the Object Explorer and select Stop.
  • In a command prompt Run As Administrator, enter net stop mssqlserver if you are using SQL Server or net stop mssql$sqlexpress if you are using SQL Server Express.
  • Select Administrative Tools | Services from the Windows Start menu. In the Service applet, right-click on SQL Server (MSSQLSERVER) or SQL Server (SQLEXPRESS) and select Stop.

Return to Visual Studio. Press F5 to run the application. Even though the database is unavailable, the quotes should still appear in the worksheet and the Actions Pane should still display data in all four grids. All of this data is in the workbook’s cache. Exit Excel. It doesn’t matter if you save your changes. The data remains in the cache.

Restart the instance of SQL Server you are using. You can use any of the following techniques:

  • In SQL Server Management Studio, right-click on the instance in the Object Explorer and select Start.
  • In a command prompt Run As Administrator, enter net start mssqlserver or net start mssql$sqlexpress.
  • In the Service applet, right-click on SQL Server (MSSQLSERVER) or SQL Server (SQLEXPRESS) and select Start.

As you have seen, if the user is offline, the worksheet uses the data in the cache and the sales manager can run reports using the cached data. What happens when she is back online? As it stands, the worksheet will continue to use cached data. The VSTO runtime does not first check if the user is online and then check the cache. It checks the cache and uses the data in it regardless of whether the user is online or offline.

You will now add code to check if the user is online and to only check the cache if the user is offline.

Return to the Sheet1 code file. Add the following code in bold to the Sheet1_Startup method.

' Visual Basic

Dim online As Boolean = False

Using connection As New System.Data.SqlClient.SqlConnection( _

  My.Settings.ContosoAutoConnectionString)



  Try

    connection.Open()

    online = True

    connection.Close()

  Catch

  End Try

End Using



If online Then

  MessageBox.Show("Online")

  Me.ManufacturersListTableAdapter.Fill( _

    Me.ContosoAutoDataSet.ManufacturersList)

// C#

bool online = false;

var projectSettings = new Properties.Settings();

using (System.Data.SqlClient.SqlConnection connection =

  new System.Data.SqlClient.SqlConnection(

    projectSettings.ContosoAutoConnectionString))

{

  try

  {

    connection.Open();

    online = true;

    connection.Close();

  }

  catch

  {

  }

}

if (online == true)

{

  MessageBox.Show("Online");

  this.manufacturersListTableAdapter.Fill(

    this.contosoAutoDataSet.ManufacturersList);

The most reliable way to tell if the database is available is to attempt to connect to it. If the code above can connect, the user is online and the code will retrieve the latest data from the database. If the code cannot connect, the user is offline and the worksheet will use the cached data.

You will use the message box only to confirm when the worksheet retrieves data from SQL Server and when it retrieves it from the cache. After confirming the code works as expected, you can remove that line.

The default connection timeout is fifteen seconds. To shorten that, select Project | Reports Properties. Select the Settings tab. Click the ellipsis in the Value text box. This displays the Connection Properties dialog box. Click Advanced to display the Advanced Properties dialog box. Set the Connect Timeout property to 5. Click OK to close the Advanced Properties dialog box and then click OK to close the Connection Properties dialog box. Close the Project Designer and save your changes.

Save your changes and select Build | Build Solution and verify that the project compiles correctly. Press F5 to run the application. You should see the message box confirming you are online. You should then see quotes in the worksheet and data in the user control grids. Exit Excel and save your changes.

Stop SQL Server and run the application again. You should not see the message box confirming you are online. You should still see quotes and data in the grids. Exit Excel and restart SQL Server.

Exercise 3: Run Reports Based on Criteria

The sales manager wants to see quotes based on criteria. To do that, she can select a manufacturer, model, customer or sales representative and then click the View quotes button. You will next write the code that retrieves quotes for a particular manufacturer, model, customer or sales representative.

If this were an online only solution, you could use methods of the ReportDataTableAdapter to retrieve quote data for a particular manufacturer, model, etc. For example, the TableAdapter could have a FillByManufacturer method. If your code called that method, it would use the following in the query it sent to SQL Server:

WHERE Quotes.ManufacturerID = idValue

In the query above, you would set idValue to the ID of the manufacturer the sales manager selected in the first user control grid. The FillByManufacturer method would then retrieve all quotes for a particular manufacturer. You would add similar methods to the ReportDataTableAdapter for retrieving quotes for models, customers and employees.

However, in the online/offline scenario you are building in this tutorial, you have already retrieved all of the quotes once the workbook loads. Rather than send queries to the database to view subsets of quotes, you can use LINQ to DataSet to query the already populated dataset.

In the Solution Explorer, right-click on the solution and select Add Reference. In the .NET tab of the Add Reference dialog box, select System.Data.Linq and click OK to add a reference.

In the Solution Explorer, right click on Sheet1 and select View|Code. Add the following declaration.

' Visual Basic

Private quotes As System.Data.EnumerableRowCollection( _

  Of ContosoAutoDataSet.ReportDataRow) = Nothing

// C#

private System.Data.EnumerableRowCollection<

  ContosoAutoDataSet.ReportDataRow> quotes = null;

Add the following code to the ShowQuotes method:

' Visual Basic

Globals.ThisWorkbook.Application.ScreenUpdating = False

Select Case criteria

  Case "Manufacturer"

    quotes = From quote In _

      Me.ContosoAutoDataSet.ReportData.AsEnumerable() _

      Where quote.ManufacturerID = idValue

  Case "Model"

    quotes = From quote In _

      Me.ContosoAutoDataSet.ReportData.AsEnumerable() _

      Where quote.ModelID = idValue

  Case "Customer"

    quotes = From quote In _

      Me.ContosoAutoDataSet.ReportData.AsEnumerable() _

      Where quote.CustomerID = idValue

  Case "Employee"

    quotes = From quote In _

      Me.ContosoAutoDataSet.ReportData.AsEnumerable() _

      Where quote.EmployeeID = idValue

End Select

Me.ReportDataBindingSource.DataSource = quotes.AsDataView

ReportDataList.RefreshDataRows()

Globals.ThisWorkbook.Application.ScreenUpdating = True

// C#

Globals.ThisWorkbook.Application.ScreenUpdating = false;



switch (criteria)

{

  case "Manufacturer":

    quotes = 

      from quote in this.contosoAutoDataSet.ReportData.AsEnumerable()

      where quote.ManufacturerID == idValue

      select quote;

    break;

  case "Model":

    quotes = 

      from quote in this.contosoAutoDataSet.ReportData.AsEnumerable()

      where quote.ModelID == idValue

      select quote;

    break;

  case "Customer":

    quotes = 

      from quote in this.contosoAutoDataSet.ReportData.AsEnumerable()

      where quote.CustomerID == idValue

      select quote;

    break;

  case "Employee":

    quotes = 

      from quote in this.contosoAutoDataSet.ReportData.AsEnumerable()

      where quote.EmployeeID == idValue

      select quote;

    break;

}

this.reportDataBindingSource.DataSource = quotes.AsDataView();

reportDataList.RefreshDataRows();



Globals.ThisWorkbook.Application.ScreenUpdating = true;

The code above first turns off screen updating to speed up the process of filling in the List object. It then queries the ReportData table and returns only the quotes for the selected manufacturer, model, customer or employee. The code then uses the AsDataView extension method to create a DataView and assigns that to the DataSource of the ReportDataBindingSource component. The code then refreshes the List, which now displays only the quotes returned by the query. The code then turns screen updating back on.

Save your changes and select Build | Build Solution and verify that the project compiles correctly. Press F5 to run the application. Select a manufacturer in the first grid of the user control and confirm the label next to the button displays “for this manufacturer”. Click View quotes. The worksheet should display only the quotes for the selected manufacturer. Select various manufacturers, models, customers and employees and confirm that you can view the quotes for each of them.

Exit Excel. Shut down SQL Server and run the application again. You should be able to view quotes in the same manner as you did before. Exit Excel and restart SQL Server.

Display Quote Breakdowns in a Chart

Each automobile has a list of available optional packages, such as the Sport package or the Navigation package. These optional packages are a great “upsell opportunity” and provide an additional venue for Contoso to make money when it sells an automobile.

The sales manager would like to see, for each quote, what percentage of the total cost the optional packages represent. That would give her the ability to explore which customers are interested in options and how sales consultants are faring in up selling customers.

You will now write code to display that information in a chart.

Return to the Sheet1 code file. Add the following declarations:

' Visual Basic

Private chartRange As Excel.Range = Nothing

Private firstDataRow As Integer = 0

Private lastDataRow As Integer = 0

Private firstDataCol As Integer = 0

Private firstPriceCol As Integer = 0

Private chart1 As Microsoft.Office.Tools.Excel.Chart = Nothing

// C#

private Excel.Range chartRange = null;

private int firstDataRow = 0;

private int lastDataRow = 0;

private int firstDataCol = 0;

private int firstPriceCol = 0;

private Microsoft.Office.Tools.Excel.Chart chart1 = null;

Add the following method:

' Visual Basic

Private Sub DisplayChart()



End Sub

// C#

private void DisplayChart()

{



}

Add the following code to the method to delete the chart if it already exists in the worksheet.

' Visual Basic

If Me.Controls.Contains("Chart1") Then

  Me.Controls.Remove("Chart1")

End If

// C#

if (this.Controls.Contains("Chart1"))

{

  this.Controls.Remove("Chart1");

}

Note that it is not necessary to delete the chart in order to change the data displayed in it. You are deleting the chart here because it always appears below the report data and so its location changes each time the sales manager runs a different report.

Add the following code to determine, based on the size of the list object, where the chart should be.

' Visual Basic

firstDataRow = ReportDataList.Range.Row + 1

lastDataRow = ReportDataList.Range.Row + quotes.AsDataView.Count

firstDataCol = ReportDataList.Range.Column

firstPriceCol = firstDataCol + 6

// C#

firstDataRow = this.reportDataList.Range.Row + 1;

lastDataRow = this.reportDataList.Range.Row +

  quotes.AsDataView().Count;

firstDataCol = this.reportDataList.Range.Column;

firstPriceCol = firstDataCol + 6;

Add the following code to get a reference to the range the chart will occupy.

' Visual Basic

chartRange = Me.Range( _

  Me.Cells(lastDataRow + 5, firstDataCol + 4), _

  Me.Cells(lastDataRow + 25, firstDataCol + 7))

// C#

chartRange = this.Range[

  this.Cells[lastDataRow + 5, firstDataCol + 4],

  this.Cells[lastDataRow + 25, firstDataCol + 7]];

The chart will start five rows below the data in the Customer column. It will be 25 rows tall and 3 columns wide. The code above determines, based on the location and number of rows in the list object, what range this represents.

Add the following code to add the chart to the worksheet.

' Visual Basic

chart1 = Me.Controls.AddChart(chartRange, "Chart1")

// C#

chart1 = this.Controls.AddChart(chartRange, "Chart1");

Add the following code to get a reference to the data series.

' Visual Basic

chartRange = Me.Range( _

  Me.Cells(firstDataRow - 1, firstDataCol + 6), _

  Me.Cells(lastDataRow, firstDataCol + 7))

// C#

chartRange = this.Range[

  this.Cells[firstDataRow - 1, firstDataCol + 6],

  this.Cells[lastDataRow, firstDataCol + 7]];

The chart includes the vehicle price and options price. These are the seventh and eight columns in the list object. The data range for the chart starts in the cell containing the Vehicle Price column heading and ends in the cell containing the last options price.

Add the following code to specify the data series and type of the chart.

' Visual Basic

chart1.SetSourceData(chartRange, Excel.XlRowCol.xlColumns)

chart1.ChartType = Excel.XlChartType.xlColumnStacked100

chart1.HasLegend = True

// C#

chart1.SetSourceData(chartRange, Excel.XlRowCol.xlColumns);

chart1.ChartType = Excel.XlChartType.xlColumnStacked100;

chart1.HasLegend = true;

This code passes the data series range to the SetSourceData method, along with the instructions that the data is in columns. The code specifies the 100% Stacked Column chart type and to include the legend in the chart.

Add the following code in bold to the ShowQuotes method to display the chart after each query runs:

' Visual Basic

ReportDataList.RefreshDataRows()

DisplayChart()



Globals.ThisWorkbook.Application.ScreenUpdating = True

// C#

reportDataList.RefreshDataRows();

DisplayChart();



Globals.ThisWorkbook.Application.ScreenUpdating = true;

Save your changes and select Build | Build Solution and verify that the project compiles correctly. Press F5 to run the application. Select a manufacturer in the first grid of the user control. Click View quotes. The worksheet should display the quotes for the selected manufacturer and a chart showing the breakdown between vehicle price and options price for each quote (see Figure 13). Select various manufacturers, models, customers and employees and confirm that you can view the quotes and the chart for each of them. Exit Excel.

Figure 13. The sales manager can see the breakdown between vehicle price and options price for each quote.

Conclusion

In this tutorial, you saw how to build a solution in Excel 2007 that makes it easy for the sales manager to see quotes for a particular manufacture, model, customer or sales representative. She can also see, for each quote, what percent of the total cost is accounted for by optional packages.

You also took advantage of the data caching features of the Visual Studio Tools for Office runtime. The workbook caches all of the data needed to run reports when the sales manager saves the workbook. This makes it easy for her to run reports offline or even on a different computer.

About the Author

Robert Green is a Senior Consultant with MCW Technologies. He is a Microsoft MVP for Visual Studio Tools for Office. Robert has written or co-authored AppDev’s Visual Studio, LINQ, Windows Communication Foundation and Windows Workflow Foundation courseware, and appears in the video training for these courses, as well. Robert is a member of the INETA Speaker Bureau and has been a frequent speaker at technology conferences. Before joining MCW, Robert worked at Microsoft for 8 years, as a Program Manager on the Visual Basic product team and as a Product Manager for Visual Studio, Visual Basic, Visual Studio Tools for Office and Visual FoxPro. Visit his blog at http://www.mcwtech.com/blogs/rgreen