Skip to main content

SQL Server Reporting Services, and ASP.NET

This post I would like to talk about delivering Microsoft SQL Reporting Services via an ASP.NET application.

Reporting Services includes an ASP.NET web control. I've found this control to be useful when I want to make the most of Reporting Services' ease of report definition (.rdl files), but don't like the way Reporting Services' renders their parameter options. Using ASP.NET, I suddenly have available to me, all the flexibility of ASP.NET and it's web form controls.

For example, you ou may want to control how those form controls are loaded in your web form - e.g. by writing some code in the On_Load event, you could pre-fills the text value of a TextBox, or pre-ticks some tickboxes in a CheckBoxList. This way, default values can be defined which are commonly used by business users.

As well as the report parameters, another key area of interest is security. In general terms, the SQL Server from which you're sourcing your report data will be configured one of two ways: a) all data is accessed via a single, generic, application account, or b) specific user accounts are created for each individual user (hopefully using their windows logon account).

A third key area is printing. More often than not, business users want to print their reports. In practice with the ASP.NET Reporting web control, this aspect is, oddly enough, intrinsictly linked to the security of your reports (see paragraph above). This is due to the architecture Microsoft have used for report printing, which will be discussed later.

OK, enough with the introductions, let's look at some code.

First, some quick background info:
  • Development environment is Microsoft Visual Studio 2008
  • Working with a project that is a plain ASP.NET web site
  • The targetted framework is .NET 2.0
  • The website can be deployed on the same server on which Reporting Services runs, but this is not a requirement
You need to add the following ReportViewer components as references to the Visual Studio project:
  • Microsoft.ReportViewer.Common
  • Microsoft.ReportViewer.Web
These two components can be found in the Microsoft Report Viewer Redistributable 2005.

In your .aspx page, register the WebForms assembly, with a line like the following:
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>

Note that the TagPrefix property will of course refer to the prefix you will use to create the Reporting Services web control in the .aspx source.

Next, we need to add the web control to the .aspx source:
<rsweb:ReportViewer ID="rptOverallTotals" runat="server" height="100%" ProcessingMode="Remote" Visible="false" AsyncRendering="true" ZoomMode="PageWidth" ShowPrintButton="false" ShowDocumentMapButton="false">
<ServerReport
ReportServerUrl="http://localhost/ReportServer"
ReportPath="/MyReportExample/FirstReport" DisplayName="My First Report" />
</rsweb:ReportViewer>

These two things alone are all you need to display a report in .aspx page, assuming the report doesn't require any parameters to be filled in. You will notice some attributes are specified in the tag. More on those later, however it's important that the ProcessingMode attribute is set to Remote in order for this example to work.

It's not often a report is needed that doesn't need any parameters specified. In order to get those parameters parsed in to the ReportViewer control, you need to specify them during the page's On_Load event. You will need to do the following to the page's code-behind file:

Add a reference to the Microsoft.Reporting.WebForms library
using Microsoft.Reporting.WebForms;

In the Page_Load function, create an array of ReportParameter variables:
ReportParameter[] theseParameters = new ReportParameter[1];

Note the number of items in this ReportParameter array should reflect the number of parameters your report contains.

Let's assume the parameter you want to parse in is a string, and its name is "DisplayText". Specify the first parameter array's item as a ReportParameter indicating this:
theseParameters[0] = new ReportParameter("DisplayText", "Hello World");

And finally, parse the ReportParameter array into the web control's ServerReport object:
rptOverallTotals.ServerReport.SetParameters(theseParameters);

Hopefully this is enough information to set you on your way. In my next post, I'll talk about the different attributes of the ReportViewer web control, including Visible, ProcessingMode, AsyncRendering, ZoomMode, ShowDocumentMapButton, and ShowPrintButton. I will also talk about security, and how to:
  • Parse the credentials of the user authenticating to your site through to Reporting Services, or
  • Take on the credentials of a generic application user

Comments

Popular posts from this blog

Thoughts, shortcomings, gotchas on SPFx Dynamic Data capabilities

It's the festive break, and I thought I'd try the new Dynamic Data capabilities that recently went to General Availability in SharePoint Framework 1.7.

I've been building a lot of React components lately, and all the SPFx web parts and application customisers with visual elements we create at Engage Squared, are built on React.  Dynamic Data in SPFx introduces a whole new world of modularity that we haven't had before. We can now split up the page elements into multiple web parts that, in the past, have been combined as one web part so state can be passed between them.  Doing this gives control back to the page author, with the ability to position components how they wish.

Breaking components up in to individual web parts also changes the way the components are designed, and forces the developer to leverage the responsive capabilities of modern pages.  Modern pages are designed from the ground up to work on many different screen sizes, and as long as each individual co…

Apps for SharePoint 2013 - Client and Server-side code

It's about time I blew the dust off this blog. Tonight I did a presentation at the Melbourne SharePoint User Group entitled Apps For SharePoint (2013). It included two demos based on the App For SharePoint 2013 solution template in Visual Studio 2012.

The two demos illustrated how you can create a separate ASP.Net Web Application. Demo 1 showed how easy it is to hook in to SharePoint to obtain List properties via server-side code (populating an ASP.Net Repeater Control). Demo 2 showed what you can do with SharePoint 2013's Javascript hooks. Specifically, using SP.UI.Controls.js from the /_layouts/15/ SharePoint folder to pull chrome elements out of SharePoint, and render them in your ASP.Net web app.

No animals were harmed in the making of these demos, but a few articles kindly provided on the Microsoft MSDN site helped put it together:
How to: Set up an on-premises development environment for apps for SharePointHow to: Create high-trust apps for SharePoint 2013 using the se…

SharePoint User Profiles and Properties error

I recently encountered an issue with the Shared Services Administration page for User Profile and Properties (the page where you schedule an Active Directory profile import).

The page in question is in the Shared Services Provider site, e.g. http://server:port/ssp/admin/_layouts/ProfMain.aspx

The error that gets displayed is:
An error has occurred while accessing the SQL Server database or the Office SharePoint Server Search service. If this is the first time you have seen this message, try again later. If this problem persists, contact your administrator.

The error was odd, because I knew SharePoint Search was functioning (search crawls were running as normal, and search queries were being filled in the web front end). After a bit of investigation, I found the following table to be missing some records that were required.

So in actual fact, the error being referred to when accessing the SQL Server database was that some expected records weren't in the table being looked up, not that …