Tag Archives: reporting

Using FusionCharts in SSRS reports

Microsoft’s SSRS is pretty advanced reporting system with multitude of advanced features. SSRS also has charting capabilities, but it’s somewhat lacking compared to more advanced desktop or web charting suites

On the other hand FusionCharts offers very cool charting package with gazillion of chart types and very cool features. But it uses JavaScript engine and renders charts client-side only!

What if there was a way to marry the two technologies together – to render cool FusionCharts in advanced SSRS repots? Continue reading →

SSRS and HTML rendering of ordered list

Microsoft’s SQL Server Reporting Services supports rendering of HTML tags, but for some reason that support stuck in 1990s – only very limited set is supported. And even using that set is problematic.

Case in point – ordered list. While officially supported – the way it is rendered is the stuff nightmares are made off. Jumble of original tags generously intermixed with DIVs and SPANs – it’s a wonder it renders at all.

And sometimes it doesn’t. If you try to view a report in Internet Explorer (especially from older, but still actively used versions of SSRS like 2008) numbering get screwed.
Continue reading →

SSRS viewer doesn’t work on IIS7 64bit

If you’re developing an ASP.NET application that utilizes Microsoft’s SSRS ReportViewer control, you may experience a weird behavior while trying to use it on 64 bit version of IIS7 (Windows Server 2008 or Windows 7) – nothing is displayed in the main report area and even icons, such as Print, Export aren’t rendering.

One possible cause for this – your application is using Application Pool with 32bit mode enabled. If so – disable it or switch to a different application pool. Chances are this will fix the above issue.

Pass NULL value to SSRS Report from ReportViewer

When working with ReportViewer ASP.NET control to display SSRS report on an ASPX page you can use ReportParameter class to create and pass parameters to the server, e.g.

aParamList.Add(New ReportParameter("MyParam", sValue, False))

Here aParamList is a Generic List of ReportParameter objects that you use in ReportViewer’s ServerReport.SetParameters method, "MyParam" is a parameter name and sValue is a string variable with value for the parameter. Third parameter – visibility, when set to False makes the parameter hidden.

All is well, but what if you need to pass NULL parameter? Unfortunately ReportParameter‘s constructor accepts only string type for it’s value parameter, so you cannot pass something like DBNull.Value or Nothing. Fortunately there’s an easy solution: Just set you string variable representing value to Nothing:

sValue = Nothing
aParamList.Add(New ReportParameter("MyParam", sValue, False))

Voila! Desired NULL is passed as a parameter value (on SSRS side parameter has to be set to accept NULL values).

Thanks Tincy for the original idea.

SSRS: How to implement build-in parameter based on external parameter passed from ReportViewer

Imagine following scenario: an SSRS report has a dropdown list “lookup” parameter based on a stored procedure. When report runs, user selects a value from the dropdown, clicks “View Report” and report is generated. The challenge is – the “lookup” parameter (and underlying stored procedure) needed to be filtered by another “filter” parameter – and this one is not available in SSRS interface, but instead is passed from ReportViewer control from an ASP.NET application.

In order to successfully implement this use case 2 items need to be addressed:

First in the report itself parameters need to be ordered in such way so “filter” comes before “dropdown”. Parameters can easily be arranged in Business Intelligent Development Studio, by expanding Parameters node, selecting a parameter, and using arrow buttons in Report Data menu.

Second in the ASP.NET application, configuring ReportViewer control (setting credentials, server URL, report path and our “filter” parameter) needs to be done in Page_Init event in the “If Not IsPostback” block.

Error using Crystal Reports 2008 in ASP.NET application on 64bit server

If you’re using Crystal Reports 2008 in your ASP.NET application, and after deploying to a 64bit server getting following error:

An error has occurred while attempting to load the Crystal Reports runtime. Either the Crystal Reports registry key permissions are insufficient, or the Crystal Reports runtime is not installed correctly. Please install the appropriate Crystal Reports redistributable (CRRedist*.msi) containing the correct version of the Crystal Reports runtime (x86, x64, or Itanium) required. Please go to http://www.businessobjects.com/support for more information.

then switch your application to 32bit mode. In case of Windows 2003/IIS6 entire server will have to be switched, in case of Windows 2008/IIS7 a dedicated 32bit application pool can be established for your application.

Dynamic number of fields in a query for SSRS 2005

I’ve encountered a problem using SQL Server Reporting Services 2005 (I am using SSRS from an ASP.NET application). Underlying stored procedure can return different number of fields depending on one of the parameter. I.e. if that parameter is NULL the fields returned are

A B C

if that parameter is “1”, the fields returned are:

A B C D_1

if that parameter is “1,2”, the fields returned are:

A B C D_1 D_2

etc. And I need to be able to use those fields with the report (in particular – I need to group on them).  But report’s dataset has a fixed number of fields based on original query and new dynamic fields are inaccessable.

The solution is to modify report definition. I am already doing it to insert custom groups in the report, and reports dataset is exposed via definition as well.  The definition can be downloaded via GetReportDefinition of SSRS Webservice and serialized into a class  (See Tutorial: Updating Reports Using Classes Generated from the RDL Schema for more details). The DataSet in that definition contains array of fields that can easily be resized in code to accomodite for new fields. This way the fields in the definition will match fields returned by the query and can be accessed within the report.