Having the ability to output data from applications can be crucial. Users often need to extract data in order to print or share with other users. Many users also search for ways to summarize or aggregate data so as to make better business decisions. Although LightSwitch includes no built-in report functionality, you can build reports with other products. You can then integrate those reports into your LightSwitch application. In this chapter, I’ll show you how to do the following:

• create reports with ASP.NET and SQL Server Reporting Services
• create printable output that includes parent and child data
• link and display reports from inside LightSwitch screens

The content in this chapter can be very beneficial for desktop applications. This is because unlike browser applications, there is no way to print from a desktop application.

Introduction to Reporting

In this chapter, I’ll focus on Microsoft SQL Server Reporting Services and Microsoft ASP.NET. These products are great for creating reports. This is because both are free of charge, are well supported by Microsoft, and integrate well with LightSwitch. Both products share one thing in common: they can produce output you can access through a web address. Once you build the report content, you can display it inside a LightSwitch screen or add a link that opens the content in a new browser window.

This web technique isn’t the only way to produce output from a LightSwitch application. Another method is to generate documents in Microsoft Word, Excel, or PDF formats. This is a big topic, and, therefore, I’ll cover this in the next chapter. Finally, you can purchase third-party reporting tools that integrate tightly into the LightSwitch development environment. One of the best known tools is the XtraReports suite from Developer Express.

Using ASP.NET to Create Reports

To begin this chapter, I’ll show you how to create an ASP.NET Web Form that includes data from your LightSwitch application. Web Forms are simple to program, and I’ve chosen this technique for the reason of simplicity. You could also create a web page with ASP.NET MVC if that’s the technology that you prefer. In Chapter 15, I showed you how to create a simple page with ASP.NET. In this chapter, I’ll extend the example by showing you how to use the Application Server Context as a data source, how to display parent-child data with nested grids, how to use charts, and how to display data from navigation properties.

To demonstrate this technique, I’ll show you how to build a report that uses a grid view control to display the issue records that are assigned to an engineer. To illustrate how to use Web Form parameters, I’ll describe how to enable the user to show data for a specific engineer by supplying an ID value via the request URL. Figure 16-1 shows the appearance of the page that we’ll create in this section.

On the topic of data connectivity, there are several ways to connect to LightSwitch data from an ASP.NET application. You can add an OData data source or you can use ADO.NET to connect directly to your Intrinsic database. In this example, I’ll use the Server Application Context to connect to our LightSwitch application. This technique is straightforward, adheres to the LightSwitch security model, and requires minimal work to access related data and navigation properties.

Showing Grid Data on a Web Form

Let’s review how to build your Web Form. First, create a folder called Reports in your Server project through Solution Explorer. Next, right-click the Reports folder and choose the menu item to add a new Web Form. Name your new file IssuesByEngineer.aspx.

In the Web Form designer, add a label to display the engineer name, and a grid view control to display the issues that are associated with the engineer. Name your label EngineerNameLabel and the grid view control IssuesGrid. You can add these controls by setting the designer into either Design or Split mode and dragging the controls from the toolbox onto the screen designer. Alternatively, you can add these controls by typing the markup directly into the Source section of the screen designer.

To further tidy the appearance of this page, you can define the columns that you want to show and use the Auto Format feature to apply header and alternate-row coloration. Figure 16-2 illustrates the layout of the page in split mode. The top part of this screenshot shows the ASPX markup, while the lower section shows the graphical view. The graphical view highlights the Auto Format and Edit Columns links.

If you choose not to define grid view columns, the grid will auto-generate a column for each column in your data source.

When you define grid view columns through the Edit Columns link, you need to specify the data field that the column shows. This is done by specifying the DataField setting against the column. The DataField value corresponds to the property name that you want to bind to. A useful technique is to show related parent data in the grid by specifying a DataField value that references a navigation property. For example, in a grid that binds to a collection of issue records, you can display the name of the “closed-by engineer” in the grid by using the DataField value ClosedEngineer.Fullname.

Note that you can easily format data columns with standard .NET format strings. This example formats the date columns in short-date formatting by setting the DataFormatString attribute value to {0:d}.

To bind your grid view control to LightSwitch data, right-click your page, select the “View Code” menu item, and add the .NET code that’s shown in Listing 16-1.

This listing shows the code that runs when the page loads. This page requires the caller to supply an engineer ID value. The code uses the request parameters collection to retrieve the engineer ID value that the user supplies . The next block of code uses the application server context to connect to your LightSwitch data. The code retrieves the required engineer record and binds the grid view control to the issues navigation property of the engineer . You can apply standard query operators to customize the data that the grid view control binds to. For example, the commented-out section shows the syntax you would use to display the first 50 issue records in date-descending order .

To include the IssuesByEngineer.aspx page in your build output, use the properties sheet to set the Build Action of your file to Content, and the Copy to Output Directory setting to Always. This is a step you need to carry out for any additional ASPX page you add to your project.

At this point, you can view your web page. Run your LightSwitch project. When your application starts, use the IIS Express tray icon in the notification area of Windows to find the HTTP endpoint for your server project. Figure 16-3 illustrates an example project where the endpoint address is http://localhost:1157/. To show data for a specific engineer, open a web browser and use the endpoint URL to build a fully qualified address that refers to your IssuesByEngineer.aspx web page. For example, you would enter the URL http://localhost:1157/Reports/IssuesByEngineer.aspx?EngineerID=1 to open the Issue report for the engineer with an ID of 1 (Figure 16-3). When the page loads, the final report will resemble the screenshot from Figure 16-1.

Displaying Grids within Grids

The structure of a typical business application includes related data, and it’s likely that you’ll want to show this type of data in a report. In this section, I’ll show you how to modify the page from the previous section to include child data. Figure 16-4 shows the end result of this example. To display the related issue-response records for each issue record, the technique requires you to nest a child grid view control inside a parent grid view control.

The key to nesting grid view controls is to add a child grid view control to a template field inside the parent grid view control. You would then write code in the RowDataBound event of the parent grid view control to set the data source of the child grid view control. Listing 16-2 shows the modification that you need to make to your parent GridView control.

As you can see from this listing, the parent grid view control defines a template field . Inside this template field, the markup defines a child grid view control called childGrid .

The next step is to write the code that handles the RowDataBound event of the parent grid view control. Listing 16-3 shows the code that you need to add.

This listing shows the code that runs when a specific row in the parent grid view control binds to the data source. This code uses the FindControl method to obtain a reference to the child grid view control . On the next line, e.Row.DataItem returns the issue object that the row binds to. The code sets the data source of the child grid view control to the issue responses navigation property of the issue record . You can now run your application, and the grid view control will show the nested data as shown in the screenshot at the start of this section (Figure 16-4).

To extend this example further, you could nest additional grid view controls beneath your child control. To add a third-level grid view control, you would add it to the template field in the second-level grid view control. In the RowDataBound method of the second-level grid view control, you would write code that adds an event handler to handle the RowDataBound event of the third-level grid view control. This event handler would data bind the third-level grid view control using the same methodology you used to bind the second-level grid view control.

Creating Charts

ASP.NET provides a chart control you can use to generate charts. With this control, you can produce all the usual chart types, such as pie, area, range, point, circular, and accumulation. To demonstrate this control, let’s look at how to create a page that uses a pie chart to summarize engineer issues by priority value.

To build this example, create a new Web Form called PriorityChart.aspx in the Reports folder of your Server project (create the Reports folder if it doesn’t exist already). In the screen designer, drag a chart control from the Data section of the toolbox onto your Web Form. Change the chart type to Pie and set the X Value Member and Y Value Member settings to PriorityDesc and PriorityCount, respectively. You can either use the properties sheet or can modify the ASPX source code directly, as shown in Figure 16-5.

Figure 16-5 shows the screen designer in split mode. The top part of this screenshot shows the ASPX markup while the lower section of the screen shows the graphical layout. The graphical section illustrates the area in the properties sheet where you can set the X and Y value members of the chart series. To bind your pie chart to data, add the code that’s shown in Listing 16-4.

The code at the start of this listing follows the same pattern as the previous example did. When the page loads, the code uses the server application context to retrieve that engineer record that the caller provides via the request URL .

This code uses a LINQ query to group the data into a structure that can bind to the chart control . This query uses the Issues navigation property of the Engineer entity as the data source. The query groups the related issue records by priority and produces a data structure that includes the priority description and a count of the issue records that match that priority.

You can now run your page; Figure 16-6 shows how the pie chart appears at runtime. As before, you would run your page by building a web address that supplies the engineer ID in the request URL (for example: http://localhost:1157/Reports/PriorityChart.aspx?EngineerID=1) .

There’s a good reason why this example uses a navigation property as the data source. When you call the code that retrieves an engineer record, LightSwitch eagerly loads the related records. This makes it possible to group your issue data with a LINQ query, because the issue records are available to ASP.NET locally. If you attempt to query the data directly by using workspace.ApplicationData.Issues as the data source, your query would fail. The reason is because workspace.ApplicationData.Issues is of type IQueryable, and, therefore, LightSwitch would attempt to execute the query at the database. LightSwitch doesn’t support this operation, because the data service cannot return non-LightSwitch entities to the ASP.NET client. Therefore, ASP.NET must load the data locally before you can query the data with LINQ. A simple way to do this is to use a navigation property. Another way is to use the take and execute methods to return EntityCollections you can work with. For example, you can use this syntax to return a set of Issue records that you can further query with LINQ: workspace.ApplicationData.Issues.Take(100).Execute().

Therefore, a caveat of this grouping technique is that it can be slow. Because the ASP.NET client needs to retrieve all of the required data from the database in order to carry out the grouping, this process can be slow if your underlying dataset is large.

Securing and Deploying Reports

When you use the application server context as a data source, the standard LightSwitch security model protects your data. Provided that you configure authentication and authorization (I will explain how to do this in Chapters 21 and 22), you don’t need to write any bespoke code to enforce access-control rules.

Because the report files belong inside a folder within the Server project, the normal deployment process includes your custom ASP.NET pages in the output. Therefore, you don’t need to carry out any additional tasks to include your reports in the deployment output that LightSwitch produces.

Using Microsoft Reporting Services

Microsoft SQL Server Reporting Services provides a reporting platform that’s more powerful than simple ASP.NET pages. The additional features in Reporting Services include subscription notifications and the ability to export data in formats such as Microsoft Word and Adobe PDF. A full Reporting Services installation requires you to install and host the product on a server.

If you don’t want the effort of setting up and maintaining a report server, you could use closely related Reporting Services technology to build “client” reports hosted through an ASP.NET page. In this section, I’ll describe both these techniques. I’ll begin by explaining how to install and create reports for a server installation of Reporting Services.

Installing Reporting Services

SQL Server Reporting Services is a part of the SQL Server product. You can obtain Reporting Services free of charge by installing the SQL Server Express with Advanced Services edition. If you already have SQL Server Express on your computer, you can upgrade a basic instance to the Advanced Services version by installing the setup package from the official Microsoft SQL Server website (http://www.microsoft.com/en-gb/download/details.aspx?id=42299).

SQL Server Setup installs a utility called Reporting Services Configuration Manager (Figure 16-7). You can use this utility to configure settings that relate to an instance of Reporting Services. In particular, you can use it to find out the Report Manager URL for your installation. Report Manager is a web-based application that you can use to upload and manage reports.

Note that for some installations, you might encounter a permissions error when you open the Report Manager in Internet Explorer. The UAC (User Account Access) feature in Windows can cause this problem. If you encounter this problem, a possible fix is to run IE in elevated mode by right-clicking the IE icon and choosing the “Run as Administrator” option.

Installing the Report Designer Components

To author reports for a server-based installation of Reporting Services, you need to install the software that enables you to do so. At the time of writing, the report-designer tools are included in a package called “SSDT October 2015 Preview in Visual Studio” (SSDT stands for SQL Server Data Tools). You can download this from the following website: https://msdn.microsoft.com/en-us/library/mt204009.aspx.

Preview versions have not been thoroughly tested and can contain bugs. You might choose not to risk installing preview software on your development machine, and so, therefore, I’ll describe the alternative supported route. The RTM (released to manufacturing)version of SSDT 2015 does not include the Reporting Services components. If you want to author reports for a server-based installation of Reporting Services with the RTM software, you need to install a product called SQL Server Data Tools Business Intelligence. The Microsoft documentation abbreviates this to SSDT-BI. Assuming that you install SQL Server 2014 Express and Visual Studio 2015 on your computer, you need to download and install SSDT-BI for Visual Studio 2013 from the same web page: https://msdn.microsoft.com/en-us/library/mt204009.aspx. At the time of writing, SSDT-BI for Visual Studio 2015 doesn’t exist, which is why you need to install the 2013 version.

When you install SSDT-BI for Visual Studio 2013 on a computer that contains only Visual Studio 2015, the setup program installs the Visual Studio 2013 shell and exposes the report-designer tools and templates through the 2013 shell. Figure 16-8 shows this option in the SSDT-BI installer dialog. If you install SSDT-BI for Visual Studio 2013 on a computer with Visual Studio 2013 already installed, the installer adds the reporting components to your existing installation of Visual Studio 2013.

If you chose not to install the SSDT preview software and want to build client reports that can run without the use of a report server, you must enable the SQL Server Data Tools feature in Visual Studio Setup (Figure 16-9). The default setup of Visual Studio doesn’t install this feature, so you might need to add it by running Visual Studio Setup. To modify an existing installation of Visual Studio, start Visual Studio setup through the Programs and Features section in Windows Control Panel.

The names of these products can confuse developers. To clarify the purpose of these RTM products, SSDT-BI is the tool that developers use to create SQL Server Reporting, Integration Services, or Analysis Services projects. SSDT is the Visual Studio add-in that provides client-side reporting templates, as well as support for database refactoring, schema comparisons, and templates to create database objects such as views and stored procedures. Although SSDT-BI and SSDT are different products, SSDT-BI used to be called SSDT in SQL Server 2012. Prior to SQL Server 2008, the SSDT-BI product was called Business Intelligence Development Studio, or BIDS for short. If you refer to the Internet for help on Reporting Services, it’s useful to understand the naming history of these products to avoid any confusion. To muddle matters even more, Microsoft has included the SSDT-BI components in the SSDT October 2015 Preview.

Creating Reports

To show you how to build reports with Reporting Services, let’s create a report that summarizes issues records by department.

The first step is to create a Reporting Services project. A Reporting Services project serves as a container for reports. With Reporting Services, you define reports in an XML-based format called Reporting Definition Language (RDL).

To add a new project, open Visual Studio 2015, or open Visual Studio 2013 if you chose to install the 2013 components. In the case of the latter, if you installed SSDT-BI for Visual Studio 2013 on a machine without Visual Studio 2013, the installer adds a shortcut called “SQL Server Data Tools for Visual Studio 2013” in the Start Menu section of your computer. When Visual Studio opens, you can use the File menu to add a new Report Server project. Next, right-click the Reports folder of your project in Solution Explorer and choose the “Add New Item” option. From here, you can either choose the option to add a blank report or choose a Report Wizard item. For simplicity, I’ll show you how to create a report by using the wizard.

The first stage of the wizard prompts you to select a data source. In an ideal world, you would configure Reporting Services to use an OData data source to connect to your LightSwitch application. Unfortunately, Reporting Services doesn’t support OData data sources. Therefore, the next best way to connect to your Intrinsic database is to configure a SQL Server data source.

To configure a data source, you can either specify a connection to a deployed version of your SQL Server database or specify a connection to your localdb development database. To connect to your Intrinsic database, type (localdb)MSSQLLocalDB into the Server Name textbox and use the “Select or enter a database name” drop-down to select the ApplicationDatabase.mdf database file for your project (Figure 16-10).

The next page of the wizard prompts you to enter a SQL query. Here, you can use the Query Builder button to construct a query. In this example, you can use the SQL that’s shown in Listing 16-5. This SQL returns a list of issues by engineer, with a corresponding count of issues broken down by priority.

This SQL returns a typical aggregated view of data that managers often like to see in reports. Rather than give an example that returns a simple tabular view of data, I chose to include this example because it more closely illustrates the type of requirement that you would encounter in real life.

This type of SQL can be convoluted, because it’s often difficult to produce this type of flattened view of data from a relational structure. This listing demonstrates the code that T-SQL developers usually write to generate this type of view. It consists of a series of nested aggregations and select statements. This code is difficult to read, but for large datasets, you can achieve quicker performance by running SQL at the database rather than using matrix or cross-tab reports in Reporting Services.

To clarify how this query works, Figure 16-11 shows the example output from each subquery. Starting with the Issues table, the first query uses a case statement to transpose the results. This produces a table structure with the columns engineer ID, low priority, medium priority, and high priority. Each row in this table stores the value 1 in the priority column that matches the issue record. The next select statement produces a sum of this result set, grouped by engineer ID. The final select statement joins the result with the Engineer table in order to include the firstname and surname values in the output.

The next step in the wizard prompts you to select a report type. The report types you can choose from are tabular and matrix. Select the tabular report option and complete the remaining steps in the wizard. You can provide a name for your report on the last page of the wizard. For this example, name your report IssuesByDepartment.rdl. When the report opens in the designer, you can use the Preview tab to see a runtime view of your report.

Working with the Report Designer

Let’s take a closer look at the features in the report designer (Figure 16-12). The left part of the designer contains a Report Data pane. If you can’t see this item, you can reveal it by choosing the View Report Data option from the top-level menu. The Report Data pane is useful, as it enables you to manage the data items in your report. Specifically, you can use this pane to add images, parameters, or built-in fields (for example, page numbers) onto your report.

The designer also provides a Toolbox pane. You can use this to add additional components onto your report, such as textboxes, lines, and subreports. Notice how the toolbox includes chart, gauge, and map controls. You can use these controls to build reports that are attractive and visually engaging.

The top section of the designer provides two tabs you can use to switch between Design and Preview modes.

Managing and Adding Datasets

In most cases, the items in a report bind to properties in a dataset. A dataset is an object that represents a tabular view of data. If you want to construct a report with multiple sections that bind to different tables, you need to add additional datasets to your report. You can do this by right-clicking the Datasets folder in the Report Data pane and selecting the “Add Dataset” menu item.

To avoid any confusion, an important distinction is that a dataset object in Reporting Services is not the same as the dataset object in ADO.NET code. Importantly, a Reporting Services dataset does not have the same programmatic properties and methods as an ADO.NET dataset object.

To illustrate another use for datasets, you can build reports where a user can filter the data that the report shows. To do this, you would define a parameter and modify your dataset query to refer to the parameter. Reporting Services can limit the parameter values that a user can select by showing the list of available options in a drop-down box. To accomplish this, you would configure the “Available values for this parameter” setting to refer to a dataset.

Displaying Data on a Report

From the toolbox, there are three group controls you can use to display data in the body of your report. These controls are the table, matrix, and list controls. Let’s look at a brief overview of these controls.

The table control displays data in rows and columns. In contrast, the list control is not as rigid. A list control enables you to display free-form data by binding data items to textboxes. Figure 16-13 illustrates the difference between the list and table layouts.

The matrix control groups the output by rows and columns. You would use this control to produce aggregated figures and to generate cross-tab or pivot-table type reports.

Although the toolbox shows three separate controls, the table, matrix, and list controls are all the same control. When you add one of these controls to a report, the designer creates a control called a tablex. This is a highly flexible control that you can configure to render data in all three of these formats. For example, you can transform a table control into a list control by adding a rectangle control and adding textboxes to the rectangle. Alternatively, you can transform a table control to a matrix control by configuring the grouping of the data and binding textboxes to aggregated figures.

Writing Code

A powerful feature in Reporting Services is the ability to customize reports with code. You can use this feature to apply custom logic and custom styles and formatting to reports. There are two ways to add custom code to a report: you can either use code from a .NET assembly or embed code into individual reports. Here are the characteristics of these two options:

• Use code from a .NET assembly: This option is ideal if you want to use your logic in multiple reports. You can take advantage of the classes in the .NET Framework, such as the string formatting and math functions.
• Embed custom code: You can create custom methods in each report by right-clicking your report in the design view and selecting the “Report Properties Code” menu item. The benefit of embedded code is that it’s quick and easy to use. There’s no need to spend time compiling your .NET code assembly in a separate project before you use it in your report.

With embedded code, you can do something very powerful. You can set the value of a report property to an expression. This enables you (for example) to apply alternate row colors to a table by simply writing one line of code (Figure 16-14):

=IIf(RowNumber(Nothing) Mod 2 = 0, "Silver", "Transparent")

This expression uses the IIf (conditional If) function. This function accepts three arguments: a test, the return value if the test succeeds, and the return value if the test fails. Figure 16-14 illustrates how to apply this expression to the BackgroundColor property of a table row.

Although this technique is very powerful, it’s also easy to forget that it’s there. For example, when you click your mouse into the BackgroundColor property, the control shows a drop-down box that contains a list of colors. The presence of this drop-down can hide the fact that you can type free-text expressions into this control. Most of the other properties in the property sheet also include custom renderers, so this behavior applies not just to the BackgroundColor property.

Creating Drill-Through Reports

Drill-through reports enable users to view additional details or related data by clicking a link. In the example I showed you, you could modify the report so that when a user clicks a department name, the report opens a list of issues for the selected department.

The key to building a drill-through report is to set the Action property of your textbox control. The properties sheet contains a button next to the Action property. When you click this button, the designer opens a dialog that you can use to select when happens when the user clicks the textbox. You can open a subreport, go to a different URL, or jump to a predefined bookmark in your report.

Deploying Reports

Reporting Services uses a web-based report server that allows end users to access reports through a web interface. As a developer, there are two ways to deploy your reports onto a report server. You can deploy them directly from the designer or you can upload RDL files through a web-based Report Manager.

Deploying a Report from the Designer

The easiest way to deploy a report is to use the deploy feature that’s built into the designer. Before you can deploy your report, you need to configure some deployment settings. To do this, right-click your project in Solution Explorer and view the property window for your project, as shown in Figure 16-15.

Within this property window, the settings that you need to configure are:

• TargetReportFolder: Enter the folder on the Report Server where you want to publish your reports.
• TargetDataSourceFolder: This specifies where the deployment function saves your data source files. If you leave this option blank, Visual Studio will publish your data sources into the TargetReportFolder.
• TargetServerURL: To publish reports, you must enter a path to the virtual directory of your report server. As an example, this would be http://server/reportserver or https://server/reportserver (as opposed to the URL of the Report Manager).

Once you enter these details, you can deploy your reports by using the right-click “Deploy” option in Solution Explorer.

Importing a Report from Report Manager

Another way to deploy a report is to upload your RDL file through Report Manager, as shown in Figure 16-16. This option is ideal if you can’t deploy your reports from within the designer. This can happen if your development computer isn’t connected to the same domain or network as your 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 or create a new folder.

Click the Import link and upload your RDL file. Once you upload your report, you will need to configure the data source using the “data” option in Report Manager. After that, you’ll be able to view your report in your browser.

Using the Report Viewer Control

With ASP.NET, you can host reports on a Web Form by using the report viewer control. This control can display reports from a Report Server, or, alternatively, you can configure the control to process reports locally without the need for a report server. To use the report viewer control in local processing mode, you need to supply a report file in RDLC (Report Definition Language Client-side) format. An RDLC file is a cut-down version of an RDL file. You can add RDLC files to your project through the “File New” dialog, or you can convert an existing RDL file into RDLC format.

To demonstrate how to use the report viewer control, let’s look at how to convert the RDL file we created earlier into RDLC format and how to host it through an ASP.NET page.

It’s very easy to convert an RDL file to RDLC format. Simply rename your .RDL file with an .RDLC extension and import the file by selecting the “Add Existing Item” option from your project menu. The import process will convert your RDL file into RDLC format. Here are the steps to add a Web Form to host your report:

1. In the Server project of your LightSwitch application, right-click your Reports folder (create this folder if it doesn’t exist already) and add a new Web Form called ReportPage.aspx. Use the properties sheet to set the Build Action to Content, and the Copy to Output Directory setting to Always.
2. Drag a SqlDataSource from the toolbox onto your Web Form. Configure your SqlDataSource to connect to your Intrinsic database. As in the Reporting Services example, you can point to your development database by entering the server name (localdb)MSSQLLocalDB as your data source.
3. Next, enter the SQL Query for your data source.
4. Drag a report viewer control from the Reporting section of your toolbox onto your Web Form. Also drag a script manager control onto your Web Form. This is necessary, because the report viewer control requires a script manager to work.
5. Use the smart tags panel in the report viewer control to select your RDLC file, and then use the link beneath to set the data source to your SqlDataSource (Figure 16-17) .

This completes the design of your web page; you can now run your application. To view your report through your web page, you’ll need to copy your RDLC report to your application’s output folder. At debug time, this would be the location: HelpDeskinDebugReports. Once you do this, you can open a web browser and view your report.

To conclude this section, here are some final comments on building RDLC reports. Unlike Reporting Services RDL reports, you can bind RDLC reports to custom .NET objects. This means in theory, you can bind reports to LightSwitch objects and avoid writing SQL that can potentially be quite complex, depending on the output that you want to produce. In practice, however, there are some problems that you need to resolve when you bind to LightSwitch objects. For example, if you use the server application context to bind entities with navigation properties to a report, the entity code that LightSwitch auto-generates raises threading exceptions during the data-binding process (Figure 16-18). Alternatively, if you attempt to build a report that uses a LightSwitch OData data source, there’s no simple way to configure the report designer to add a list of available properties to the toolbox. Therefore, the simplest way to build an RDLC report is still to use a SQL Server data source.

You should also be aware that when you author RDLC reports in Visual Studio, the Add Dataset dialog omits the option to populate a dataset with a stored procedure or custom SQL. If you want to populate a report with the output from custom SQL, you can build an RDL report in SSDT-BI and convert the report to RDLC format afterwards.

Linking Reports from Desktop Applications

Once you have created a web-based report in ASP.NET or Reporting Services, you’ll need some way to link it to your LightSwitch application. There are two approaches you can use, which are:

• To open your web report in a new browser window
• To display a web report inside a LightSwitch screen

Opening Reports in a New Browser Window

To open your web page in a new browser window, you can either shell an instance of your browser (in a desktop application) or use Silverlight’s HtmlPage class (in a browser application).

To demonstrate this technique, I’ll show you how to build a screen that contains a list of engineers. Each engineer row will include a link that opens the IssuesByEngineer report in a new browser window. To create this example, carry out the following steps.

1. Add a reference to System.Windows.Browser assembly in your Client project.
2. Create an Editable Grid screen that’s based on the Engineer table and name your screen EngineersManagerGrid.
3. In the command bar for the data grid, add a new button called OpenEngineerIssueReport. Use the drop-down to change the control type to "link." Figure 16-19 shows the appearance of your screen.

Now, add the code that’s shown in Listing 16-6

Listing 16-6 shows the code that runs when a user clicks a link to open the report. The first part of this code builds the URL to the IssuesByEngineer.aspx web page. The code appends the ID of the record that the user selects in the data grid to the URL .

This example hard codes the root part of the URL. In practice, it’s wise to store this in a table or somewhere you can modify after you deploy your application. The next part of the code detects your application type . For a desktop application, the code uses COM automation to shell an instance of your browser . For a browser application, the code uses the Silverlight HtmlPage class instead .

When you now run your application, you’ll be able to view engineer timesheet reports by clicking the button that appears against each engineer record (Figure 16-20) .

Displaying Reports inside LightSwitch Screens

Rather than open web pages in a new browser window, you can show reports inside your LightSwitch screen by using the Silverlight web browser control. However, a slight disadvantage is that browser applications don’t support this control, so this method works only for desktop applications.

Showing Reports in a Screen

To demonstrate how to use the web browser control to display a web page on a screen, let’s look at how to add the web page with the RDLC report to the EngineersManagerGrid screen that you created above.

1. Open the EngineersManagerGrid screen. Click the Add Data Item button and add a new string property called ReportProperty. Make sure to uncheck the “Is Required” checkbox.
2. Drag ReportProperty onto your screen and change the control type from a textbox to a custom control. You can add this control onto a new tab layout to keep it separate from the existing items on your screen.
3. In the properties sheet for ReportProperty, click the Change link and set the control type to System.Windows.Controls.WebBrowser control.
4. In the properties sheet for ReportProperty, set the Min Width and Min Height fields to 300. Your web page won’t appear if you leave the width and height properties set to Auto. Figure 16-21 shows the design-time view of your screen.

Now, append the code that’s shown in Listing 16-7 to the Created method of your screen. This completes your screen; Figure 16-22 shows how it looks at runtime.

Showing Reports in a List Screen

Another way you can use the web browser control is to apply it to a screen that contains a data list or data grid. As the user changes the selected record via the list or grid control, the page that appears in the web browser control refreshes itself to match the currently selected record.

To demonstrate this technique, I’ll show you how to modify the EngineersManagerGrid screen. I’ll describe how to add a list of engineers, and how to add a web browser control that displays the IssuesByEngineer report that corresponds to the selected engineer. Follow these steps to carry out this example:

1. Build the web browser custom control from Chapter 13 (Listing 13-9) into an assembly called ApressControls.dll.
2. Open the Engineers Manager Grid screen. In a new tab, drag the Engineers collection onto your screen and set the control type to a list control.
3. Drag the Engineer ID property of the selected item onto the screen and change the control type from a label to a custom control. Make a note of the name that designer assigns to this property. In this example, the designer sets the ID of this control to Engineers_SelectedItem_Id.
4. In the properties sheet, click the Change button and open the Add Custom Control dialog. Use the Browse button to select the ApressControls.dll file, and set the custom control type to ApressControls.WebBrowserControl. Set the Min Width and Min Height fields to 300.
5. Add the code to the Created method of your screen, as shown in Listing 16-8.
6. This code uses a value converter called IdToReportUrlConverter. Create this class immediately after your EngineersManagerGrid class. Listing 16-8 shows the code that you’ll need to add.

Figure 16-23 shows the screen designer. This completes the screen-design tasks; you can now run your application. Figure 16-24 shows the runtime view of the screen.

This code uses the web browser custom control from Chapter 13. This custom control exposes a dependency property called URIProperty. Whenever this property changes, the control navigates to the value of the URIProperty.

In this example, we added an ID property onto the screen and changed the control type to the custom web browser control. Therefore, the underlying data context of this control is the numeric ID value. The code calls the FindControl method to return a reference to the custom control . Make sure that the name you pass to this method matches the name of your custom control (Engineers_SelectedItem_Id, in this example). On the next line, the SetBinding method binds the numeric ID to the custom web control’s URIProperty. The URIProperty needs to be in the format of a web address. Therefore, the IdToReportUrlConverter value converter takes the numeric ID and returns a web address that opens the report for the selected engineer .

Printing Reports

An important requirement in many business applications is having the ability to print documents. Unfortunately, desktop applications include no support for printing. But with a small modification to your ASP.NET reports, you can call JavaScript code that opens the browser’s Print dialog. This would allow the user to send the contents of the web page to the printer. Follow these steps to modify the IssuesByEngineer report so that it includes a Print button:

1. Open the IssuesByEngineer.aspx page in the designer.
2. Switch your page to the source view. Just above the grid view control, add the following line of code:

   

This line of HTML defines a Print button. When the user clicks this button, it triggers a piece of JavaScript that opens the browser’s Print dialog. Figure 16-25 shows how this button looks at runtime.

Linking Reports from HTML Client Applications

With HTML client applications, you can very easily show external web content on LightSwitch screens. This technique relies on custom controls. I showed you how to use custom controls in previous chapters. Therefore, this section provides a brief explanation of how to show the ASP.NET and Reporting Services output on a LightSwitch screen.

Displaying Content inside an iframe

An effective way to show web content on a screen is to use an iframe. Here’s how to use an iframe to display the pie chart page from earlier in this chapter.

First, create a Browse screen and name it ReportOverview. For this example, you can leave the data source of the screen blank. In the section where you want to show your content, create a rows layout and change the control type to a custom control (Figure 16-26).

Now, add the render code for your custom control, as shown in Listing 16-9.

The code in this listing defines an HTML iframe. The first line of code determines the root URL of your application and uses this to build a path to your report . You can now run your screen; Figure 16-27 illustrates the appearance at runtime.

Adding Hyperlinks

Rather than show the web content in an iframe, you can easily modify your custom control so that it shows a hyperlink instead. To make this change, modify the render code for your custom control as shown in Listing 16-10.

This code replaces the iframe with an HTML hyperlink. The target='_blank' attribute configures the hyperlink to open the content in a new browser window . You can now run your screen; Figure 16-28 shows how it looks at runtime.

Summary

LightSwitch comes with no built-in reporting function. To help extract data from a LightSwitch application, this chapter showed you how to build reports with ASP.NET and SQL Server Reporting Services. Both of these products generate content that you can access through a web address. In the second part of this chapter, I showed you how to link and display this content inside LightSwitch screens.

In the first part of this chapter, I showed you how to build reports with ASP.NET. Within a Web Form, you can use the grid view control to display tabular data and the chart control to display charts. An effective way to display parent-child data is to use a set of nested grid view controls. To apply this technique, you would add a child grid view control to a template column inside the parent grid view control. You would then write code in the RowDataBound event of the parent grid view control to bind the child control. To bind the web controls to data, I showed you how to use the LightSwitch server application context. This enables you to bind to strongly typed LightSwitch data objects. This technique honors any security-access rules you define in LightSwitch, and another benefit is that you easily access related data through navigation properties.

The ASP.NET chart control enables you to display data through all the usual chart types. This includes line, bar, area, and pie. I showed you how to aggregate data with LINQ and how to display the results with a pie chart. The LightSwitch data sources you can use to aggregate data with LINQ include navigation properties and local entity collections that you return by calling the execute method on an entity set.

After the ASP.NET examples, I showed you how to use SQL Reporting Services. With Reporting Services, you can generate neat output that renders well when printed. The full version of Reporting Services includes powerful features such as scheduled reporting and email notifications. You can obtain a free, cut-down version of Reporting Services by installing SQL Server Express with Advanced Services.

To author reports in Visual Studio 2015, you need to install the SSDT October 2015 Preview for Visual Studio 2015. If you don’t want to install preview software on your computer, you can install a tool called SQL Server Data Tools–Business Intelligence (SSDT-BI) instead. At the time of writing, a 2015 version of SSDT-BI doesn’t exist. Therefore, the latest RTM version of SSDT-BI will integrate the report designer components into Visual Studio 2013. If you don’t have Visual Studio 2013 on your computer, the installer will install the report designer components into a standalone version of Visual Studio 2013.

Reporting Services defines reports in an XML format called RDL (Reporting Definition Language). The toolbox in the Reporting Services designer includes three controls you can use to display data. These are the table, list, and matrix controls. The table control displays data in rows and columns; the list control enables you to author free-form layouts; and the matrix control displays aggregated data in a cross-tab or pivot-`table manner.

With Reporting Services, you can customize your reports with embedded code, or with .NET code from a separate assembly. I showed you how to use this feature to style and format your reports. An additional feature is the ability to build drill-through reports. With this feature, users can click links in a report to open secondary reports.

Reporting Services is a server-based solution. Microsoft provides similar client-side report capabilities through RDLC reports. You can render RDLC reports without the need of a server. I showed you how to display an RDLC report on ASP.NET page through the use of the report viewer control. To author RDLC reports, you need to install the SSDT feature from Visual Studio setup. Another way to create RDLC reports is to convert existing Reporting Services RDL files to RDLC format.

To display an ASP.NET or Reporting Services page on a desktop application screen, you can use Silverlight’s web browser control, or you can write code that opens the page in a web browser. Desktop applications that run in the browser don’t support the web browser control. For these types of applications, you can open your report in a new browser window by using Silverlight’s HtmlPage object. For HTML client applications, you can add a custom control that displays your report in an iframe, or you can add a custom control that adds a hyperlink to your report.