Reporting Strictly for Developers: Microsoft SQL Server Reporting Services

Reporting Strictly for Developers: Microsoft SQL Server Reporting Services

ost report design tools attempt to please both developers and non-technical end users by combining high levels of flexibility and power and ease-of-use. This accommodation naturally requires various sacrifices on both sides. With the Reporting Services Report Designer, Microsoft has abandoned this strategy and made a commitment to the developer. The most obvious indicator of this is that the design-time environment has been integrated into Visual Studio rather than it being a standalone application.

Reporting Services is a Web-enabled reporting tool that delivers reports via a Web browser. It also, or can be used to exports to a variety of formats, including Adobe Acrobat (PDF), Microsoft Excel, and XML. It ships with an easy-to-use, Web-based report manager which allows users to schedule and run reports and export them to a variety of formats.

How You Get It
SQL Server Reporting Services is an add-on to SQL Server 2000?you must own a license for SQL Server on a server in order to use Reporting services on that same server. Existing SQL Server owners can get it at no additional license cost (other than postage and handling).

MSDN subscribers can download Reporting Services from the MSDN subscriber downloads site. Everyone else can contact their local Microsoft office to order Reporting Services on a CD?check out the Web site for more information.Components
Reporting Services consists of five components:

  • Reporting Services Web Service: The main engine of this service is its reporting Web service. It provides access to all functionalities, including the rendering engine, scheduling components, and report database. Use it to programmatically add and remove folders, deploy reports, execute reports, and schedule reports.
  • ReportingServices Service: This component is a Windows service. It handles scheduled execution and delivery of reports.
  • Report Manager Web Site: The Web-based report manager allows users to submit and organize reports, execute and schedule reports. You can access the report manager using the URL http://yourserver/reports.
  • Report Designer: The report designer is integrated into Visual Studio 2003. It adds a new project type “Report Project” inside the category “Business Intelligence Projects” which you use to create reports. Your Report project stores data source definitions as .rds files and report definition files as .rdl files on your local file system. You can deploy these files to a reporting server from within Visual Studio, from the Report Manager or with your own code that uses the Reporting Services Web Service.
  • Reporting Services Database: Reporting Services uses SQL Server to store your reports. The .rdl files that you save in the development environment are parsed and stored in the database tables in the “ReportServer” database, which is created during setup. Setup also creates a database called ReportServerTempDB.

The Reporting Services Web Service and Report Manager Web Site are installed to your default Web site in IIS.

You can install all of the Reporting Services components on to your development system if you have SQL server and Internet Information Systems installed, or you can install just the report designer component on your development system and install the rest of Reporting Services on a server.

Creating Reports
Creating a report is very similar to creating an ASP.NET Web page or a Windows form?you draw controls on the report surface, and set the properties of the controls to determine their appearance and behavior. The language used for formulas (or “expressions” in Reporting Services) is VB.NET, which is also immediately familiar.

The concept of a report with one or more group headers, followed by a report details section, followed by group footers is replaced with the more free-form, controls-based approach. The ability to produce “banded” results is still available (using the list control), but it is no longer the fundamental core building block of a report.

Another limitation of some of the existing reporting tools that Reporting Services has improved upon is the ability to use multiple datasets. Reporting Services allows you to create and use multiple datasets within a single report, which do not need to be related to one another. Because you link datasets to controls in the report rather than to the report itself, different controls can reference entirely different datasets.

Figure 1. Report Projects: To create a new report project from Visual Studio, select “New Project,” and select “Report Project” or “Report Project Wizard” from the “Business Intelligence Projects” project type.

Data Sources and Datasets
To create a new report project from Visual Studio, select “New Project,” and select “Report Project” or “Report Project Wizard” from the “Business Intelligence Projects” project type.

The first thing you do to create a new report project is to create a data source (if you use the wizard you will be led though this process). Reports projects can have as many data sources as you need?they are simply XML (.rds) files containing ADO connection strings. To create a data source, right-click the “Shared Data Sources” project item and select “Add New Data Source.”

Figure 2. Data Sources: To create a data source, right-click the “Shared Data Sources” project item and select “Add New Data Source.”

You will be prompted to select a database server and database. Your data source can be any ADO data source?it does not have to be SQL server.

Once you have created a data source you can create your first report. Right-click the Reports item in the solution explorer, then select “Add New Report” to run through the New Report Wizard, or select “Add ? Add New Item?” and choose “Report” from the “Add New Item” dialog.

The report wizard will prompt you for the data source to use, then prompts for a query string. You can click “Edit” to use the query builder to create an ad-hoc query (which automatically creates a dataset), then follow the wizard to automatically generate a report. I prefer to design my reports from scratch, particularly because the wizard does not allow you to use a stored procedure for your data source.

To add a dataset to your report, select the “Data” tab, then choose the “New Dataset” item in the “Dataset” drop-down list. You will be prompted for a dataset name, data source, command type and query string.

Figure 3. Adding a Dataset: To add a dataset to your report, select the “Data” tab, then choose the “New Dataset” item in the “Dataset” drop-down list.

The dataset name is whatever you want to call your dataset, and the data source will be the name of the dataset you created earlier. The command type will be one of the following options:

  • Text: An ad-hoc SELECT statement, which you can type directly into the query string text box.
  • StoredProcedure: The name of a stored procedure. You have to enter the stored procedure name in the query string textbox.
  • TableDirect: For reading from a single table only. Enter the name of the table in the query string textbox.

In my opinion, the dataset property page dialog is missing some key productivity enhancements. For the Text query type, the query designer is not available, and for Stored Procedures and TableDirect, the query string textbox should really be turned into a drop-down list to save you the trouble of entering a stored procedure or table name.

As you create datasets, the IDE automatically adds report parameters to your report. These provide data for the parameters of your stored procedure or select statement. Report parameters can be manipulated in the “Layout” view. You can specify available ranges from a pre-defined list or from another dataset, and the selection pages that are automatically generated by Reporting Services will display a drop-down list with appropriate values.

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.

Formulas
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:
    	=Fields!Employees.Surname 
  • 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!ID.Value

    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:
    	=ReportItems!txtName.Value

    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.
Figure 5. Adding functions: You can create functions using the VB.NET syntax.

Creating and Accessing Custom Code
To add your own code to a report, display the report properties page by right-clicking the gray section surrounding the report and click Properties. You can create functions using the VB.NET syntax.

To access your custom functions in an expression, use the syntax Code.Function. For example, to call the FormatDuration function above, use the code:

	=Code.FormatDuration(75)

You can also reference your own assembly and use its properties and methods in your code, although this requires some manual modification of configuration files on your report server.

The Expression Editor is very basic. There is no code coloring, syntax checking, or IntelliSense. The tree view does not display the ReportItems and User collections, and does not list any custom functions you have added.

Note: The syntax for calling code uses a “dot” character, not an exclamation point like you use for accessing report collections.

Deployment
Deploying reports to a server from Visual Studio is very straightforward?just right-click your report in the solution explorer and select the “Deploy” option. In my test environment, the first time I used this option, the operation took a long time (several minutes), but subsequent deployments were fairly fast, so be patient the first time. You can configure test and production server URLs in the project properties page and use the configuration type?Reporting Services adds the “local debug” and “production” configuration types to the existing list that already includes “debug” and “release”?to control which server reports are sent to.

Writing code to automate the deployment of reports to a server (as in a custom action for your setup program) is straightforward using the methods of the Reporting Services Web Service, and the Web service documentation is very thorough.

You can run reports by browsing to the Report Manager (http://yourserver/reports by default) and selecting your report. A pre-generated ASP selection page is displayed which you use to select options and render your report.

You can also create your own custom selection page, or render the report from a Windows Forms application using the Reporting Services Web Service.

Observations
The story behind the initial release of Reporting Services is that it was targeted for launch as part of SQL Server 2005, but the beta was so popular that Microsoft chose to release it early. This story rings true because Reporting Services is missing a number of key usability and productivity features that should really be present in a full release.

As a VB.NET developer, I like that fact that the expression language is VB.NET, but Microsoft should have included C# support, and should ideally support any .NET language. Being a server-side application, there are no client-side runtimes (or related problems) to worry about?this is a truly “zero footprint” application. I was surprised to see no code colouring or syntax checking in the expression editor. Hopefully, this feature will be added soon.

On the positive side, the fact that the developers “eat their own dog food” by using use their own Web service from the Visual Studio IDE means that as developers, we can do everything Microsoft can do with the reporting engine. It is very straightforward to develop code to deploy and render reports, and this area is well covered in the Reporting Services Books Online documentation.

I also like the developer focus of Reporting Services, although I won’t be surprised if an end-user focused report designer is released at some stage in the future in order to round out the product.

I’m generally happy with Reporting Services, it is very much a useable and useful tool in it’s current form, and I intend on continuing to use it. It’s got flaws, but I believe my criticisms are very much “version one” issues. Other reporting tools I’ve used still have their problems despite having been around for years. .NET and SQL Server developers should go and get Reporting Services, and see if it helps to provide better reporting for your customers and users.

devx-admin

devx-admin

Share the Post:
Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor

Revolutionized Low-Code

You Should Use Low-Code Platforms for Apps

As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with

Global Layoffs

Tech Layoffs Are Getting Worse Globally

Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019

Clean Energy Adoption

Inside Michigan’s Clean Energy Revolution

Democratic state legislators in Michigan continue to discuss and debate clean energy legislation in the hopes of establishing a comprehensive clean energy strategy for the state. A Senate committee meeting

Chips Act Revolution

European Chips Act: What is it?

In response to the intensifying worldwide technology competition, Europe has unveiled the long-awaited European Chips Act. This daring legislative proposal aims to fortify Europe’s semiconductor supply chain and enhance its

Revolutionized Low-Code

You Should Use Low-Code Platforms for Apps

As the demand for rapid software development increases, low-code platforms have emerged as a popular choice among developers for their ability to build applications with minimal coding. These platforms not

Cybersecurity Strategy

Five Powerful Strategies to Bolster Your Cybersecurity

In today’s increasingly digital landscape, businesses of all sizes must prioritize cyber security measures to defend against potential dangers. Cyber security professionals suggest five simple technological strategies to help companies

Global Layoffs

Tech Layoffs Are Getting Worse Globally

Since the start of 2023, the global technology sector has experienced a significant rise in layoffs, with over 236,000 workers being let go by 1,019 tech firms, as per data

Huawei Electric Dazzle

Huawei Dazzles with Electric Vehicles and Wireless Earbuds

During a prominent unveiling event, Huawei, the Chinese telecommunications powerhouse, kept quiet about its enigmatic new 5G phone and alleged cutting-edge chip development. Instead, Huawei astounded the audience by presenting

Cybersecurity Banking Revolution

Digital Banking Needs Cybersecurity

The banking, financial, and insurance (BFSI) sectors are pioneers in digital transformation, using web applications and application programming interfaces (APIs) to provide seamless services to customers around the world. Rising

FinTech Leadership

Terry Clune’s Fintech Empire

Over the past 30 years, Terry Clune has built a remarkable business empire, with CluneTech at the helm. The CEO and Founder has successfully created eight fintech firms, attracting renowned

The Role Of AI Within A Web Design Agency?

In the digital age, the role of Artificial Intelligence (AI) in web design is rapidly evolving, transitioning from a futuristic concept to practical tools used in design, coding, content writing

Generative AI Revolution

Is Generative AI the Next Internet?

The increasing demand for Generative AI models has led to a surge in its adoption across diverse sectors, with healthcare, automotive, and financial services being among the top beneficiaries. These

Microsoft Laptop

The New Surface Laptop Studio 2 Is Nuts

The Surface Laptop Studio 2 is a dynamic and robust all-in-one laptop designed for creators and professionals alike. It features a 14.4″ touchscreen and a cutting-edge design that is over

5G Innovations

GPU-Accelerated 5G in Japan

NTT DOCOMO, a global telecommunications giant, is set to break new ground in the industry as it prepares to launch a GPU-accelerated 5G network in Japan. This innovative approach will

AI Ethics

AI Journalism: Balancing Integrity and Innovation

An op-ed, produced using Microsoft’s Bing Chat AI software, recently appeared in the St. Louis Post-Dispatch, discussing the potential concerns surrounding the employment of artificial intelligence (AI) in journalism. These

Savings Extravaganza

Big Deal Days Extravaganza

The highly awaited Big Deal Days event for October 2023 is nearly here, scheduled for the 10th and 11th. Similar to the previous year, this autumn sale has already created

Cisco Splunk Deal

Cisco Splunk Deal Sparks Tech Acquisition Frenzy

Cisco’s recent massive purchase of Splunk, an AI-powered cybersecurity firm, for $28 billion signals a potential boost in tech deals after a year of subdued mergers and acquisitions in the

Iran Drone Expansion

Iran’s Jet-Propelled Drone Reshapes Power Balance

Iran has recently unveiled a jet-propelled variant of its Shahed series drone, marking a significant advancement in the nation’s drone technology. The new drone is poised to reshape the regional

Solar Geoengineering

Did the Overshoot Commission Shoot Down Geoengineering?

The Overshoot Commission has recently released a comprehensive report that discusses the controversial topic of Solar Geoengineering, also known as Solar Radiation Modification (SRM). The Commission’s primary objective is to

Remote Learning

Revolutionizing Remote Learning for Success

School districts are preparing to reveal a substantial technological upgrade designed to significantly improve remote learning experiences for both educators and students amid the ongoing pandemic. This major investment, which

Revolutionary SABERS Transforming

SABERS Batteries Transforming Industries

Scientists John Connell and Yi Lin from NASA’s Solid-state Architecture Batteries for Enhanced Rechargeability and Safety (SABERS) project are working on experimental solid-state battery packs that could dramatically change the

Build a Website

How Much Does It Cost to Build a Website?

Are you wondering how much it costs to build a website? The approximated cost is based on several factors, including which add-ons and platforms you choose. For example, a self-hosted

Battery Investments

Battery Startups Attract Billion-Dollar Investments

In recent times, battery startups have experienced a significant boost in investments, with three businesses obtaining over $1 billion in funding within the last month. French company Verkor amassed $2.1

Copilot Revolution

Microsoft Copilot: A Suit of AI Features

Microsoft’s latest offering, Microsoft Copilot, aims to revolutionize the way we interact with technology. By integrating various AI capabilities, this all-in-one tool provides users with an improved experience that not

AI Girlfriend Craze

AI Girlfriend Craze Threatens Relationships

The surge in virtual AI girlfriends’ popularity is playing a role in the escalating issue of loneliness among young males, and this could have serious repercussions for America’s future. A

AIOps Innovations

Senser is Changing AIOps

Senser, an AIOps platform based in Tel Aviv, has introduced its groundbreaking AI-powered observability solution to support developers and operations teams in promptly pinpointing the root causes of service disruptions