SWAT - A simple Web-based Anomalies Tracker - Part 9

• Download SWAT source files - 74.9 Kb

Fig.1 Swat's Report Page

SWAT Part 9

This is the last SWAT article. This article implements the reporting feature and completes all the requirements described in the first article. For those readers new to SWAT, this series of articles described the development of an application I devised as a learning project. The purpose of the project was to gain experience developing in the .NET environment. The goal I had given myself was to define a web-based application and then develop the application using ASP.NET. The articles describe my implementation solution for the application. The application being developed is a full-featured bug tracking application.

• Swat Part 1
• Swat Part 2
• Swat Part 3
• Swat Part 4
• Swat Part 5
• Swat Part 6
• Swat Part 7
• Swat Part 8

SWAT's Reporting Feature

First, let me describe my goal for this page. The requirement simply stated that we needed to provide a reporting feature that was intuitive and easy to use. Actually it said 'completely flexible', but intuitive and easy to use are always a given. That's how most of the time requirements come from marketing or customers. Which leaves us with the responsibility (and opportunity) to be creative. The first thing I knew I had to provide was complete flexibility in the request options. That is, the user should be able to define as general or as specific a request as possible. Second, I wanted to provide some immediate feedback to the user that indicated what would be returned as a result of the selected options. As with any report I also wanted to allow the user some control over the format of the report. Which means allowing them to select which fields would be displayed. And finally no report would be complete if the user could not select how the results would be sorted. That's a little more specific in terms of requirements but still leaves a lot of leeway when it comes to the implementation. Just a little side note. As I mentioned in prior articles on the importance of a development process. I'm assuming there is always some feedback mechanism to the customer or marketing prior to starting implementation. Even if it's just as simple as the few sentences described above.

There's two request options that I intentionally did not provide for. First, I did not think that anybody would want to view the whole database in the report. So as a minimum, a project and user must be specified for any report. Second, there are no 'dated' reports. That is, a user can't ask for a report for all bugs for 'May thru June of 2001'. I didn't see that as providing any additional benefit.

There's probably a host of additional functionality that could be added to reporting. For example, we could persist the user's last report format and have the options selected when the page is first displayed. We could provide a mechanism to define the order of the columns on the report. We could also expand the sorting to provide more than one field. These are enhancements that the customer can pay for later in enhancements if desired;)

I think you'll find a few interesting implementation approaches on this page. First there's a lot of functionality implemented on the client side. And you may find use for some of the functions implemented there. There's some SQL constructs that you may find instructive. And there's also some DataGrid cell formatting solutions that I'm sure will come in handy.

Note, the downloadable source has a slightly different layout for the Reporting page. I re-arranged the controls a little to make the images for the article fit CP's image requirements. Still, I am guessing that the layout for this page could use a little improvement. Perhaps some cosmetic re-arrangement might help.

Fig.2 Swat's Report Page-Default View

So let's get going and finish up SWAT! Add a new WebPage to the project and name it SwatReport. For those of you that are coding as we go along, we'll do things a little different this time. This will give us an opportunity to see the end results of 'drag-and-drop' ASP.NET programming. If you haven't downloaded the source code do so now and place the files in some temporary directory so there won't be any conflict with the project you're building. Now, open the SwatReport.aspx file with Notepad or inside Visual Studio. Find the 'form' section and there you'll see all of the controls that are shown in Fig.2. Drag and drop the controls shown in Fig.2 from the Toolbox to the new SwatReport page. Set the ID and appropriate properties from the information in the 'form' section of the SwatReport.aspx file you just opened up. The 'options', which are the items in a DropDownList (or ListBox) can be added by selecting the 'Items...(Collection)' property.

Over there

As I mentioned above there's some interesting code on the client side of this page so let's begin with that. The whole idea of the client side code is that I wanted to show the user in 'English' what exactly s/he was getting (psst, it also gave me an opportunity to explore more client side coding) and without making a trip to the server for every user selection. The easiest solution would have been to just provide the selection controls, have the user make the desired selection, press the button and see the result. I wanted an intermediate step that would show the user what would be returned once s/he pressed the button. I also didn't want to just show the SQL statement that was going to be executed. I wanted a more 'readable' version that would be understood by more than just developers. For example if SWAT was to find a gig as a customer issue tracking system the users would not be technical.

So basically what we want is that anytime the users makes a selection we want to update a text control with a string that is representative of the current selections made by the user. Once the user is satisfied with the selections then s/he presses the button to request the report. Seems like a pretty straightforward requirement, right?

There are two other options that we want to make available to the user in specifying the report. First the user can define which items are to be displayed on the report (need at least one). Second, the user can specify a sorting arrangement for the displayed items. There's some special coding required by these two options which needs some additional explanation. If the user has selected a specific severity level that s/he is interested in then it does not make any sense to show that field in the report since it will be the same for all rows. The same thing applies for the 'State' and 'Priority' options. When the report is returned, the request string will still show what was requested so the information is not lost. What we need to do is remove those options from the appropriate DropDownList if the user selects any of those options. In other words, it doesn't make sense to allow the user to sort by 'Severity' if the user requested to get all the 'Showstopper' bugs! You'll see code below that actually removes and adds these options from the lists as required. So we can add and remove items on the client side without having to go back to the server. Pretty neat huh? Note: The number of values being manipulated for this functionality is small and is hard coded within the functions. For a larger data set, the logic could easily be expanded to use a client side data island.

function GenRequestText()
{
  var s = "Get ";
  var ddl = SwatReport.ddlUsers;
  //Is 'My' the owner? If not add 's to make it more better English
  s += ddl.options[ddl.selectedIndex].text;
  if (ddl.selectedIndex > 0)
    s += "'s";
  s += " ";
  ddl = SwatReport.ddlCount;
  //All or just some?
  if (ddl.selectedIndex > 0)
  {
    s += ddl.options[ddl.selectedIndex].text;
    s += " ";
  }
  //Add these to lstItems list, if they don't exist
  var lstItems = SwatReport.lstItems;
  AddItemToList(lstItems,"Status","State");
  AddItemToList(lstItems,"Severity","Severity");
  AddItemToList(lstItems,"Priority","Priority");

  //All or just bugs in a specific state
  ddl = SwatReport.ddlState;
  if (ddl.selectedIndex > 0)
  {
    s += ddl.options[ddl.selectedIndex].text;
    s += " ";
    //Remove this item from lstItems list since all rows would be same
    RemoveItemFromList(lstItems,"Status");
  }
  s += "bugs found in ";
  ddl = SwatReport.ddlModules;
  //Which project or module is user interested in?
  if (ddl.selectedIndex > 0)
    s += ddl.options[ddl.selectedIndex].text;
  else
    s += SwatReport.ddlProjects.options[
           SwatReport.ddlProjects.selectedIndex].text;
  s += ".";
  
  var ddlSeverity = SwatReport.ddlSeverity;
  var ddlPriority = SwatReport.ddlPriority;
  
  //Add these two options to sort, if they don't exist
  var ddlSortItem = SwatReport.ddlSortItem;
  AddItemToList(ddlSortItem,"Priority","Priority");
  AddItemToList(ddlSortItem,"Severity","Severity");
  
  if (ddlSeverity.selectedIndex > 0 || ddlPriority.selectedIndex > 0)
  {
    s += " Only show the bugs that";
    if (ddlSeverity.selectedIndex > 0)
    {
      s += " have severity as ";
      s += ddlSeverity.options[ddlSeverity.selectedIndex].text;
      //Remove this option from ddlSortItem since 
      //they will be same on report
      RemoveItemFromList(ddlSortItem,"Severity");
      //And there's no sense in displaying it in the report
      RemoveItemFromList(lstItems,"Severity");
    }
    if (ddlSeverity.selectedIndex > 0 && ddlPriority.selectedIndex > 0)
      s += " and";
    if (ddlPriority.selectedIndex > 0)
    {
      s += " are level ";
      s += ddlPriority.options[ddlPriority.selectedIndex].text;
      s += " priority."
      //Remove this option from ddlSortItem and lstReport
      RemoveItemFromList(ddlSortItem,"Priority");
      RemoveItemFromList(lstItems,"Priority");
    }
    else
      s += ".";
  }
  
  //Now, what items are to be in report
  if (lstItems.selectedIndex >= 0)
  {
    s += "\n";
    s += "Display ";
    var bFirst = true;
    for (n = 0; n < lstItems.options.length; n++)
    {
      if (lstItems.options[n].selected)
      {
        if (!bFirst)
          s += ", ";
        else
          bFirst = false;
        s += lstItems.options[n].text;
      }
    }
    s += " in the report";
    //Did user request any sorting?
    if (ddlSortItem.selectedIndex)
    {
      s += " and sort the items by ";
      s += ddlSortItem.options[ddlSortItem.selectedIndex].text;
      s += " ";
      s += SwatReport.ddlSortOrder.options[
          SwatReport.ddlSortOrder.selectedIndex].text;
      s += ".";
    }
    else
      s += ".";
  }
  //Show it
  SwatReport.txtRequest.value = s;
}

The comments in the code pretty much describe what is happening at each step. So we have this function but nothing will happen unless we hook it in to the user's action. For each of the DropDownList controls (and list) we need to add an event handler that will call GenReportText(). And that event is the 'onchange' event. Add the 'onchange' event as shown below to all the DropDownList controls and the ListBox. We'll be revising this for the ddlProject and ddlModule later.

...
<asp:dropdownlist id="ddlUsers" onchange="GenRequestText()...>
</asp:dropdownlist>
...

Here's the two helper functions called within the GenRequestText() function that add or remove items from list controls.

function AddItemToList(list,value,text)
{
  var bExists = false;
  //If it doesn't exist
  for (n = 0; n < list.options.length; n++)
  {
    if (list.options[n].value == value)
    {
      bExists = true;
      break;
    }
  }
  if (!bExists)
    list.options[list.options.length] = new Option(text,value);
}
function RemoveItemFromList(list, item)
{
  for (n = 0; n < list.options.length; n++)
  {
    if (list.options[n].value == item)
    {
      list.options[n] = null;
      break;
    }
  }
}

Pretty straight forward. Each one adds or removes items from the passed in list based on whether the item exists or not. These are generic functions that can be used anytime this functionality is required so they would probably find their way into some library.

I've coded the script right in the 'HEAD' section of the page. It was just the easiest way right now. You can probably store the scripts in a file and then programmatically load them as needed. This way code could be shared between different applications. Revise the SwatReport.aspx file to add the script block and functions as shown below.

...
    <script language="JavaScript">
function GenRequestText()
{
...
}
function AddItemToList(list,value,text)
{
...
}
function RemoveItemFromList(list, item)
{
...
}
    </script>
  </HEAD>
...

Over here

Let's add some server side functionality so we can start testing this feature of the application. When the page is requested the only thing we need to do is to get the list of projects and users for the respective DropDownLists. We do that when the page is initially loaded. Here's the code for the Page_Load() event.

private void Page_Load(object sender, System.EventArgs e)
{
  //We know we have this because the user is logged in
  if (Request.Cookies["UserID"] != null)
  {
    Response.Cookies.Add(Request.Cookies["UserID"]);
    Response.Cookies["UserID"].Expires = DateTime.MaxValue;
  }
  if (!Page.IsPostBack)
  {
    try
    {
      SqlConnection cnn;
      SqlCommand cmd;
      SqlDataReader dr;
      string ConnectionString = ConfigurationSettings.AppSettings["dbconn"];
      cnn = new SqlConnection(ConnectionString);
      cmd = cnn.CreateCommand();
      cnn.Open();
      cmd.CommandType = CommandType.StoredProcedure;

      cmd.CommandText = "SWATGetAllProjects";
      dr = cmd.ExecuteReader();
      ddlProjects.DataSource = dr;
      ddlProjects.DataTextField = "itemname";
      ddlProjects.DataValueField = "id";
      ddlProjects.DataBind();
      dr.Close();

      if (Response.Cookies["Project"].Value != null)
        ddlProjects.SelectedIndex = ddlProjects.Items.IndexOf(
          ddlProjects.Items.FindByValue(Response.Cookies["Project"].Value));
      else
        ddlProjects.SelectedIndex = 0;
      dr.Close();

      //Populate the culprit combo...
      cmd.CommandText = "SWATGetUserNames";
      dr = cmd.ExecuteReader();

      ddlUsers.DataSource = dr;
      ddlUsers.DataTextField = "itemname";
      ddlUsers.DataValueField = "id";
      ddlUsers.DataBind();
      dr.Close();
    
      //Add 'My' to the user's list
      ListItem ls = new ListItem("My","0");
      ddlUsers.Items.Insert(0,ls);
      ddlUsers.SelectedIndex = System.Convert.ToInt32(
          ddlUsers.Items.IndexOf(ddlUsers.Items.FindByText("My")));

      cnn.Close();
    }
    catch(Exception ex)
    {
      txtRequest.Text = ex.Message;
    }
  }
}

Same stuff as we've done on other pages including the insertion of an additional item to a list. So now we're ready to try it out. Stay away from selecting a project/module for the request because we still need to do some more work with those. But you can select the other options and see how the request text changes according to the selection.

Fig.3 Swat's Report Page-Sorted Request

Master/Detail part deux

In the last article we made use of the MasterDetail server control that provided client side functionality which eliminated a trip to the server to populate the modules list when the user selected a different project. In this article I decided to provide the same functionality but implement it differently. Kinda the same but different if you will. Just one little requirement forced me into this. And, yes I could have modified the control but didn't. That little requirement was the need to add an 'All' entry into the list. The MasterDetail control will just display what was retrieved from the database.

We still need to implement the same functions that the server control provided but we'll do it 'manually'. We'll read the slave data from the database and provide it on the client as XML data. As with the control there'll be a couple of functions on the client that will read the XML data and populate the detail DropDownList according to the selected item in the master DropDownList. Here's how we'll do it.

Back over there

Add the following functions to the script block on the SwatReport.aspx page.

function doMasterClick()
{
  var sel = SwatReport.ddlProjects.value;
  //Save the selection. I don't know why it's not being sent back.
  document.all.hiddenSelProject.value = sel;

  var optCount = 0;
  SlaveData = document.all.dso.XMLDocument;
        //Clear out the modules ddl
  SwatReport.ddlModules.options.length = 0;
  
  //Add an 'All' entry. The reason why we're here.
  var option = new Option("All",0);
  SwatReport.ddlModules.options[optCount++] = option;
  
  for(j=0;j <SlaveData.childNodes(0).selectNodes('MODULES').length;j++)
  {
    var data = SlaveData.childNodes(0).selectNodes('MODULES')(j);
    if(sel == data.childNodes(1).text)
    {
      var option = new Option(
         data.childNodes(2).text,data.childNodes(0).text);
      SwatReport.ddlModules.options[optCount++] = option;
    }
  }
}
function doSlaveClick()
{
  //Save the selected index
  document.all.hiddenSelModule.value = 
    document.all.ddlModules.value;
}

Whenever the user changes the selected project we detect the change and call the doMasterClick() function. In the doMasterClick function the first we do is save the selection so that it can be read on the server (see note below). Then we get a reference to the XML data island and iterate through it looking for a match with the project ID. For each match found we add an entry into the module DropDownList. Easy, right? The doSlaveClick() function simply persists the selected item so that it can be processed on the server.

Note. I don't know why I needed to do this and didn't pursue trying to figure it out. The SelectedIndex for the project DropDownList was not being sent back to the server. Since I already needed a hidden field to persist the selected module (this I HAD to do), I took the easy road and added a second hidden field for the selected project. Just incase you were wondering why.

Here's the rest of the changes that are needed on the page. First, add a call to doMasterClick in the 'onchange' event for the project DropDownList. Add a call to doSlaveClick in the 'onchange' event for the modules DropDownList. And add two hidden fields to persist the project/module selection. Finally, we want to initialize the module DropDownList when the page is initially displayed so we will add calls to doMasterClick and doSlaveClick in the 'onload' event. These changes are summarized below.

...
<body bgColor="#fffbf0" onload="doMasterClick();doSlaveClick()" 
       MS_POSITIONING="GridLayout">
    <form id="SwatReport" method="post" runat="server">
        <input type="hidden" id="hiddenSelModule" runat="server" 
            NAME="hiddenSelModule">
        <input type="hidden" id="hiddenSelProject" runat="server" 
            NAME="hiddenSelProject">
...
        <asp:dropdownlist id="ddlProjects" 
          onchange="doMasterClick();GenRequestText()"...></asp:dropdownlist>
...
        <asp:dropdownlist id="ddlModules" 
          onchange="doSlaveClick();GenRequestText()"... ></asp:dropdownlist>
...

Back here

Just a few more items to take care of on the server side and we're done. Well, a couple of little things and one BIG thing. Let's start by adding the XML data island to support the project/module client side selection. Add an override for the Page.Render() and revise as follows.

protected override void Render(HtmlTextWriter output)
{       
  SqlConnection cnn;
  SqlCommand cmd;
  SqlDataReader dr;
  
  string ConnectionString = ConfigurationSettings.AppSettings["dbconn"];
  cnn = new SqlConnection(ConnectionString);
  cmd = cnn.CreateCommand();
              
  cnn.Open();
  cmd.CommandType = CommandType.StoredProcedure;

  cmd.CommandText = "SWATGetAllModules";
  SqlDataAdapter da = new SqlDataAdapter(cmd);
  
  //Setup the MasterDetail DropDownList control
  DataSet ds = new DataSet("ProjMod");
  da.Fill(ds,"MODULES");
  output.Write("<xml id=\"dso\">\n");
  ds.WriteXml(output);
  output.Write("</xml>\n");
  cnn.Close();
  base.Render(output);
}

All we're doing here is getting the modules table from the database, assigning a name, and writing it out as an XML data island so that the code on the client can process it. Next add a couple of server controls to service the two hidden fields we added. Add these at the top of the page along with all the other controls.

...
  public class SwatReport : System.Web.UI.Page
  {
...
    protected System.Web.UI.HtmlControls.HtmlInputHidden hiddenSelModule;
    protected System.Web.UI.HtmlControls.HtmlInputHidden hiddenSelProject;
...

You can compile and test the application and you'll see the module list changing as the project selection changes. Now we need to add what the user actually wants, the report. Start by adding an event handler for the 'Get Reports' button. Here's where the fun starts. We need to process the user's selections and convert them into a SQL statement to get the data. Revise the event handler as follows.

private void btnReport_Click(object sender, System.EventArgs e)
{
    try
    {
        SqlConnection cnn;
        StringBuilder sqlString = new StringBuilder("SELECT ");
        //How many items do we display?
        if (ddlCount.SelectedIndex > 0)
        {
            sqlString.Append(ddlCount.SelectedItem.Text);
            sqlString.Append(" ");
        }
        //Which items are to be in the report?
        if (lstItems.SelectedIndex >= 0)
        {
            bool bFirst = true;
            for (int n = 0; n < lstItems.Items.Count; n++)
            {
                if (lstItems.Items[n].Selected)
                {
                    if (!bFirst)
                    {
                        sqlString.Append(",");
                    }
                    else
                        bFirst = false;
                    //We don't want the ID for these, we want the name
                    if (lstItems.Items[n].Value == "EnteredBy")
                    {
                        sqlString.Append(
           "(SELECT itemname FROM users WHERE id=EnteredBy) AS EnteredBy");
                    }
                    else
                    {
                        if (lstItems.Items[n].Value == "FixedBy")
                        {
                            sqlString.Append(
                "(SELECT itemname FROM users WHERE id=FixedBy) AS FixedBy");
                        }
                        else
                        {
                            if (lstItems.Items[n].Value == "ClosedBy")
                            {
                                sqlString.Append(
             "(SELECT itemname FROM users WHERE id=ClosedBy) AS ClosedBy");
                            }
                            else
                            {
                                sqlString.Append(lstItems.Items[n].Value);
                            }
                        }
                    }
                }
            }
        }
        else
            return;  //nothing to show, sould be detected on client!!!

        string ConnectionString = 
            ConfigurationSettings.AppSettings["dbconn"];
        cnn = new SqlConnection(ConnectionString);
        cnn.Open();

        SqlCommand cmd = cnn.CreateCommand();

        sqlString.Append(" FROM bugs");
        //We need as a minimum an owner
        sqlString.Append(" WHERE AssignedTo=@assignedto");
        //Setup the parameter
        if (ddlUsers.SelectedIndex != 0)
            cmd.Parameters.Add("@assignedto", SqlDbType.Int).Value = 
                ddlUsers.SelectedItem.Value;
        else
            cmd.Parameters.Add("@assignedto", SqlDbType.Int).Value = 
                Response.Cookies["UserID"].Value;
    
        //Now, what other criteria was selected?
        //Project or module
        if (int.Parse(hiddenSelModule.Value) != 0)
        {
            sqlString.Append(" AND module=@moduleid");
            cmd.Parameters.Add("@moduleid", SqlDbType.Int).Value = 
                hiddenSelModule.Value;
        }
        else
        {
            //Don't know why I'm not getting back the select 
            //value from the ddlProjects, so
            //had to add a hidden field for it also.
            if (int.Parse(hiddenSelProject.Value) >= 0)
            {
                sqlString.Append(" AND project=@projectid");
                cmd.Parameters.Add("@projectid", SqlDbType.Int).Value = 
                    hiddenSelProject.Value;
            }
        }
        //Was a state specified?
        if (ddlState.SelectedIndex != 0)
        {
            sqlString.Append(" AND status=@status");
            cmd.Parameters.Add("@status", SqlDbType.Int).Value =
                  ddlState.SelectedItem.Value;
        }

        //Any severity level specified?
        if (ddlSeverity.SelectedIndex != 0)
        {
            sqlString.Append(" AND severity=@severity");
            cmd.Parameters.Add("@severity", SqlDbType.Int).Value =
                  ddlSeverity.SelectedItem.Value;
        }

        //And finally what about priority?
        if (ddlPriority.SelectedIndex != 0)
        {
            sqlString.Append(" AND priority=@priority");
            cmd.Parameters.Add("@priority", SqlDbType.Int).Value = 
                ddlPriority.SelectedItem.Value;
        }

        //Any special sort preferences
        if (ddlSortItem.SelectedIndex != 0)
        {
            sqlString.Append(" ORDER BY ");
            sqlString.Append(ddlSortItem.SelectedItem.Value);
            sqlString.Append(" ");
            sqlString.Append(ddlSortOrder.SelectedItem.Value);
        }

        cmd.CommandText = sqlString.ToString();
  
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();

        da.Fill(ds);

        grdReport.DataSource = ds;
        grdReport.DataBind();
    
        //Why can't I do this?!
//  for (int nCol = 0; nCol < grdReport.Columns.Count; nCol++)
//  {
//      BoundColumn grdCol = (BoundColumn)grdReport.Columns[nCol];
//      if (grdCol.DataField == "EnteredDate" ||
//          grdCol.DataField == "FixedDate" ||
//          grdCol.DataField == "ClosedDate")
//          grdCol.DataFormatString = "0:d";
//  }
        
        cnn.Close();
    }
    catch(Exception ex)
    {
        //We can improve this...
        txtRequest.Text = ex.Message;
    }
}

Whew! That's a lotta logic just to create a string. Most of it you should be able to follow with the comments provided. There's a couple of items that I'd like to expand on. When you do a select on the bug table, as we've done on other pages, what is returned for a number of fields is the ID for an entry in another table. For the report we do not want to display the ID, which would be meaningless to the user, but rather the name that is associated with that ID. That's what the funky SQL construct shown above for the EnteredBy, FixedBy, and ClosedBy fields is doing. It is translating the ID right inside the SELECT statement! I thought you might be interested in case you ever had the same requirement.

The rest is just grinding away at constructing the SQL string from the user's selection, except for the commented out section that I've left in there. If you compile and run the application right now you'll get back the data from the database as expected. However there are a couple of fields whose rendering could be improved. First, the dates returned back from the database contain both the date and time information. The time for the bug is superfluous information that simply takes up space on the report and provides no additional benefit. So, I know that you can specify a format to use for columns in the DataGrid control. If you know what the layout of the grid is to be at design time (what columns will contain what data) then it's a piece of cake. In our case we don't know what the user's selection will be so we need to set the format when we know what the user selected, at run time.

What's in the commented section was my first guess as to how to set the format string for a DataGrid column at run time. I figured that once the data has been bound to the DataGrid I could just iterate through the columns, find out which ones were holding DateTime data, and then just simply assign the required format to the column. NOT! The easy road is not available. Checking the column count at that point returns zero. Now what?. I couldn't leave the date as it was being returned. That's the whole point of these articles, to learn.

I thought of another approach which was to iterate through the columns of the DataTable in the DataSet and create a column in the DataGrid for each DataTable column. If the column was a date then I could assign a format to the DataGrid column at this time. I expected that later when the DataGrid was bound, the column configuration would remain the same. I didn't pursue this and can't say if it would have worked.

The solution I wound up implementing is a more general solution which at first seems to be a lot more work but in our case I needed it anyway. The date fields were not the only ones that I needed to modify. The 'State' and 'Severity' data is stored in the database as enumerations which would not mean much in the report. In this case I need to process each row and depending on the what I'm doing I'll either check the column name or the DataType of the cell and make the appropriate change. Check out how the enumeration actually returns it's string name!. For the date fields, obviously, it would be less work if I could just set the column format once and be done with it. But since I HAD to be here anyway I did all the substitutions at the same time.

The solution then, is to hook into the ItemDataBound event of the DataGrid control which provides you with a 'last minute' chance to change what is going to be displayed for each cell. You'll need to add an event handler for the DataGrid's 'ItemDataBound' event. Here's the code for the handler.

private void grdItemDataBound(object sender, 
   System.Web.UI.WebControls.DataGridItemEventArgs e)
{
  if (e.Item.ItemType == ListItemType.Item ||
    e.Item.ItemType == ListItemType.AlternatingItem)
  {
    DataRowView rv = (DataRowView)e.Item.DataItem;
    DataView dv = (DataView)rv.DataView;
    for (int nCell = 0; nCell < e.Item.Cells.Count; nCell++)
    {
      
      DataColumn dc = (DataColumn)dv.Table.Columns[nCell];
      if (dc.DataType == System.Type.GetType("System.DateTime"))
      {
        //If it's not null...how do test for null entry at this stage?
        //Kludgy!
        if (e.Item.Cells[nCell].Text[0] != '&')
        {
          System.DateTime dt = System.Convert.ToDateTime(
              e.Item.Cells[nCell].Text);
          e.Item.Cells[nCell].Text = dt.ToShortDateString();
        }
      }
      //We also want to display something different for status and severity
      if (dc.ColumnName == "Severity")
      {
        int nSeverity = int.Parse(e.Item.Cells[nCell].Text);
        Swat.BugSeverity bs = (BugSeverity)nSeverity;
        e.Item.Cells[nCell].Text = 
            System.Enum.GetName(bs.GetType(),nSeverity);
      }
      if (dc.ColumnName == "Status")
      {
        int nStatus = int.Parse(e.Item.Cells[nCell].Text);
        Swat.BugState bs = (BugState)nStatus;
        e.Item.Cells[nCell].Text = System.Enum.GetName(bs.GetType(),nStatus);
      }
    }
  }
}

As you can see above there was one final little 'gotcha'. If the date returned from the database was empty...how do you detect that? Anyway, I really did need to finish this since it has been dragging for a while taking back seat to other more important life issues like the next paycheck. So if there's a better way to detect it let me know, the kludge stands for now.

I'm outta here!

That's it. Compile and build the application and hopefully everything works on your machine. It works on mine! There's a few things that could be added or improved but I'll leave those as an exercise for the reader. I learned a few things along the way and hope this effort was beneficial to some of you. As I've mentioned before if you find the application useful and make any improvements to it, please share it by sending it in.

About Me

Currently on assignment in Atlanta. But am ALWAYS interested in hearing about interesting opportunities.