Browse DevX
Sign up for e-mail newsletters from DevX


Reporting Strictly for Developers: Microsoft SQL Server Reporting Services  : Page 4

With the release of SQL Server Reporting Services, Microsoft finally makes a commitment to developer-centric reporting. They've integrated the design-time environment integration with Visual Studio and enabled report delivery through Web browsers, PDFs, Excel, and XML. Learn how to make this tool work for you.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Basic Controls
There are five controls you can use to render data in Reporting Services.
  • Text Box: The text box is most like a label in ASP.NET or Windows Forms. You can set the text box value property to a static value (as a label for a field or column), or you can link to a data source and field (see "formulas" later in this article). Text boxes can be placed directly in the report body or inside a list or table control.
  • List: The list will be the most familiar control to users of existing reporting tools, being a repeating set of output that can be sorted and grouped. Lists can contain any of the Reporting Services controls, including other lists. Use lists inside lists to produce grouped results.
  • Table: Use the table control to render data in columns. The table control serves solely as a container for other controls—you will generally place text box controls inside table cells. The table control is a good way to controls in columns aligned, and the table control is quite flexible—you can merge cells in the same way you can use the COLSPAN and ROWSPAN attributes in HTML.
  • Chart: The chart control provides basic graphing abilities.
  • Matrix: A matrix is similar to a pivot table in Excel, and allows you to display multi-dimensional data.
A key element that makes Reporting Services powerful is its use of expressions. An expression is a formula written in VB.NET that returns a value. Expressions can be used to control nearly every property of a report control; the most obvious one of these is the value the control displays.

Figure 4. The Expression Editor: The Expression Editor displays most of the available data collections in a tree view.

Many control properties expose the ability to control their values using an expression. For example, the properties a text box exposes as an expression include its displayed value, background color, background image, font style, family, weight and size, format (and the list goes on). Table rows and columns expose their "hidden" property as an expression and lists use expressions to control grouping.

Expressions can access all of the data sources and their fields, as well as report parameters, custom functions, and other report items like the page number, number of pages in the report, and report name. The Expression Editor displays most of the available data collections in a tree view you can use to add code to your expression.

Accessing Data in Expressions
Reporting services exposes data to expressions by way of a number of collections—that is, objects that contain named values, which work just like the collection and hashtable types in VB.NET.

In general, you access collections in code using the syntax collection!field. For example, to use the Globals collection to access the page number, use Globals!PageNumber. For items that return an object, use collection!field.property. Use an equals ("=") sign as the first character of the expression to indicate that your expression returns a value.

Note: Any VB.NET syntax for accessing collections will work—so collection!field, collection.items("field") or collection("field") will all work. For consistency, I always use the collection!field syntax form in reports because the expressions that are automatically generated by the report designer use this syntax.

The main collections you access in reporting services are:

  • Fields: The Fields collection provides access to the fields returned by the control’s dataset. Controls are related to a dataset by setting the DataSetName property of the control to the name of the dataset. Leave this property blank to inherit the DataSetName from the control that contains your control. For example, to access the Surname field of a dataset called Employees use:


  • Globals: The Globals collection contains the page number, total pages, report name, and other report properties. To create a control that displays the current page number and count of pages in the report, use:

    ="Page " & Globals!PageNumber & " of " & Globals!TotalPages

  • Parameters: The Parameters collection contains the Report parameters. To view a report parameter called "ID," use:


    Parameters are defined in the property page of the report.
  • ReportItems: The ReportItems collection provides access to the other controls in the current report. To access another control called txtName, use:


    Use the Me keyword to access properties of the current control.
  • User: The user collection contains two items, Language and UserID. Language is the current user's language code, and UserID is the name of the logged-on user. These are also available as properties of the Globals collection.

Thanks for your registration, follow us on our social networks to keep up-to-date