C H A P T E R 11
Creating and Displaying Reports
Reports are perhaps the most important part of any IT system. Reports are often seen by decision makers or by those in a position of seniority who carry influence within an organization. Being responsible for good reports can therefore be politically good for any IT professional wanting to progress in their career. At a lower level, reports are used to create items that are seen by other stakeholders. Examples could include packing notes, remittance advice, and invoices. These pieces of output provide a public face to a company and are important in terms of creating the right impression.
Given the importance of reporting, it seems a shame that this feature could not make it into version 1 of LightSwitch. It’s a particular surprise for those from an Access background, where reporting is firmly integrated into the product. Despite this limitation, there are still lots of methods for producing functional and good-looking reports in LightSwitch.
Microsoft SQL Server Reporting Services and Microsoft ASP.NET are two natural choices that developers can lean toward because both products belong inside the Microsoft technology stack. Both products share one thing in common: they are capable of producing output that can be accessed through a web address. This chapter shows you how to produce output by using both products and teaches you a common technique for hooking up your report with your LightSwitch application.
Programming Microsoft Word through COM automation is another option that can be used for out-of-browser applications. Finally, there are many third-party controls that you can purchase to produce reports. Table 11-1 summarizes the available options that are described in this chapter.
Using ASP.NET to Create Reports
A simple method of producing printable output is to create an ASP.NET web site that contains web pages populated with data. In this section, you’ll learn how to create a simple ASP.NET web project and some simple data-driven web pages. This will be a fairly basic overview of ASP.NET. If you want to learn more, the official ASP.NET web site provides a great resource.
When deployed onto a web server, the reports will be accessible through a URL that you’ll define during deployment. Later, you’ll learn how to create reports by using SQL Reporting Services, which also exposes its output through a web URL. The technique of linking the web pages together with screens is described later, in the “Linking Reports to LightSwitch” section.
Creating an ASP.NET Project
The first step is to create an ASP.NET web site. You can either create this as a stand-alone project or you can create a new project in an existing LightSwitch solution. Adding a web site into an existing solution requires Visual Studio 2010 Professional or above. If this is not available, you can create a stand-alone web site by downloading a free copy of Microsoft Visual Studio Web Developer Express from the official Microsoft web site.
To add a web site into an existing solution, you have to first switch into file mode (see Chapter 2 for details). In Solution Explorer, right-click the solution and choose File 
New Project ASP.NET Web Application to create a new web site. For the purposes of this demonstration, the new web site will be named ReportingWebSite.
Figure 11-1 shows how the new project appears in Solution Explorer. When you press F5 or run your solution, the new web site project will appear and is hosted by default by using the ASP.NET Development Server.
By default, dynamic ports are used, which means that the URL used to access web pages during development may change, particularly after Visual Studio is restarted. To simplify development, you can specify a particular port through the properties of your project (as shown in Figure 11-2). This results in a more predictable URL and eases the task of development.
Figure 11-1. The web site as it appears in Solution Explorer
Figure 11-2. Choosing a static port
Creating a New Page
After creating a web project, you can produce new pages to display the desired output. In the following example, a time-sheet report is created to display the time-sheet entries relating to a given person. The page, named TimesheetEntries.aspx, accepts a UserID argument. The important point about this example is that it demonstrates how arguments can be passed into web pages.
To add a new page, right-click the ReportingWebSite project and select the option to add a new item. Select the Web Form option and rename the file TimesheetEntries.aspx.
To connect to a SQL database, use the buttons toward the lower part of the screen to place your page into either Split or Design view, as shown in Figure 11-3. Next, go the Data section of your toolbox and drag a SqlDataSource object onto your web form.
Figure 11-3. Adding a SQL Server data source
With SqlDataSource selected, click the Configure Data Source context menu item. The Configure Data Source Wizard appears. You can click the New Connection button to connect to your database. If you want to connect to your intrinsic database by using the LightSwitch intrinsic ApplicationData.mdf file, you can use the same technique that was described in Chapter 2. Make sure to set the User Instance option to True if you want to do this.
After entering your connection details, the wizard prompts you to save the changes into the web.config file. Choosing to do this is better than keeping the credentials hard-coded because it simplifies the process of deployment and allows the details to be shared across multiple web pages.
Continue through the wizard, and when you reach the Configure the Select Statement page, you can select the option to Specify a Custom SQL Statement or Stored Procedure. The next page in the wizard includes a Query Builder button that allows you to graphically build a SELECT query based on the Timesheet table. A WHERE clause can be added to the end of the statement by using the syntax WHERE UserID=@UserID.
@UserID defines a placeholder parameter. The data source wizard detects that this is a parameter and allows you to define additional details in the following wizard page, as shown in Figure 11-4.
Figure 11-4. Defining the parameters that are used in a WHERE clause
In the Parameter Source drop-down box, select the QueryString option to enable the parameter to be passed through the URL. Finally, name the parameter by setting the QueryStringField text box to UserID.
Now that you’ve created a data source, you’ll need to add a control to display the contents of the query. The ASP.NET GridView control allows you to bind to a SqlDataSource and creates a tabular view of the data. To add a GridView control onto the page, drag an instance of a GridView into your form from the Data section of the toolbox.
As shown in Figure 11-5, the DataSource of the GridView can now be set to the SqlDataSource that you’ve created. You can use the Auto Format link in the same dialog box to format your grid in a more attractive fashion.
Figure 11-5. Setting the data source of the GridView
When you run your project, an instance of the ASP.NET Development Server is started. If you configured a static port earlier (shown in Figure 11-2), you’ll already know the port number. If not, don’t worry. Hovering the mouse over the icon in the notification area (shown in Figure 11-6) will show you the port number that is being used (for example, 1419).
Figure 11-6. The Open in Web Browser option
Finally, you can construct a URL by appending a UserID argument to the address and then view the page in your browser. The format of the URL will look something like this: http://localhost:1419/TimesheetEntry.aspx?UserID=8. Figure 11-7 shows what the final report looks like. Make a note of the URL, because you’ll need it to link your report with your LightSwitch application.
Creating Charts
ASP.NET 4 also contains a Chart control that you can use in your reports. All of the usual chart types are included, such as pie, area, range, point, circular, and accumulation. The following example shows you how to create a pie chart for displaying time-sheet hours, broken down by client code.
First, create a new web page and name it TimesheetHrsByProject.aspx. Just as in the earlier example, create a SqlDataSource (shown in Figure 11-8). When you reach the Configure the Select Statement page, select the option to Specify a Custom SQL Statement or Stored Procedure. In the following screen that appears, enter the SELECT statement shown in Listing 11-1.
Listing 11-1. T-SQL to Sum Time-Sheet Hours Grouped by Client
SELECT cl.ClientName , SUM(datediff(mi,StartTime,EndTime)) AS 'TotalMins'
FROM dbo.Timesheet ts JOIN dbo.Client cl
ON ts.ClientId = cl.ClientID
GROUP BY cl.ClientNameFrom the toolbox, drag a Chart control onto your form. Change the chart type to Pie, as shown in the Chart Tasks dialog box in Figure 11-8. Set the X Value Member and Y Value Members options as well.
Figure 11-8. Creating a pie chart
When you run the page, the pie chart appears, as shown in Figure 11-9.
Figure 11-9. ASP.NET page showing a pie chart
Securing Reports
By allowing users to connect to your database outside of LightSwitch, you could be exposing data that should not be seen by certain individuals. If both the LightSwitch application and ASP.NET web site are set up to use Windows authentication, you can deny access to particular users. Listing 11-2 shows the example code that might be used to deny access to the user Tim, or to anyone belonging in the Active Directory Warehouse group.
Listing 11-2. Securing Access to Reports When Windows Authentication Is Used
VB:
File: ReportingWebsiteTimesheetHrsByProject.aspx.vb
Protected Sub Page_Load(ByVal sender As Object,
ByVal e As System.EventArgs) Handles Me.Load
If User.Identity.Name = "DOMAINTim" Then
Throw New SecurityException("Access Denied to Tim")
End If
If User.IsInRole("DOMAINWarehouse")Then
Throw New SecurityException("Access Denied to users the Warehouse group")
End If
End Sub
C#:
File: ReportingWebsiteTimesheetHrsByProject.aspx.cs
protected void Page_Load(object sender, System.EventArgs e)
{
if (User.Identity.Name == "DOMAIN\Tim") {
throw new SecurityException("Access Denied to Tim");
}
if (User.IsInRole("DOMAIN\Warehouse")) {
throw new SecurityException("Access Denied to users the Warehouse group");
}
}If you’ve set up your LightSwitch application to use forms authentication, you can configure your ASP.NET application to share the same authentication database. This means that logging into your LightSwitch application will also log you into your ASP.NET application. Chapter 13 describes the steps required to configure this in both your ASP.NET and LightSwitch applications.
Listing 11-3 shows the equivalent code that is used if forms authentication is in place. To illustrate the use of the ASP.NET membership class, we’ve used the GetUser method to return the current logged-on user (we could have used User.Identity again). Afterward, the User.IsInRole method is used to check whether the user belongs in the LightSwitch group Warehouse rather than an Active Directory group.
Listing 11-3. Securing Access to Reports When Forms Authentication Is Used
VB:
File: ReportingWebsiteTimesheetHrsByProject.aspx.vb
Protected Sub Page_Load(ByVal sender As Object,
ByVal e As System.EventArgs) Handles Me.Load
If Membership.GetUser().UserName = "Tim" Then
Throw New Exception("Access Denied to Tim")
End If
If User.IsInRole("Warehouse")Then
Throw New Exception("Access Denied to users the Warehouse group")
End If
End Sub
C#:
File: ReportingWebsiteTimesheetHrsByProject.aspx.cs
protected void Page_Load(object sender, System.EventArgs e)
{
if (User.Identity.Name == "Tim") {
throw new Exception("Access Denied to Tim");
}
if (User.IsInRole("Warehouse")) {
throw new Exception("Access Denied to users the Warehouse group");
}
}Instead of imperatively specifying the permissions in code on each page, another option for securing permissions is to specify the permissions declaratively in your web.config file. Listing 11-4 shows the snippet of code that would be added to the web.config file.
Listing 11-4. Securing Access via the web.config File
<system.web>
<authorization>
<deny role="Warehouse"/>
<deny users="Tim"/>
<deny users="?"/>
<allow users="*"/>
< /authorization>
…..ASP.NET iterates through the entries in the authorization element and applies the first matching rule. In this example, users in the Warehouse role and the Tim user will be denied access. Next, all unauthenticated users will be denied access (indicated by the ? entry). Finally, all remaining users (indicated by the * entry) will reach the allow rule and will subsequently be granted access.
Using Microsoft Reporting Services
Microsoft SQL Server Reporting Services is a natural choice for creating reports because it is a product developed and supported by Microsoft. It allows more-powerful reports to be created beyond what you can achieve using simple ASP.NET pages. Other features in Reporting Services include subscription notifications and the ability to export data in formats such as Microsoft Word and Adobe PDF.
In this section, a Reporting Services report will be created in the ShipperCentral system. The report shows the number of subscription cancellations each month.
Note When deploying your Reporting Services solution into IIS (Internet Information Services), the Reporting Services Redistributable package must be installed on the server. This can be downloaded from the following URL: www.microsoft.com/downloads/en/details.aspx?FamilyID=a941c6b2-64dd-4d03-9ca7-4017a0d164fd&displaylang=en.
Installing Reporting Services
SQL Server Reporting Services comes as part of SQL Server. Although it’s not included in the basic SQL Server Express version, it is available for free in the SQL Server Express with Advanced Services version. By default, SQL Server Express is installed during the LightSwitch installation. This basic instance can be upgraded to the Advanced Services version by installing the setup package that you can download from the official Microsoft SQL Server web site.
Creating Reports
Reporting Services reports are created, designed, and edited by using the Business Intelligence Development Studio (or BIDS, for short). When installing or upgrading SQL Server, an option will be available to install this component.
Reports are defined and saved in Reporting Definition Language (RDL) files. After starting BIDS, create a new Reporting Services project. Right-click the project menu and choose the Add New Item option. From here, you can either create a blank report or use a wizard. Because this is our first report, we will choose to go with the wizard. After specifying a name for your report, the first step in the wizard prompts you to select a data source. Similar to the ASP.NET example that was described earlier you can specify a connection to your intrinsic database if you choose to do so. The next step in the wizard prompts you to enter a query. In the Query String text box that appears, enter a query, as shown in Listing 11-5. The SQL shown here generates a count of cancellations, grouped by month and year.
Listing 11-5. SQL to Return Cancellation Counts Grouped by Date
SELECT
MONTH(cancelDate) AS 'Month',
YEAR(cancelDate) AS 'Year' ,
COUNT(SubscriptionHeaderId) AS 'CancellationCount'
FROM dbo.SubscriptionHeader
GROUP BY MONTH(cancelDate), YEAR(cancelDate)The next dialog box prompts you to select a report type. The available options include tabular, matrix, and tablix. We will create a simple tabular report . In the grouping section that appears, add Month, Year, and CancellationCount into the details section of the report. Complete the remaining steps of the wizard. At the end of the wizard, you can choose to preview the report.
Using the Report Designer
In this section, you’ll take a look at the report that has been created in the designer and some of the features that are available. Along the top of the report designer are three tabs titled Design, Preview, and possibly Data (depending on the version of BIDS that you’ve installed). If you can’t see the Data tab, you can open the Report Data window by choosing View Report Data from the top-level menu. This then appears in a tool window, as shown in Figure 11-10. Figure 11-10 also highlights some of the features that are available in the designer. In design mode, the toolbox appears on the left side and allows you to add additional components onto your report such as text boxes, lines, and subreports.
Figure 11-10. The BIDS report design surface
Data Tab or Data Window
The Data tab allows you to select the data sources that are available in your report. The data that you bind to are stored in DataSets, although these are not the same as the ADO.NET DataSet objects that .NET developers might be familiar with.
If you look at the data window (or Data tab), you’ll find a DataSet that contains the cancellation data shown in the body of the report. Additional DataSets can be added here if extra data needs to be shown on the report. For example, if you want to add a drop-down box that allows your report to be filtered, you’d add an additional DataSet here to populate the drop-down box.
Design Tab
Clicking the Design tab takes you into the designer, and this allows you to edit and design the report.
Looking at the cancellation report that we’ve designed (shown earlier in Figure 11-10), the body of the report contains a table. A table is a control that allows data to be shown in a tabular row and column format.
A table is an example of a data region. Other data regions include the list, matrix, and tablix controls. The list control displays individual data items by using text boxes. This provides more flexibility in terms of layout compared to tables. You can position the text boxes anywhere inside the row section, which is therefore much less rigid compared to the layout imposed by the table control.
The matrix control displays data grouped by row and column. This allows you to produce reports that are similar to cross tabs and pivot tables.
Writing Code
Although you can write simple reports without code, Reporting Services provides two options for writing custom code. Custom code can greatly assist with operations such as setting styles and formatting.
Option 1 is to write a .NET assembly and to reference it from the report. This option is ideal if you want to write code that is shared across multiple reports. It’s also possible to reference classes from the .NET Framework, such as those from the System.Text namespace.
The second and simpler option is to embed the code into the report. If you view the properties of a report, a code section allows you to enter your own custom methods and functions.
A powerful technique is to set properties in your report by using expressions. For example, alternate row coloring can be applied in a report by entering the expression IIf(RowNumber(Nothing) Mod 2 = 0, "Silver", "Transparent") into the BackgroundColor property, as shown in Figure 11-11.
Figure 11-11. Setting property values by using expressions
In the properties pane, the default visualizer for BackgroundColor is a color picker. It might not be obvious that you can enter text expressions into this text box. This same behavior applies for many of the other properties that you see.
The example expression also illustrates the use of the IIf (conditional If) function. The first parameter accepts a test, the second parameter accepts the true condition, and the final parameter accepts the false condition. Combining expressions with the IIf function provides a powerful technique for authoring reports.
Drill-Through Reports
Drill-through reports allow a user to view additional details or other related data by clicking a link on the main report. For example, the cancellation report that was created earlier can be expanded to show a customer report when a link on a cancellation row is clicked.
Deploying Reports
Reporting Services uses a web-based report server. Any reports that you create will be finally deployed here. After deployment, the reports can be viewed through a URL on the server. You can directly deploy reports through an option in BIDs. Alternatively, you can use the web-based Report Manager to upload your RDL files.
If a report server is unavailable, the RDL report can be hosted through an ASP.NET page that contains a ReportViewer control. Using this method, the ReportViewer control can render the report by using local processing. This processing mode removes the dependency on the Report Server, and all of the report processing will be done by the control.
Deploying a Report from BIDS
The easiest way for you to deploy a report is to use the deploy option that is built into BIDS. Before you can deploy your reports, you’ll need to configure some of the deployment options in your project. Right-click your project in Solution Explorer and open the property pane for your project, as shown in Figure 11-12.
Figure 11-12. The properties of a Reporting Services project
You’ll need to set up the following items:
TargetReportFolder: Enter the folder on the Report Server where you want to publish your reports.TargetDataSourceFolder: If you leave this blank, the data sources will be saved in theTargetReportFolderspecified in the preceding option.TargerServerURL: Enter the URL of your report server. Before you publish a report, this must be set to a valid report server URL. Type in the path to the virtual directory of your report server (for example,http://server/reportserverorhttps://server/reportserver), rather than the URL of the Report Manager.
After this is done, you can deploy your reports by using the right-click option in Solution Explorer.
Importing a Report from Report Manager
Another way to deploy a report is to import your RDL file by using Report Manager, shown in Figure 11-13. This option is ideal if you’re unable to deploy from BIDS—for example, if you have developed a report on a computer that isn’t connected to the same domain or network as the target report server.
To use this method, open a web browser and navigate to the Report Manager URL (for example, http://server/reportmanager). Navigate to the folder where you want to deploy your report. If you want to, you could create a new folder for your report.
Now click the Import link and upload your RDL file. After you’ve uploaded your report, you’ll need to configure the data source by using the data option in Report Manager.
Figure 11-13. The Report Manager interface
Using the ReportViewer Control
If you don’t have access to a report server, you can use a report viewer control instead. You’d add this control onto an ASP.NET web page. The processing mode on the control has to be set to local processing to remove the dependency on a report server.
The report viewer control is designed to render RDLC (Report Definition Language Client-side) files. These are cut-down versions of RDL files. You can create a new RDLC report in your ASP.NET project by using the File New menu option. If you’ve created an RDL report in BIDS and want to display it by using the report viewer control, you’ll need to convert it into RDLC format before you can use it. Fortunately, this is quite an easy task. You can simply rename your RDL file with an .rdlc extension. If you now return to your ASP.NET project, you can import your report by choosing the Add Existing Item option from the project menu.
To populate your report with data, you’ll need to create a new data source. On the web page that you want to display your report, drag a ReportViewer control from the data group in the toolbox. In the ReportViewer Tasks smart tags panel, select your RDLC file by using the Choose Report drop-down list, as shown in Figure 11-14. Next, select your data set by using the Choose Data Sources drop-down list. This completes the design of your web page.
Figure 11-14. Adding the report viewer control onto a web page
REAL-LIFE LIGHTSWITCH
Linking Reports to LightSwitch
The earlier sections have shown you how to create reports by using ASP.NET or Reporting Services. Having created these web-based reports, the remaining step is to link them to LightSwitch. In this section, you’ll learn how to do the following:
- Open a web report in a new browser window
- Display a web report inside a LightSwitch screen
Opening Reports in a New Browser Window
There are two methods for opening reports in a new browser window. The first technique opens a report by using a button on a screen. The second technique uses a hyperlink, and is particularly suitable for screens that contain grids.
Technique 1: Using a Button
The necessary code for opening a report in a browser differs based on whether you’re running in a browser or out of a browser.
If you’re writing desktop applications, you can use COM automation to shell an instance of your browser. In a browser application, you use the Silverlight HtmlPage class instead. In order to use the HtmlPage class, you have to add a reference to the System.Windows.Browser DLL in your client project. To do this, switch to File view, right-click the client project, and select the Add Reference option.
We’ll now show you how to carry out this technique on a details screen, based on a user entity. This screen will contain a button that opens a time-sheet report for the selected user in a new browser window.
After you’ve added a reference to System.Windows.Browser, you’ll need to add a helper class that launches the report. Switch to File view and add the VB ReportHelper module or C# ReportHelper class, as shown in Listing 11-6. We recommend adding this code into the UserCode folder (if it exists).
Now create a details screen based on the user entity. The steps that you’ll need to carry out in the screen designer are as follows:
- Click the Add Data Item button. When the Add Data Item dialog box appears, create a new method and call it
ShowReport. - After adding the method, it’ll appear in the screen member section of the screen designer. Create a button by dragging this method into the command bar area of your screen.
- Right-click the
ShowReportmethod in the screen member section and select the Edit Execute Code option. Add the code shown in Listing 11-7.
If you now run your application and open this screen, clicking the ShowReport button opens the time-sheet report in a new browser window.
Listing 11-6. The ReportHelper Class
VB:
File: OfficeCentralClientUserCodeReportHelper.vb
Imports System.Runtime.InteropServices.Automation
Imports System.Windows.Browser
Namespace Central.Utilities
Public Module ReportHelper
Const TimeSheetReportUrlFormat As String =
"http://localhost:1419/TimeSheetEntry.aspx?UserId={0}"
Public Sub LaunchUrl(ByVal userId As String)
Dim urlPath = string.Format(TimeSheetReportUrlFormat, UserID)
If AutomationFactory.IsAvailable Then
Dim shell = AutomationFactory.CreateObject("Shell.Application")
shell.ShellExecute(urlPath, "", "", "open", 1)
Else
HtmlPage.Window.Invoke(urlPath)
End If
End Sub
End Module
End Namespace
C#:
File: OfficeCentralClientUserCodeReportHelper.cs
using System.Runtime.InteropServices.Automation;
using System.Windows.Browser;
namespace Central.Utilities
{
public static class ReportHelper
{
const string urlPath = string.Format(TimeSheetReportUrlFormat, UserID);
public static void LaunchUrl (string userId)
{
if (AutomationFactory.IsAvailable)
{
var shell = AutomationFactory.CreateObject("Shell.Application");
shell.ShellExecute(urlPath, "", "", "open", 1);
}
else
{
HtmlPage.Window.Invoke(urlPath);
}
}
}
}Listing 11-7. The ShowReport Method Code for Opening a Report
VB:
Private Sub ShowReport_Execute()
ReportHelper.LaunchUrl(User.UserID.ToString())
End Sub
C#:
partial void ShowReport_Execute()
{
ReportHelper.LaunchUrl(User.UserID.ToString());
}The code in the helper method stores the URL to the time-sheet report in a constant. To improve the code, you might want to consider storing this URL path in a configurable fashion to avoid hard-coding it.
The LaunchURL method performs a check to see whether you’re running out of browser. If so, the method uses the ShellExecute command to open the URL in the default browser. If not, the HtmlPage object is used instead.
Technique 2: Using a Hyperlink
In this example, a Silverlight hyperlink is used to open the same ASP.NET time-sheet entry report. This HyperlinkButton control is used for displaying hyperlinks that users can use to navigate to other web pages.
One of the beauties of using a hyperlink is that you can easily control the text that is shown on the link. For example, you can data bind the text shown to some other data item on your screen. You might also choose to use a hyperlink as a matter of personal preference (for example, you might prefer to use hyperlinks over buttons in your application).
First, we’ll apply this technique on a details screen in the same way as the earlier button example. We’ll then extend the example so that it works in a grid.
Using a Hyperlink on a Details Screen
In this illustration, we’ll create a details screen based on a user entity. We’ll create a hyperlink that displays the surname of the user. Clicking this hyperlink opens the time-sheet report.
After you’ve created a details screen for your user entity, change the control type for the Surname field from a text box to a custom control, as shown in Figure 11-15. Set the Custom Control type to be of type System.Windows.Controls.HyperlinkButton.
Figure 11-15. Creating a HyperlinkButton control on a screen
Now enter the code shown in Listing 11-8.
Listing 11-8. Hyperlink Control to Open Report
VB:
File: OfficeCentralClientUserCodeUserDetailHyperlinkReport.vb
Private Sub User_Loaded(succeeded As Boolean)
Me.SetDisplayNameFromEntity(Me.User)
Dim surnameControl = Me.FindControl("Surname")
AddHandler surnameControl.ControlAvailable, AddressOf Me.OnSurnameAvailable
End Sub
Private Sub OnSurnameAvailable(sender As Object, e As ControlAvailableEventArgs)
Dim url = "http://localhost:1419/TimesheetEntry.aspx?UserID=" & User.UserID.ToString()
Dim control = DirectCast(e.Control, HyperlinkButton)
control.NavigateUri = New Uri(url)
control.Content = Me.User.Surname
End Sub
C#:
File: OfficeCentralClientUserCodeUserDetailHyperlinkReport.cs
private void User_Loaded(bool succeeded)
{
this.SetDisplayNameFromEntity(this.User);
var surnameControl = this.FindControl("Surname");
surnameControl.ControlAvailable += this.OnSurnameAvailable;
}
partial void OnSurnameAvailable(object sender, ControlAvailableEventArgs e)
{
var url = "http://localhost:1419/TimesheetEntry.aspx?UserID=" + User.UserID.ToString();
var control = (HyperlinkButton)e.Control;
control.NavigateUri = new Uri(url);
control.Content = this.User.Surname;
}In this code sample, we’ve obtained a reference to the surname control by using the FindControl method. As you saw in Chapter 8, the FindControl method returns an IContentItemProxy object. Because you might have added multiple surname controls onto your screen, you can work out the correct name to use by referring to the Name text box in the properties pane for the control.
When the control becomes available, the NavigateUri and Content properties are set in code. The NavigateUri property indicates the web address that the browser navigates to when the user clicks the link. The web address is constructed in code, and the UserID argument is appended by referencing the LightSwitch user property that we’ve added to the screen.
The Content property specifies the text that is shown on the hyperlink. The surname text is assigned to this property. When the screen is run, a surname hyperlink appears, which opens the time-sheet report when clicked.
Using a Hyperlink on a Grid Screen
We’ll now extend the HyperlinkButton example so that it works on a grid of users. To begin, create a screen based on the Users table by using the Editable Grid Screen template. Using the same method as shown in the preceding example, change the Surname control from a text box to a custom control of type HyperLinkButton. Use the Add Data Item dialog to add a string property called blankTarget. Now enter the code shown in Listing 11-9.
Listing 11-9. Hyperlinkbutton in Grid
VB:
File: OfficeCentralClientUserCodeEditableUsersReportGrid.vb
Imports System.Windows.Data
Imports System.Windows.Controls
…..
Public Partial Class EditableUsersGrid
Private Partial Sub EditableUsersGrid_Activated()
Dim control = Me.FindControl("Surname")
blankTarget = "_blank"
Dim converter As New String2UriConverter()
control.SetBinding(HyperlinkButton.ContentProperty,
"Value", BindingMode.OneWay)
control.SetBinding(HyperlinkButton.NavigateUriProperty,
"Details.Entity.UserID", converter, BindingMode.OneWay)
control.SetBinding(HyperlinkButton.TargetNameProperty,
"Screen.blankTarget ", BindingMode.OneWay)
End Sub
End Class
Public Class String2UriConverter
Implements IValueConverter
Public Function Convert(value As Object, targetType As Type,
parameter As Object, culture As System.Globalization.CultureInfo) As Object
If Not value Is Nothing Then
Return New Uri("http://localhost:1419/TimesheetEntry.aspx?UserID=" &
Convert.ToString(value))
Else
Return New Uri("")
End If
End Function
Public Function ConvertBack(value As Object, targetType As Type,
parameter As Object, culture As System.Globalization.CultureInfo) As Object
Throw New NotImplementedException
End Function
End Class
C#:
File: OfficeCentralClientUserCodeEditableUsersReportGrid.cs
using System.Windows.Data;
using System.Windows.Controls;
…..
public partial class EditableUsersGrid
{
partial void EditableUsersGrid_Activated()
{
var control = this.FindControl("Surname");
blankTarget = "_blank";
String2UriConverter converter = new String2UriConverter();
control.SetBinding(HyperlinkButton.ContentProperty,
"Value", BindingMode.OneWay);
control.SetBinding(HyperlinkButton.NavigateUriProperty,
"Details.Entity.UserID", converter, BindingMode.OneWay);
control.SetBinding(HyperlinkButton.TargetNameProperty ,
"Screen.blankTarget ", BindingMode.OneWay);
}
}
public class String2UriConverter : IValueConverter
{
public object Convert(object value, Type targetType, object parameter,
System.Globalization.CultureInfo culture)
{
if (value != null)
{
return new Uri(@"http://localhost:1419/TimesheetEntry.aspx?UserID=" + value.ToString());
}
else
{
return new Uri(@"");
}
}
public object ConvertBack(object value, Type targetType, object parameter,
System.Globalization.CultureInfo culture)
{
throw new NotImplementedException();
}
}In the previous example, the NavigateUri and Content properties were set directly in code by using the ControlAvailable method. You can’t use this technique on a grid. This is because the grid contains multiple surname controls, one for each row in the grid. Therefore, you have to set the NavigateUri and Content properties on the HyperLinkButton by using the SetBinding method instead.
The NavigateUri dependency property expects to be bound to an object of type URI. However, the web address that we’ve constructed is of type string. A value converter is therefore needed to convert the string URL representation into an object of type URI. The convert method in the String2UriConverter class generates the URL to the time-sheet web page and appends the UserID argument. Because we’re binding one way and don’t need to update the surname field, it‘s not necessary for us to implement the ConvertBack method. (Value converters were described in Chapter 9.)
In the Activated method of the screen, the SetBinding method is called three times to bind ContentProperty, NavigateUriProperty, and TargetNameProperty. Table 11-2 describes in more detail the binding paths that are used.
Figure 11-16 shows the final result of this screen.
Figure 11-16. Clicking a link in the grid opens the report in a new window.
Displaying Reports Inside a LightSwitch Screen
Rather than opening pages in a new browser window, you might prefer to display the reports inside your LightSwitch screen. If you’re writing a desktop application, you can use the Silverlight WebBrowser control to display web pages inside a LightSwitch screen. By setting the URL of this control to the URL of your report, the contents can be displayed without having to open other windows. This technique works only in desktop applications, because the WebBrowser control is not supported in LightSwitch browser applications.
We’ll demonstrate this technique by using the time-sheet report shown earlier. We’ll show you how to use this control on a screen that displays a single user, and a screen that shows a list of users.
Caution The WebBrowser control lives in a different windowing plane than Silverlight. Odd things can therefore happen when your resize or scroll your screens. The control also appears on top of all other controls on your screen. It won’t honor any z-order values that you might try to apply. If you place an AutoCompleteBox control above the WebBrowser control, for example, the drop-down contents will appear behind the WebBrowser control. There isn’t any easy way to fix this behavior. We recommend that you thoroughly test any screens that use the WebBrowser control.
Showing Reports in a Details Screen
First, create a screen based on the user table by using the Details Screen template.
In the screen designer, add a second instance of the UserID property. Change this control from a text box to a custom control, and set the control type to a System.Windows.Controls.WebBrowserControl control (refer to Chapter 9 for more details). Now enter the code shown in Listing 11-10. When you run the application, the screen appears as shown in Figure 11-17.
Listing 11-10. Showing a Web Page on a LightSwitch Details Screen
VB:
File: OfficeCentralClientUserCodeUserDetailReportInPage.vb
Imports System.Windows.Controls
Private Sub UserDetailReportInPage_Loaded(succeeded As Boolean)
Me.SetDisplayNameFromEntity(Me.User)
Dim control As = Me.FindControl("UserID1")
AddHandler control.ControlAvailable, AddressOf Me.webControlAvailable
End Sub
Private Sub webControlAvailable(sender As Object, e As ControlAvailableEventArgs)
DirectCast(e.Control, WebBrowser).Navigate(
New Uri("http://localhost:1419/timesheetentries.aspx?userid=" & User.UserID.ToString()))
End Sub
C#:
File: OfficeCentralClientUserCodeUserDetailReportInPage.cs
Using System.Windows.Controls;
partial void User_Loaded(bool succeeded)
{
this.SetDisplayNameFromEntity(this.User);
var control = this.FindControl("UserID1");
control.ControlAvailable += this.webControlAvailable;
}
private void webControlAvailable (object sender, ControlAvailableEventArgs e)
{
((WebBrowser)e.Control).Navigate (
New Uri(@"http://localhost:1419/timesheetentries.aspx?userid=" +
User.UserID.ToString()));
}
Figure 11-17. Report shown inside a LightSwitch screen
Tip If you’re displaying web pages that you’ve created yourself by using the WebBrowser control, you can embed your own JavaScript into these pages. For example, you could add an HTML link onto your report that calls the JavaScript Window.Print() method. This would open the standard print dialog box and prompt the user to send the WebBrowser content to the printer. A useful C#/VB method that you can use is the WebBrowser.InvokeScript method. This allows you to call JavaScript methods on your web page from your C# or VB code.
Showing Reports in a List Screen
In this section, we’ll use this technique on a list- or grid-type screen. In this example, we’ll bind the contents of the WebBrowser control to the item that’s selected on the list or grid. As we change the selected record, the page shown in the WebBrowser control automatically updates itself.
In the earlier example that uses a details screen, we’ve navigated to our web page by using code in the ControlAvailable method. The ControlAvailable event happens only once during the life cycle of a screen. Therefore, we can’t use this technique to navigate to a different page when the selected record changes. We have to use the SetBinding method instead.
However, a limitation of the WebBrowser control is that the source property isn’t exposed through a dependency property. This makes it impossible to for us to data bind to a source URL. To work around this limitation, we created our own custom web control in Chapter 9. This control includes a dependency property called URIProperty that you can use to set the source URL.
To create this example, you’ll need to build the custom WebBrowserControl (as shown in Chapter 9). We’ve named this control Central.Controls.WebBrowserControl, and have built it into a DLL called CentralControls.dll.
To build this example, add a reference to the CentralControls.dll file that you’ve built. Now create a screen based on the user table by using the List and Details Screen template.
In the screen designer, add a second instance of the UserID property in the details section. By default, LightSwitch will name this UserID1. Change this control from a text box to a custom control, and set the control type to a Central.Controls.WebBrowserControl control. Now enter the code shown in Listing 11-11. When you run the application, the screen appears as shown in Figure 11-18.
Listing 11-11. Showing a Web Page on a LightSwitch List and Details Screen
VB:
File: OfficeCentralClientUserCodeUserListReportInPage.vb
Imports System.Windows.Controls
Private Sub UserListReportInPage_Activated()
Dim control As = Me.FindControl("UserID1")
Dim converter As New String2UriConverter()
control.SetBinding(
Central.Controls.WebBrowserControl.URIProperty,
"Value", converter, BindingMode.OneWay)
End Sub
C#:
File: OfficeCentralClientUserCodeUserListReportInPage.cs
Using System.Windows.Controls;
partial void UserListReportInPage_Activated()
{
String2UriConverter converter = new String2UriConverter();
var control = this.FindControl("UserID1");
control.SetBinding(Central.Controls.WebBrowserControl.URIProperty ,
"Value", converter, BindingMode.OneWay);
}
Figure 11-18. Report shown inside a screen based on the List and Details Screen template
Creating Reports with Microsoft Word
If you’re writing an out-of-browser application, you can create reports by programming Microsoft Word using COM automation. A prerequisite is that you must install Microsoft Word on the end-user computer. In this section, you’ll learn how to create simple reports based on a single record, and also how to create more-complex reports based on a collection of data. This is ideal for carrying out procedures such as mail merges.
Tip Microsoft Excel is also a great tool for creating charts and reports. You can adapt the COM automation techniques that are described here to automate Excel rather than Word.
Performing Simple Automation
In the first example, we’ll show you how to use Word automation to create a simple letter. We’ll write code in a customer screen that opens an existing Microsoft Word template. Next, we’ll retrieve the customer details from the LightSwitch screen and insert the contents into bookmarks that we’ve added to the Word template.
This method of automating Word is well established, and you might already be familiar with this technique. Unlike some other methods that rely on generating XML, for example, there’s no requirement to have a modern version of Word.
Creating a Word Template
To create a Word template, first open Microsoft Word and type the body of your letter. In our example letter, the top section contains the recipient’s address and name. These bits of data will be retrieved from the LightSwitch screen, and Word bookmarks will be added into these locations to allow a data substitution to take place.
To insert a bookmark, click the Insert menu and choose Bookmark, as shown in Figure 11-19. In the Bookmark dialog box that appears, enter a bookmark name and add the bookmark. For the purposes of this example, we’ve created a bookmark called firstname.
Figure 11-19. Inserting a bookmark in a template
Notice that the bookmarks are identified by an I symbol. By default, bookmarks are hidden in Word. However, you can make them visible by selecting the Show Bookmarks check box in the Word Options dialog box (shown in Figure 11-20).
After you’ve created your template, save the file as LetterTemplate.dot.
Figure 11-20. Enabling the option to show bookmarks in word.
Writing the Code
Having created your Word template, you can write code to show the results on screen, or you can send the results direct to a printer. We’ll now describe both techniques.
Displaying Documents Onscreen
In your LightSwitch application, create a screen based on the Customer table by using the Details Screen template. Create a method called DoWordExport, add a button onto the screen, and enter the code shown in Listing 11-12. You’ll need to add a reference to the System.Runtime.InteropServices.Automation namespace by using the imports or using statement.
Listing 11-12. Microsoft Word Automation Code
VB:
File: ShipperCentralClientUserCodeCustomerDetailWord.vb
Private Sub DoWordExport_Execute()
If AutomationFactory.IsAvailable Then
Try
Using wordApp = AutomationFactory.CreateObject("Word.Application")
Dim wordDoc = wordApp.Documents.Open(
"\FileServerTemplatesLetterTemplate.dot")
wordDoc.Bookmarks("firstname").Range.InsertAfter(
CustomerProperty.Firstname)
wordApp.Visible = True
End Using
Catch ex As Exception
Throw New InvalidOperationException("Failed to create customer letter.", ex)
End Try
End If
End Sub
C#:
File: ShipperCentralClientUserCodeCustomerDetailWord.cs
partial void DoWordExport_Execute()
{
if (AutomationFactory.IsAvailable) {
try {
using (wordApp == AutomationFactory.CreateObject("Word.Application")) {
dynamic wordDoc = wordApp.Documents.Open(
@"\FileServerTemplatesLetterTemplate.dot");
wordDoc.Bookmarks("firstname").Range.InsertAfter(
CustomerProperty.Firstname);
wordApp.Visible = true;
}
}
catch (Exception ex) {
throw new InvalidOperationException("Failed to create customer letter.", ex);
}
}
}In Listing 11-12, we’ve created a COM reference to Word by calling the CreateObject method on the AutomationFactory class. The COM object is instantiated by supplying the string argument Application.Word (known as a ProgID). If you want to automate Excel instead, for example, you’d replace this with the string Application.Excel.
Creating a COM object by passing in a ProgID string illustrates the use of late binding in COM. By using late binding, there are no dependencies on any particular version of Word. As long as any version of Word is installed on the client computer, this code will work. A major disadvantage of this technique is that errors will not be caught at compile time, and IntelliSense is also not available in the code designer.
After carrying out the standard checks to make sure that the application is a desktop application, the Word template document is opened by calling the Documents.Open method (in the mail merge section, we’ll show you how to disable the button if you’re running inside a browser). In this call, a hard-coded path to the Word template document is supplied. To allow multiple users to access the Word template file, the location is specified in UNC (Universal Naming Convention) format (\FileServerTemplatesLetterTemplate.dot ). In practice, you’ll want to save this file path in a configuration setting that you can modify afterward, rather than hard-coding it.
The data is then inserted into the document at a point immediately after the firstname bookmark by using the Range.InsertAfter method. CustomerProperty.Firstname refers to the LightSwitch Customer property on the screen. After generating the Word document, it is shown to the user by setting the visibility property of the Word application to True.
Sending Documents Directly to a Printer
Instead of showing the document to the user on the screen, you could send the Word document to a printer and discard it immediately afterward without saving changes. This would allow you to easily send reports to a printer without any additional user intervention.
Listing 11-13 shows the code that you would use to do this. The Close method expects you to pass in a SaveChanges argument. This argument forces Word to discard changes, to save changes, or to prompt the user to save their changes. We want Word to quit without saving changes, and this is done by passing 0 into the Close method.
Listing 11-13. Printing and Closing a Word Document
VB:
File: ShipperCentralClientUserCodeCustomerDetailWord.vb
wordApp.PrintOut()
wordDoc.Close(0)
C#:
File: ShipperCentralClientUserCodeCustomerDetailWord.cs
wordApp.PrintOut();
wordDoc.Close(0);Distributing the Template with the LightSwitch Client
The preceding automation example relies on a Word template file being available via a UNC file path (alternatively, you could have chosen to use a mapped drive). The biggest disadvantages of this technique are that it works only on the internal network, and it won’t work in environments where Windows file sharing is disallowed.
You can overcome this limitation by embedding your Word template in the XAP file. There are several other advantages and disadvantages of using this technique. These are summarized in Table 11-3.
To include the LetterTemplate.dot Word template in your XAP file, switch to File view and right-click the ClientGenerated project. Choose the Add Existing Item option, select the document, and set the Build Action property to Content, as shown in Figure 11-21.
Figure 11-21. Setting the Build Action to Content
Listing 11-14 illustrates the code that extracts the file from the XAP package and saves it into the My Documents folder on the client computer. The template is saved into this location because security restrictions in Silverlight limit the places where you can save files on the local file system.
Listing 11-14. Saving the Word Template to My DocumentsLetterTemplate.dot
VB:
File: ShipperCentralClientUserCodeCustomerDetailWord.vb
Dim resourceInfo = System.Windows.Application.GetResourceStream(
New Uri("LetterTemplate.dot", UriKind.Relative))
Dim path = Environment.GetFolderPath(
Environment.SpecialFolder.MyDocuments) + "LetterTemplate.dot"
Dim file = System.IO.File.Create(path)
file.Close()
'Write the stream to the file
Dim stream As System.IO.Stream = resourceInfo.Stream
Using fileStream = System.IO.File.Open(path,
System.IO.FileMode.OpenOrCreate,
System.IO.FileAccess.Write,
System.IO.FileShare.None)
Dim buffer(0 To stream.Length - 1) As Byte
stream.Read(buffer, 0, stream.Length)
fileStream.Write(buffer, 0, buffer.Length)
End Using
C#:
File: ShipperCentralClientUserCodeCustomerDetailWord.cs
var resourceInfo = System.Windows.Application.GetResourceStream(new
Uri("LetterTemplate.dot", UriKind.Relative));
dynamic path = Environment.GetFolderPath(
Environment.SpecialFolder.MyDocuments) + "LetterTemplate.dot";
dynamic file = System.IO.File.Create(path);
file.Close();
//Write the stream to the file
System.IO.Stream stream = resourceInfo.Stream;
using (fileStream == System.IO.File.Open
(path, System.IO.FileMode.OpenOrCreate, System.IO.FileAccess.Write, System.IO.FileShare.None)) {
byte[] buffer = new byte[stream.Length];
stream.Read(buffer, 0, stream.Length);
fileStream.Write(buffer, 0, buffer.Length);
}This code uses the GetResourceStream method. This allows you to load resources that are embedded inside your application package. The URI identifies the resource to be loaded. This path is relative and doesn’t require a leading slash.
After obtaining a resource stream, the Word template is extracted into the My Documents folder by using the methods in the System.IO.File namespace. In practice, you’ll most likely want to save the template into a more specific location, and to perform some additional error checking to check whether the file exists.
Performing Mail Merges with Word
Another common scenario in many business applications is the ability to perform mail merges. In our example scenario, the marketing department wants to run a marketing campaign to encourage lapsed subscribers to resubscribe. The department uses the documents generated from a Word mail merge as a basis for a postal marketing campaign.
Creating a Mail Merge Word Template
The first step is to create a letter template in Word. This example uses Word 2010. The necessary steps might be slightly different if you have an earlier version of Word.
From the Word ribbon, click the Mailings tab, click the Start Mail Merge button, and select the Letters option. Next, click the Select Recipients button and choose the Type New List option. The New Address List window appears, as shown in Figure 11-22.
Figure 11-22. Creating the mail merge fields
The New Address List window displays the mail merge fields as columns in the grid. As you can see, some of the default mail merge fields include Title, FirstName, and LastName. You can add fields by clicking the Customize Columns button. Clicking the OK button prompts you to save the list as a new MDB file. Name this file MailMergeData.mdb and after saving, the Insert Merge Field buttons will become enabled on the ribbon. Although you’ll no longer need to refer to this MDB file, these steps are required in order to create the mail merge fields. Save the file as MailMergeTemplate.docx.
Writing the Code
You can use the letter template that you’ve created as the basis for your mail merge. But if you’re not sure what mail merge fields you’ll need, or if you want to create a mail merge that’s not based on a static template, you can create your mail merge fields in code instead. We’ll explain both techniques in this section
Using the mail merge fields that are specified in the Word Template
We’ll start by showing you how to carry out a mail merge by using the data from a grid screen. We’ll create a button that mail merges the screen data with the Word template that was created earlier.
First, create a screen for the Customer table based on the Editable Grid Screen template. Next, create a method called DoMailMerge. By default, the customer collection shows only 45 records at time. Therefore, you might want to increase this by setting the No. of Items to Display per Page text box. If you want to mail merge all of the records in a table, you can adapt your code so that it uses a query rather than a screen collection.
Now enter the code as shown in Listing 11-15. Finally, create a button on your screen by dragging the DoMailMerge method onto the command bar area of your screen.
Listing 11-15. Mail Merge Code
VB:
File: ShipperCentralClientUserCodeCustomersWordMerge.vb
Imports System.Runtime.InteropServices.Automation
Imports System.Reflection
Namespace LightSwitchApplication
Partial Public Class CustomersWordMerge
Private wordApp As Object
Private wordDoc As Object
Private missingValue As Object = System.Reflection.Missing.Value
' Here are the values of the WdMailMergeDestination Enum
Const wdSendToNewDocument As Integer = 0
Const wdSendToPrinter As Integer = 1
Const wdSendToEmail As Integer = 2
Const wdSendToFax As Integer = 3
Private Sub CreateMailMergeDataFile()
Dim wordDataDoc As Object
Dim fileName As Object = "\FileserverDocumentsDataDoc.doc"
Dim header As Object = "First_Name, Last_Name, Title, Address"
wordDoc.MailMerge.CreateDataSource(fileName, missingValue, missingValue, header)
' Open the data document to insert data.
wordDataDoc = wordApp.Documents.Open(fileName)
' Create the header items
For count = 1 To 2
wordDataDoc.Tables(1).Rows.Add(missingValue)
Next
' Loop through the customer screen collection
Dim rowCount As Integer = 2
For Each c As Customer In Customers
FillRow(wordDataDoc, rowCount, c.Firstname, c.Surname, c.Title, c.Address)
rowCount += 1
Next
' Save and close the file.
wordDataDoc.Save()
wordDataDoc.Close(False)
End Sub
Private Sub FillRow(WordDoc As Object, Row As Integer,
Text1 As String, Text2 As String, Text3 As String,
Text4 As String)
If Row > wordDoc.Tables[1].Rows.Count Then
wordDoc.Tables[1].Rows.Add();
End If
' Insert the data into the table.
WordDoc.Tables(1).Cell(Row, 1).Range.InsertAfter(Text1)
WordDoc.Tables(1).Cell(Row, 2).Range.InsertAfter(Text2)
WordDoc.Tables(1).Cell(Row, 3).Range.InsertAfter(Text3)
WordDoc.Tables(1).Cell(Row, 4).Range.InsertAfter(Text4)
End Sub
Private Sub DoMailMerge_CanExecute(ByRef result As Boolean)
result = AutomationFactory.IsAvailable
End Sub
Private Sub DoMailMerge_Execute()
Dim wordMailMerge As Object
Dim wordMergeFields As Object
' Create an instance of Word and make it visible.
wordApp = AutomationFactory.CreateObject("Word.Application")
wordApp.Visible = True
' Open the template file
wordDoc = wordApp.Documents.Open("\FileserverDocumentsMailMergeTemplate.docx")
wordDoc.Select()
wordMailMerge = wordDoc.MailMerge
' Create a MailMerge Data file.
CreateMailMergeDataFile()
wordMergeFields = wordMailMerge.Fields
wordMailMerge.Destination = wdSendToNewDocument
wordMailMerge.Execute(False)
' Close the original form document.
wordDoc.Saved = True
wordDoc.Close(False, missingValue, missingValue)
' Release References.
wordMailMerge = Nothing
wordMergeFields = Nothing
wordDoc = Nothing
wordApp = Nothing
End Sub
End Class
End Namespace
C#:
File: ShipperCentralClientUserCodeCustomersWordMerge.cs
using System.Runtime.InteropServices.Automation;
using System.Reflection;
namespace LightSwitchApplication
{
public partial class CustomersWordMerge
{
dynamic wordApp;
dynamic wordDoc;
Object missingValue = System.Reflection.Missing.Value;
// Here are the values of the WdMailMergeDestination Enum
const int wdSendToNewDocument = 0;
const int wdSendToPrinter = 1;
const int wdSendToEmail = 2;
const int wdSendToFax = 3;
private void CreateMailMergeDataFile()
{
dynamic wordDataDoc;
Object fileName = @"\FileserverDocumentsDataDoc.doc";
Object header = "First_Name, Last_Name, Title, Address";
wordDoc.MailMerge.CreateDataSource(ref fileName, ref missingValue,
ref missingValue, ref header);
// Open the data document to insert data.
wordDataDoc = wordApp.Documents.Open(ref fileName);
// Create the header items
for (int iCount = 1; iCount <= 2; iCount++)
{
wordDataDoc.Tables[1].Rows.Add(ref missingValue);
}
// Loop through the customer screen collection
int rowCount = 2;
foreach (Customer c in Customers)
{
FillRow(wordDataDoc, rowCount, c.Firstname, c.Surname, c.Title, c.Address);
rowCount++;
}
// Save and close the file.
wordDataDoc.Save();
wordDataDoc.Close(false, ref missingValue, ref missingValue);
}
private void FillRow(dynamic wordDoc, int Row, string Text1,
string Text2, string Text3, string Text4)
{
if (Row > wordDoc.Tables[1].Rows.Count)
{
wordDoc.Tables[1].Rows.Add();
}
// Insert the data into the table.
wordDoc.Tables[1].Cell(Row, 1).Range.InsertAfter(Text1);
wordDoc.Tables[1].Cell(Row, 2).Range.InsertAfter(Text2);
wordDoc.Tables[1].Cell(Row, 3).Range.InsertAfter(Text3);
wordDoc.Tables[1].Cell(Row, 4).Range.InsertAfter(Text4);
}
partial void DoMailMerge_CanExecute(ref bool result)
{
result = AutomationFactory.IsAvailable;
}
partial void DoMailMerge_Execute()
{
dynamic wordMailMerge;
dynamic wordMergeFields;
// Create an instance of Word and make it visible.
wordApp = AutomationFactory.CreateObject("Word.Application");
wordApp.Visible = true;
// Open the template file
wordDoc =
wordApp.Documents.Open(@"\FileserverDocumentsMailMergeTemplate.docx");
wordDoc.Select();
wordMailMerge = wordDoc.MailMerge;
// Create a MailMerge Data file.
CreateMailMergeDataFile();
wordMergeFields = wordMailMerge.Fields;
wordMailMerge.Destination = wdSendToNewDocument;
wordMailMerge.Execute(false);
// Close the original form document.
wordDoc.Saved = true;
wordDoc.Close(false, ref missingValue, ref missingValue);
// Release References.
wordMailMerge = null;
wordMergeFields = null;
wordDoc = null;
wordApp = null;
}
}
}The mail merge works by saving the data from the customers screen collection into a new Word document called DataDoc.doc. This takes place in the method CreateMailMergeDataFile. This method creates a Word table in the DataDoc.doc file, and populates it with customer data by using the helper method FillRow. The first row in this table contains column headers that are defined in the variable header. The column names defined in header should correspond to the names of the mail merge fields that were specified in the template. This method also sets the mail merge data source of the Word template to the DataDoc.doc file that you’ve just created.
After creating the DataDoc.doc file, the remaining code in the DoMailMerge method performs the mail merge by calling the Execute method on the wordMailMerge object. At this point, the mail merge is complete, and the remaining code tidies up the objects that have been used. You can optionally add some code here to delete the DataDoc.doc file if you wish.
Prior to calling the Execute method, the Destination property of the wordMailMerge object is set to wdSendToNewDocument. This represents the numeric value of 0 and indicates that the results of the mail merge will be shown in the document. Other acceptable values are shown in Table 11-4.
This mail merge technique works only in desktop applications. Therefore, the code in the DoMailMerge_CanExecute method disables the button if automation isn’t available.
Figure 11-23 shows the mail merge screen in action.
Figure 11-23. Word mail merge example
Note This scenario is an ideal example of where you might want to use a multi select piece of UI. This would allow you to select only those customers that you want to appear in your mail merge. Chapter 8 presents details on how to do this.
Creating Mail Merge Fields Programmatically
The preceding example relies on you creating a Word template in advance. If you prefer not to do this, you could use Word automation to create a blank Word document. You would then build up the content and merge fields of your document in code before performing the mail merge. Listing 11-16 demonstrates how you would modify the DoMailMerge_Execute method to create your template in code.
Listing 11-16. Creating the Mail Merge Fields Programmatically
VB:
File: ShipperCentralClientUserCodeCustomersWordMerge.vb
Dim wordMailMerge As Object
Dim wordMergeFields As Object
' Create an instance of Word and make it visible.
wordApp = AutomationFactory.CreateObject("Word.Application")
wordApp.Visible = True
' Create a new file rather than open it from a template
wordDoc = wordApp.Documents.Add()
Dim wordSelection As Object
wordSelection = wordApp.Selection
wordMailMerge = wordDoc.MailMerge
' Create a MailMerge Data file.
CreateMailMergeDataFile()
wordMergeFields = wordMailMerge.Fields
' Type the text 'Dear' and add the 'First_Name' merge field
wordSelection.TypeText("Dear ")
Dim wordRange As Object = wordSelection.Range
wordMergeFields.Add(wordRange, "First_Name")
wordSelection.TypeText(",")
' programatically write the rest of the document here....
' Perform mail merge.
wordMailMerge.Destination = 0
wordMailMerge.Execute(False)
' Close the original form document.
wordDoc.Saved = True
wordDoc.Close(False, missingValue, missingValue)
' Release References.
wordMailMerge = Nothing
wordMergeFields = Nothing
wordDoc = Nothing
wordApp = Nothing
C#:
File: ShipperCentralClientUserCodeCustomersWordMerge.cs
dynamic wordMailMerge;
dynamic wordMergeFields;
dynamic wordSelection;
// Create an instance of Word and make it visible.
wordApp = AutomationFactory.CreateObject("Word.Application");
wordApp.Visible = true;
// Create a new file rather than open it from a template
wordDoc = wordApp.Documents.Add();
wordSelection = wordApp.Selection;
wordMailMerge = wordDoc.MailMerge;
// Create a MailMerge Data file.
CreateMailMergeDataFile();
wordMergeFields = wordMailMerge.Fields;
// Type the text 'Dear' and add the 'First_Name' merge field
wordSelection.TypeText("Dear ");
wordMergeFields.Add(wordSelection.Range, "First_Name");
wordSelection.TypeText(",");
// programatically write the rest of the document here....
// Perform mail merge.
wordMailMerge.Destination = 0;
wordMailMerge.Execute(false);
// Release References.
wordMailMerge = null;
wordMergeFields = null;
wordDoc = null;
wordApp = null;Creating Adobe PDF Documents
In some cases, businesses may prefer to provide reports in PDF format. PDF documents are more difficult for the end user to edit (unlike Word files) and are better at preserving the positioning and layout of visual elements such as images.
In LightSwitch, PDF documents can either be generated on the server or within the Silverlight client. Microsoft Reporting Services includes an option for exporting reports in PDF format, and this provides a simple mechanism for generating PDFs on the server.
To demonstrate the creation of a PDF report on the client, we’ll create a screen in the ShipperCentral application to create dispatch notices. Because there is no built-in function for generating PDFs in LightSwitch or Silverlight, writing your own procedures for doing this could be very time-consuming. Fortunately, various third-party libraries exist to help simplify the creation of PDF documents. The one that we’ll use in this example is silverPDF, an open source library that’s available on CodePlex. This is based on two other open source projects (iTextSharp and PDFsharp). If you’ve used either of these libraries before, the code shown here may be familiar to you.
To get started with silverPDF, download the silverPDF.dll file from the CodePlex web site (http://silverpdf.codeplex.com/). In your LightSwitch project, switch to File view and add a reference to the silverPDF.dll file in your client project.
Now create a screen for the Order table by using the Details Screen template. Create a method and add a button onto your screen (we’ve called this method DoPDF). Now insert the code shown in Listing 11-17 to trigger the creation of your PDF file.
Listing 11-17. Programming silverPDF
VB:
File: ShipperCentralClientUserCodeOrderDetailPDF.vb
Imports PdfSharp
Imports PdfSharp.Drawing
Imports PdfSharp.Pdf
…..
Private Sub DoPdf_Execute()
' Write your code here.
Microsoft.LightSwitch.Threading.Dispatchers.Main.BeginInvoke(
Sub()
Dim document As New PdfDocument()
document.Info.Title = "Dispatch Notice"
' Create an empty page
Dim page As PdfPage = document.AddPage()
' Get an XGraphics object for drawing
Dim gfx As XGraphics = XGraphics.FromPdfPage(page)
' Create a font
Dim fontHeader1 As New XFont("Verdana", 18, XFontStyle.Bold)
Dim fontHeader2 As New XFont("Verdana", 14, XFontStyle.Bold)
Dim fontNormal As New XFont("Verdana", 12, XFontStyle.Regular)
' Create the report text
gfx.DrawString ("ShipperCentral Dispatch" , fontHeader1,
XBrushes.Black, new XRect(5, 5, 200, 18), XStringFormats.TopCenter )
gfx.DrawString ("Thank you for shopping......" , fontNormal ,
XBrushes.Black, new XRect(5, 18, 200, 18), XStringFormats.TopLeft )
gfx.DrawString ("Order Number: " + Order.OrderID.ToString(), fontHeader2,
XBrushes.Black, new XRect(5, 32, 200, 18), XStringFormats.TopLeft )
'.... create other Elements here
' Save the document here
Dim myDocuments As String = Environment.GetFolderPath(
Environment.SpecialFolder.MyDocuments)
document.Save(myDocuments & "DispatchNotice.pdf")
End Sub
)
End Sub
C#:
File: ShipperCentralClientUserCodeOrderDetailPDF.cs
using PdfSharp;
using PdfSharp.Drawing;
using PdfSharp.Pdf;
…….
partial void DoPDF_Execute()
{
Microsoft.LightSwitch.Threading.Dispatchers.Main.BeginInvoke(() =>
{
PdfDocument document = new PdfDocument();
document.Info.Title = "Dispatch Notice";
// Create an empty page
PdfPage page = document.AddPage();
// Get an XGraphics object for drawing
XGraphics gfx = XGraphics.FromPdfPage(page);
// Create a font
XFont fontHeader1 = new XFont("Verdana", 18, XFontStyle.Bold);
XFont fontHeader2 = new XFont("Verdana", 14, XFontStyle.Bold);
XFont fontNormal = new XFont("Verdana", 12, XFontStyle.Regular );
// Create the report text
gfx.DrawString ("ShipperCentral Dispatch" , fontHeader1,
XBrushes.Black, new XRect(5, 5, 200, 18), XStringFormats.TopCenter );
gfx.DrawString ("Thank you for shopping......" , fontNormal ,
XBrushes.Black, new XRect(5, 18, 200, 18), XStringFormats.TopLeft );
gfx.DrawString ("Order Number: " + Order.OrderID.ToString(), fontHeader2,
XBrushes.Black, new XRect(5, 32, 200, 18), XStringFormats.TopLeft );
//.... create other Elements here
// Save the document here
string myDocuments = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
document.Save(myDocuments + "\DispatchNotice.pdf");
});
}The PDF creation code is invoked on the main dispatcher. If you don’t do this, an Invalid cross-thread access exception will be thrown by LightSwitch. This is because many objects in the System.Windows namespace (and others) are internally guarded to be created and used on the main Silverlight dispatcher thread. The silverPDF library is likely to be using these objects internally.
The code creates a PDF document by using pdfDocument and pdfPage objects. Instances of XFonts objects are created to style the text that we’ll add to our document.
The XGraphics object contains several Draw methods for creating graphical elements, and the code in Listing 11-17 demonstrates the use of the DrawString method to display text. Figure 11-24 illustrates some of the other Draw methods that are available for drawing shapes and lines.
Figure 11-24. silverPDF Draw methods
Later in the code, the order number is drawn by using the DrawString method, and a reference is made to the LightSwitch order by using the code Order.OrderID.ToString(). Finally, the PDF file is saved into the My Documents folder by calling the Save method of PDFDocument. Figure 11-25 shows how the final report might look.
Figure 11-25. PDF output produced with silverPDF
Tip Microsoft Word 2010 allows you to natively save documents in PDF format. You can therefore create PDF files by using COM automation and the Word object model (in desktop applications). You’d create your PDF file by calling the Save method on your Word document, and by passing in the file format type of 17 (this relates to Word’s wdFormatPDF enumeration value). The C# code would look something like this: myWordDoc.Save(17);
Using Other Third-Party Solutions
The final option for producing reports is to use a third-party reporting solution. Such products can simplify the report-writing process and offer an experience that’s more integrated into the LightSwitch development environment.
Third-party reporting controls have sometimes been known for causing compilation problems during development and other issues during deployment. We therefore recommend that you research the controls that you want to use to beforehand. This will help you discover the types of problems that other developers may have encountered.
This section mentions a couple of third-party controls that are tailored for LightSwitch. Silverlight controls from other vendors could also be used, but this would require a custom control to be written around the control that you want to use.
DevExpress XtraReports
DevExpress offers LightSwitch support through their XtraReports reporting suite. This product will appeal to you if you’ve had some prior experience using DevExpress controls.
After installing XtraReports, you’ll need to first enable it by using the extensions property pane in LightSwitch. To author a report, here are the steps that you would follow:
- You can’t directly base a report on any existing tables that you have. Instead, you’ll need to add a new WCF RIA Service data source. Start the Attach Data Source Wizard and select the option to add a WCF RIA Service. In the next page that appears, you’ll be able to select a WCF RIA Service Class called
XtraReportsService. Choose this option, and you’ll be prompted to select the entities from your LightSwitch application that you want to make available. - Now that you’ve added your data source, you can start to create your reports. Reports are created in the server project, so you’ll need to switch into File view beforehand. Your reports are authored by using a graphical report designer, which is integrated into the Visual Studio IDE.
- To display your reports, XtraReports adds a Report Preview Screen template to the list of templates that you’ll find in the Add New Screen dialog box. You can create a new screen based on this template. Another option is to add the XtraReports
ReportViewercontrol onto your screen.
Infragistics NetAdvantage
Infragistics has released a version of its NetAdvantage suite specifically designed for LightSwitch. Although there is no built-in reporting tool, it contains a set of gauge and data chart controls that you can incorporate into your LightSwitch application.
Summary
In this chapter, you’ve learned about reporting. Although built-in reporting is not available in LightSwitch, you have lots of options for building your own reports. The main topics covered in this chapter were as follows:
- Creating web-based reports using ASP.NET or SQL Reporting Services
- Linking and displaying these reports in LightSwitch
- Creating documents and mail merges by using Microsoft Word
- Creating PDF documents on the client
You can use ASP.NET to build web pages that display data or charts. You can add parameters to your pages, if you want to filter the data shown to the user.
If you need to produce richer output, you can use SQL Server Reporting Services. Reports are created in Business Intelligence Development Studio (BIDS) and saved in a format called Reporting Definition Language (RDL). The types of reports that you can create include matrix and tablix. The matrix option produces cross-tab and pivot-table reports. You can also create drill-through reports that allow users to click a link and to view additional details. Code can be added into reports to assist with operations such as styles and formatting. Visual Web Developer 2010 Express and SQL Server Reporting Services with Advanced Services are no-cost options but are more limited in functionality.
After creating your reports in ASP.NET or Reporting Services, you’ll need a method to link them to your LightSwitch application. You can create a button or a hyperlink on your screen that opens the report in a new browser window. Alternatively, you can display your reports inside your LightSwitch screen by using the WebBrowser control, in the case of desktop applications.
If you want to open your report by using a hyperlink, you’d use the HyperlinkButton control. You’ll need to point the HyperlinkButton to the URL of your report by setting the NavigateUri property. This can be done in the ControlAvailable event of the control or by using the SetBinding method. To convert a string representation of a URL into an object of type URI for the purposes of data binding, you’ll need to use a value converter.
On desktop applications, you can create reports in Microsoft Word by using COM automation. You can also create mail merges based on Word templates that you’ve created in advance. Alternatively, the template document could be built entirely in code if you prefer. The mail merge works by creating a separate data document. Word automation methods are then called to merge the data document with the template document.
If it’s important for you to create reports that look the same on most computers, you could create reports in PDF format. This chapter has shown you how to create PDF documents on the client by using the silverPDF library.
Finally, you could choose to purchase a third-party reporting tool, or you could build your own custom reporting control. To simplify the task of building your own reporting control, you could base your control on existing third-party Silverlight controls.