We had a requirement which was to give a (WinForm) system, which uses a SQL Server 2005 Express database, simple table structure ad-hoc reporting. Another requirement was to allow all the reports to be exportable to other formats i.e.: HTML, CSV, Excel and PDF. Now most of the developers around the table didn't like the sound of these requirements because it would have to involve a lot coding to deliver all the reporting functionality; then there was the export functionality which involved a fair bit of work; the dynamic creation of SQL was the only fairly easy thing. Then I made the suggestion to use RDL with reporting services and/or report viewer control. The reason was that most reporting functionality is automatically written and the export functionality was also written for us as well. This was well received and was then adpoted as the method we would use. Now I was tasked with validating how we could deliver the requirements using RDL. All this was happening before the release of reporting services 2005 express so I started to look into the report viewer control and found these issues.
When using local processing, the Report Viewer control can’t do the dataset creation. The dataset(s) that are defined in the report will need to be created within the program; also the dataset(s) will also need a table adapter created and bound to the report viewer control. A solution to overcome this limitation is to define a dataset with a set number of columns which would contain generic names like: Col1, Col2, Col3 etc. Then specify that dataset would use a stored procedure, which will take a string parameter to get the data. All the stored procedure will do is execute the parameter, as it will contain the SQL statement with all the fields being used in the select statement, aliased to the generic names in the dataset; all the unused generic fields will be set to null.
Another issue I came across was with the report viewer. When it had loaded the report it kept that layout definition even when loading a different report. The way to solve this problem was to reset the control, rebind the dataset and then refresh the report.
One more thing I found out with this control was the exporting for local processing only supports Excel and PDF.
Now that there is a Report Services 2005 Express version we also took a look at this. The one issue that I have found currently is with the report viewer when using server processing. The export formats were still only Excel & PDF, there should have been HTML as the other export format that Report Services 2005 Express supports.
To meet our requirements we going use Reporting Services Express and the Report Viewer control because it is going to save a lot of code writing dealing export and viewing the report. So all we have deal with is the creating of the report. N.B. There is some RDL generation code available in Books on-line.