Managing and Adding Data Sources

The ‘Report Data’ pane allows you to manage the data items in your report. With it, you can add images, parameters, or built-in fields (for example, page numbers) to your report.

The Datasets folder allows you manage the table data that your report shows. It’s important to understand that a Dataset in Reporting Services isn’t the same as the DataSet object that you’ll find when you’re writing ADO.NET code.

You can use the right-click context menu on the Datasets folder to add additional DataSets. For example, if you want to add a drop-down box that allows your report to be filtered, you add an additional DataSet here to populate the choices that you want to show in your drop-down box.

Using the Report Design Surface

When you click the Design tab, SSDT shows a graphical designer that allows you to edit and design reports. If you examine the issues report that you’ve created, the body of the report contains a table. A table is a control that allows you to show data in rows and columns. This control is an example of a data region. Other data regions include the list, matrix, and tablix controls.

The list control allows you to show individual data items by using text boxes. The list control is more flexible than the table control because you can position the text boxes anywhere inside the row section.

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

A great feature in Reporting Services is the ability to customize your reports through code. By doing this, you can apply custom logic to style and format your reports more attractively. With Reporting Services, you can add custom code in two ways: you can either use code from a .NET assembly, or you can embed code into your report. 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 Design view and choosing Report Properties Code. The advantage of embedded code is that it’s quick and easy to use understand—there’s no need to mess about compiling your .NET code assembly in a separate project before you can use it in your report.

Embedded code allows you to do something that’s very powerful: set the value of a report property to an expression. For example, you can apply alternate row coloring in table rows by simply writing one line of code:

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 is true, and the return value if the test is false. Figure 14-12 shows how you apply this expression to the BackgroundColor property of a table row.

Although this technique is very powerful, it’s also quite easy to overlook. When you click your mouse into the BackgroundColor property, SSDT shows a drop-down box that allows you to choose a color. The presence of this drop-down box might make you forget that you can type code expressions into this control. Many 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 allow users to click on a link to view additional details or related data. In the example that you’ve just created, you could modify the report so that it opens a child report of department issues when the user clicks on a department name. The key to creating a drill-through report is to set the Action property of your TextBox control. The properties sheet contains a button that’s next to the Action property. When you click on this button, SSDT opens a dialog that allows you to choose what happens when the user clicks on the TextBox control. You can either open a subreport, go to a different URL, or jump to a predefined bookmark on your report.

Deploying a Report from BIDS to a Report Server

The easiest way to deploy a report is to use the deploy option that’s built into SSDT. Before you can deploy your report, you’ll need to configure some deployment options. Right-click your project in Solution Explorer, and view the property window for your project, as shown in Figure 14-13.

You’ll need to set the following items:

• TargetReportFolder: Enter the folder on the report server where you want to publish your reports.
• TargetDataSourceFolder: This specifies the folder where SSDT saves your Data Source files. If you leave this option blank, SSDT will publish your data sources in the TargetReportFolder.
• TargerServerURL: This field is mandatory if you wish to publish reports. Enter the path to the virtual directory of your report server (for example, http://server/reportserver or https://server/reportserver), rather than the URL of Report Manager. (Report Manager is the web application that allows you to manage Reporting Services.)

After 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 import your RDL file in Report Manager, shown in Figure 14-14. This option is ideal if you’re unable to deploy from SSDT. An example of where this might apply is 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 your 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’ve uploaded your report, you’ll need to configure the data source by using the data option in Report Manager. After that, you’ll be able to view your report in your browser.

Hosting a Report on an ASP.NET Page

If you don’t have access to a report server, you can still use reports by creating an ASP.NET web page that includes a Report Viewer control. The Report Viewer control can display reports from a report server, or it can process reports locally without needing a report server. When you set the Report Viewer control to Local Processing mode, it expects to process Report Definition Language Client-side (RDLC) files. These are cut-down versions of RDL files. You can create them in your ASP.NET project by using the File New menu option. If you created an RDL report in SSDT and want to use the Report Viewer control to process it locally, you need to first convert it to the RDLC format. Fortunately, this is quite an easy task. To carry out the conversion, you can simply rename your RDL file with an .rdlc extension. After you do this, return to your ASP.NET project and import your report by choosing the Add Existing Item option from your project menu. This process will convert your RDL file to RDLC format.

To show you how to use the Report Viewer control, here’s how to display the Issue Report By Department report you created earlier in this chapter. The first steps are to create a new page in your ASP.NET project and set up the data source for your report:

1. Right-click your ASP.NET project in Solution Explorer, and select the Add New Item option. When the Add New Item dialog appears, go to the Data group that appears in the left pane and select DataSet. Name your object ReportingDataSet.xsd.
2. When the Dataset designer opens, choose the right-click Add TableAdapter option. This starts the TableAdapter Configuration Wizard. The first page prompts you to specify a connection to your SQL Server database. The next page asks you how the TableAdapter should access the database. Choose Use SQL Statements and in the next page, enter the SQL that’s shown in Listing 14-4. Now build your project.

After you’ve set up your DataSet, import the IssuesByDepartment.rdl report from earlier in this chapter. If you didn’t create this report, you can create a new RDLC report in your ASP.NET project. To create an RDLC report, select the Add New Item option in Solution Explorer. When the Add New Item dialog appears, go to the Reporting group that appears in the left pane and select Report. Name your object IssuesByDepartment.rdlc. Now carry out the following two steps:

1. In the designer for your report, right-click the DataSets folder and choose Add DataSet. This opens the DataSet Properties dialog. In the Data Source drop-down box select the ReportingDataSet that you’ve just created.
2. From the toolbox, drag a Table control onto your report. Use the designer to add the DepartmentName and DeptIssueCount fields to your table control.

Here’s how to create a new web page that uses the Report Viewer control:

1. Add a new Web Form to your ASP.NET project, and call it IssuesByDepartment.aspx.
2. Switch your page to Design View, and drag a ReportViewer control from the Reporting section of the toolbar onto your page.
3. In the smart tag for the ReportViewer control, choose the IssuesByDepartment.rdlc report.
4. Click on the Choose Data Sources option. When the Choose Data Sources dialog appears, use the drop-down box to select the ReportingDataSet Departments DataSet. (See Figure 14-15.)

   

   Figure 14-15. Adding the Report Viewer control onto a web page

   This completes the design of your web page. You’re now ready to run your page.

Generating and Showing Documents Onscreen in Word

In your LightSwitch application, create a Details Screen for the Department table, and name it DepartmentDetail. Add a button to your screen called CreateWordDoc, and enter the code that’s shown in Listing 14-8. You’ll need to add a reference to the System.Runtime.InteropServices.Automation namespace by using the imports or using statement.

Listing 14-8.  Microsoft Word Automation Code

VB:
File:HelpDeskVBClientUserCodeDepartmentDetail.vb
  
Imports System.Runtime.InteropServices.Automation
  
Private Sub CreateWordDoc_Execute()
    If AutomationFactory.IsAvailable Then                                                    
        Try
            Using wordApp = AutomationFactory.CreateObject("Word.Application")               
                Dim wordDoc = wordApp.Documents.Open(                                        
                    "\FileServerTemplatesLetterTemplate.dotx")
                wordDoc.Bookmarks("DepartmentName").Range.InsertAfter(                       
                    Department.DepartmentName)
                wordApp.Visible = True                                                       
            End Using
        Catch ex As Exception
            Throw New InvalidOperationException("Failed to create letter.", ex)
        End Try
    End If
End Sub
  
C#:
File:HelpDeskCSClientUserCodeDepartmentDetail.cs
  
using System.Runtime.InteropServices.Automation;
  
partial void CreateWordDoc_Execute()
{
    if (AutomationFactory.IsAvailable)                                                       
    {
        try
        {
          using (dynamic wordApp =
              AutomationFactory.CreateObject("Word.Application"))                            
           {
                dynamic wordDoc = wordApp.Documents.Open(                                    
                    @"\FileServerTemplatesLetterTemplate.dotx");
                wordDoc.Bookmarks("DepartmentName").Range.InsertAfter(        
                    Department.DepartmentName);
                wordApp.Visible = true;                                       
           }
        }
        catch (Exception ex)
        {
            throw new InvalidOperationException("Failed to create letter.", ex);
        }
    }
}

The first part of this code checks that your application is running on the desktop . To improve this code, you could also add the same logic to the CreateWordDoc button’s CanExecute method. This hides your button if your application runs in a browser, and it’s a good idea to add this logic to the other examples in this chapter that are desktop specific. The next part of the code creates a COM reference to Word by calling the AutomationFactory class’s CreateObject method . The way that you instantiate a COM object is to supply a string argument that’s known as a ProgID. This example supplies the ProgID Application.Word. But if you want to automate Excel instead, for example, you’d replace this with a ProgID of Application.Excel.

This technique of creating a COM object by passing in a ProgID string is known as late binding. The advantage of late binding is that it doesn’t rely on any particular version of Word. As long as a version of Word exists on the client computer, your code will work. A major disadvantage, however, is that during design time, Visual Studio’s code designer won’t provide you with any IntelliSense. This means that you can easily write buggy and invalid code that you won’t discover until run time.

The next part of the code opens your Word template document by calling the Documents.Open method . In this example, the Word template file is held on a file share that’s accessible through a UNC (Universal Naming Convention) file path. You’ll need to modify this location so that it points to where you’ve saved your Word template. Storing your Word template in a local file share is ideal for applications that run on a local network, and you’ll find out more about the pros and cons of using a local file share later in this section.

The next line of code uses the Bookmarks collection to locate the DepartmentName bookmark. It then inserts the screen entity’s DepartmentName property after the bookmark location by calling the Range.InsertAfter method . At the end of the process, the code shows the Word document 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 can send your Word document to a printer and discard it immediately afterward without saving changes. This allows you to easily send reports to a printer without any additional user intervention.  

Listing 14-9 shows the code that allows you to accomplish this. The Close method allows you to pass an argument that forces Word to either discard or save changes, or prompts the user to save their changes. Because we want Word to quit without saving any changes, passing 0 into the Close method allows you to do this.

Listing 14-9.  Printing and Closing a Word Document

VB:
File:HelpDeskVBClientUserCodeDepartmentDetail.vb
  
wordApp.PrintOut()
wordDoc.Close(0)
  
C#:
File:HelpDeskCSClientUserCodeDepartmentDetail.cs
  
wordApp.PrintOut();
wordDoc.Close(0);

Using the Mail-Merge Fields Specified in the Word Template

Let’s begin by creating a mail merge using the MailMergeTemplate.docx template. In this example, you’ll create a screen that includes a list of departments and add a button that mail merges the screen data with your Word Template file. Here are the steps to carry out this example:

1. Create a query that returns departments that have raised a high number of HelpDesk issues. (Refer to Chapter 6 if you need help on this.) If you just want to run this example, you can skip this step and base the next step on the Department table rather than a custom query.
2. Create an editable grid screen that’s based on this query, and name it DepartmentsWithHighIssues. The screen template creates a data grid of departments. Change the control from Data Grid to Data List.
3. Create a new GroupBox beneath the data list, and add a button to your screen. Name your button DoMailMerge. Figure 14-23 shows the design of your screen.

   

   Figure 14-23. Screen design

4. Enter the code shown in Listing 14-11.

Listing 14-11.  Mail-Merge Code

VB:
File:HelpDeskVBClientUserCodeDepartmentsWithHighIssues.vb
  
Imports System.Runtime.InteropServices.Automation
Imports System.Reflection
  
Namespace LightSwitchApplication
  
   Public Class DepartmentsWithHighIssues
  
      ' Declare Global Variables
      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 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(
             "\FileserverDocsMailMergeTemplate.dotx")                      
         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

      Private Sub CreateMailMergeDataFile()
  
         Dim wordDataDoc As Object
  
         'Specify a Location with write access
         Dim fileName As Object = "\FileserverDocsDataDoc.doc"             
  
         Dim header As Object =
            "DepartmentName, DepartmentManager, Address1"                     
  
         wordDoc.MailMerge.CreateDataSource(                                  
            fileName, missingValue, missingValue, header)
  
         ' Open the data document to insert data.
         wordDataDoc = wordApp.Documents.Open(fileName)
  
         ' Loop through the department screen collection
         ' Start at rowCount 2 because row 1 contains the column headers
  
         Dim rowCount As Integer = 2
         For Each d As Department In Departments                             
             FillRow(wordDataDoc, rowCount,                                  
                 d.DepartmentName, d.DepartmentManager, d.Address1)
             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)
  
         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)
  
      End Sub
  
   End Class
  
End Namespace
  
C#:
File:HelpDeskCSClientUserCodeDepartmentsWithHighIssues.cs
  
using System.Runtime.InteropServices.Automation;
using System.Reflection;
  
namespace LightSwitchApplication
{
    public partial class DepartmentsWithHighIssues
    {
  
        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;
  
        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(
                  @"\FileserverDocsMailMergeTemplate.dotx");                    
            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;
  
        }
  
  
        private void CreateMailMergeDataFile()
        {
            dynamic wordDataDoc;
            var fileName = @"\FileserverDocsDataDoc.doc";                       
            var header = "DepartmentName, DepartmentManager, Address1";            
  
            wordDoc.MailMerge.CreateDataSource(ref fileName, ref missingValue,     
                ref missingValue, ref header);
  
            // Open the data document to insert data.
            wordDataDoc = wordApp.Documents.Open(ref fileName);

            // Loop through the customer screen collection
            // Start at rowCount 2 because row 1 contains the column headers
            int rowCount = 2;
            foreach (Department d in Departments )                                 
            {
                FillRow(                                                           
                  wordDataDoc, rowCount,
                  d.DepartmentName , d.DepartmentManager , d.Address1 );
                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)
  
        {
            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);
        }
    }
}

The high-level overview of the process that’s shown in Listing 14-11 is as follows:

1. The code creates a Word document and populates it with data from your LightSwitch screen.
2. It uses your Word template file to create a new Word document, and sets the mail-merge data source to the document that you created earlier. It then carries out the actual mail merge.

The method that runs when the user clicks on the Mail Merge button is called DoMailMerge_Execute . This method creates a Word document by opening your MailMergeTemplate.dotx template file . It then calls a method called CreateMailMergeDataFile . This creates a data document called DataDoc.doc and sets the data source of your Word document to this data document . It does this by calling Word’s CreateDataSource method . This method expects you to supply the column headers for your data. The column names that you define must match the names of the mail-merge fields that you defined in the New Address List dialog. So in this example, these headings are called DepartmentName, DepartmentManager, and Address1 . The remaining code in this method loops through the departments in the Departments collection and calls the FillRow  method to populate the data document (DataDoc.doc).

Once the code populates the data document, the remaining code in the DoMailMerge_Execute method performs the mail merge by calling the Execute method on the wordMailMerge object . This completes the mail merge, and the remaining code tidies up the objects that have been declared. You can optionally add some code here to delete the DataDoc.doc file if you want.

Prior to calling the Execute method, the code sets the Destination property of the wordMailMerge object to wdSendToNewDocument. This represents the numeric value of 0 and forces Word to show the document to the user. Table 14-3 shows the other acceptable values that you can set.

This mail-merge code populates your data document by using your screen’s Departments collection. By default, this shows only 45 records at time. Therefore, you might want to increase this by changing the value of 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 modify your code so that it uses a query rather than a screen collection.  

Creating Mail Merge Fields in Code

The preceding example relies on a static Word template file (MailMergeTemplate.dotx). If you want to dynamically change your letter body contents, you can use Word automation to create a blank Word document. You would then build the content and merge fields of your document in code before performing the mail merge.

To demonstrate this technique, this example customizes your screen to allow users to specify the first paragraph that appears in the letter, and also adds a check box that allows users to specify the level of formality of the letter. To create these screen controls, open your DepartmentsWithHighIssues screen, click on the Add Data Item button and create the following two local properties:

• FirstParagraphProperty: Data Type: String, Is Required: False
• FormalityProperty: Data Type: Boolean, Is Required: False

Now modify the contents of your DoMailMerge_Execute method, as shown in Listing 14-12.

Listing 14-12.  Creating the Mail Merge Fields in Code

VB:
File:HelpDeskVBClientUserCodeDepartmentsWithHighIssues.vb
  
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
  
   ' 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 salutation and add the 'DepartmentManager' merge field
   If FormalityProperty.GetValueOrDefault(False) Then                         
      wordSelection.TypeText("Dear ")
   Else
      wordSelection.TypeText("Hi ")
   End If
  
   Dim wordRange As Object = wordSelection.Range

   wordMergeFields.Add(wordRange, "DepartmentManager")                        
   wordSelection.TypeText(",")
   ' add the paragraph text that the user has entered
   wordSelection.TypeText(FirstParagraphProperty)                             
  
   ' 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

End Sub
  
  
C#:
File:HelpDeskCSClientUserCodeDepartmentsWithHighIssues.cs
  
private void DoMailMerge_Execute()
{
    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 salutation and add the ' DepartmentManager' merge field
    If(FormalityProperty.GetValueOrDefault(false))                            
    {
       wordSelection.TypeText("Dear ");
    }
    else
    {
       wordSelection.TypeText("Hi ");
    }
      
  
    wordMergeFields.Add(wordSelection.Range, "DepartmentManager");            
    wordSelection.TypeText(",");
    // add the paragraph text that the user has entered
    wordSelection.TypeText(FirstParagraphProperty);                           
  
    // Perform mail merge.
    wordMailMerge.Destination = 0;
    wordMailMerge.Execute(false);
  
    // Release References.
    wordMailMerge = null;
    wordMergeFields = null;
    wordDoc = null;
    wordApp = null;
}

This code works in much the same way as the preceding example. The first difference is that it creates a brand new Word document rather than creating one from a template . It generates the data document just as before by calling the CreateMailMergeDataFile method . If the user has checked the Formality check box , the code sets the salutation of the letter to “Dear” Otherwise, it sets it to “Hi.” This is a perfect example of the type of conditional logic that you couldn’t apply if you used a static Word template. Immediately after the salutation text, the code adds the mail-merge field that allows the department manager name to be substituted in . It then adds the paragraph text that’s been added by the user . Figure 14-24 shows how the final screen looks.