SQL Server Reporting Services (SSRS): Databinding with CustomReportItem implementation

For my current project, I've been attempting something that would seem simple to do, but can't be done in Sql Server Reporting Services (SSRS). I am trying to draw 2 simple rectangles on the grid of a scatter (XY) chart. The reason for doing this is to visually indicate good and bad regions of data. But when the report is rendered (at least using the Html viewer) the rectanges I draw above the chart have moved. This is because items cannot overlap in the web viewer.

I have come to find that I can create a CustomReportItem based on any image I can draw or retrieve (Gdi+ or any other method of creating an image like the file system or a remote url). So I started looking at the alternative charting options I had available to me through my client's software licenses.

My options were charting solutions by ComponentArts and Infragistics (UltraChart). As it stands the ComponentArts documentation was dismal and I am still waiting to hear back about a message I posted on their forum over a week ago. With Infragistics I found the documentation much better. When I needed help I got an immediate response.

So I am using Infragistics for my charting needs. The solution involves creating a custom report item which allows the user to define a set of properties which I then use to design the chart. I am actually using the Web version of UltraChart, which is not supported, but works well. I could have used the WinForms version, but it doesn't seem to make any difference and I didn't have it installed at the time I began my discovery.

The next problem I encountered was creating the CustomReportItem. Again, like the ComponentArts library, the documentation was pitiful. But here are the links I did find to nonetheless. This was difficult to track down :

Rdl definitions: http://download.microsoft.com/download/c/2/0/c2091a26-d7bf-4464-8535-dbc31fb45d3c/rdlNov05.pdf . When you are working on your designer class, this will make sure the xml output is correct (hint: pay attention to all the 1-N, 0-1, 1 designations to know what's required and what's optional).

Custom Report Item docs: http://msdn2.microsoft.com/en-us/library/ms345224.aspx . For whatever reason I didn't find this when I first started searching. It never came up in google. I found it later when I was looking for information on a specific class.

MSDN Magazine: http://msdn2.microsoft.com/en-us/magazine/cc188686.aspx . A pretty good learning example, but didn't have everything I need. Also, there was no direct link to the source code. You have to download the source for the entire issue and extract it locally. The source can be found here: http://download.microsoft.com/download/f/2/7/f279e71e-efb0-4155-873d-5554a0608523/MSDNMag2006_10.exe

A data bound example of drawing polygons by Chris Hays: http://blogs.msdn.com/chrishays/archive/2005/10/04/CustomReportItemSample.aspx . The code was difficult to follow and there weren't many comments. This didn't help me until the end when I understood more of what was going on. Also, you have to register with the group before you can download the code.

The last problem I had to solve was how to bind a dataset to my custom report item. The only thing that really helped me here was the Rdl documentation. The problem was that the code which was relevant to performing this operation was uncommented and wasn't explained anywhere in the documentation or samples. So here's my own databinding snippets with comments and explaination.

A) In the CustomReportItemDesigner class (your custom class which inherits from it as a base), override the InitializeNewComponent() method like this:

public override void InitializeNewComponent()
{
 base.InitializeNewComponent();

 CustomData = new CustomData();

 //at least one grouping is required for both rows and columns

 //initialize row grouping
 CustomData.DataRowGroupings = new DataRowGroupings();
 CustomData.DataRowGroupings.DataGroupings = new List<DataGrouping>(1);
 CustomData.DataRowGroupings.DataGroupings.Add(new DataGrouping());
 CustomData.DataRowGroupings.DataGroupings[0].Grouping = new Grouping();
 CustomData.DataRowGroupings.DataGroupings[0].Grouping.Name = Name + "_Row";
 CustomData.DataRowGroupings.DataGroupings[0].Grouping.GroupExpressions = new GroupExpressions();
 CustomData.DataRowGroupings.DataGroupings[0].Grouping.GroupExpressions.Add(new Expression());

 // initialize a Static column grouping
 CustomData.DataColumnGroupings = new DataColumnGroupings();
 CustomData.DataColumnGroupings.DataGroupings = new List<DataGrouping>(1);
 CustomData.DataColumnGroupings.DataGroupings.Add(new DataGrouping());
 CustomData.DataColumnGroupings.DataGroupings[0].Static = true;

 //initialize data row with empty values for designer
 CustomData.DataRows = new List<DataRow>(1);
 CustomData.DataRows.Add(new DataRow());
 CustomData.DataRows[0].Add(new DataCell());

 //define the fields you will store (these will be populated with expressions by custom designer properties you define)
 CustomData.DataRows[0][0].Add(new DataValue("X", String.Empty));
 CustomData.DataRows[0][0].Add(new DataValue("Y", String.Empty));
}

B) Define a custom designer property to set the expression for your data row group.

        [Browsable(true), Category("Data")]
        public string LabelValue
        {
            get
            {
                if (CustomData.DataRowGroupings.DataGroupings.Count > 0)
                {
                    return GetGroupLabel(CustomData.DataRowGroupings.DataGroupings[0].Grouping);
                }
                else
                    return "Point Label";
            }
            set
            {
                CustomData.DataRowGroupings.DataGroupings[0].Grouping.GroupExpressions[0].String = value;
            }
        }

        private string GetGroupLabel(Grouping group)
        {
            if (group.GroupExpressions != null && group.GroupExpressions.Count > 0)
                return group.GroupExpressions[0].String;

            return null;
        }

C) Define custom designer properties to set the expressions for the DataValuesCollection (only showing one property here to keep things shorter)

public string XValue
{
 get
 {
  if (CustomData.DataRows.Count > 0 && CustomData.DataRows[0].Count > 0)
   return GetDataValue(CustomData.DataRows[0][0], "X");
  else
   return "X Coordinate";
 }
 set
 {
  SetDataValue(CustomData.DataRows[0][0], "X", value);
 }
}


private string GetDataValue(DataCell cell, string name)
{
 foreach (DataValue value in cell)
  if (value.Name == name)
   return value.Value;
 return null;
}

private void SetDataValue(DataCell cell, string name, string expression)
{
 foreach (DataValue value in cell)
  if (value.Name == name)
  {
   value.Value = expression;
   return;
  }
 DataValue datavalue = new DataValue(name, expression);
 cell.Add(datavalue);
}

D) Make sure your designer provides a property to set the data set name (CustomData.DataSetName).

E) Your CustomReportItem class can then read the data like this:

private void ReadCustomData()
{
 DataMemberCollection members = m_CRI.CustomData.DataRowGroupings[0];
 DataCellCollection rows = m_CRI.CustomData.DataCells;

 //get the position of each field in the data value collection
 int xpos = -1, ypos = -1;

 for (int field = 0; field < rows[0, 0].DataValues.Count; field++)
 {
  switch (rows[0, 0].DataValues[field].Name)
  {
   case "X":
    xpos = field;
    break;
   case "Y":
    ypos = field;
    break;
  }
 }

 //populate the series for the chart data
 for (int row = 0; row < rows.RowCount; row++)
 {
  DataValueCollection values = rows[row, 0].DataValues;

  String label = members[row].GroupValues[0].ToString();

  double x = Convert.ToDouble(values[xpos].Value);
  double y = Convert.ToDouble(values[ypos].Value);
 }
}

I haven't posted the full code here but I plan on doing so in the future. I also plan on dressing this post up and making a codeproject article out of it.

ASP.NET MVC Error Handling

When working with the latest MVC release (Preview 2) I was having trouble working with the Application_Error event in global.asax. While this does work if you want to redirect to an html file, if you want to redirect to an aspx file you need to use the Controller.OnActionExecuted method. Here's an example:

        protected override void OnActionExecuted(FilterExecutedContext filterContext)
       {
           if (filterContext.Exception != null)
           {
               Exception ex = filterContext.Exception;
               if (ex.InnerException != null)
                   ex = ex.InnerException;

               filterContext.ExceptionHandled = true;

               RedirectToAction(new RouteValueDictionary(new
               {
                   Controller = "Error",
                   Action = "Error",
                   Message = ex.Message
               }));
           }
          
           base.OnActionExecuted(filterContext);
       }

UPDATE 7/8/09: as of the final RTM for MVC the signature for OnActionExecuted has changed, it now reads : void OnActionExecuted(ActionExecutedContext). Thanks to jobr31 for reminding me to update the information.

I imagine if you wanted to use Application_Error you could use a different extension, like ".err" and configure ASP.NET to map the extension to the Page HttpHandler. But it seems like too much work to me to even try it when this works perfectly fine.

A more extensible method would be to use Action Filters. I have seen an example demonstrated by Troy Goode, but I haven't tried it yet.

ASP.NET MVC Custom Routing Logic

I've been working with the new ASP.NET MVC framework since the first CTP back in December. I love it and it keeps getting better.

The application I'm working on uses custom Session management and so I have to manage the SessionId in the url myself. So I wanted to be able to make sure that all Redirects, Links and Urls had the SessionId. But I didn't want to have to add it myself (maintenance nightmare) and end up with having to track down errors from missing it.

Because the routing framework builds the urls for you I wanted to interject my logic into one place where I could intercept each request to build a url and add the SessionId.

Before the March CTP (aka the MIX '08 CTP) I had to add extensions to all the classes which constructed urls. Here's a link to a post of mine at the time: http://forums.asp.net/p/1216840/2159920.aspx#2159920

But now with the recent CTP refresh it has become very easy to manage the session id from one place. I just create a class which inherits from System.Web.Routing.Route (or you can implement it from scratch by inheriting from System.Web.Routing.RouteBase). Here's how I did it.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Web.Routing;
using System.Web;
using System.Web.Mvc;

using DevelopmentalMadness.Web.Mvc;

namespace DevelopmentalMadness.Web.MvcExtensions
{
    public class SessionRoute : Route
    {
        public SessionRoute(String url, IRouteHandler routeHandler) : base(url, routeHandler)
        {

        }

        public SessionRoute(String url, RouteValueDictionary defaults, IRouteHandler routeHandler)
            : base(url, defaults, routeHandler)
        {
       
        }

        public SessionRoute(String url, RouteValueDictionary defaults, RouteValueDictionary constraints, IRouteHandler routeHandler)
            : base(url, defaults, constraints, routeHandler)
        {

        }

        public SessionRoute(String url, RouteValueDictionary defaults, RouteValueDictionary constraints, RouteValueDictionary dataTokens, IRouteHandler routeHandler)
            : base(url, defaults, constraints, dataTokens, routeHandler)
        {

        }

        public override VirtualPathData GetVirtualPath(RequestContext requestContext, RouteValueDictionary values)
        {
            //how can I get the view context or controller context ?????
            if (requestContext is ControllerContext)
            {
                ControllerContext cCtx = (ControllerContext)requestContext;

                if (cCtx.Controller is SessionBaseController)
                {
                    SessionBaseController sCtl = (SessionBaseController)cCtx.Controller;
                    if (values.ContainsKey("sid") == false && sCtl.ServerCookie != null)
                        values.Add("sid", sCtl.ServerCookie.Ticket);
                }
            }

            VirtualPathData virtualPath = base.GetVirtualPath(requestContext, values);

            return virtualPath;
        }
    }
}

SessionBaseController and ServerCookie are two custom classes of mine. ServerCookie is my session state management class and SessionBaseController is the class which exposes and maintains it. So I am able to access it through the RequestContext.

I haven't fully tested this yet, so I'm not sure if RequestContext will ever be something other than ControllerContext. I'm assuming that it can be, especially in the context of a unit test. Because they didn't explicitly declare ControllerContext it would be correct to assume that it won't always be ControllerContext, I just haven't discovered yet what the other possible values could be. Maybe for my next post.

The only other requirement is to design your routes so that they have a parameter for the values you are adding to the url, like this:

namespace MvcApplication
{
    public class Global : System.Web.HttpApplication
    {
        public static void RegisterRoutes(RouteCollection routes)
        {
            // Note: Change Url= to Url="[controller].mvc/[action]/[id]" to enable
            //       automatic support on IIS6

            routes.Add(new Route(
                "Login.mvc/Index/{result}",
                new RouteValueDictionary(new
                {
                    controller = "Login",
                    action = "Index",
                    result = (String)null
                }),
                new MvcRouteHandler()
                )
            );

            routes.Add(new SessionRoute(
                "Login.mvc/{action}/{sid}",
                new RouteValueDictionary(new
                {
                    controller = "Login",
                    action = "Index",
                    sid = (String)null
                }),
                new MvcRouteHandler()
                )
            );

            routes.Add(new SessionRoute(
                "{controller}.mvc/{action}/{sid}",
                new RouteValueDictionary(new
                {
                    action = "Index",
                    sid = (String)null
                }),
                new MvcRouteHandler()
                )
            );

            routes.Add(new Route(
                "Default.aspx",
                new RouteValueDictionary(new
                {
                    controller = "Home",
                    action = "Index"
                }),
                new MvcRouteHandler()
                )
            );
        }

        protected void Application_Start(object sender, EventArgs e)
        {
            RegisterRoutes(RouteTable.Routes);
        }
    }
}

Each route which requires my custom session management uses SessionRoute instead of Route. Also, the route will include the "sid" argument in the route definition.

Now I can manage the routes I need to by simply updating my Routes defined in global.asax instead of worring wither or not every url in my application was written correctly.

SARGable DateTime Handling in SQL Server

I can't believe I never thought of doing things this way before. It's so much better than concatenating strings and converting back and forth between varchar, int and datetime. I'd like to thank Marc for posting this jewel. Here's the link to the original content: Marc's Musings: More on DATEs and SQL.

I'm including the actual functions below, just as a quick reference:

SELECT DATEADD(dd, DATEDIFF(dd, 0, GetDate()), 0) As Today, 
    DATEADD(wk, DATEDIFF(wk, 0, GetDate()), 0) As ThisWeekStart, 
    DATEADD(mm, DATEDIFF(mm, 0, GetDate()), 0) As ThisMonthStart, 
    DATEADD(qq, DATEDIFF(qq, 0, GetDate()), 0) As ThisQuarterStart, 
    DATEADD(yy, DATEDIFF(yy, 0, GetDate()), 0) As ThisYearStart, 
    DATEADD(dd, DATEDIFF(dd, 0, GetDate()) + 1, 0) As Tomorrow, 
    DATEADD(wk, DATEDIFF(wk, 0, GetDate()) + 1, 0) As NextWeekStart, 
    DATEADD(mm, DATEDIFF(mm, 0, GetDate()) + 1, 0) As NextMonthStart, 
    DATEADD(qq, DATEDIFF(qq, 0, GetDate()) + 1, 0) As NextQuarterStart, 
    DATEADD(yy, DATEDIFF(yy, 0, GetDate()) + 1, 0) As NextYearStart, 
    DATEADD(ms, -3, DATEADD(dd, DATEDIFF(dd, 0, GetDate()) + 1, 0)) As TodayEnd, 
    DATEADD(ms, -3, DATEADD(wk, DATEDIFF(wk, 0, GetDate()) + 1, 0)) As ThisWeekEnd, 
    DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, GetDate()) + 1, 0)) As ThisMonthEnd, 
    DATEADD(ms, -3, DATEADD(qq, DATEDIFF(qq, 0, GetDate()) + 1, 0)) As ThisQuarterEnd, 
    DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GetDate()) + 1, 0)) As ThisYearEnd

ASP.NET AJAX - Update Panel Timeout

When using the asp:UpdatePanel server control for asyncronous communication with the web server the default timeout is 90 seconds. For some processes this may not be long enough.

For example, I am currently working on a reporting application and at the moment I have to query a table with over 36 million records. Some queries take longer than 90 seconds. But unfortunately I had trouble finding out how to extend this.

It turns out that this is a page-level setting. The asp:ScriptManager server control has a property named AsyncPostBackTimeout. Set this value to the number of seconds you need to resonably run the process in question and you'll be fine.

<asp:scriptmanager id="scriptManager1" runat="server" enablepartialrendering="true" asyncpostbacktimeout="1000"></asp:ScriptManager>