Click here to monitor SSC
  • Av rating:
  • Total votes: 58
  • Total comments: 15
Robyn Page and Phil Factor

Reporting Services Cribsheet

07 August 2007

Reporting Services Crib Sheet

For things you need to know rather than the things you want to know

Contents

Introduction

SQL Server Reporting Services (SSRS) aims to provide a more intuitive way of viewing data. It allows business users to create, adapt and share reports based on an abstraction or 'model', of the actual data , so that they can create reports without having to understand the underlying data structures. This data can ultimately come from a variety of different sources, which need not be based on SQL Server, or even relational in nature. It allows also developers a wide range of approaches to delivering reports from almost any source of data as part of an application.

The reports are interactive. The word 'reporting', in SSRS, does not refer just to static reports but to dynamic, configurable, reports that can display hierarchical data with drill-down, filters, sorting, computed columns, and all the other features that analysts have come to expect from Excel. Users can specify the data they are particularly interested in by selecting parameters from lists. The reports can be based on any combination of table, matrix or graph, or can use a customized layout. Reports can be printed out, or exported as files in various standard formats.

SSRS provides a swift, cheap way of delivering to the users all the basic reports that are required from a business application, and can provide the basis for customized reports of a more advanced type.

The design of SSRS

The surprising thing about Reporting Services is its open, extensible, architecture. With SSRS, Microsoft has taken pains over a product that has an obvious long-term importance for data handling in .NET.

From a programmer's perspective, the 'Big Idea' behind Reporting Services is to have a standard way of specifying reports. In a way, it is an attempt to do for reports what HTML did for rendering pages. Report Definition Language (RDL) is an XML-based open standard grammar. It was designed to provide a standard way to define reports, to specify how they should appear, their layout and content. It specifies the data source to use and how the user-interaction should work.

In theory, there could be a number of applications to design business reports; several ways of managing them, and a choice of alternative ways of rendering them. All these would work together because of the common RDL format.

SQL Server Reporting Services is the first product to adopt the architecture. It is a combination of report authoring, report management and report delivery. It is not limited to SQL Server data. It can take data from any ODBC source. Reporting Services can use a SQL Server Integration Services package as a data source, thereby benefiting from Analysis Service's multidimensional analysis, hierarchical viewing and data mining. It can just as easily report from OLAP data as relational data. It can also render reports to a number of media including the browser, application window, PDF file, XML, Excel, CSV or TIFF.

The API of SSRS is well-enough documented to allow the use of custom data, custom ways of displaying data or special ways of delivering it. Because Microsoft has carefully documented the RDL files, and the APIs of the ReportingServices namespace, it is reasonably easy to extend the application for special data or security requirements, different data sources, or even the way the reports are rendered. One can, of course, replace a component such as the report authoring tool with one designed specially for a particular application.

When SSRS is installed, it is set to deliver reports via a 'Report Server' which is installed as an extension to the IIS service on the same server as that on which SQL Server is installed. The actual portal, with its hierarchical menu, report models and security, can be configured either via a browser or from Visual Studio. The browser-based tools are designed more for end-users, whereas the Visual Studio 'Business Intelligence Development Studio' tools are intended for the developer and IT administrator.

The 'Report Server' is by no means the only possible way of delivering reports using Reporting Services, but it is enough to get you started.

So let's look in more detail at the three basic processes that combine to form SQL Server Reporting Services (SSRS): Report Authoring, Report Management and Report Rendering

The components of SSRS

Report Authoring

The Report Authoring tools produce, as their end-product, RDL files that specify the way that the report will work.

Any application capable of producing an XML file can produce an RDL file, since RDL is merely an XML standard. There is nothing to stop an application from producing an RDL and then using Microsoft's ReportViewer component to render the report.

Hopefully, third-party 'Report Designer' packages will one day appear to take advantage of the applications that are capable of rendering RDL files.

The report designers of SSRS are of two types: 'Report Builder' designed for end users and 'Report Designer' designed for developers.

Report Builder

Report Builder is an 'ad-hoc reporting tool', and designed for IT-savvy users to allow them to specify, modify and share the reports they need. It can be run directly from the report server on any PC with the .NET 2 framework installed. It allows the creation of reports derived from 'report models' that provide a business-oriented model of the data. These reports can then be managed just like any others. The Report Builder allows the users to specify the way data is filtered and sorted, and allows them to change the formulas of calculated columns or to insert new columns. These reports have drill-down features built into them.

Report Designer

Visual studio has a 'Report Designer' application hosted within Business Intelligence Development Studio. It allows you to define, preview and publish reports to the Report Server you specify, or to embed them into applications. It is a different angle on the task of designing reports to 'Report Builder', intended for the more sophisticated user who understands more of the data and technology. It has a Query Builder, and expression editor and various wizards. The main designer has tabs for the data, layout and preview.

With the embedded Query Designer , you can explore the underlying data and interactively design, and run, a query that specifies the data you want from the data source. The result set from the query is represented by a collection of fields for the dataset. You can also define additional calculated fields. You can create as many datasets as you need to for representing report data. The embedded Layout Designer allows the insertion or alteration of extra computed columns. With the Layout Designer, you can drag fields onto the report layout, and arrange the report data on the report page. It also provides expression builders to allow data to be aggregated even though it has come from several different data locations. It can then be previewed and deployed.

Model Designer

The Model designer in Visual Studio allows you to define, edit and publish 'report models' for Report Builder that are abstractions of the real data. This makes the building of ad-hoc reports easier. These models can be selected and used by Report Builder so that users of the system can construct new reports or change existing reports, working with data that is as close as possible to the business 'objects' that they understand. The model designer allows the programmer to specify the tables or views that can be exposed to the users who can then use the models to design their reports. One can also use it to determine which roles are allowed access to them.

Report Management

There are configuration, monitoring and management tools in SSRS which are provided within the Business Intelligence Development Studio.

Report Manager

Report Manager is a web-based tool designed to ease the management task of connections, schedules, metadata, history and subscriptions. It allows the administrator to categorize reports and control user access. The data models that are subsequently used by the ad-hoc Report Builder tool to translate the data into business entities can be edited in this tool. The report portal , which provides the 'homepage' for the Report Server, can be edited to create or modify the directory hierarchy into which the individual reports are placed. The RDF files can be uploaded to the report server using this tool and placed in their logical position within the hierarchical menu.

One can create or assign the roles of users that are allowed access the various levels of access to this report. These roles correspond to previously defined groups in the Active Directory. One can specify whether and how often a report should be generated and email the recipients when the report is ready.

SSRS uses role-based security to ensure that appropriate access to reports is properly enforced. It controls access to folders, resources and the reports themselves. With SQL Server Standard and Enterprise editions, one can add new roles, based on Active Directory groups. There are APIs for integrating other security models as well.

Management Studio

The SQL Server Management Studio (SSMS) tool mirrors most of the capabilities of the Report manager with the addition of instance configuration and scripting. Management Studio itself uses RDL files in order to implement the performance Dashboard so as to get reports on the performance of the server itself, and this is easily extended to provide additional reports.

Report Rendering

Viewing Reports on an intranet

When SSRS is installed, it sets up a virtual directory on the local IIS. From there, users with the correct permissions can gain access to whatever reports you choose to deploy. The idea of allowing users to interact with reports and to drill-down into the detail is fundamental to the system, so it is possible to allow users to design their own reports or to use pre-existing ones and to hyperlink between reports or drill down into data to get more detailed breakdowns. SSRS now provides 'floating headers' for tables that remain at the top of the scrolled list so one can easily tell what is in each column

Report parameters are important in SSRS. If, for example, the users can choose a sales region for a sales report then all possible sales regions for which data exists are displayed for selection in a drop-down list. This information is derived from the data model that forms the basis for the report.

Reports can be viewed via a browser from the report server, from any ASP.NET website and from a Sharepoint portal.

Reports in applications

One is not restricted to browser-based access of SSRS reports. Any .NET application can display such reports easily. The latest version of SSMS, for example, uses reporting services in order to get performance reports.

There are alternatives. such as using the Web Browser control or the ReportViewer control.

To use the web browser control in an application, all one needs to do is to provide the URL of the report server. The report is then displayed. One can, of course launch the browser in a separate window to display the reports. The URL parameters provide precise control over what information is returned. Using the appropriate parameters, not only can you get the report itself for display, you can also access the contents of the Data Source as XML, the Folder-navigation page, the child items of the report, or resource contents for a report. You can also specify whether it should be rendered on the browser or as an image/XML/Excel file.

The report viewer control, 'ReportViewer', ships with Visual studio 2005 and can be used in any Windows Form or web form surface, just by dragging and dropping. After you assign a report url and path, the report will appear on the control. You can configure the ReportViewer in a local report-processing mode where the application is responsible for supplying the report data. In local-processing mode, the application can bind a local report to various collection-based objects, including ADO.NET regular or typed datasets.

One can use the Report Server Web Service to gain access to the report management functionality such as content, subscription and data source, on top of all the facilities provided by using a URL request s. This allows reporting via any development tool that implements the SOAP methods. This Web Service approach provides a great deal of control over the reporting process greatly facilitates the integration of Reporting Services into applications, even where the application is hosted in a different operating environment.

SSRS DataSources and Datasets

SSRS Data Sources

Data that is used to provide the Dataset that forms the basis for a report usually comes from SQL Server, or a source for which there is an OLEDB or ODBC provider. It is possible to create the dataset in another application, even a CLR, and bind it to a report. One can access other data sources, such as an ADO.NET dataset, by using a Custom Data Extension (CDE).

Report delivery can be from a Sharepoint site, using the SharePoint Web parts that are included in the SSRS package.

The information contained within a data source definition varies depending on the type of underlying data, but typically includes information such as a server name, a database name, and user credentials.

Data sources can include Microsoft SQL Server, Microsoft SQL Server Analysis Services, ODBC, and OLE DB, Report Server Model, XML, Oracle, SAP NetWeaver Business Intelligence or Hyperion Essbase

A data source can be contained within a report, or it can be shared by several. In the first case, the definition for a report-specific data source is stored within the report itself, whereas for a shared source, the definition is stored as a separate item on the report server. A report can contain one or more data sources, either report-specific or shared.

SSRS DataSets

A Reporting Services dataset, which is not the same as a .NET dataset, is the metadata that represents the underlying data on a specific data source. It contains a data source definition, a query or stored procedure of the data source and a resulting fields list, and the parameters if any, calculated fields, as well as the collation. A report can contain one or more datasets, each of which consists of a pointer to a data source, a query, and a collection of fields. These datasets can be used by different data regions on the report, or they can be used to provide dynamic lists of parameters.

The datasets used as the basis for reports can come from a wide variety of sources. The examples are mostly queries involving SQL Server base tables, and this has given the impression that this is all that can be used. Reports can, in fact, easily use Stored Procedures to provide the dataset for a report. However, the queries for datasets that fetch the items in the drop-down Parameter lists must be provided too.

Dataset Fields

Each dataset in a report contains a collection of fields. These fields generally refer to database fields and contain a pointer to the database field and a name property but this can be overwritten with a more meaningful name where necessary. These fields can, alternatively, be calculated fields, which contain a name and an expression .

Conclusion

When implementing an application, one ignores Reporting Services at one's peril. The benefit to almost any application of implementing standard reports from SSRS is immediate and always impressive to end-users. The impact is far greater than the effort involved. One of us (Phil) suffered intense embarassment through believing the users of an application when they said that they would never require interactive reports and only wanted strictly defined and cross-checked standard reports in an application. When someone else implemented both Business Intelligence and SSRS, and gave the users the freedom to explore their own data, Phil was left in no doubt as to his foolishness in having neglected to do so.

There is always a point when developing an application that the standard fare that can be provided by SSRS is not quite enough for the more advanced reporting requirements. However, it is prudent to make sure that all other reporting up to that point is done via SSRS.

The worst mistake of all is dismissing SQL Server Reporting Services as being just an end-user tool for simple reports. Its architecture is such that it forms the basis of an extremely powerful tool for delivering information to users of an application.

Further Reading….


Robyn Page and Phil Factor

Author profile:


Robyn Page has worked as a consultant with Enformatica and USP Networks with a special interest in the provision of broadcast services over IP intranets. She was also a well known actress, being most famous for her role as Katie Williams, barmaid and man-eater in the Television Series Family Affairs. She is currently having a career break to raise a young family.

Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 20 years of experience with database-intensive applications. Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

Search for other articles by Robyn Page and Phil Factor

Rate this article:   Avg rating: from a total of 58 votes.


Poor

OK

Good

Great

Must read
Have Your Say
Do you have an opinion on this article? Then add your comment below:
You must be logged in to post to this forum

Click here to log in.


Subject: Dyamically generated RDLs
Posted by: Tad Richard (not signed in)
Posted on: Tuesday, August 07, 2007 at 4:07 PM
Message: I am curious as to whether it is possible / practical to generate RDL files on the fly?

Just as the web started with flat HTML files and moved to system generated HTML by passing data to the server, I would like to be able to systematically generate report templates (RDLs) based upon input parameters.

My understaning (I am a true novice with SSRS) is that the RDL files must be registered/stored in SQL Server and therefore are not easily generated on the fly. Is this correct?

Thanks!

Subject: re: Dyamically generated RDLs
Posted by: Phil Factor (view profile)
Posted on: Tuesday, August 07, 2007 at 4:40 PM
Message: An RDL file can be created by any application capable of writing an XML file. You'd only need the RDL file to be 'deployed' (generally using Business Intelligence Development Studio - Report Designer or Report Manager.) if you are accessing it from the Report Server or via SOAP, which most of us do. If you are accessing the RDLs from your filesystem in order to render them, (as SSMS does for its Performance Dashboard for example) there is no need to 'deploy' them. If you have a lot of files to deploy, you can write a script for the deployment process - there are several around on the Internet.

Reporting Services is not always dependent on SQL Server, strangely enough.

Subject: Opposite Experience
Posted by: Anonymous (not signed in)
Posted on: Wednesday, August 08, 2007 at 9:49 PM
Message: I set up an interactive reporting solution, where users could pick and choose their data, set their own drill-downs, etc., and quickly found that the people using it were having me set up single-view reports for them in it, and never using any of the controls/drills/etc.

Same sort of thing with Excel pivot-tables. Set up several of them to handle common data requests. One guy in the whole company ever used them at all. The rest were scared because they couldn't grasp the essential idea behind it.

So I went back to having zillions of flat reports based on single-parameter procs, and everyone was much happier.

So, Phil and I just needed to have our experiences swapped, and it would have been perfect. :)

Subject: (Anonymous posting above)
Posted by: GSquared (view profile)
Posted on: Wednesday, August 08, 2007 at 9:50 PM
Message: The post above, about "Opposite Experience", was posted by GSquared, just forgot to sign in first.

Subject: Re: GSquared Opposite experience
Posted by: Phil Factor (view profile)
Posted on: Thursday, August 09, 2007 at 3:23 AM
Message: (wry smile)

Maybe the law of human nature that covers both experiences is 'The Law of Forbidden Fruit'; which is that the users always hanker after whichever solutions you have decided not to give them.

The virtue of the hand-crafted report is that it can be validated, tested, understood and checked by all participants before it is used in anger. The Drilldown stuff always earns a round of applause from the users, but can cause chaos and misunderstandings. All that glitters is not gold. SSRS works wonderfully with AdventureWorks, but real data is always hedged about with uncertainties.

The way I like to argue the case is that introducing SSRS always buys you time, and keeps the users smiling, whilst you develop the real, powerful, validated, hand-crafted reports.

Subject: about adding columns
Posted by: Sorin (not signed in)
Posted on: Monday, August 13, 2007 at 1:29 AM
Message: Hi,
I am using Reporting Services on SQL Server 2000 and I am developing reports on SQL Server Business Intelligence. I am wondering, however, if it is possible to add a chunk of columns to a table. Now, I have to right-click on a column and add columns one-by-one.

Subject: Deployment
Posted by: Jonny Kickass (view profile)
Posted on: Wednesday, August 22, 2007 at 11:14 AM
Message: The one really annoying problem i've found with SSRS is that VS doesn't allow you to organise reports into folders / categories in the solution file. To add to this VS only allows you to deploy to a single folder on the reporting server.

Does anyone know of a way to categorise reports and/or upload them to multiple folders (preferably using a script of some kind so that it can be incorporated into our automated build process)?

Thanks
Jon

Subject: reporting services whit drill down
Posted by: ingrid (not signed in)
Posted on: Saturday, September 01, 2007 at 8:46 PM
Message: hi
i am using Reporting Services on SQL Server 2005 and i have to do a report with temporal tables, the dataset can't have more than one table i am doing one dataset for each table or sp and when i use the tables the report don't use the relations and i trying to use the drilldown but it doesn't work please if any body cant help me, left me know my e-mail is ingrid.j.romero@gmail.com

Subject: Doubt in Report Model
Posted by: Naveen J V (not signed in)
Posted on: Wednesday, October 17, 2007 at 8:20 AM
Message: Hi,

I'm using SSRS 2005, I have created a report model, which uses around 4 dimensions. Is it possible to take one or two members from all the dimension while creating a report using this model? In my present report I'm able to take members from only ONE dimension at a time. Please, let me know on my email: naveenjv@businessonetech.com

Thanks & Regards,
Naveen J V

Subject: Using relational data without joins
Posted by: James Johnson (not signed in)
Posted on: Monday, December 03, 2007 at 7:43 PM
Message: @ingrid

A bit late for this info but maybe it'll help someone else as well.

Data Dynamics (www.datadynamics.com) has a new product called Data Dynamics Reports which is compatible with Reporting Services reports. Our reporting engine has support for data relations in ADO.NET DataSets, objects, and XML data sources.

We are currently writing a sample to show off this feature and it will be included in the next release. If you (or anyone else) wants it before then you can request it on our support forum.

Cheers,

James Johnson
Product Manager - Data Dynamics Reports

Subject: Assign a new model to existing report
Posted by: Philiphi (view profile)
Posted on: Thursday, April 24, 2008 at 10:06 AM
Message: Is it possible to assign a new model to a report that has been published?

Subject: Assign a new model to existing report
Posted by: Philiphi (view profile)
Posted on: Thursday, April 24, 2008 at 10:06 AM
Message: Is it possible to assign a new model to a report that has been published?

Subject: My query
Posted by: Anonymous (not signed in)
Posted on: Wednesday, May 21, 2008 at 10:08 AM
Message: Hi,
I am using SSRS 2005 and I have made a report using drill down functionality. It works fine with the reporting manager. Then I hook it up with my website. When I try to run it thru the web browser, the drill down does not work. Can anybody please help me out? What could possibly be the problem??
Thanks!

Subject: Passing Parameters when SSRS implemented as a web service
Posted by: Muscunga (view profile)
Posted on: Monday, May 09, 2011 at 9:53 AM
Message: The text above states - "One can use the Report Server Web Service to gain access to the report management functionality such as content, subscription and data source, on top of all the facilities provided by using a URL requests."

Our SSRS environment is implemented as a web service. I need to pass parameters in a URL to render the report WITHOUT going through the Report Manager. In all the documentation I have found, it states that you have to include the 'report server' name in your URL.

http://msdn.microsoft.com/en-us/library/ms155391(v=sql.90).aspx

EX: http://<SERVER>/<REPORTSERVER>/Pages/Report.aspx?ItemPath=%2fPacTrac+Test+Reports%2ftest%2fDelivery+Manifest&rs:Command=Render&rc:Parameters=false&batchnum=10006012

As stated before, our SSRS environment is implemented as a web service, therefore I do not have a SERVER NAME. The link I was given was to the report manager.

EX: http://sqlreports.pre.companyname.com/Reports/Pages/Folder.aspx?ItemPath=%2fPacTrac+Test+Reports

Any ideas on how to pass parameters and render a report when SSRS is implemented as a web service?

Thanks
Kevin

Subject: SOLVED: Passing Parameters when SSRS implemented as a web service
Posted by: Muscunga (view profile)
Posted on: Friday, May 13, 2011 at 2:28 PM
Message: So to pass parameters in a URL whe SSRS is implemented as a web service I used the following:

http://sqlreports.whatever.com/reportserver/pages/reportviewer.aspx?%2fApplication+Test+Reports%2ftest%2fDelivery+Manifest&rs:Command=Render&rc:Parameters=false&batchnum=10006012

Look closely at the URL and you will see the "reportviewer.aspx"

That is what did it!

 

Phil Factor
Searching for Strings in SQL Server Databases

Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine like Google.... Read more...

 View the blog

Top Rated

Searching for Strings in SQL Server Databases
 Sometimes, you just want to do a search in a SQL Server database as if you were using a search engine... Read more...

Continuous Delivery and the Database
 Continuous Delivery is fairly generally understood to be an effective way of tackling the problems of... Read more...

The SQL Server Sqlio Utility
 If, before deployment, you need to push the limits of your disk subsystem in order to determine whether... Read more...

The PoSh DBA - Reading and Filtering Errors
 DBAs regularly need to keep an eye on the error logs of all their SQL Servers, and the event logs of... Read more...

MySQL Compare: The Manual That Time Forgot, Part 1
 Although SQL Compare, for SQL Server, is one of Red Gate's best-known products, there are also 'sister'... Read more...

Most Viewed

Beginning SQL Server 2005 Reporting Services Part 1
 Steve Joubert begins an in-depth tour of SQL Server 2005 Reporting Services with a step-by-step guide... Read more...

Ten Common Database Design Mistakes
 If database design is done right, then the development, deployment and subsequent performance in... Read more...

SQL Server Index Basics
 Given the fundamental importance of indexes in databases, it always comes as a surprise how often the... Read more...

Reading and Writing Files in SQL Server using T-SQL
 SQL Server provides several "standard" techniques by which to read and write to files but, just... Read more...

Concatenating Row Values in Transact-SQL
 It is an interesting problem in Transact SQL, for which there are a number of solutions and... Read more...

Why Join

Over 400,000 Microsoft professionals subscribe to the Simple-Talk technical journal. Join today, it's fast, simple, free and secure.