Welcome to the last part of a three-part series on Test Driven Development (TDD) for Business Intelligence projects.  Specifically, I’m writing about TDD in .NET using Crystal .NET version 10 as the Business Intelligence tool. 

In the first part of this series, I showed how I start a Business Intelligence project using C#, Crystal .NET, and nUnit.   This is done in small steps.  First, we created a simple test to load the report.  Next, we created methods and tests for logging onto the database for the Crystal Report object.

In the second part of this series, I showed how to write a test comparing the data from the report output to the data from a straight SQL query. 

In this, the last part of the series, we finish the test by coding to the scenario given to us.  Once we’re done with this test, the report is ready to be rolled into an application, and the unit tests are always there should we need to make changes.

This article was developed using Visual Studio 2003, SQL Server 2000, nUnit 2.2, the Data Access Application Block from the Enterprise Library available on MSDN, and Crystal Reports 10 Developer edition.

As I mentioned in Part 1, I used a scenario to describe what needs to be developed.  The scenario includes personas Katie (a sales representative) and Jay (her supervisor).

Description:

Jay wants to see sales totals for his sales force for any time period he might specify.  For instance, Jay might want to run a report for sales for the week of 1 January to 7 January 2002.  He also wants to be able to see the total for each sales rep.

To achieve the scenarios specification allowing Jay to see the total for each sales rep, we need to either create a new stored procedure or modify an existing one.  Looking through the stored procedures available in the Northwind database, I encountered a stored procedure called “Employee Sales by Country” that closely fits the data we need.  Let’s base the new report on this stored procedure.  We need to alter this stored procedure a little to add the month and year. 

View Code Listing 1 to see the altered stored procedure.  We added ShipMonth and ShipYear to allow grouping and sorting by that date grouping.  This also could be accomplished by adding formulas in the report, or through coding in the unit test.  But I prefer to have that data in our stored procedure to make maintenance easier.

Code Listing 1

SET QUOTED_IDENTIFIER ON 
GO SET ANSI_NULLS ON 
GO ALTER  procedure "Employee Sales by Country" 
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Employees.Country, Employees.LastName,
Employees.FirstName, Orders.ShippedDate,
Orders.OrderID, "Order Subtotals".Subtotal
AS SaleAmount, month(Orders.ShippedDate)
as 'ShipMonth', year(Orders.ShippedDate) as 'shipyear'
FROM Employees INNER JOIN 
      (Orders INNER JOIN "Order Subtotals" ON
Orders.OrderID = "OrderSubtotals".OrderID) 
      ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date
order by employees.lastname, employees.firstname, shipyear, shipmonth 
GO SET QUOTED_IDENTIFIER OFF 
GO SET ANSI_NULLS ON 
GO

Now I believe that we have a stored procedure on which to base our report.  I simply walk through the wizard for creating a report.  This report is included in the download of the other reports.  I did not add any formatting enhancements to the standard report.

This report now fulfills our scenario.  Before we put the report into production let’s write a test, using the techniques in the other parts of this series, that brings together the report and verifies that the totals in the report match the totals from the database.

Before setting up the report, make sure to run all our previous unit tests to achieve a green bar.  If any test should have a red bar, this is a great time to fix it. 

Now I create a test named SalesByYearByEmployeeReportResults.  Similar to the test SalesByYearReportResults from Part 2, just cut and paste, and then rename that test.  Then you need to change the SQL code for the ExecuteScalar method.  See Code Listing 2 for that code.

Code listing 2

Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase();
string sqlCommand = "SELECT sum([Order Subtotals].Subtotal) " + 
  "FROM Employees INNER JOIN (Orders INNER JOIN [Order Subtotals] ON Orders.OrderID =" +
  "[Order Subtotals].OrderID) ON Employees.EmployeeID = Orders.EmployeeID " +
  "WHERE Orders.ShippedDate Between 01/01/1998 And 12/31/1998 " +
  "order by employees.lastname, employees.firstname, shipyear, shipmonth "; 
DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommand);
Decimal dSQLTotal = System.Convert.ToDecimal(db.ExecuteScalar(dbCommandWrapper).ToString());
Assert.IsTrue(dSQLTotal>0, "No results was returned");

The code in sqlCommand contains the same joins as what we have in the stored procedure.  The SQL contains the sum we should have in report for the grand total.

Now we’ll use similar code from Part 2 to compare the SQL results to the report's results.  We use the existing parameter values with this report.  Then we look for “Grand Total” in the report to find the total to compare.  See Code Listing 3 for a listing of that code.

Code Listing #3

CrystalObjects.ReportObjects oCrObjs = new CrystalObjects.ReportObjects();
oCrObjs.ServerName="(local)";
oCrObjs.UserName="NorthWind";
oCrObjs.UserPassWord="NorthWind";
oCrObjs.Database="NorthWind";
oCrObjs.LoadReport(@"C:\Dev\UnitTestBICrystalPartII\SalesReportByRepToTest.rpt");
if (System.IO.File.Exists(@"c:\temp\ReportTest.html"))
{
  System.IO.File.Delete(@"c:\temp\ReportTest.html");
}
oCrObjs.FolderName=@"C:\Temp\";
oCrObjs.FileName=@"ReportTest.html";
oCrObjs.ExportData();
String line = "";
using (StreamReader sr = new StreamReader(@"c:\temp\ReportTest.html")) 
{
  while ((line = sr.ReadLine()) != null) 
  {
    if (line.IndexOf("Grand Total") > 0)
    {
      Assert.IsTrue(true,"Not found");
      break;
    }
  }
}
Decimal dReportTotal;
if (line != "")
{
  Int32 iIndex = line.IndexOf("$")+1;
  String strTotal = line.Substring(iIndex).Replace(",","");
  dReportTotal = System.Convert.ToDecimal(strTotal);
  Assert.AreEqual(dSQLTotal, dReportTotal);
}
else
{
  Assert.IsTrue(false, "No data was returned from the report.");
}

This code does the trick using the new report SalesReportByRepToTest.rpt.  I get a green bar when I run this code.  Now we can implement the code in a web application or a windows application.  Also use this time to refactor any code.

The three articles in this series take you through the process I use when developing a test driven Business Intelligence project.  In Part 1 of this series, I introduced my methods for Test Driven Development for a Business Intelligence project, starting with a simple test or two.  In Part 2, we went further and created methods to get the report into the format needed to compare to data retrieved via SQL queries.  Then we compared the total that is produced in the report to the total produced from a straight SQL statement.

In this third part of the series, we coded to the scenario.  The report now has totals by Sales Rep as well as totals by time period.  The next step in this process includes refactoring and adding things like methods for the parameters.  I’ve presented my method of TDD for a Business Intelligence project.  Please feel free to use parts or all of this method, and extend it.  Happy Coding! 

Books

Test Driven Development By Example by Kent Beck

Test-Driven Development in Microsoft .NET  By James W. Newkirk

Web Sites

Testdriven.com – website for TDD on all platforms

Introduction to Test Driven Development – From  Agile Data website