Test Driven Development for a Business Intelligence Project with Crystal Reports
 
Published: 05 Jul 2005
Unedited - Community Contributed
Abstract
In the first part of a three-part series on Test Driven Development (TDD) in .NET, Eric Landes shows how to set up the initial tests for a Business Intelligence project using Crystal Reports. Some preliminary knowledge of TDD is necessary.
by Eric Landes
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 27345/ 72

Introduction

Kent Beck originally introduced test-driven development (TDD) to software development circles. I first learned of this development technique on mailing lists, after learning about Test First practices.  I then read Kent’s book Test Driven Development: By Example to learn how to implement this technique in my software projects.   

TDD evolved from the Extreme Programming (XP) methodology of Unit Testing first. It is a great part of any methodology, and is easily used in .NET projects. This article assumes that the reader has some familiarity with Test Driven Development and Unit Testing concepts. For resources to help you get started with TDD, I refer you to the end of this article, with links to other resources concerning TDD and unit testing. 

Unit Testing Tools

In .NET development there are a number of frameworks one can use for Unit Testing.  nUnit is the big daddy, and is probably the most familiar to developers.  Developed by Jim Newkirk, who is currently employed by Microsoft, nUnit is a C# port of jUnit. Other unit testing frameworks for .NET include csUnit and mbUnit (the latter is part of the Generative Unit Test framework). Most of these frameworks are open source, so the price is right.  This article assumes the use of nUnit for any code snippets. 

Articles and samples abound explaining unit testing for various types of projects. However, there seems to be a lack of articles or samples explaining TDD for a Business Intelligence project. This article hopes to fill that void.  My experience with Crystal Reports, and its inclusion with Visual Studio.NET, made Crystal the obvious BI TDD choice.   

Requirements

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

Start with a Simple Test

TDD development follows the Extreme Programming (XP) principle of coding the unit test first. Even though your project may need 200 reports, each with different results, start with a simple test. I start with a scenario of the report. The next section contains the User Story that constitutes our definition.

For instance, one should start with a test that will fail. For a BI project, first test loading a report file into an object successfully. My normal architecture for Crystal projects involves invoking the reports from a physical file location, rather than compiling the reports into a .NET dll. So my first test would look like this:

Listing 1: nUnit Simple Test Code

[Test]
public void TestLoadReport()
{
      CrystalObjects.ReportObjects oCrObjs = new CrystalObjects.ReportObjects();
      oCrObjs.LoadReport(@"C:\Dev\UnitTestBICrystal\SalesReportToTest.rpt");
      Assert.AreEqual(oCrObjs.currDoc.SummaryInfo.ReportTitle,"Customer Orders by Customer");
}

Now this may be a little contrary to normal TDD teaching. Mainstream TDD practices include starting with a test that fails. This assumes you are creating a new object for that test. In this case however, a third party has provided the Crystal Object. I believe that we can forgo the failing test in this case. 

Note that nUnit 2.2 throws an error when utilizing the Crystal Document object directly.  To overcome this problem, I started refactoring a bit earlier, by creating a class based on the Crystal object. That is the CrystalObjects.ReportObjects class in the above nUnit Simple Test Code.

To continue to the next effective test, you must have an idea of what you are testing. In this case, we need to test for a large BI project. Make sure to include explicit Unit Test results in any specification you create. Since BI projects normally have very defined results that the users want, the tests are almost written before you need to code! 

Sample Report Story

There are many ways to define the specifications for your application. Since we are discussing doing this in an agile way, the specifications come in a scenario. Below is the description from a scenario for our first report. It includes personas Katie (a sales representative) and Jay (her supervisor).

Description:

Jay wants to be able 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 from 1 January to 7 January 2002.  He also wants to be able to see the total for each sales rep.

This seems like a simple request. For the purposes of this article, I based the report on the Northwind database that comes standard with SQL Server 2000. 

We have the definition for the report, which gives us the basis for the first test. To make sure that the report returns the right information, let us compare the reports result to a straight SQL query utilizing a dataset. Let’s start by using a stored procedure that is included with the Northwind database.

Since I’m basing the report on the Sales by Year stored procedure, let’s create a test to get data directly from the database. This result will then be compared with the actual report.  From the code listing below, take out the actual SQL and insert it in the test with the same filter criteria you would pass from the report into the stored procedure.

Listing 2: Sales by Year Stored Procedure

SET QUOTED_IDENTIFIER ON 
GO 


SET ANSI_NULLS ON 
GO 


ALTER  procedure "Sales by Year" 
      @Beginning_Date DateTime, @Ending_Date DateTime AS 


SELECT Orders.ShippedDate, Orders.OrderID,
"Order Subtotals".Subtotal, DATENAME(yy,ShippedDate) AS Year FROM
Orders INNER JOIN "Order Subtotals" ON Orders.OrderID
= "Order Subtotals".OrderID WHERE Orders.ShippedDate
Between @Beginning_Date And @Ending_Date
GO 


SET QUOTED_IDENTIFIER OFF 
GO 


SET ANSI_NULLS ON 
GO

The name of this first test should be something descriptive, so I chose SalesByYearResults. I start this test by using a straight query with SQL similar to that used in the stored procedure. So the initial code looks like the code in Listing 2:

Listing 3: SalesByYearResults Test

[Test]
public void SalesByYearResults()
{
      Microsoft.Practices.EnterpriseLibrary.Data.Database db
      = DatabaseFactory.CreateDatabase(); 


      string sqlCommand = "SELECT Orders.ShippedDate, Orders.OrderID,
      [Order Subtotals].Subtotal, DATENAME(yy,ShippedDate) AS Year
      FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID =
      [Order Subtotals].OrderID WHERE Orders.ShippedDate Between
      '01/01/1998' And '12/31/1999'"; 


      DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommand); 


      DataSet productsDataSet = db.ExecuteDataSet(dbCommandWrapper);
      Assert.IsTrue(productsDataSet.Tables[0].Rows.Count>0, "No results were returned");
}

This simple test proves that our code returns data. Each iteration of the test builds toward the ultimate test. So now, let us add the stored procedure to this test. Since the report is based on this stored procedure, this step helps verify that the results from running a stored procedure with the same filters as the straight SQL expression give the same results. I will then be sure that any problems encountered when I test the report against a straight SQL expression are related to the Crystal Report, not the stored procedure. I added the code in Listing 3 to the code in Listing 2.

Listing 4: Testing the Stored Procedure

sqlCommand = "Sales By Year";
dbCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand); 


dbCommandWrapper.AddInParameter("@Beginning_Date",
DbType.DateTime, System.Convert.ToDateTime("01/01/1998")); 


dbCommandWrapper.AddInParameter("@Ending_Date", DbType.DateTime,
System.Convert.ToDateTime("12/31/1999")); DataSet salesDataSetSP =
db.ExecuteDataSet(dbCommandWrapper); Assert.IsTrue(salesDataSetQuery.
Tables[0].Rows.Count == salesDataSetSP.Tables[0].Rows.Count,
"Does not contain the same data.");

This test brings back a green bar in the nUnit interface. Let’s add some Crystal code to our tests to continue developing our application.

Adding More Methods to the Crystal Object

Before we begin running tests against totals from the reports, let’s add a login method to our class for Crystal Reports. Add to the custom Crystal Object class the following login method that I use to log into a database from a Crystal Report Document. The method is named ApplyCRLogInfo. See the code in Listing 4 to see what I’ve added.

Listing 5: ApplyCRLogin Method

private void ApplyCRLogin()
{
 CrystalDecisions.CrystalReports.Engine.Database oCRDb = currDoc.Database; 
 CrystalDecisions.CrystalReports.Engine.Tables oCRTables = oCRDb.Tables; 
 CrystalDecisions.Shared.TableLogOnInfo oCRTableLogonInfo; 
 CrystalDecisions.Shared.ConnectionInfo oCRConnectionInfo =
   new CrystalDecisions.Shared.ConnectionInfo(); 
 oCRConnectionInfo.DatabaseName = _dataBase; 
 oCRConnectionInfo.ServerName = _serverName; 
 oCRConnectionInfo.UserID = _userID; 
 oCRConnectionInfo.Password =_userPass; 
 foreach (CrystalDecisions.CrystalReports.Engine.Table oCRTable in oCRTables) 
 { 
  oCRTableLogonInfo = oCRTable.LogOnInfo; 
  oCRTableLogonInfo.ConnectionInfo = oCRConnectionInfo; 
  oCRTable.ApplyLogOnInfo(oCRTableLogonInfo); 
 }  
}

I have added this private method within the ReportObjects class . The method is called from the Load method that is already used in another test. In LoadReport, if the UserID, Password, DatabaseName, and ServerName properties are filled, then call the ApplyCRLogin method.

The test is simple.  Add a section in the SalesByYearResults test to use this part of the object.  This is enacted by populating the UserID, Password, DatabaseName, and ServerName properties.  See Listing 6 for the code I have added to the test.

 

Listing 6: Extended SalesByYearResults Test

[Test]
public void SalesByYearResults()
{
 // Create the Database object, using the default database service. The
 // default database service is determined through configuration.
 Microsoft.Practices.EnterpriseLibrary.Data.Database db = DatabaseFactory.CreateDatabase(); 


 string sqlCommand = "SELECT Orders.ShippedDate, Orders.OrderID, [Order Subtotals].Subtotal,
   DATENAME(yy,ShippedDate) AS Year
   FROM Orders INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID
   WHERE Orders.ShippedDate Between '01/01/1998' And '12/31/1999'";
 DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommand);
 // DataSet that will hold the returned results  
 DataSet salesDataSetQuery = db.ExecuteDataSet(dbCommandWrapper);
      Assert.IsTrue(salesDataSetQuery.Tables[0].Rows.Count>0, "No results was returned"); 


 sqlCommand = "Sales By Year";
 dbCommandWrapper = db.GetStoredProcCommandWrapper(sqlCommand);


 // Retrieve products from the specified category.
 dbCommandWrapper.AddInParameter("@Beginning_Date", DbType.DateTime,
   System.Convert.ToDateTime("01/01/1998"));
 dbCommandWrapper.AddInParameter("@Ending_Date", DbType.DateTime,
   System.Convert.ToDateTime("12/31/1999"));


 // DataSet that will hold the returned results  
 DataSet salesDataSetSP = db.ExecuteDataSet(dbCommandWrapper);
 Assert.IsTrue(salesDataSetQuery.Tables[0].Rows.Count== salesDataSetSP.Tables[0].Rows.Count,
    "Does not contain the same data.");


 CrystalObjects.ReportObjects oCrObjs = new CrystalObjects.ReportObjects();
 oCrObjs.ServerName="(local)";
 oCrObjs.UserName="NorthWind";
 oCrObjs.UserPassWord="NorthWind";
 oCrObjs.Database="NorthWind";
   oCrObjs.LoadReport(@"C:\Dev\UnitTestBICrystal\SalesReportToTest.rpt");


}

Now run the SalesByYearResults test to get a green bar. Next, the TestLoadReport should return a green bar as well. Now it is on to writing the meat of the tests so to speak. These are the tests comparing results from a database query to results in a report. I'll explore those tests in Part II of this series.

Summary

Part I of this article has shown my approach to Test Driven Development of a Business Intelligence project using Crystal Reports. Part I sets up the initial tests to introduce you to the methods I use in TDD BI projects. Part II will introduce more complex testing of the Report, including comparing report output to data obtained directly from the database.

Below I have included a listing of resources for Test Driven Development. These have helped me in getting started with TDD.

Test Driven Resources

Books

Test Driven Development: By Example by Kent Beck

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

Web Resources

Explanation of TDD by Scott Ambler

Testdriven.com, a web site of TDD news



User Comments

Title: Sales trends   
Name: William
Date: 2005-08-24 2:10:09 AM
Comment:
The codings and explanations provided are pretty comprehensive. Is there any way to run a report for sales for 2 weeks from 1 January to 14 January 2002 ? From such, i would be able to see the sales figs for wk 1 and 2, which i can determine the trends.




Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-10-22 3:50:19 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search