Report Development

---

Now that you have data available in a SQL Database and a server set up to host reports in SQL Reporting, the next step is to develop reports. In this chapter, I introduce you to the tools you can use to create reports and explain the basic features that you can use in reports. In addition, I describe some differences you encounter if you decide to develop reports in Report Builder rather than BIDS or SSDT. Last, I show you how to work with the ReportViewer control if your goal is to develop a custom application for reporting.

Tools

The authoring process for SQL Azure Reporting uses the same on-premises tools that are available for SQL Server Reporting Services:

• Report Designer
• Report Builder
• ReportViewer control

If you already have Reporting Services in-house, then you can use Report Designer in Business Intelligence Development Studio (BIDS) if you are working with SQL 2008 R2 or SQL Server Data Tools (SSDT) if you are working with SQL Server 2012. If you do not have Reporting Services already, that’s okay, too. You can download SQL Server 2012 Express with Advanced Services at no cost and use Report Designer in SQL Server Data Tools to develop reports.

Another option is to download Report Builder, also at no cost. Like BIDS and SSDT, it provides support for all Reporting Services features. The difference between these products is that Report Builder allows you to work with only one report at a time, whereas BIDS and SSDT allow you to work with multiple reports as part of a project. On the other hand, if you decide to implement report parts, as I explain how to do later in this chapter, you must use Report Builder to create new reports based on those report parts.

One more option is to use the ReportViewer control in Visual Studio as part of a custom Windows or Web application. Just like BIDS, SSDT, and Report Builder, you can create an interactive, dynamic, and visually appealing report using a variety of layout options.

Report Development Fundamentals

Report development is a multi-step process. In this section, I describe how to use the Report Designer. Most of the functionality in Report Builder and ReportViewer is similar, but I explain the differences in those tools later in this chapter should you need to use one of those tools instead. After you select a tool, your next step is to acquire data for your report, so you create data sources and datasets to define where the data is and what you want to retrieve. Once you have the data, you can design the report layout. During this step, you specify how the data is structured in the report, add titles, header and footer text, and images. You apply formatting and configure any interactive features to control not only the look and feel of the report, but also the behavior of the report.

As you work, you can preview the report to ensure you get the desired results. When you finish, you deploy the report to SQL Reporting. Because report development is a very iterative process, you are likely to preview the report and then return to working on the report layout to fine-tune the report design multiple times until you get the results you want.

Report Projects

As already mentioned, the report designer is the report development tool of choice for most professional report developers. The advantage of working with it rather than Report Builder is the ability to work with multiple reports as part of a project. When you create a new project, you can choose to create one of the following project types:

• Report Server Project Wizard
• Report Server Project

For quick development of a basic report, you can use the Report Server Project Wizard. You use the wizard interface to step through the process of defining the data source, defining a query, and selecting a layout from a few options. Because you can make modifications to a report after using the wizard, it’s an easy way to start simple reports.

The other project type is the Report Server Project. This is the project type that you select most frequently for report development. In this chapter, I use the Report Server Project type to explain report development fundamentals.

Project Items

After you create a project, you then add items to it. As you add items, they are visible in the Solution Explorer window, as shown in Figure 7. The report server project supports three different types of items:

• Data Source
• Dataset
• Report

7. Report project items in Solution Explorer

The data source is stored as an RDS file in the project directory on your computer and is uploaded separately to SQL Reporting when you deploy the project. You can create one or more data sources for use with a report. When a data source is created independently of a report, it is known as a shared data source and available for you to use with any report. That way, any changes to data source settings can be made once for multiple reports.

The dataset is stored as an RSD file, and is also uploaded separately. Like data sources, you can use one or more datasets with any report and a change made to the shared dataset affects all reports.

The third item type is the report, which is stored as an RDL file. Although it’s possible to have a project without data source and dataset files, you will always have at least one report file in your project.

To add a project item, right-click on the applicable folder in Solution Explorer, and use the applicable command, such as Add New Data Source or Add New Dataset. However, if you choose Add New Report from the Reports folder, you launch a wizard. To bypass the wizard and create a blank report, point to Add after you right-click the Reports folder and select New Item. In the Add New Item dialog box, select Report and replace the default name, such as Report1.rdl, with a more suitable name. Be sure to retain the RDL file extension in the name.

Project Properties

You must configure project properties before deployment if you plan to use Report Designer to deploy your reports to SQL Reporting. These properties are used only by the Deploy command. At deployment, these properties identify target locations for your various project files on the SQL Reporting server. Many of the project properties have default values and refer to folders to use for storing project items. For example, your shared datasets go to the Datasets folder, and shared data sources go to the Data Sources folder. You can change these target folders to an alternate path if you like.

Right-click the project name in Solution Explorer and select Properties to open the project properties, shown in Figure 8. Notice the TargetReportFolder defaults to the name of the current project, which in this case is Reports. You might prefer a different folder name for your reports than the project name, so be sure to change this value if necessary. Also, you must specify the TargetServerURL for the report server before you can deploy reports. It will be empty in a new project, and the project won’t deploy until you provide a value for this property. Use the Web service URL that displays in the SQL Reporting portal, such as https://xyz123.reporting.windows.net/ReportServer.

8. Report project properties

Data for Reports

As part of the data acquisition process, you define data sources to provide instructions to the SQL Reporting server about where to locate data to be used in the reports and how to authenticate the requests for the data. You also need to create datasets to define the columns to retrieve from the data sources for placement in your report.

Data Sources

To display data in your report, you need at least one data source. A Reporting Services data source includes a data provider, a connection string, and credentials to use for authentication. Because SQL Reporting only supports SQL Database as a source, you must select Microsoft SQL Azure in the Type drop-down list, as shown in Figure 9. The example in Figure 9 illustrates a shared data source, which you create by right-clicking the Shared Data Sources folder in Solution Explorer, but you can also create an embedded data source in the Report Data pane as I explain later in this chapter.

9. Shared data source for SQL Database

The format of the connection string to SQL Database looks like this:

You enter the login name and password on the Credentials tab of the Shared Data Source Properties dialog box, shown in Figure 10.

10. Credentials for data source connection

There are two different types of data sources:

• Shared data source
• Embedded data source

Typically, you create a shared data source to define the connection information once for use with many reports. If connection information changes later, such as when you move a source database to a different SQL Database server, you can update the data source definition once to update all associated reports.

Your other option is an embedded data source. In this case, you don’t create a separate data source file for the project, but instead create a data source definition inside of each report’s RDL. The advantage of using an embedded data source is that you can use dynamic connection strings. The disadvantage is that if you need to make a change to the connection string, you must open every report and edit each connection string, which can be a very tedious process if you have a lot of reports.

Note: If you decide to use an embedded data source, be aware that the only way you can change credentials is to open the report in the report designer or Report Builder, edit the credentials, and then redeploy the report. The management portal does not support changes to embedded data sources.

Regardless of the type of data source you decide to use, you must add a reference to the data source in your report. To do this, open the report and then, in the Report Data pane, right-click the Data Source folder and click Add Data Source. Here you can choose the Embedded Connection option and then define the connection string and credentials or the Shared Data Source Reference option and then select the applicable data source in the drop-down list, as shown in Figure 11.

11. Shared data source reference added to report

Tip: Consider naming the data source to match the shared data source name or the name of the database in an embedded connection to make the data source more easily identifiable in your report.

Datasets

After creating a data source definition, your next step in the report development process is to create a dataset. In simple terms, a dataset is the query used to get data for the report. However, a closer look reveals that it contains a description of the fields or table columns to retrieve from the data source, among other information. During report development, you use these collections of fields as placeholders for data in your report.

There are two types of datasets you can create:

• Shared dataset
• Embedded dataset

If you need to use the same query repeatedly with many reports, then a shared dataset is the better option, much like you can use a shared data source with many reports. That way, you can make any necessary changes to one file only. When you create a shared dataset, the output is an RSD file that is added to your project.

An embedded dataset, on the other hand, is associated with one report only. The dataset definition, although similar to the contents of an RSD file, is embedded directly within the report’s RDL file.

Whether you use a shared or embedded dataset, you must include it in your report. To do this, open the report and then, in the Report Data pane, right-click the Dataset folder and click Add Dataset. Here you can choose the Embedded Connection option, as shown in Figure 12, and then define the query type and query string or select the shared dataset option and then select the applicable dataset in the drop-down list.

12. Report dataset properties

If you select Text as the Query Type option, you can type the query string directly into the query box or click the Query Designer to open a simple query designer. Even if you open the query designer, you can click a button to switch to a text editor. Either way, you will find it helpful to open this separate dialog box because it allows you to execute a query to confirm both that it’s valid and that you get the results you need. At this point, the Fields collection for your dataset is generated to match the data columns that you see in the query results.

You can also use the Stored Procedure option for the Query Type. When you choose this option, the query box is replaced with a drop-down list from which you select one of the available stored procedures.

As an alternative to providing the query text or selecting a stored procedure, you can click Import and locate a SQL script file or an existing RDL. With this option, you can reuse an existing query by importing it from another file. However, any changes made to the original query will not update the dataset into which you imported it.

Another important property to configure in the dataset is the Time Out property. By default, it is set to 0, which means the query will wait indefinitely for the data source to return data. If you want to put a time limit on the query, and fail the report if the data source is taking too long, you can put in the maximum number of seconds that you want to wait as the timeout value.

Report Items

You spend the majority of your report development time working with report items in the design layout. Report items are the objects that you position on reports, such as text boxes and images. Some report items are independent of data, while others, known as data regions, require an association with a dataset in the report.

To add a report item to your report, open the Toolbox window and drag the item to a specific position on the report. You can drag edges of the item to resize it if necessary, or select the item and then set the Location and Size properties in the Properties window for greater precision. Of course, there are other properties available to configure, but these vary by report item type.

Independent Report Items

Each of the following independent report items is shown in Figure 13:

• Text Box. Use for report titles or freestanding labels.
• Line. Create visual separation between items on a report.
• Rectangle. Put a border around one or more objects or use as a container to keep objects together on a page.
• Image. Display a logo on a report, use as a watermark on a page, or display in a data region if the image is part of a dataset, such as product images stored with product data.
• Subreport. Build a separate report and then embed it in other reports as a subreport.

13. Independent report items

Data Regions

The following data regions, shown in Figure 14, present the data just as you see it in the query results with optional summarization:

• Table. A table has a fixed number of columns. The number of rows will vary according to your query results. Although this table doesn’t show it, you can also add grouping and subtotals to a table.
• Matrix. A matrix has a variable number of rows and columns. You can also add grouping and subtotals to a matrix layout.
• List. A list is a free-form area that repeats its contents according to a grouping that you’ve defined. In Figure 14, you can see a table and a chart inside of a list, grouped by sales territory. In other words, there is one instance of the table and chart for Europe and another instance of the table and chart for North America due to the grouping configured for the list.

14. Data regions

• Chart. Many different types of charts are supported, such as column, bar, pie, and scatter charts, to name a few. You have the ability to configure the properties of each chart element, such as the axis titles; whether to include a legend and how it appears; colors for the series; and so on.
• Databar. Although a databar is not required to be placed inside a data region, more often it is added to a table or matrix as an inline chart to display a single data point in chart form.
• Sparkline. A sparkline is similar to a databar in that it is commonly used as an inline chart. However, the sparkline plots multiple data points, such as sales by month of year.

• Gauge. A gauge is useful for visual comparisons of a value, as shown by a pointer, to a range such as a red zone.
• Indicator. You can use an indicator as another way to compare values to goals, using a variety of shapes or colors, and even your own images if you prefer not to use the available icons.

Graphical data regions use data visualization techniques to summarize the data for comparative purposes or pattern discovery, as shown in Figure 15.

15. Graphical data regions

Another special type of data region is the map report item that you use for spatial data visualization, as shown in Figure 16. You can create a map containing any or all of the following types of spatial data:

• Point. Each point represents a coordinate on a map. You can optionally use Bing integration to bring in street-level detail.
• Line. Lines are pairs of points. As an example, you might use multiple lines to show routing information.
• Polygon. A polygon is a series of points for which the first and last points are the same.

16. Spatial data types in maps

Now let’s take a closer look at working with data regions. Although the data regions are listed separately in the Toolbox window as Table, Matrix, and List, they are actually the same structure, known as a Tablix. The difference between them lies in the definition of detail rows, row groups, and column groups.

On the other hand, the placement of fields in each type of data region is similar. You can drag a field from the dataset list in the Report Data pane and drop it into a text box in the data region. Another way to place fields is to point to a text box, click the Field List icon that is displayed, and select a field.

When working with data regions, you can also group data. In fact, the matrix allows you to work only with groups instead of detail rows. You can drag the field to use for grouping from the Report Data pane to the Row Groups or Columns group pane that displays at the bottom of the report designer, as shown in Figure 17.

17. Groups

Table

A table is simply a tabular collection of text boxes. The number of columns it contains always remains static, but the number of rows in the table will vary by the number of rows that are returned by the query. These are called the detail rows, as shown in Figure 18. When you work in preview mode, you can see the detail row in the center of the table data region with three horizontal lines in the gray row handle on the left edge of the table.

18. Table layout

For each row in your query results, a corresponding detail row appears in the rendered table when you switch to the Preview tab. Optionally, you can include header and footer rows for the table and for groups. In fact, you always have a table header row at the top of the table, unless you decide to delete it, and it appears only once. A table footer is an optional addition to your table, and like the table header, it appears only once after all detail rows and any group rows have rendered.

If you add a group to the table, you can include a group header, which appears once for each instance of the group before the detail rows belonging to that instance. Similarly, we can add a group footer row, which displays after the detail rows belonging to the respective group. In the example shown in Figure 18, the detail rows group by year, with a group header displaying the year and a group footer displaying the subtotal of sales for the year.

Matrix

A matrix is a crosstab collection of text boxes displayed in a dynamic arrangement of columns and rows, like a pivot table in Excel. The arrangement is dynamic because the number of columns and rows can vary in each rendering of the matrix when your dataset returns different results, either because the data has changed in the source, or because you have applied a filter. For dynamic row groups, the matrix will display one row per instance of the group defined. Similarly, for dynamic column groups, you’ll get one column per instance defined for column groups. The matrix shown in Figure 19 has only one group on columns and one group on rows, but you can create multiple groupings on both columns and rows.

19. Matrix layout

When working with a matrix, you must use an aggregate function with the field you place in the text box at the intersection of a row and column group. Incidentally, the text box in the top left corner of the matrix can contain static text, but its use is optional. Another optional feature is the addition of subtotal rows or columns by group.

List

The list is a freeform collection of any report item that you want to repeat as part of a group. This collection is usually a combination of text boxes and data regions. A list supports one group only, but you can put a list inside of a list if you need to create a freeform layout with multiple groupings. Figure 20 illustrates a list that contains a text box, a table, and a matrix that are all part of one group. In the rendered report, each combination of text box, table, and matrix repeats once per instance of the defined group, which in this example is sales territory.

20. List layout

Expressions

Expressions provide added flexibility in your reports by allowing you to modify data from your dataset or to adjust the setting of a property at the time of report execution. In this section, I start with an overview of the possibilities that expressions enable, explain how you can use placeholders in simple expressions to more easily interpret the report design layout, and review the global collections that you can reference in expressions. I also show you how to use the Expression Editor to create more complex expressions and explore some of the functions that you can use in expressions.

Expression Usage

There are two types of expressions in Reporting Services:

• Simple. A simple expression references an object and usually a property of that object. For example, placement of a field into a text box automatically generates an expression to display the Value property of a particular field in the Fields collection of a dataset looks like this:

• Complex. Complex expressions perform a calculation using Visual Basic .NET syntax. For example, an expression that returns a total of the value of a field for each detail row in a dataset looks like this:

As you develop reports, you are likely to use expressions in many different ways:

• Dynamic connection string. Although this is not the most common way to use expressions, this approach does allow you to generate a connection string at run-time so that you can specify the conditions under which one connection string might be used versus another. However, the downside of using dynamic connection strings is that you can use it only with an embedded data source, which means you could have some administrative overhead when you need to make changes to the expression.
• Dynamic dataset. You can use an expression to create a query string at runtime, perhaps based on parameters provided in the report. A dynamic dataset might use completely different sources in the FROM clause or modify the JOINS to the query in different ways depending on the conditions at runtime.
• Calculated field. You can create scalar calculations with expressions. A scalar calculation resolves as a separate value for each row in the dataset. The calculated field displays in the Report Data pane along with fields generated by the dataset. You can then add a calculated field to a data region as if the field came from the data source. That way, when you use the same calculation multiple times in your report, the definition is stored in one place and you can reference the field as many times as needed. Furthermore, if you need to make a change to the expression, you have only one place to make the change.

Tip: Generally it’s better to put a scalar expression in the query. However, sometimes you might not have access to the query directly, such as when you use a stored procedure in your dataset.

• Text box calculation. The most common reason to use expressions is to use the results of a calculation to display a value in a text box.
• Filter. The use of an expression in a filter is often used in combination with a parameter, although a filter does not require a parameter. For example, you can use a filter expression that calculates today’s date and then filters the data from a query to display only values for today.

Placeholders

There are many expressions that you use in reports that are considered simple expressions. Simple expressions refer to a single item and display in a text box in design mode as a placeholder so you can easily see what the text box contains during report development. Prior to SQL Server 2008, only a portion of the expression in a text box was visible. To see the entire expression, you were required to open the Expression Editor. Now you can use placeholders with the following collections:

• Fields. The Fields collection is part of a dataset and stores the values retrieved by a query. When you drag and drop a field from the Report Data pane into a text box, the report designer automatically creates a placeholder that includes the field name and encloses it in brackets. In other words, when you add the CalendarYear field to a text box, you see [CalendarYear] display in the text box instead of the actual expression stored in the RDL, =Fields!CalendarYear.Value. You can also use placeholders with aggregate functions, which the report designer creates when you use the Add Total command in a tablix.
• Parameters. The Parameters collection allows you to reference report parameter selections in an expression. I explain more about parameters later in this chapter – specifically, report parameters. If your report includes a report parameter named BusinessType, you can refer to it by using a placeholder that uses the @ symbol as a prefix like this: [@BusinessType].
• Globals. The Globals collection contains a variety of fields that are commonly used in reports, such as execution time or page number. You use the & symbol as a prefix like this: [&PageNumber].

Any other type of expression is a complex expression, and the report designer displays a generic placeholder, <<Expr>>. The only way for you to know what it represents is to open the Expression Editor to view the underlying expression.

Expression Editor

It’s usually not practical to type expressions directly into a text box or to set a property value. Instead you use the Expression Editor to use as a graphical interface. You can right-click a text box and click Expressions to open the editor, click the Expression button, or select Expression in a property’s drop-down list, as shown in Figure 21.

21. Opening the Expression Editor

In the box at the top of the Expression Editor, shown in Figure 22, you can type your expression directly. You can also use the following lists in the bottom part of the editor to display items that you can use to build an expression:

• Constants. This list contains values only when you are editing an expression for a property that has constants. For example, if you are adding an expression for the Color property, this list includes a set of colors, such as blue, red, or green.
• Built-in Fields. This list contains items found in the Globals collection, such as ReportName, PageNumber, and TotalPages.
• Parameters. If your report contains report parameters, their names appear in this list.
• Fields. Here you find the fields in the dataset associated with the text box or property to which you’re adding the expression. This list is empty otherwise.
• Variables. If you create report variables, you see them in this list. Group variables, on the other hand, do not display here.
• Operators. This list displays all operators available for mathematical, logical or string operations, among others.
• Common Functions. Having a list of functions is helpful, especially if you’re not familiar with the available functions or can’t remember a function’s syntax. The functions are grouped logically by operation. For example, string manipulation function, arithmetic operations, and aggregate functions are organized as separate groups.

22. Expression Editor

Expression Examples

There are many ways to use expressions. In this section, I explain a few of the more common examples.

First, if you prompt the user to make a selection by using a report parameter, it’s considered a best practice to display the label of the selection somewhere in the report. As you learn later, you can set up a report parameter to allow the user to select multiple values. In that case, you need a way to make one string out of all the values. To do this, you can use the Join function, reference the Parameters collection, and specify a delimiter to combine values that are stored in an array, like this:

When you use a multi-value parameter, the values and labels are stored in a zero-based array, and you must reference a single item by using an index, like this:

Another common operation is concatenation of string values. For example, you might combine a field value with a constant string to use as a label in a text box, like this:

Tip: When concatenating values, you need to be sure that all the items are strings. Use conversion functions if necessary.

Conditional formatting is a great way to create dynamic and visually informative reports. Any property that accepts expressions is a candidate for conditional formatting. For example, you can set the Color property of a text box based on whether the sales amount for the group of rows in scope is above a specified value like this:

Collections

The Datasets collection allows you to access information about the datasets that are included in a report. From a user perspective, this isn’t a particularly useful collection, but it can be for developers who need to troubleshoot report problems, particularly when using dynamic datasets. You can add a text box that displays the command text and see exactly what query is being sent to the data source by using the command text property as shown here:

Likewise there’s a DataSources collection that’s not commonly used except for troubleshooting purposes. You can use it to check the reference to a data source like this:

ReportItems is a collection that is not displayed in the Report Data pane or in the Expression Editor. However, it can be useful when you need to reference the value in a particular text box that in turn may be the result of a calculation. Rather than copy and paste that calculation multiple times, you can define it once and then use the ReportItems collection to reference it. Another important use for the ReportItems collection is access to a text box value after a page renders. SQL Reporting renders a page by preparing the report body first and then the page header and footer. After the report body renders, the dataset is no longer available. However, you can use the ReportItems collection in an expression to access a text box value that you want to display in the page header or footer, like this:

The User collection is a special collection that you use to set an expression based on the current user. You can use User!UserID in an expression to get the current user’s Windows login when you want to apply data-level security. When you need to create multilingual reports, you can set report properties based on the user’s regional settings by using User!Language in an expression.

You can create a variable to store a value at the report level or at the group level and then use the Variables collection to retrieve the current value of the variable at report execution time. The value you store in a variable can be a constant value or an expression. You might use a report variable as part of an expression that you use to display the execution time because an expression in the report evaluates only when the page renders, but a report variable doesn’t change. That is, if you put the expression Globals!ExecutionTime in a page footer of a multiple page report, you find a different value on each page of the rendered report. However, if you store Now() in a report variable called ReportExecutionTime, and then put the expression Variables!ReportExecutionTime.Value in the page footer, you see the same value on each page of the rendered report.

You can also create a variable with group scope. A group variable evaluates when the group instance changes, so it can potentially change multiple types on the same page of a report. You use a group variable when you need to have a value that is unique to the current group.

The Globals collection is useful for displaying information that is specific to your report, such as the execution time or the report server URL. You can use an object in this collection in any text box in your report, but you can use the page-related objects, such as Globals!PageNumber or Globals!TotalPages in the page header or footer only. In the Report Data pane, the Built-in Fields folder contains eight fields that you can use to quickly add a Globals object to a text box. If you need to change the appearance of your report based on the current rendering, such as hiding a report title if you render a report to Excel, you can assign an expression like this to the Hidden property of a text box:

Aggregate Functions

You use aggregate functions primarily to summarize many records of numerical data and display a value, such as a total or average. You can also use aggregate functions to get the first or last string value in a group of records. An aggregate function uses a scope argument to determine which detail rows to include in the aggregation. It can refer to group scope, the scope of a data region, or a dataset.

If you omit the scope argument when using an aggregate function, the scope is inferred by the location of the expression. If the expression appears in a text box in a group or a property of that text box, the scope is limited to the dataset rows associated with that group. However, if you want to use a scope other than the inferred scope, you must specify the scope explicitly in the aggregate function by placing the scope name in double quotes, like this:

Any reference to a group, data region, or dataset must match the actual case used in the name, or the expression fails. You only know if the expression succeeds or fails due to a case mismatch if you attempt to preview the report, since no warning displays in the Expression Editor to alert you to the mistake.

Report Parameters

You use report parameters to accept input from a user. At report execution time, the user is prompted to provide the report parameter value. You can supply default values so that the report executes first but allows the user to execute the report again after changing a report parameter value. You can also leave the value empty and force the user to make a selection or type a value before executing the report.

A common way to use this input is to filter data by filtering one or more data regions or by passing the user’s selection to a dataset’s query parameter. You can also use report parameters in expressions. For example, you can use parameters to prompt the user for information that determines how the report should appear, such as sorting of rows or visibility of columns.

There are three ways that a user can provide input for a report parameter value:

• Available values. With this option, the user makes a selection from a drop-down list. You can restrict the user to one selection, or you can allow the user to select multiple values.
• No value. You can require the user to type a value. However, apart from data type checking, there’s nothing in Reporting Services that you can use to validate the input. If you prompt the user for input in this way, you should not use the value directly within a SQL query because of the potential for a SQL injection attack. If you must use an input value like this as input for a query parameter, such as when you have a value like an invoice number that is not well suited for a drop-down list, you should always use a stored procedure for the dataset so that you can perform validation on the input value before using it in a query.
• Date value. When you set the date data type for a parameter, the user can either type the value directly or use the calendar control to select a date.

To add a report parameter to your report, right-click the Parameters folder in the Report Data pane, and then click Add Parameter. At minimum, you need to specify a name for the report parameter, provide a prompt that the user will see in the report viewer, and specify a data type, as shown in Figure 23.

23. Report parameter properties

By default, the report parameter is configured without available values, which means that the user must directly enter a value. You also have the option to allow a blank value or a null value. A blank value is interpreted as an empty string.

If you do provide available values, the default setting is to allow the user to select only one value at a time from the list, but you can enable the option to allow the user to select multiple values as shown in Figure 23. However, if you enable the multi-value option, you cannot allow null values.

Typically, report parameters are visible to users. Use the Hidden option if you don’t want the prompt to display when users view the report, but need the ability to set the parameter value on the report server using URL access or when creating a subscription. Use the Internal option when you need to use a parameter value in a report, but set the parameter value without user interaction. For example, you can get a parameter value by evaluating an expression or retrieving a value from a dataset, and bypass the need to prompt the user.

When you want the user to select values from a drop-down list, you must configure the Available Values for the report parameters. On the Available Values page of the report parameter properties, you have the following three options:

• None. By default, the None option is selected, which forces the user to directly enter a value.
• Specify Values. You can enter a static list of valid values for the report parameter. Value is the default passed to a parameter expression and to a query parameter, whereas Label is the text that is displayed in the report parameter’s drop-down list when the user opens the report. For both Label and Value, you can specify an expression rather than a static value.
• Get Values From a Query. Whenever possible, you should use a dataset to provide a list of available values for a report parameter. Ideally, your dataset should reference a view in your data source. Then, if you need to add or remove items in the list, you can manage the change in the data source and can thereby avoid the need to edit the report parameter list in each report using the same list. When you use a dataset to supply report parameter values, you typically have two columns in the dataset, one for the parameter value and one for the parameter label. You can, however, use a single column as both value and label, as shown in Figure 24.

24. Report parameter available values from a query

On the Default Values page of the report parameter properties, you again have three options:

• No Default Value. By default, this option is selected, which means the report does not render until the user selects a value.
• Specify Values. You can also provide a default value to allow the report to execute before the user selects a parameter value. Just as with the available values, you can use expressions to specify default values instead of using static text.
• Get Values From a Query. You can create a dataset that sets the default value. If the report parameter is configured to accept only a single value, the dataset must return only a single row. A multi-value report parameter can accept multiple rows. Either way, the field that you reference must contain a valid value as defined by the value column on the Available Values page.

Filters

One way to change the contents of a report dynamically at report execution time is to use a filter. Although you can create a filter without a report parameter, the most common way to configure a filter is by using a report parameter that prompts the user for the filter value. When you use a report parameter with a filter defined for a report element, the report filter has no effect on the data retrieved from the source. That means any change to the filter value resulting from a new report parameter value selection affects what you see in the report, but does not cause a new execution of the dataset’s query to retrieve different data.

When you configure a filter, you define an expression that determines the condition that must be met in order for data to be visible in the report. If you’re creating a dynamic filter based on user input, you can use a report parameter to store the user’s selection and then reference that parameter in the filter condition.

There are different ways to add a filter to your report:

• Dataset. Because a filter is applied to the dataset rows after the query executes, even if you apply the filter to the dataset, the time to render the report after selecting a new report parameter value is quite low. When you want to both minimize the number of query executions against the source database and maximize report performance when changing report parameter values, use a report filter on the dataset. To add a filter to a dataset, double-click the dataset in the Report Data pane, and then open the Filters page of the Dataset Properties dialog box.
• Data region. When you need access to all data in some data regions and filtered data in other data regions on the same report, use a report filter on the applicable data regions instead of filtering the dataset. To add a filter to a data region, select the object in the drop-down list at the top of the Properties window, click the Filters property box, and then click the ellipsis button that displays.
• Group. A less commonly used option is applying a filter to a group. To do this, double-click the group in the Row Groups or Column Groups pane, and open the Filters page of the Group Properties dialog box.

Regardless of where you apply the filter, the process is similar. You compare one value, such as a field from the dataset, to another value, such as a report parameter value. However, any valid expression can be used for the comparison.

Most of the time, the operator you use for comparison is an equal sign, but when you have a multi-value report parameter, you use the In operator instead, as shown in Figure 25.

25. Filtering a data region

You can use other operators for comparison, as shown in Table 3.

Filter Operators

Query Parameters

You can use a query parameter to filter data at the source. You might want to do this when it’s important to reduce the amount of network traffic caused by returning a dataset’s query results to a report. Other reasons include a need to reduce the amount of time required to render a report or security requirements.

You start by adding a query parameter to the WHERE clause of your dataset query like this:

By default, the report designer adds a matching report parameter to your report and associates the query parameter with the report parameter. You can see this association on the Parameters page of the Dataset Properties dialog box. You are not required to use a report parameter, though. You can delete the report parameter and then associate the query parameter with an expression, such as the date or the current user.

Tip: It’s possible that a query parameter value produces a filter that does not return any data from the source. When that happens, the data region associated with the dataset is not displayed in the report, and it can be confusing to the user to see a report title without anything else. To clarify the situation, you can use the NoRowsMessage property of a data region to display a message.

Subreports and Drillthrough

Another way to use parameters is to share data between reports. You can create relationships between reports, either by using a subreport to display one report inside of another report or by using a drillthrough report to allow the user to click on a text box in one report to open another report.

To use this feature, you can configure a report to pass the current context to the associated subreport or drillthrough report. The current context comes from data available for the current scope. It might be a field from the dataset, a custom expression based on a field, or a report parameter. In the case of a drillthrough report, the current context could also come from a text box that the user clicks on.

To implement this feature, the subreport or drillthrough report must have a parameter already defined in the report. After you add a subreport to your report, right-click it and then select Subreport Properties, select the report to add, and go to the Parameters page of the dialog box. If you are building a drillthrough action instead, right-click the text box to use as a link to the report, click Text Box Properties, go to the Action page of the dialog box, select the Go To Report option, and select the report to use for drillthough. In either case, click Add to add a row for defining the parameter to pass, and then select that parameter in the Name drop-down list. In the Value field, you configure the expression that passes into the parameter in the target report. In Figure 26, a field in the current report will pass to the drillthrough report.

26. Configuring a parameter for drillthrough

The Omit option for the parameter shown in Figure 26 is applicable only to drillthrough reports. Use it to define an expression that must evaluate to True when you want to disable passing the parameters.

Interactivity

Reporting Services has many interactive features that you can add to reports to enhance the online viewing experience for users. Some of these features affect the report layout such as interactive sorting, fixed headers, tooltips, and visibility. Other features are useful as navigation aids, such as a document map, actions, and embedded HTML.

Interactive Sort

When you design a report, you can define the sort order of the data by including an ORDER BY clause in the dataset or by setting the Sorting property on the tablix or group. You can also add a parameter to the report to prompt the user for the sort direction and use the input to set the Sorting property. However, another option is to configure a text box for interactive sort. To do this, right-click the text box, select Text Box Properties, go to Interactive Sorting, and select the check box to enable this feature.

Usually, the text box to which you add interactive sorting is a column header or row header in a data region. As shown in Figure 27, you can configure each column header in a tablix to use interactive sort, as indicated by the arrow icons in each text box. The user can sort one column at a time. The first time that a user clicks on a column, the data sorts in ascending order with the smallest numbers in the top rows, and the highest numbers in the bottom rows. If the data is organized in groups, the detail rows are sorted in groups. A second click on the column sorts the data in descending order.

27. Interactive sorting

The interactive sort feature is not limited to placement in column header rows or to sorting only detail rows. Rather than sort the entire tablix, you can also define the sort to apply only within a specific group by adding interactive sort to a text box within the data region and then limiting the scope to the detail rows within that group. Or you can apply the sort to entire groups rather than to the detail rows.

Fixed Headers

If you have a report that doesn’t fit completely on the screen, the user must scroll to view the information at the bottom of the page. However, a user can lose track of which information is contained in which column by the time the bottom of the page is reached. To address this problem for vertical scrolling, you can select the tablix in the Properties window, and then set the FixedColumnHeaders property to True. You can change the FixedRowHeaders property when your report requires horizontal scrolling.

Tip: When you use either of the fixed headers properties, it’s important to change the BackgroundColor property of the row or column. If no color is defined, the background is transparent, and the user will see the data scrolling behind the text in the fixed headers. When you add a color, the data will no longer be visible during scrolling.

Tooltips

You can use a tooltip to display a message when the user positions the cursor over an item in the report such as a text box, as shown in Figure 28. You can use the tooltip to display other data from the dataset that isn’t visible in the report or the result of a calculation, like the percentage in this example. Select the item in the report, and then click Expression in the drop-down list for the Tooltip property in the Properties window. You can then type static text in double quotes or add an expression that resolves as a string value.

28. Tooltip

Visibility

The visibility feature is not limited to online viewing, but the interactive aspect of it is. A common reason to use this feature is to include both summary and detail data in a report. Figure 29 is an example of a tablix that contains a group row for Sales Territory Group and child rows for Sales Territory Country.

29. Using visibility properties to toggle the display of detail rows

To create the appearance of a drilldown report in which only the summary data is displayed when the user opens the report, use the visibility properties Hidden and ToggleItem. Visibility can apply to an entire row, an entire column, or a specific text box. Select the text box or click the column or row handle and then, in the Properties window, set the Hidden property to True. In the ToggleItem property’s drop-down list, select the name of the text box in which you want the toggle switch icon to display.

Document Map

If you have more than one group defined in a tablix report item, you can add the document map feature as a navigation aid for a long report. It works like an interactive table of contents for the report that allows the user not only to see the group instances at a glance, but also to jump to the location of a specific instance. When the report includes a hierarchy of groups, the document map displays the list of bookmarks in a tree form. The user must expand a specific group instance to view the instances of groups contained by the selected group, as shown in Figure 30.

30. Document map

For each instance in a group, you define the expression to display in the document map. This expression becomes the equivalent of a bookmark and is displayed next to the report. To define this expression, double-click the group in the Row Groups pane and go to the Advanced page.

Note: The document map works not only for reports rendered in HTML, but also for reports rendered in Excel and PDF.

Actions

Each text box and image has an Action property that you can use to define a target location for display when the user clicks the text box or image. Select the item in the report body, click the Actions property box in the Properties window, and then click the ellipsis button. You can implement the following three types of actions:

• Go To Bookmark. A bookmark is useful for jumping from one section of a report to another. Consider a scenario in which you have a tablix on one page of a report, and a list containing charts spread out over multiple pages. You can set the Bookmark property of the chart using an expression that produces a unique name for each chart in the list. Then, configure an action on a text box in the tablix that uses an expression based on the value in the text box the user clicks to generate the bookmark target. At run time, clicking the text box causes the report to jump to the applicable chart.
• Go To Report. To configure this action, you define a target report. Optionally, you can map expressions in the parent report to parameters in the target report. In that case, the target report can display information using the same context as the source report if the target report is designed to filter based on report parameter values.
• Go To URL. This action requires the user to click on a text box in the report and that in turn causes Reporting Services to open the default browser using a target URL as the address. You can provide a static URL or use an expression that resolves as a URL.

Embedded HTML

Another way to work with URLs and other types of HTML markup is to configure text as HTML markup, also known as embedded HTML. To use this technique, you start by using an expression in a text box that resolves as a valid hyperlink. This expression can come directly from the dataset, or can be derived as a complex expression in the text box.

You can use HTML markup tags listed in Table 4, such as FONT, DIV, LI, or B, to control the report layout, but the A HREF tag in particular is useful for navigation. For example, you might have a report that displays email addresses. When you include the HTML tag in the string expression for the text box, the report renders the HTML tag as text. To render the expression as HTML, you must select the text in the text box rather than select the text box, and change the MarkupType property to HTML in the Properties window. The report then renders the string as a link, and the user’s email client opens when the user clicks the link and creates a new message addressed to the selected recipient.

HTML Markup Tags

Pagination

You can configure properties in your report to define page size, margin, and orientation. By default, the page size is set to 8.5 inches wide by 11 inches high, which produces a portrait orientation, but you can change it to landscape by setting the Width property to 11 inches and the Height property to 8.5 inches. To access these properties, select Report in the drop-down list at the top of the Properties window.

Although you can configure the height and width of the report, there are other properties that affect the distribution of report items on each page of the report. Margins, body size, and the presence or absence of page headers and page footers each have an effect on the overall page size, as shown in Figure 31. The renderer determines the page size by subtracting the margin sizes from the height and width defined for the report. Then within that area, the render allocates space to the page header and page footer, if you use them, and the remainder is available to the body of the report.

31. Page sizing

When you select Report in the Properties window drop-down list, you can set the Width and Height properties as well as the properties for the margins: Left, Right, Top, and Bottom. If you right-click in the report designer outside the report body, you can click the command to add a page header or page footer and then select these items in the Properties window to adjust the Height property. Additionally, you can select Body to set Width and Height properties.

Tip: If the body size plus the margins exceeds the page size, the report contains an extra blank page between pages containing report content when you print it or export to PDF or TIFF format.

Another factor affecting page size is the white space in the body of your report. If the body size is wider than the report size, blank pages appear in your printed report. This problem might not be evident on the report design surface. For example, in a matrix, where columns are dynamic, the white space to the right of a column group is included in the report after all the columns are rendered resulting in blank pages, even when the matrix itself fits on one page. To disable this behavior, select Report in the Properties window and change the value of the ConsumeContainerWhiteSpace property to True.

By default, SQL Reporting attempts to fit as much of a data region onto a page as it can before creating a new page, known as a soft page break. However, you can insert hard page breaks relative to a data region or when a group instance changes within a data region. A rendering extension might continue to insert soft page breaks if the location of the page break that you add results in a page size larger than the report properties allow.

Page Breaks

You can create page breaks on most report items. To do this, select the data region to which you want to add a page break, and then expand the PageBreak category in the Properties window, as shown in Figure 32, to set the following properties:

• BreakLocation. Set the location of the page break relative to the selected report item: None, Start, End, or Start And End. You can set the break location at the start of a report item to force a page break before the report item renders. Or you can set the break location at the end of a report item. This might not make sense if nothing appears in the report after the report item that you set the page break on, because you’ll end up with an extra blank page at the end. You can also have a page break before and after the report item renders.
• Disabled. Use a conditional expression to determine whether to apply a page break. For example, you might set it to True when the render format is HTML to prevent a page break, but set it to False when the render format is PDF to add the page break.

32. Page break properties

You can also configure a page break in the Properties dialog box for a data region, such as the Tablix Properties dialog box shown in Figure 33. In the Page Break Options section, you can add a break before or after the data region or both before and after it renders. For the tablix, you can also select the Keep Together On One Page If Possible check box to set the KeepTogether property to True.

33. Tablix pagination properties

When you set the KeepTogether property to True, the rendering engine pushes the tablix to a new page if it doesn’t fit on the same page as the preceding items. If it’s not possible to render the tablix on a single page, the rendering engine renders as much of the table as possible on the first page and then continues on subsequent pages.

You can also set page breaks by group within a data region. The same properties that you have for setting page breaks by report item are applicable to groups: BreakLocation and Disabled. However, for BreakLocation, you define a page break between group instances in addition to breaking at the start or end of a group. That way, you don’t create an unnecessary page break at the beginning or end of a report item and create a new page only when a new group starts. To do this, select the group in the Groupings Pane, and then expand the Group category in the Properties window to locate the PageBreak options. Another way to do this is to right-click the group and open the Page Breaks page of the Group Properties dialog box, as shown in Figure 34.

34. Group page break options

Repeating Headers

By default, the column headers appear only once when the table renders, so when a table spans multiple pages, you lose the context of each column on subsequent pages. To make the tablix easier to read, you can configure repeating column headers. You can do this by setting the RepeatColumnHeaders property to True in the Properties window for the tablix.

If your tablix doesn’t have column groups in the tablix, you won’t achieve the desired effect. First, you need to view the Row Groups in Advanced Mode. To do this, click the arrow to the right of the Column Groups and select Advanced Mode. Notice that Static Groups now appear in the Row Groups, as shown in Figure 35.

35. Viewing groups in Advanced Mode

You can set the column headers by selecting the static group that highlights the leftmost column header. This is generally the first static group listed. In the Properties window, set the RepeatOnNewPage property to True and the KeepWithGroup property to After. This latter property specifies which group the static member needs to stay with as follows:

• After. The static member stays with the group after or below it, acting as a group header.
• Before. The static member stays with the group before or above it, acting as a group footer.
• None. The rendering engine automatically decides where to put the static member.

Page Numbering

When a report spans multiple pages, you should add page numbers in a page header or page footer by using one of the following global variables in an expression:

• Globals!PageNumber. The current page number relative to the last page number reset.
• Globals!TotalPages. The total number of pages in the current page numbering group.
• Globals!OverallPageNumber. The current page number in the report.
• Globals!OverallTotalPages. The total number of pages in the report.

If you do not reset page numbers, the OverallPageNumber variable will be the same as the PageNumber variable, and the TotalPages variable will be equal to the OverallTotalPages variable. You can use the ResetPageNumber property in the set of page break properties for a report item or group to reset the numbering.

Page Naming

In addition to numbering pages, you can also assign each page a unique name. The first page of a report has a blank name by default. If you decide to assign a page name, you assign a constant value or expression to the report property InitialPageName.

You can then reset the page name when you create a page break for a report item or group. For example, you might use the name of a group to reset the page name. You can set the page name property only by using the Properties window. Select the item that you want to use to control the page name, such as the tablix group, and then set the PageName property, as shown in Figure 36.

36. PageName property

When you export a report to Excel, the page name becomes the sheet name, as shown in Figure 37. If you set an initial page name for your report but do not set page names for the other pages in the report, all the sheets in Excel will have the initial page name.

37. Page names become sheet names in Excel

Report Builder

Report Builder is a click-once application that you can download from an on-premises report server on demand or install as a separate client application on your computer. Report Builder is primarily a tool for information workers and power users. Although it only allows you to work with one report at a time, it otherwise provides the same functionality as BIDS or SSDT. Regardless of which tool you decide to use, the report development process remains the same.

Getting Started Wizard

The Getting Started Wizard is displayed when you open Report Builder unless you select the Don’t Show This Dialog Box At Startup check box in the lower left corner and close the window. This wizard prompts you to start a new task by choosing one of the following options:

• New Report. This selection starts another wizard that steps you through the process of building a table, matrix, chart, or map.
• New Dataset. Choose this option to open the Dataset Designer to create a shared dataset to publish to SQL Reporting for use in multiple reports.
• Open. Use this option to connect to SQL Reporting and locate a report to edit in Report Builder.
• Recent. You can return to a report that you opened recently or view often.

Report Development in Report Builder

The report development process in Report Builder is very similar to working in BIDS or SSDT, except that you can only work with one report at a time. Furthermore, instead of using the Toolbox window to add report items to your report, you use the ribbon displayed across the top of the application window. The ribbon includes the following tabs:

• Home. This tab of the ribbon contains commands to toggle between design and preview mode, copy and paste items, and format text, borders, and numbers.
• Insert. You use this tab to add report items to your report or to open the Report Part Gallery. Commands on this tab allow you to add items directly to the report or use a wizard to help you arrange dataset fields in the item.
• View. This tab contains check boxes that you use to hide or show the Report Data, Grouping, or Properties, windows or the ruler.

ReportViewer Control

By using the ReportViewer control in your applications, you can focus your development efforts on application logic and save the time required to build the same reporting capabilities that SQL Reporting already includes. You can create an on-premises application or host your application in Windows Azure.

On-Premises Application

To make an on-premises application, create a Windows or Web form in your application and then add the ReportViewer control to it. You can find it in the Reporting group in the Toolbox. Next, in the design window, click anywhere in the center of the ReportViewer control to set the focus of the Properties window. In the Properties window, the ServerReport section contains the properties that define the connection to a report on the SQL Reporting server, as shown in Figure 38.

38. ReportViewer properties

Note: Before you configure the ReportViewer control, you must deploy reports to the SQL Reporting server as described in Chapter 4, Report Management.

At a minimum, you must configure the ReportViewer control’s ProcessingMode, ReportPath, and ReportServerURL properties. To use reports managed by Reporting Services, you must change the ProcessingMode property to Remote. When you define ReportPath, you provide only the folder and report name. You cannot include URL access commands. For ReportServerURL, you use the Web Service URL, which is the same URL that you use when deploying reports from BIDS or SSDT.

The Toolbar section of the Properties window includes several properties that allow you to configure the visibility of each item in the HTML Viewer. You can remove buttons from the HTML Viewer by setting the corresponding property to False. For example, you can hide the Export button if you want to restrict users to viewing reports online only.

You also need to modify the code in your form to pass the login user and password like this:

When you build your application, Visual Studio creates its executable in the bin\Debug folder. You can then distribute this executable file to anyone who has access to the SQL Reporting server and has permissions to the report.

Windows Azure Application

Another option is to use Windows Azure to host your application. When you create a Windows Azure application in Visual Studio, you must select the ASP.NET Web Role, assign a name to the role (such as RptVwrWebRole), and add it to your solution. You need to add a ScriptManager control to the design surface of your Web form above the ReportViewer control. Then add the ReportViewer control from the Reporting group in the Toolbox to the Default.aspx form, and then set the properties for this control as described for an on-premises application in the previous section of this chapter.

Next you need to add a class to implement and call the IReportServerCredentials interface like this:

You also need to update the Web.config file in your solution with the details about your report location and the credentials the application uses to access the report. To do this, you specify the application settings in the <appsettings> element inside the <configuration> element like this:

To run the ReportViewer control in a Windows Azure application, you must add the Microsoft.ReportViewer.Common.dll reference to your project. You can find this DLL in the C:\Windows\assembly\GAC_MSIL\Microsoft.ReportViewer.Common path and then navigating to the version folder that matches the version of Visual Studio you are using. The Microsoft.ReportViewer.WebForms.dll is also required, but is added to the folder when you add the control to the form. Then, for both assemblies, set the Copy Local property to True.

Now prepare the deployment package for Windows Azure. To do this, right-click the cloud service name in Solution Explorer, click Package, and then click Package in the dialog box. To upload the deployment package, go to the new Windows Azure portal, open the Cloud Services page, click Create a Cloud Service, give the service a name, and select a region.

After the cloud service is created, click the service name to open its dashboard. Click the Certificates link at the top of the page, and then click Upload a Certificate. Browse to the certificate on your computer and select it for upload, and then type the private key password.

Return to the dashboard for your cloud service and click the link Upload a New Production Deployment. Provide a deployment name and then browse to the bin\release\app.publish folder of your solution to locate the CSPKG and CSCFG files for your package. Be sure to select Deploy Even If One Or More Roles Contain a Single Instance if necessary.

When the deployment is complete, return to the dashboard for your deployment to get the site URL for the service. You can click the link to open your application in the browser.