Reporting Work

At work I've been assigned to upgrading reports that are in Access and Crystal Reports into SQL Server Reports. The project website is being developed in Visual Studio 2008 with the 3.5 framework. Naturally we wanted to develop the reports and have them contained in the 2008 project. Visual Studio 2008 supports reports but does not support a Report Server Project. My frustrations and solution to reports in VS 2008 is found below, however, we decided to stick with developing the reports in VS 2005 (BIDS).



Here is the best solution I was able to come up for building reports in Visual Studio 2008.


1. Create a new web application project (or open the your current project).

2. Add New Item to your project. Select a Report and give it a name. Note that these reports are rdlc not rdl. ( http://msdn.microsoft.com/en-us/library/ms252109(VS.80).aspx for information on rdlc versus rdl)

3. Now to add the dataset for the report data. Add New Item again, this time add a DataSet. This should walk you through a wizard for adding a new DataSet. Note that if you have a connection string in your web.config file to point to a database, you WILL still NEED to add a NEW connection string. It will give you servers to choose from that currently exist in the Server Explorer. Once you add a new connection string through this dataset wizard it will be avaliable for the next TableAdapter that you add. Grrrr... I'm going to stop here because I have many gripes...

4. A tableadapter for a dataset that you want for a report should now exist. Go back to the Report rdlc file (you may have to click around on the design area to get all the menu items to show up) and select Report, Data Sources..., find the DataSet that you just created and Add to Report

5. Build the report now that you have the dataset information.


Now let's say you have built many reports and want to use 1 report viewer to dynamically display the reports in a report viewer. Add a new page or open an existing aspx page. Add a Report Viewer to the page. (This is were I think things get ugly, and there may be a better solution, feel free to post a comment if there is a better solution)

Report View definition on aspx page:




The code behind to have the report viewer set the report:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Report is going to run local on the website
ReportViewer1.ProcessingMode = Microsoft.Reporting.WebForms.ProcessingMode.Local
ReportViewer1.LocalReport.DataSources.Clear()
Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ReportConnectionString").ConnectionString)
Dim cmd As New SqlCommand()
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "sp" & Request.QueryString("reportname") ' Build stored procedure string from an example query string
Dim sqlAdapter As Object
Dim dt As Object

' Depending on what report you want to display, you'll need to call the right table adapter, hence why they are defined as objects at first
sqlAdapter = New DataSet1TableAdapters.[NameDefinedInDataSet1]TableAdapter()
dt = New DataSet1.[NameDefinedInDataSet1]DataTable

sqlAdapter.Fill(dt)
conn.Close()

Dim Source As New ReportDataSource("DataSet1_[NameDefinedInDataSet]"), dt)
ReportViewer1.LocalReport.DataSources.Add(Source)
ReportViewer1.LocalReport.ReportPath = "Report.rdlc"
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Now if you followed that jumbled piece of code, congrats. If you're smart you'll have the name defined in the data set match up to the report name so you can use the query string (or whatever other method). You'll still need a case statement or bunch of if's for defining the sqlAdapter and dt. Also, the ReportDataSource will be looking for the specific DataSet1_[NameOfTableAdapterDefinedInDataSet].

If you are thinking what on earth?, so am I. Hence why we decided to stick with the Reporting Service Project even though the project will need to be seperate and can only be done in Visual Studio 2005.

Cheers, I'm out, and I do not want to think about this again until reporting improves for Visual Studio 2008.