Walkthrough: Creating a Report

Article
04/28/2015

LightSwitch doesn’t have built-in reporting capabilities, but you can create and print reports from a LightSwitch application by integrating Word. You can automate reporting by using Visual Studio and APIs for Word, but the Office Integration Pack LightSwitch extension simplifies the task.

By using the Office Integration Pack, you can more easily automate Word 2010, Excel 2010, and Outlook 2010 in a variety of ways. For example, you can import and export data, create documents and reports, and work with email and appointments. You can download the Office Integration Pack extension for free from CodePlex.

Prerequisites

This walkthrough requires the following components:

Create the Report Template

First, you create a Word document that will serve as a template for the report.

To create the report template

Open Word 2010.

A new blank document appears.
At the top of the document, type PrescriptionContoso Product Catalog.
Highlight the text, and then, in the Styles group of the Home tab on the ribbon, choose the Title command.
Move the cursor below the title, and then, on the Insert tab, choose the Table command, and then choose the Insert Table command.

The Insert Table dialog box appears.
In the Number of columns text box, enter 5, and then, in the Number of rows text box, enter 2.
Choose the AutoFit to Window option button, and then choose the OK button.
In the first row of the table, enter the following column headings: Product ID, Product, Description, Price, and Packaging.
Highlight the table, and then, in the Links group of the Insert tab, choose the Bookmark command.
In the Bookmark dialog box, name the bookmark Catalog, and then choose the Add button.
In the Page Setup group of the Page Layout tab, choose the Orientation command, and then choose the Landscape command.
On the File tab, choose the Save As command.
In the Save As dialog box, open the My Documents folder, name the file Product Catalog, and then choose the Save button.
On the File tab, choose the Exit command.

Add a Report to the Application

After you create the report template, you enable the Office Integration Pack extension, add a button to the application toolbar, and add code to create the report. You can also change the document type.

Note

If you haven’t previously built the Vision Clinic sample application, you'll first need to install and configure the PrescriptionContoso database, which is downloaded as part of the sample package. Open the Install.htm file, and then follow the instructions to install the database.

To enable the extension

On the Visual Studio menu bar, choose File, Open, Project/Solution.
Locate the Vision Clinic.sln file, and then choose the Open button.
In Solution Explorer, open the shortcut menu for the Properties node, and then choose Open.
In the application designer, choose the Extensions tab, and then select the Office Integration Pack check box.

To create the report

In Solution Explorer, open the shortcut menu for the ProductList screen node, and then choose Open.
In the content tree pane, expand the Screen Command Bar node, and then choose Add, New Button.
In the Add Button dialog box, name the button that you're creating Catalog, and then choose the OK button.
Open the shortcut menu for the Catalog button, and then choose Edit Execute Code.
In the Code Editor, enter the following Imports or using statement at the top of the file:
```
Imports OfficeIntegration
```
```
Using OfficeIntegration;
```

Add the following code to the Catalog_Execute method:

' Function to format a field as Currency.
Dim formatPrice = Function(x As Decimal) As String
                      Return Format(x, "c2")
                  End Function

' Map the Word column names to the entity column names.
Dim mapContent As New List(Of ColumnMapping)
mapContent.Add(New ColumnMapping("ProductID", "ProductID"))
mapContent.Add(New ColumnMapping("ProductName", "ProductName"))
mapContent.Add(New ColumnMapping("Description", "Description"))
' Format the price as Currency using the Function created above.
mapContent.Add(New ColumnMapping("CurrentPrice", "CurrentPrice", FormatDelegate:=formatPrice))
mapContent.Add(New ColumnMapping("ProductImage", "ProductImage"))

' Define the document object.
Dim doc As Object = Word.GenerateDocument(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) & "\Product Catalog.docx", Me.Products.SelectedItem, mapContent)
' Export the document object to Word.
Word.Export(doc, "Catalog", 2, False, Me.Products, mapContent)

{
    // Function to format a field as Currency.
    dynamic formatPrice = (decimal x) => { return Strings.Format(x, "c2"); };

    // Map the Word column names to the entity column names.
    List<ColumnMapping> mapContent = new List<ColumnMapping>();
    mapContent.Add(new ColumnMapping("ProductID", "ProductID"));
    mapContent.Add(new ColumnMapping("ProductName", "ProductName"));
    mapContent.Add(new ColumnMapping("Description", "Description"));
    // Format the price as Currency using the Function created above.
    mapContent.Add(new ColumnMapping("CurrentPrice", "CurrentPrice", FormatDelegate: formatPrice));
    mapContent.Add(new ColumnMapping("ProductImage", "ProductImage"));

    // Define the document object.
    object doc = Word.GenerateDocument(Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Product Catalog.docx", this.Products.SelectedItem, mapContent);
    // Export the document object to Word.
    Word.Export(doc, "Catalog", 2, false, this.Products, mapContent);
}

On the menu bar, choose Debug, Start Debugging to run the application.
On the Tasks menu, choose Product List, and then choose the Catalog button to view the report.

(Optional) Add the following line of code to the end of the Catalog_Execute method to save and view the report in a .pdf format:

Word.SaveAsPDF(doc, Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) & "\Product Catalog.pdf", True)

Word.SaveAsPDF(doc, Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments) + "\\Product Catalog.pdf", true);

Next Steps

Explore the APIs in the OfficeIntegration namespace to discover many more things that you can do by using the Office Integration Pack.