ThinkingCog

Articles written by Parakh Singhal

Microsoft Reporting Services – The quirky uncle in the family

Key take away:

A Microsoft Reporting Services report needs a strongly typed dataset to work with, but it is generally overridden with an in-code dataset that is used to power the report. The report is sensitive to the name of the in-code dataset, and only works if provided with same name as what gets recorded in the XML innards of the report.

Read on:

Recently I got an opportunity to help a team shift their reports from Microsoft Reporting Services (MRS) to SQL Server reporting Services. Now I had left working with Microsoft Reporting Services back in 2010, when my team migrated all the reports to hosted SSRS environment, so I spent some time creating a simple yet effective proof of concept ASP.NET application working with a Microsoft Reporting Services report that I can show to the team in transition, on how import MRS reports in hosted SSRS environment. Nearly two-third of my time got spent in dealing with a quirk in Microsoft Reporting Services, which is providing the correct name of the dataset in the code, as the report wants it. I remembered this quirk from my days of working with Microsoft Reporting Services, but had forgotten the specifics of remedying the problem.

Microsoft Reporting Services is a port of SSRS that produces report(s) you can store in an ASP.NET application, along with the app contents and use the computing resources of the web application server to process data and serve the rendered view to the client. SSRS reports are bit more capable, easier to work with and can be offloaded to a dedicated report server, and thus computing resources (compute cycles, memory, bandwidth etc.) be saved on your primary web application server. Microsoft Reporting Services reports are a good choice when your reporting needs are simple, report generation is less frequent, and when on a shoe string budget. Otherwise, try to always opt for hosted SSRS environment.

clip_image001

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Figure 1. Setup in Microsoft Reporting Services compared with hosted SQL Server Reporting Services (SSRS)

Microsoft Reporting Services requires a strongly typed dataset to work with, and process data from it. For SSRS the requirements are the same, but the process of making and dealing with a strongly typed dataset is easy and somewhat hidden. Both the types of reports use Report Definition Language (RDL), a format used to convey the report contents and related artifacts. RDL in turn is based on XML, which means that RDL is case sensitive and any change in the schema can potentially render your report unusable. SSRS reports inherit the extension of “.rdl” while Microsoft Reporting Services reports end with “.rdlc” extension, with “c” in “.rdlc” denoting client, i.e. processing at application server.

Now that we have understood a little background about the Microsoft Reporting Services, let’s understand the quirk.

Almost always, when developers deal with Microsoft Reporting Services reports, they make a report with a very simple strongly typed dataset; connect artifacts on the report with this simple dataset (tablix, matrix, charts etc.), but then override this dataset in their application code, containing data table(s) filled with data from the actual query that essentially produces the same columns, but different data qualitatively. I usually do this because strongly typed datasets cannot be de-bugged, and they are generally more time consuming to deal with than actual verbose code. Once the functionality to override the dataset is in place the strongly typed dataset can be removed entirely. But till the overriding facility is not in place, you have to retain the strongly typed dataset in place, otherwise the report won’t build.

Here I present a simple example consisting of three tables in a data model. The tables are self-explanatory in nature. I have filled the tables up with some junk data. I will use these to make a report in an ASP.NET Webforms website, and will show where one needs to be careful in producing code that over-rides the strongly typed dataset powering the report.

clip_image002

 

 

 

 

 

 

 

 

 

 

 

Figure 2 Data model

Use case: I might bind a tablix on an MRS report with a simple query of “Select FirstName, MiddleName, LastName, DateOfBirth from Person”. While the actual query that I want to run is “Select FirstName, MiddleName, LastName, DateOfBirth fromPerson inner join Address on Person.PersonID = Address.PersonID where Address.State = @State” to get me details of people living in a certain state. So the columns that need to go on report are the same, but the data that will now appear is different qualitatively.

clip_image004

Figure 3 Dataset name appears as “DataSet1” in MRS report powering my tablix.

The moment you insert a tablix, MRS is going to prompt to create a data source containing a dataset powering the tablix. Once you finish the operation, you will find a strongly typed dataset in the App_Code folder of your ASP.NET web application.

clip_image005

 

 

 

 

 

 

 

 

 

 

Figure 4 Dataset name as it appears in a strongly typed dataset.

You can double click the typed dataset and get the name of the strongly typed dataset by clicking in the blue section and bringing up the properties. The name came “DatabaseDataSet” came up by default. You can change that, but what matters is what is getting registered in the report.

clip_image007

 

 

 

 

 

Figure 5 The name of the dataset as it appears in XML that makes up the report is of most importance.

Now here’s the code that you have to produce very carefully.

Make sure that when you are passing the report data source object to the report, it bears the same name as that of the dataset bound to the report, and more appropriately as found in the XML of the report. It doesn’t matter what is the name of the strongly bound dataset.

To see the name as it appears in the report’s innards, open with the inbuilt XML editor or just open it in Notepad and look for the entry with the name.

clip_image009

Figure 6 The name of the dataset that you provide in a report data source should be the same as in report's innards.

This is quirk is only applicable on the name of the dataset that you need to provide. The name of the data source can be anything, and so is the name of the data table that carries the actual data in the dataset.

The sample code consists of two ASP.NET websites. One consists of a strongly typed dataset, while the other one does not consists of it. Both the websites have the same report being powered by an in-code dataset. The code was made in Visual Studio 2012 and targets .NET Framework 4.5. The code can be downloaded from: