Microsoft has just published my article “Microsoft Reporting Services in Action: Extending Microsoft SQL Server 2000 Reporting Services with Custom Code“ on MSDN. Excerpted from my book Microsoft Reporting Services in Action, this article demonsrates how to implement advanced features in reports by integrating them with .NET assemblies.
Please join me at the following events to learn more about Reporting Services:
Looking forward to meeting you at one of these events!
Often, report enabling Internet-facing applications require replacing the default RS Windows-based security with Forms Authentication. This allows clients to request reports by URL with the all the goodies that URL addressability entail, namely support for interactive features and the HTML Viewer toolbar. While RS Forms Authentication is well-documented (see the Forms Authentication whitepaper by Microsoft), something which may not be so obvious is that Forms Authentication can be used not only with URL addressability but also with the RS web service. Let’s say you want most of your reports to be requested by URL but some to be generated on the server-side of the application by SOAP. Or, you need to manage a Report Server installation configured for Forms Authentication from a WinForm client by calling the management SOAP APIs. The RS Forms Authentication model supports all these scenarios. All in all, it boils down to to storing the authentication ticket (cookie) received from the LogonUser call on the client side and passing it back with each request. I whipped out a simple WinForm app (sorry C# only, but they are free converters out there for C#->VB ) to show how this could be done. This sample doesn’t have any bugs! Common, we all know this is not true :-). Please let me know if something buggy raises its ugly head. Usual disclaimers apply also…you are on your own basically. DISCLAIMER: One thing that you may need definitely to change if you are planning to retrofit my code to a web app is way the app stores the cookie. You will notice that the sample app stores the cookie in a static class member. This is all fine with WinForm apps running on single thread by definitely no-no for web applications where you need to be aware of multi-threading and multi-user issues. If the web app is the same one that requests reports by URL, the simplest solution may be not to store the cookie at all but to get it from the current HTTP context, similar to the original implementation of the overridden proxy class that comes with the MS sample.
For time being, the sample Forms Authenticator app can be downloaded from here.
I have recently discovered the Excel Add-in for SQL Server Analysis Services. If Office Web Components are overkill for your Analysis Services smart client deployment or dev requirements, check out this toy. It requires Excel 2002 and higher installed on the user machine. Once connected to Analysis Services (standard, offline and HTTP connectivity is supported) you can create easility pivot-style reports.
One cool feature that the add-in supports is exporting an AS cube to a local offline cube so you can move it between machines. More cool features:
The Excell Add-in for SQL Server Analysis Services is one of the several Office solution accelerators.
I finally understand why Office is positioned as an integral component of the Microsoft Business Intelligence Platform 🙂
My book is shipping on Amazon.com. It is also available on Barnes & Noble.
Hurry up until supplies last 🙂
One of the limitations of RS version 1.0 is that it doesn’t allow you set the connnection string (a.k.a. data source) during runtime. For example, you may need to support separate databases per client and based on the logged on client you may need to redirect the user to the appropriate database.
To address this issue, Toby Riley developed an interesting dynamic query data extension which allows you to pass the connection string as a report parameter. If you don’t want to pass the connection string as a parameter, you should be able easily to retrofit the code to read the connection string from a configuration file. For more information how this could be done read my posting at http://tinyurl.com/3tcpj.
Toby’s extension is buit upon mine ADO.NET custom dataset data extension. You can download and give the Dynamic Query Data Extension a spin at http://workspaces.gotdotnet.com/appworld.
My custom ADO.NET dataset extension can be downloaded from http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=B8468707-56EF-4864-AC51-D83FC3273FE5.
Judging by the questions post on the RS newsgroup, many developers are often confused about which implementation choice will best meet their integration requirements. I hope the following comparison will help you evaluate them:
URL addressability (HTTP-GET)
· Implementation–The report request is submitted on the client side of the application directly to the Report Server. For Internet reporting, the Report Server has to be accessible on Internet.
· Functionality–Limited to requesting reports only.
· Interactive features (drilldown, drill-through, etc.)–Yes
· HTML Viewer toolbar–Available
· Integration effort–Easy, especially if the HTML Viewer meets your reporting requirements. For example, you can hardcode the report URL address in a hyperlink.
· Security–Windows-based (intranet reporting) or Form Authentication (Internet reporting)
· Vulnerability– A malicious user can see and exploit the report request
· Flexibility–Less flexible
· Performance—Better than RS Web service
RS Web Service (SOAP)
· Implementation– With web-based applications the report request is typically submitted on the server side of the application.
· Functionality–Exposes all RS functionality, including requesting reports, managing the report catalog, subscribed delivery, etc.
· Interactive features (drilldown, drill-through, etc.)–No
· HTML Viewer toolbar—Not available
· Integration effort– More involved. You need to take care of downloading the report images explicitly, handling report parameters, and rendering the report to the end user.
· Security– Typically Windows-based authentication
· Vulnerability– More secured, since the report is generated entirely on the server side.
· Flexibility– Flexible integration scenarios, e.g. reporting off ADO.NET datasets, evaluating business rules, unattended report generation, etc.
· Performance– The report payload needs to be serialized on the server-side to a byte array and de-serialized on the client side.
Recently I had to migrate a Report Server installation from one server to another by keeping the Report Server catalog intact. If you find yourself in the same situation, here are the steps (hope I recall them right ): rskeymgmt -e -fa:\keys.txt -p<password>, where password is any password you want to use to secure the encryption keys. You will need it later to restore the encryption keys on the new installation. For more info about the encryption keys, read Bryan Keller’s posting (http://blogs.msdn.com/bryanke/archive/2004/03/18/92102.aspx). resconfig.exe –c –s That’s it.. and don’t forget to store the encryption keys file in a save place.
rskeymgmt -a –f
Many of you probably know that one of the Microsoft RS partners, Cizer, offers an ad-hoc reporting tool called Cizer Query (mentioned in my book).
Now, Cizer has released a report designer tool – Cizer Report Builder, as an alternative for authoring reports if you don’t have or want to purchase VS.NET. According to the vendor:
“Cizer Report Builder (CRB) 2004 v1.00 is now available. Bringing full-featured production report design into the browser, CRB makes the rapid deployment of reporting with Microsoft Reporting Services a reality. Cizer .NET Reporting now provides both ad hoc and production report design in a single web interface with zero client installation or download required. Charts, graphs, parameters, images, calculations and formatting are now easy to develop and manage. Other highlights include:
Guided Report Development Wizards
Links to Cizer Quick Query
Access to Multiple Data Sources
Drag and Drop Placement of Report Objects
Local and Global Libraries for Report Templates, Queries and Parameter Panels
Private and Public Report Sharing
Saves as RDL Format Which Easily Exports to Multiple Formats Including Excel and PDF
Deploys to Microsoft Reporting Services to Support Enterprise-Wide Distribution
Cizer Report Builder is available for purchase on the Cizer website (www.cizer.com). “
Please let me know if you like Cizer Report Builder. Do you think that it provides a viable alternative to the RS Report Designer?
My book “Reporting Services in Action” is finally available in print format. For time being, it can be purchased only from the publisher’s website (http://www.manning.com/lachev). It will probably take a couple of weeks before the book reaches other outlets such as Amazon.com.
Many thanks to those of you that will purchase my book. I sincerely hope you will find it as much fun to read as it was to write. I am always interested to know my product meet your needs. Please drop me a line to let me know what you liked or disliked about my book.
Happy reporting with Microsoft Reporting Services in Action!
