A Microsoft post, oh my! Let me explain. I’ve recently had the extremely unfortunate pleasure of developing an ASP.NET application. Now, we’re an OSS development studio, but under these extreme and wholly unworldly conditions, it came about that I was allowed to write this particular jewel in ASP.

Without making this already budding post too wordy, let me justify the opening paragraph by justifying my dislike for ASP with the following reasons:

• No OR-Mapping (I’m aware of the ActiveRecord port, Castle, but this still requires that you derive your own container classes). This means that in order to access a table in the database, I must create a container class (private variables to hold each value, get/set methods for each value), methods to translate these objects into another container like object class that can be passed into a stored procedure, stored procedures to create, update, and query the database, and then methods in order to iterate through data-readers (returned array like objects with my query results) in order populate said container classes. Good lordie! The time it takes to add just one field to a table and make it accessible …
• Massively tremendous quantities of magic. I’m a proponent of magical development components, as long as they don’t obfuscate the underlying framework to an extent that it teaches bad practices. If you use ASP’s built-in designer, it wraps every page in a form, hiding the concepts of POST/GET requests from the developers entirely. I could go through more examples, but this isn’t an Anti-Microsoft post, I promise!
• Entirely too many components to chose from. Numerous versions of Visual Studio, SQL Server, Reporting Services, etc. I am actually on the phone with “Empower for ISV” support right now as I type this, uninstalling conflicting bits software. Because of the many versions of many components that exist, disperse documentation exists on the web, with noone really knowing if it a particular reply to a a particular query applies to their individual case.

Ok, whew. Deep breath. Meditative breathing! Let’s move forward to discussing the concepts behind getting started with Microsoft Reporting tools.

Firstly and foremost, read http://gotreportviewer.com/. Read it all … every bit of it. If you read it you may even be able to stop reading this wordy article. It is a site written by one of the developers of the reporting tools, and is a great first step to understand who means what.

After that, let’s look at what we have to choose from. Unlike so many other tutorials and resources on this topic (including MSDN resources!), I’m only going to make one assumption: You’ve installed Visual Studio 2005.

Firstly, let’s look at the most fundamental component of your webapp. With an install of Visual Studio 2005, you will also receive SqlServer Express Edition. This is a great database environment, which provides you with a scaled down version of SqlServer, and allows you to create .mdf based databases that can be moved to a full fledged server install at a later date. All well and good right?

When we began working with reports, we did this entirely within this scope, using frontend aspx pages to host a ReportViewer (the component which displays the reported embedded within the page), a codebehind C# class that drives this page, as well as .rdlc report itself. So a typical view of what I was working on would look like this:

This was contrary to many examples I saw on the web, which used a tool which looked altogether different; something like this:

It turns out that this is referred to as SqlServer Business Intelligence Development Studio.

So, let me state here that working with reports in the way described above (rdlc based reports) is mind-bendingly painful. I am unable to go through all details within this post, as we ran into far too many problems over the few weeks we worked in this environment. To give a brief description, we went through processes where one must first set up an arbitrarily named (unmodifiable) dataset bound to our stored procedure (that would accept parameters). You then create the report, defining the parameters it will accept within the report. When we would then create a subreport, following the logical steps, dragging a subreport from the the toolbox, and then linking it to an actual report (parameters, etc). If you run the report at this point, your subreport will error out. One must add code in the codebehind to the reportviewer in order to doubly specify the parameters and how they are interlinked. The code to do so looks something like this:

Just in case you’re scanning this post, let met say:

This is NOT how you will have to design reports!

public partial class reporting_rf_all_inspections : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        ReportViewer1.LocalReport.SubreportProcessing +=
        new SubreportProcessingEventHandler(subproc);

        // Array of report parameters
        Microsoft.Reporting.WebForms.ReportParameter[] myparams = new Microsoft.Reporting.WebForms.ReportParameter[6];

        // the sale, for which, we are running the report
        string sale_id = Request.Params.Get("sale_id");

        // Array of sale dates
        string sd = Request.Params.Get("sale_dates");
        string[] sds = sd.Split(',');

        // Array of zones
        string zone_string = Request.Params.Get("zones");
        string[] zones = zone_string.Split(',');

        // Set for hip number range
        string lh, uh;
        if (Request.Params.Get("lower_hip") != "")
        {
            lh = Request.Params.Get("lower_hip");
        }
        else
        {
            lh = "0";
        }
        if (Request.Params.Get("upper_hip") != "")
        {
            uh = Request.Params.Get("upper_hip");
        }
        else
        {
            uh = "1000000";
        }
        // end hip number

        // Array for sex parameter
        int hsex = Int32.Parse(Request.Params.Get("sex"));
        string[] sex_string = new string[] { "blank", "blank" };
        if (hsex == 1)
        {
            sex_string[0] = "Filly";
            sex_string[1] = "Colt";
        };
        if (hsex == 2)
        {
            sex_string[0] = "Filly";
        };
        if (hsex == 3)
        {
            sex_string[1] = "Colt";
        };
        // end sex parameter

        myparams[0] = new Microsoft.Reporting.WebForms.ReportParameter("lower_hip", lh, true);
        myparams[1] = new Microsoft.Reporting.WebForms.ReportParameter("upper_hip", uh, true);
        myparams[2] = new Microsoft.Reporting.WebForms.ReportParameter("sex", sex_string, true);
        myparams[3] = new Microsoft.Reporting.WebForms.ReportParameter("sale_dates", sds, true);
        myparams[4] = new Microsoft.Reporting.WebForms.ReportParameter("zones", zones, true);
        myparams[5] = new Microsoft.Reporting.WebForms.ReportParameter("sale", sale_id, true);
        ReportViewer1.LocalReport.SetParameters(myparams);
    }

    void subproc(object sender, SubreportProcessingEventArgs e)
    {
  // Sub Report 1
        if (e.ReportPath == "inspections_sub") { 
            string conn = ConfigurationManager.ConnectionStrings["TMC"].ConnectionString;
            int horseID = Int32.Parse(e.Parameters["horse_id"].Values[0]);
            SqlConnection dbConnection;
            // Note! New DB Connection. Ahh!
            dbConnection = new SqlConnection(conn);
            string strSelectSql = "rf_looks_by_horse_id";

            //Open the connection
            dbConnection.Open();

            SqlCommand command = new SqlCommand(strSelectSql, dbConnection);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(
                new SqlParameter("horse_id", horseID));
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
            adapter.Fill(ds);
            e.DataSources.Add(new ReportDataSource("DataSet19_rf_looks_by_horse_id", ds.Tables[0]));

        }
    }
}

Are you disgusted? You should be! There are endless problems that can occur at this point, too many to go into. Needless to say, it becomes an amazingly painful process which should be rather simple. Adding insult to injury, the error messages thrown are slightly less than helpful.

Finally, I went out in search of the Reporting Services tool that I had seen others use online in various tutorials. This documentation indicated that I needed to install SqlServer Express Advanced Edition (do not do this). I did this, and low and behold I was presented with the SqlServer Business Intelligence Development Studio tool. After working with this for a matter of minutes, I was able to generate a report that included looping through multiple rows returned from a stored procedure, and then embedding a subreport based on one of those returned values.

Amazing! No codebehind, just basic linking of parameters! So I hit F5 to deploy my newfound report, but promptly receive an error about the lack of the Reporting Server Service. In order to actually deploy these reports to the web, you must install the Reporting Services components that are included with SqlServer2005 Standard (What our client provided), Developer (equivalent to Enterprise but runs on XP), or Enterprise Editions (for servers specifically).

At this point, I cannot configure the Reporting Services properly (due to a conflict of the various versions of SQLServer on my development machine) ... I’m on hold with “Empower for ISV’s” at Microsoft. Please give me an hour or two and I’ll finish this entry once I’ve worked through this and the service is installed.

.....
....
...
..
.

Ok! Service installed: I have a reporting server, abstracted from my actual ASP application, which allows me to develop reports w/ subreports without additional codebehind mumbo-jumbo.

So, in summary:

• Do not attempt to develop reports based solely on the Microsoft Reporting Tools included with Visual Studio, unless it is absolutely necessary that you embed complex application level logic into your reports (it’s probably not).
• Do develop reports using the blah blah blah app and link your ASP application to a Reporting Server.
• The above can only be done by installing the Reporting Components from a fully complete version of SqlServer2005.
• Don’t install too many instances of SqlServer tools, as they may get confused behind the scenes.
• What should be a relatively simple task has consumed precious hours of my time.

Now, this is not to say that Reporting Services is not without it’s advantages. Once you’ve gotten it running, it has some nice features. I truly do apologize if this article came off a bit ranty. :) As I am re-reading it before posting it now (I wrote it while on hold during a 6 hour call to Microsoft) I realize that I have done a bit of shoulder crying, but read past it and hopefully you’ll get a much quicker start into developing your reports than I did. I guess I could have just posted the “So, in summary …” bit :)