// Arrange Act Assert

Jag Reehal on Agile Development, ASP.NET MVC and all manner of good stuff

How To Create Heat Maps In Sql Server Reporting Services

Posted on | February 8, 2010 | No Comments

Recently I’ve been doing some work with Microsoft Sql Server Reporting Services. One of the tasks I was given was to create a report with a heat map, which means you transform a report that looks like this

Plain Report

to something that looks like this

Bobs Heat Map Report

As you can see the a heat map is excellent way to visualize data.

This was done without the use of any plugins. I implemented the heat map in Reporting Services by following Bob Meyers excellent solution in his ‘Add Excel-like “color scale” conditional formatting to your reports‘ blog post.

Exporting to Excel

One of the requirements for my work was for users to be able to export the report into Microsoft Excel. Unfortunately it was at this point Bob’s solution didn’t work out for me. The problem was I was getting cells with a black background color as shown below.

Errors Exporting To Excel

So I came up with an alternative solution.

An Alternative Sql Server Reporting Services Heat Map Generator

So instead of using hex codes for the color like Bob does, I wanted to play it safe and use Microsoft colors i.e. those in the System.Drawing library.

The code below shows the GetHeatmapColor function which takes in three arguments and returns a string for the color

Public Class SqlReportingServicesHeatMapGenerator
    Public Function GetHeatmapColor(ByVal textBoxValue, ByVal minDataSetValue, ByVal maxDataSetValue) As String
        Dim colours As String() = New String() {"Green", "LimeGreen", "Yellow", "Orange", "OrangeRed", "Red"}

        If textBoxValue = 0 Then
            Return colours(0)
        End If

        If minDataSetValue = maxDataSetValue Then
            Return colours(0)
        End If

        If textBoxValue > maxDataSetValue Then
            Return colours(colours.Length - 1)
        End If

        Dim divider As Integer = (maxDataSetValue - minDataSetValue + 1) / 5
        Dim index As Integer = textBoxValue / divider
        GetHeatmapColor = colours(index)

    End Function
End Class

This function can be unit tested like this. Yes I know this falls a long way from 100% test coverage, but this is just an example ;) 

Imports NUnit.Framework

<TestFixture()> _
Public Class When_Getting_The_Fill_Color
    Private _heatMapGenerator As New SqlReportingServicesHeatMapGenerator()
    <Test()> _
    Public Sub Should_Return_Green_For_Zero()
        Dim result = _heatMapGenerator.GetHeatmapColor(0, 1, 100)
        Assert.AreEqual("Green", result)
    End Sub

    <Test()> _
    Public Sub Should_Return_Green_For_Lowest_Value()
        Dim result = _heatMapGenerator.GetHeatmapColor(1, 1, 100)
        Assert.AreEqual("Green", result)
    End Sub

    <Test()> _
    Public Sub Should_Return_Red_For_Highest_Value()
        Dim result = _heatMapGenerator.GetHeatmapColor(100, 1, 100)
        Assert.AreEqual("Red", result)
    End Sub

    <Test()> _
    Public Sub Should_Return_Green_If_All_Values_Are_The_Same()
        Dim result = _heatMapGenerator.GetHeatmapColor(1, 1, 1)
        Assert.AreEqual("Green", result)
    End Sub

    <Test()> _
    Public Sub Should_Return_Red_Even_If_Cell_Value_Is_Greater_Than_The_Maximum()
        Dim result = _heatMapGenerator.GetHeatmapColor(1164, 1, 896)
        Assert.AreEqual("Red", result)
    End Sub
End Class

Implementing The Solution

The colors on the report are shown using the fill property of a text box and an expression to determine what the fill color should be. Follow the steps below to create a heat map in Reporting Services using either Report Builder or Visual Studio.

First create a data source and dataset (we will not be connecting to any databases). Right click on the dataset and select query

Right click on the dataset and select query

In the query for the dataset paste in the sql below 

DECLARE @LogTable TABLE (WebServer VARCHAR(10),[Hour] TINYINT, NumberOfUsers INT)
INSERT INTO @LogTable VALUES ('WebServer1',0,0)
INSERT INTO @LogTable VALUES ('WebServer1',1,2)
INSERT INTO @LogTable VALUES ('WebServer1',2,4)
INSERT INTO @LogTable VALUES ('WebServer1',3,6)
INSERT INTO @LogTable VALUES ('WebServer1',4,8)
INSERT INTO @LogTable VALUES ('WebServer1',5,10)
INSERT INTO @LogTable VALUES ('WebServer1',6,12)
INSERT INTO @LogTable VALUES ('WebServer1',7,14)
INSERT INTO @LogTable VALUES ('WebServer1',8,16)
INSERT INTO @LogTable VALUES ('WebServer1',9,18)
INSERT INTO @LogTable VALUES ('WebServer1',10,20)
INSERT INTO @LogTable VALUES ('WebServer1',11,22)
INSERT INTO @LogTable VALUES ('WebServer1',12,24)
INSERT INTO @LogTable VALUES ('WebServer1',13,22)
INSERT INTO @LogTable VALUES ('WebServer1',14,21)
INSERT INTO @LogTable VALUES ('WebServer1',15,20)
INSERT INTO @LogTable VALUES ('WebServer1',16,18)
INSERT INTO @LogTable VALUES ('WebServer1',17,16)
INSERT INTO @LogTable VALUES ('WebServer1',18,14)
INSERT INTO @LogTable VALUES ('WebServer1',19,12)
INSERT INTO @LogTable VALUES ('WebServer1',20,10)
INSERT INTO @LogTable VALUES ('WebServer1',21,8)
INSERT INTO @LogTable VALUES ('WebServer1',22,6)
INSERT INTO @LogTable VALUES ('WebServer1',23,4) 

SELECT WebServer, [Hour], NumberOfUsers FROM @LogTable

Next create a table, using hour as the column and the number of users as the data field

Creating the table

Now in right click on the report designer and select the “Report Properties” option, and then the “Code” tab. Copy the GetHeatmapColor function (NOT the SqlReportingServicesHeatMapGenerator class) and paste it into the window

Entering the VBA code into the report

Right click in the text box that you want to generate the heat map for and select “Text Box Properties” (or use the properties window)

Text Box Properties

Then choose the “Fill” tab and click the Expression button.

Entering the Expression

In the expression window enter the line below

=Code.GetHeatmapColor(Sum(Fields!NumberOfUsers.Value), Min(Fields!NumberOfUsers.Value, "DataSet1"), Max(Fields!NumberOfUsers.Value, "DataSet1"))

As Bob says his post “The argument ‘Dataset1′ defines the scope in which the min or max value is calculated, which must be a parent scope of the current scope.”

If you preview the report you should see the following

Jags Heat Map Report

Category: SQL Server Reporting Services

Pimp Up Your Unit Test Assertions

Posted on | January 13, 2010 | No Comments

In this post I’m going to show you how you can write your unit test assertions to read more fluently and allow you to be more productive.

The Plain Jane Unit Test Assertion

[Test]
public void The_Plain_Jane_Assertion()
{
    const string firstString = "Hello World";
    const string secondString = "Hello World";

    const int smallNumber = 1;
    const int bigNumber = 500;

    // Are Equal
    Assert.AreEqual(firstString, secondString);

    // Greater Than
    Assert.Greater(bigNumber,smallNumber);

    // Less Than
    Assert.Less(smallNumber,bigNumber);
}

This is what most developers will start off using. No problem with the equals assertion it easy enough to read and use.

However when using the greater or less than assertions, it’s not clear if the first argument should be the lower or the higher value as shown in the screen shot below.

NUnit Assert Confusion

This distraction can easily stop your flow.

The More Fluent NUnit Test Assertion

[Test]
public void The_More_Fluent_Nunit_Assertion()
{
    const string firstString = "Hello World";
    const string secondString = "Hello World";

    const int smallNumber = 1;
    const int bigNumber = 500;

    // Are Equal
    Assert.That(firstString, Is.EqualTo(secondString));

    // Greater Than
    Assert.That(bigNumber, Is.GreaterThan(smallNumber));

    // Less Than
    Assert.That(smallNumber, Is.LessThan(bigNumber));
}

The unit test assertions read a lot more fluently, we no longer have to be concerned about the order of arguments.

If you don’t want to use another third party library, this is for you.

The Super Fluent NBehave Spec Assertion

[Test]
public void The_Super_Fluent_NBehave_Spec_Assertion()
{
    const string firstString = "Hello World";
    const string secondString = "Hello World";

    const int smallNumber = 1;
    const int bigNumber = 500;

    // Are Equal
    firstString.ShouldEqual(secondString);

    // Greater Than
    bigNumber.ShouldBeGreaterThan(smallNumber);

    // Less Than
    smallNumber.ShouldBeLessThan(bigNumber);
}

This example uses the NBehave Spec Framework which you can download from the NBehave site. In addition to being very easy to understand, the use of extension methods means there is a lot less noise.

Some developers will be concerned by losing the word ‘Assert’ in the line that does the assertion. If you arrange your tests using the Arrange, Act, Assert pattern this should not be a problem. See the example below.

[Test]
public void The_NBehave_Spec_Assertion_Using_Arrange_Act_Assert()
{
    // Arrange
    const int inputValue = 1;

    // Act
    var result = inputValue + 1;

    // Assert
    result.ShouldBeGreaterThan(inputValue);
}
Category: Unit tests

Ever Thought About Changing How You Name Unit Tests?

Posted on | January 11, 2010 | No Comments

In the last couple of months I’ve seen a new convention of how developers are naming their unit test classes.

Traditionally the naming convention most developers use is to append the word ‘Tests’ to the class name as shown below.

public class UserValidation
{
}

[TestFixture]
public class UserValidationTests
{
}

There is absolutely nothing wrong with it, but this can lead to problems because a test class can become unwieldy and difficult to maintain either because it’s too long, testing a lot of different concerns or both.

In some cases it would make more sense to take different approach and adopt an alternative naming convention. Sticking with the example above instead of having tests for validating a user in one class, a new class could be created for each concern. So instead of 

using System;
using NUnit.Framework;
using NBehave.Spec.NUnit;

namespace MyApplication.Validation
{
    [TestFixture]
    public class UserValidationTests
    {
        [Test]
        public void Should_Return_False_When_Username_Length_Is_To_Short()
        {
            // Arrange
            UserValidation userValidation = new UserValidation();

            // Act
            bool result = userValidation.IsUsernameValid(string.Empty);

            // Assert
            result.ShouldEqual(false);
        }

        [Test]
        public void Should_Return_False_When_Username_Length_Is_To_Long() { }

        [Test]
        public void Should_Return_False_When_Username_Contains_Invalid_Characters() { }

        [Test]
        public void Should_Return_True_When_Username_Is_Valid() { }

        [Test]
        public void Should_Return_False_When_Password_Length_Is_To_Short() { }

        [Test]
        public void Should_Return_False_When_Password_Length_Is_To_Long() { }

        [Test]
        public void Should_Return_False_When_Password_Is_Just_Numbers()
        {
            // Arrange
            UserValidation userValidation = new UserValidation();

            // Act
            bool result = userValidation.IsPasswordValid("123456");

            // Assert
            result.ShouldEqual(false);
        }

        [Test]
        public void Should_Return_False_When_Password_Is_Just_Alphabetical_Characters() { }

        [Test]
        public void Should_Return_False_When_Password_Contains_Invalid_Characters() { }

        [Test]
        public void Should_Return_True_When_Password_Is_Valid() { }
    }
}

we create two test classes

using System;
using NUnit.Framework;
using NBehave.Spec.NUnit;

namespace MyApplication.Validation
{
    [TestFixture]
    public class When_Validating_A_Users_Username
    {
        [Test]
        public void Should_Return_False_When_Username_Length_Is_To_Short()
        {
            // Arrange
            UserValidation userValidation = new UserValidation();

            // Act
            bool result = userValidation.IsUsernameValid(string.Empty);

            // Assert
            result.ShouldEqual(false);
        }

        [Test]
        public void Should_Return_False_When_Username_Length_Is_To_Long() { }

        [Test]
        public void Should_Return_False_When_Username_Contains_Invalid_Characters() { }

        [Test]
        public void Should_Return_True_When_Username_Is_Valid() { }
    }

    [TestFixture]
    public class When_Validating_A_Users_Password
    {
        [Test]
        public void Should_Return_False_When_Password_Length_Is_To_Short() { }

        [Test]
        public void Should_Return_False_When_Password_Length_Is_To_Long() { }

        [Test]
        public void Should_Return_False_When_Password_Is_Just_Numbers()
        {
            // Arrange
            UserValidation userValidation = new UserValidation();

            // Act
            bool result = userValidation.IsPasswordValid("123456");

            // Assert
            result.ShouldEqual(false);
        }

        [Test]
        public void Should_Return_False_When_Password_Is_Just_Alphabetical_Characters() { }

        [Test]
        public void Should_Return_False_When_Password_Contains_Invalid_Characters() { }

        [Test]
        public void Should_Return_True_When_Password_Is_Valid() { }
    }
}

I think this behavior driven development type naming convention will help focus on the problem you are solving and nothing more. This is very useful when doing test driven development.

When I first saw tests written like this my first thought was how am I going to find the all the tests for User Validation because they are no longer in a single class? If this is a problem for you think about using namespaces, folders or test attributes to organise your tests. Don’t forget if you use tools like ReSharper help you find where a class is being used very easily.

Remember this is about renaming your tests classes and not your methods. So even if you follow the MethodName_StateUnderTest_ExpectedBehavior pattern for test methods this could still work.

Category: Unit tests

Congratulations Chris Alcock on 500 Morning Brew Posts

Posted on | December 17, 2009 | No Comments

My iGoogle page helps me keep up to date with the latest .Net news, podcasts and blogs with many RSS feeds I have subscribed to.

Unfortunately I don’t have the time to check out all of them and so it can be difficult to decide what is worth reading.

It’s lucky for me then Chris Alcock does a really good job of giving a summary for each of the blog posts he thinks are worth reading on his Morning Brew posts.

I reckon his Reflective Perspective blog must be the most underrated .Net blog.

Well done Chris, keep up the great work.

If you’re ever in Cambridge, I’ll buy you a beer.

I've contributed to The Morning Brew - Daily .NET News and Views

Category: Uncategorized

Pair Programming Is Not a Silver Bullet

Posted on | December 9, 2009 | 1 Comment

Over the past four weeks I have been working with a new start up company where the owners (who are developers themselves) want to use pair programming to do everything.

In this blog post I want to share my experiences and opinions on pair programming.

This is not the first time I have done pair programming and it won’t be the last. I chose the title because when you read about pair programming in extreme programming books, the ideas look great on paper but can be difficult to implement and don’t work in the real word.

Two heads are better than one

Developers who have had positive pair programming experiences write about

  • how much more focused they were
  • increased productivity
  • better code quality
  • shared knowledge/less dependency on a single developer
  • how it helps new developers learn the ropes
  • quicker investigation and resolution of bugs
  • on the job training
  • why it’s so much better than doing code reviews

On that last point Theodore Nguyen-Cao has made an excellent observation in his blog post Pair Programming > Code Reviews

In code reviews, people sit down to review someone’s code. Everyone has an opinion but not everyone is going to be working with the code on a daily basis. At the time, everyone seems to be involved in the process but there is no vested interest. They are just looking at some code and asking themselves “does this code look good and is it correct?”. It’s a very passive standpoint. On the other hand, pair programmers are completely invested (committed?) in the task at hand. They immediately are using the code they are writing together and collaborating their thoughts on design, code layout, etc. Both programmers are taking on an active role and are emotionally invested in the task at hand because they are attacking the same problem together.

Shared Code Ownership

Have you ever been in a situation where a ‘friend’ has become so attached to their code, they find it difficult to accept it doesn’t do what it’s supposed to or is no longer required?

My best experience of pair programming has been taking over the lead development of a project where the previous developer had been working alone.

When a colleague and I came to conclusion what had be done did not have anything to do with the problem domain, we decided to delete the code and pair together to ensure the same mistake did not happen again. We continued to pair until we had a solid foundation to build upon.

When code is written by a single developer, they will inevitably end up owning it. No doubt you’ve heard something like “that’s Bobs login controller”. If there’s a bug or issue with that code the developer can become very defensive.

When a pair is told the same thing I think it’s a lot easier to deal with because no one person owns the code. In addition developers working in a pair find it easier to accept the path they had followed has come to a dead end a lot quicker than a developer working alone.

Sounds great. Where do I sign?

Hold on comrade not so fast.

Pair programming can be a bit like communism, too good to be true.

One of the most difficult things in pair programming is finding a good pairing partner. The general consensus is that developers need be at similar skill levels, understand the domain and offer something their partner other can’t.

All good relationships are about give and take

Unless you’re a total idiot and couldn’t care what your pairing partner thinks, you will be conscious how your partner is feeling.

If you hog the keyboard too much your partner will become a chicken, involved but not committed. They will play with their phone, think about what to have for dinner, or even fall asleep.

If you’re too passive, not wanting to ‘rock the boat’ you will be putting your name against something you don’t necessarily agree with. This can even be an issue even when you have the keyboard, because all you’re doing is typing what the other is saying. Lets hope your secretarial skills are up to the job.

Some of you will be quick to point out the solution to this problem is to swap the driver/observer roles every 30 minutes or by adopting the ping pong pair programming pattern.

However this doesn’t always solve the issue especially if you’re working with a control freak who says something like “why don’t you try this?” before taking back the keyboard.

In addition to changing roles within a pair, the developers in the pair should be rotated.

In the Turning on a sixpence – No excuses: Concept To Cash Every Week video, developers working on a project for a broadcasting company changed pair in morning and afternoons. This means over a course of two days five different developers would have been involved.

For example in the morning developer A would be ‘driving’, and developer B would be the ‘observer’. In the afternoon developer A, would move onto something else, developer B would be ‘driving’ and developer C would be ‘observer’.

I know this is cynical but I can’t imagine that the first few minutes of a new pair starting aren’t spent bitching about the developer who has moved on, followed another half an hour taking out what they think is wrong and doing what they wanted to do.

Developer Values

There’s no doubt a developer has to make sacrifices when pairing. What if you like doing test driven development, but your partner doesn’t? You could

  • suggest to try it out – and then try to look pleased when they don’t.
  • raise the issue – because that’s really going to help you build a relationship.
  • do nothing – it’s the easiest thing to do and you simply can’t face another day at work wasting time fighting your corner.

A good developer in a pair can’t argue with everything they don’t agree with, they have to bite their lip and let things slide. After all how much value is there in arguing if underscores should be used for private member variables.

Thanks But No Thanks

Many developers have said that pair programming is that it’s not for them.

Lionel Messi European and soon to be crowned World footballer of the year is fantastic when he plays for Barcelona. But when he plays for Argentina things don’t go so well. The problem is the system he has to fit into.

Why should a developer who is able to write good quality code on time be forced to fit into a process? After all that isn’t agile about ‘Individuals and interactions over processes and tools’.

And if you believe ‘people work harder when there is someone looking over their
shoulder’ (see Mendelt Siebenga’s post The dirty secret of pair programming), well that fits in with the Tayloristic view that workers are stupid, can’t be trusted because they don’t care about things and only managers know better.

When I’m working alone and have to solve a problem I can look on the internet Stack Overflow or ask a colleague.

Sometimes when you’re pair programming trying to find a solution is like having a back seat driver on board, you’re unable to concentrate and have to speed read because your partner wanted to click on a different link or search for something else. This clip sums up what I mean.


Harry Enfield Television Programme – You Didn’t Wanna – A funny movie is a click away

On a lighter note some developers have pointed out they don’t like pair programming because to hygiene issues. Sharing a keyboard and mouse with someone who doesn’t wash their hands, or having to sit in close proximity with someone who smells, is not a pleasant experience.

Jag Reehal’s Final Thought on ‘Pair Programming Is Not a Silver Bullet’

Should developers do pair programming all of the time? No.

Developers should be trusted and left to their own deceives. They should pair when they think it’s the right thing to do.

When pair programming works it’s very good, not only for the project but for individuals as well. The amount of keyboard shortcuts and tips and tricks I’ve picked up when pairing is amazing. Check out Keyboard Jedi when you are pairing, it shows a visual list of shortcuts as they are being typed.

It’s important that developers are able to read blogs, explore and experiment, which pair programming doesn’t really allow for because your partner has to agree with everything you want to do. In his PDC 09 ASP.NET MVC 2: Ninjas Still on Fire Black Belt Tips talk Scott Hanselman said ‘you can spot a good dev if their recent project list contains projects where they are just trying out stuff’.

For me code reviews come too late in the development process. Pair programming should seriously be considered if code reviews are of paramount importance to you.

As for the arguments about productivity and focus etc, yes it can help. But I also believe developers working alone and using the pomodoro technique could achieve similar results.

I will never dictate my team have to do pair programming. But I would be disappointed if they never tried.

Category: Agile, Extreme Programming (XP)
keep looking »