Dynamic Query Data Extension Available

/1 Comment/in /by

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.


 

URL addressability vs. RS Web service

/2 Comments/in /by

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.

Time to migrate

/3 Comments/in /by


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 ):



  • If you haven’t done this already, back up the encryption key of the old RS installation. RS uses encryption keys to encrypt vital information, such as credentials, connection strings, etc. in the RS catalog. You won’t be able to point the new installation to use the old catalog if you don’t migrate the encryption keys. To extract (backup) your encryption keys, issue the following command:

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).



  • If you need to move the RS catalog to another machine, back up the ReportServer and ReportServerTempDB databases and restore them on the new SQL Server  instance.
  • Install RS on the new server. In the RS setup, specify different database names that the default ones, e.g. ReportServer1 and ReportServerTempDB1. We will later drop these databases since we will re-configure RS to point to the old RS catalog.
  • Verify that RS is installed properly by browsing to the Report Manager portal.
  • Make sure that the new installation is the exact same version as the ole one. For example, if the old RS installation has SP1 applied, apply SP1 to the new installation as well. Otherwise, you will get an error that the RS catalog version is different.
  • Use the rsconfig utility to re-configure RS to use the old catalog, e.g.:

resconfig.exe –c –s -d -a -u -p



  • At this point if you try to instantiate RS you will get rsNotActivatedError. Why? Because, RS is trying to decrypt the catalog data with the new encryption key which doesn’t match the old. Therefore, the last step is to migrate the encryption keys.
  • Use the rskeymgmt utility to apply the old encryption keys, as follows:
    rskeymgmt -a –f -p

 


That’s it.. and don’t forget to store the encryption keys file in a save place.





 

Don’t want to buy VS.NET to author reports? Try Cizer Report Builder

/0 Comments/in /by

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?

School is here…and so is my book

/4 Comments/in /by

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!

New RS features in SQL Server 2005 Beta 2

/1 Comment/in /by

For those of you who would like to know what new RS features have made it to SQL Server 2005 Beta 2, here is the list:

  • Ability to load report projects in the new SQL Server 2005 Intelligence Development Studio IDE. The consequence of this is important. You don’t have to purchase VS.NET anymore just to get the Report Designer because the Intelligence Studio IDE comes bundled with the Report Designer.
  • Integration with the SQL Server Management Studio. The third toolbar button on the Registered Servers toolbox brings to the RS configuration. Now you have a single console to connect and manage the RS configuration properties, security, jobs and shared schedules.
  • The Analysis Services MDX and DMX Query Designers that allow you to craft MDX and data mining queries in a graphical way. Those of you targeting Microsoft Analysis Services will probably appreciate these two designers. Both designers operate in design and query modes similar to the Graphical and Generic Query Designers included in version 1.0. When used in Graphical mode, the designers allow you to drag and drop dimensions and measures to the query pane to make the tedious task of authoring MDX queries much easier. In addition, Analysis Services 2005 supports parameterized MDX queries and you can declare any dimension member as parameterized. If you have time to play with SQL Server Beta 2, check the new query designers. They really rock!

The rest of the RS new features will be included in SQL Server Beta 3 scheduled to be released around Q2 2005.

 

I highly recommend you start evaluating SQL Server 2005 as soon as you can. There are so many new and cool features that it may take you months to go over them. On the downside, you need a beefed up testing machine. Based on my experience, the Beta 2 performance is far from being optimized. Loading the Books Online, Management and Intelligence Studio pretty much take up all of the 700 MB of RAM allocated to my Virtual PC. Ouch!

 

If you need community support, check out the SQL Server 2005 newsgroups at http://communities.microsoft.com/newsgroups/default.asp?icp=sqlserver2005 .

Happy yukoning!

TechEd Europe 2004

/1 Comment/in /by

Well, I have to admit that I will miss the echo of the drums and buzz surrounding TechEd Europe 2004. There are many new and exciting things coming for developers next year which means a lot of reading too J

 

My favorite session (besides DAT354 which I presented of course) was Scott Guthrie’s one about the next generation of ASP.NET 2.0. I enjoyed also the Tim Sneath’s session. SQL Server 2005 integration with .NET is cool although I have to admit that I was a bit disappointed that it supports only external .NET assemblies that have to pre-registered in similar way that RS integrates with .NET assemblies. Why can I write the whole darn thing in managed code? Hopefully the next SQL Server incarnation will finally kiss T-SQL good bye … Another sad thing was that .NET 2.0 Enterprise Services still require COM+ interop. COM dies hard…

 

The buzz surrounding Reporting Services was impressing too. The RS sessions were the ones most attended and looks like many of you are planning to give Reporting Services a spin and/or bring it to the max. Stay tuned; many good things are happening in the RS 2005 including the next generation of WinForm and ASP.NET controls…

 

I enclose download links to my presentation and source code for those of you who missed the event. 

  • DAT354 Presentation “Building SQL Server Reporting Service Applications”
    #
  • Source code
    #
  • Source code script
    #