Test Driven Development for a Business Intelligence Project with Crystal Reports: Part 2
 
Published: 15 Aug 2005
Unedited - Community Contributed
Abstract
In the second part of a three-part series on Test Driven Development (TDD) in .NET, Eric Landes shows how to set up a further test for a Business Intelligence project. He uses Crystal Reports 10 with nUnit to compare the output of a report to the raw SQL data from which the report is generated.
by Eric Landes
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 27006/ 31

Introduction

Welcome to the second part of a three-part series on Test Driven Development for Business Intelligence projects.  Specifically, I’m writing about TDD in .NET with Crystal .NET as the Business Intelligence tool.

In Part 1, I showed how to start a Business Intelligence project using C#, Crystal .NET, and nUnit.   This was done in small steps.  First, we created a simple test to confirm the loading of the report into our custom object.  Next, we created methods for logging onto the database for the Crystal Report object.  Finally, we set up a test comprising the logging methods and loading the report into our custom object. 

In Part 2 of this series, I show how to write a test that compares the data from the report output to that coming from a straight SQL query.

Requirements

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.

Sample Report Story

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

Test Output of Report to Text File

The next unit test checks the grand total of sales generated from a query in the database against the output of the report.  We haven’t changed the report format yet, but will in Part 3 to meet the scenario mentioned.

We need to determine how we capture the output from the report in order to perform the comparison against the SQL query.  In the case of Crystal, I don’t care what format it’s in, but I need to be able to read the report format programmatically.  So I decided to use the text format for output in my test case.

Before I actually test the grand total, I create a test that will generate the report and output it to a text file. After we verify that we can find the grand total, we’ll parse the data and compare the grand total to that calculated from a straight-up SQL query.

Code Listing #1

[Test]

public void SalesByYearReportResults()
{
 
      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");
      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();
      using (StreamReader sr = new StreamReader(@"c:\temp\ReportTest.html")) 
      {
            String line;
            // Read and display lines from the file until the end of 
            // the file is reached.
            while ((line = sr.ReadLine()) != null) 
            {
                  if (line.IndexOf("Total") > 0)
                  {
                        Assert.IsTrue(true,"Not found");
                  }
            }
      }
                  
}

 

[code in CrystalObjects]

public void ExportData() 
{ 
      CrystalDecisions.Shared.DiskFileDestinationOptions oDest 
            = new CrystalDecisions.Shared.DiskFileDestinationOptions(); 
      CrystalDecisions.Shared.ExportOptions oExpOptions 
            = new CrystalDecisions.Shared.ExportOptions();
      string ExportFileName = _fileName; 
      try 
      { 
                  
            oExpOptions.ExportDestinationType = ExportDestinationType.DiskFile;
                        oExpOptions.ExportFormatType = ExportFormatType.Text;
                        oDest.DiskFileName=FolderName + FileName;
                        oExpOptions.ExportDestinationOptions=oDest;
                        currDoc.Export(oExpOptions);
      } 
      catch (Exception e) 
      {
            throw new Exception("Error encountered in ExportData: " + e.Message);
      } 
}

The code above starts out using my custom object, CrystalObjects, introduced in Part 1.  The test loads the report and checks for the existence of a file that contains the report results.  Two new properties are now part of CrystalObjects: FileName and FolderName.   I then created the ExportData method which exports a text version of the report to the file and folder that are set in the FileName and FolderName properties.  ExportData will need to be refactored in the future to allow for different export options.  But right now it does what I need it to do: export to a text file.

When I run this test, I see a green bar (indicating a successful test).  That means it’s time to do some actual comparisons.

Test and Verify Grand Total

Now let’s add some code to generate a grand total directly from the database. This should be the same filtered data that the report will return. So after outputting the report, we should be able to parse the text file for the grand total, compare it to our SQL total, and see a green bar in nUnit.

First, at the beginning of the test, we load the total using the ExecuteScalar method from the Enterprise Library Data Access Application Block. Then we test the result to make sure that a number is returned with an Assert.IsTrue.

After checking the totals directly from the database, we use the code from before to load the report using the CrystalReport object. After running the export I put in the code to parse the exported file.

Using a StreamReader to store lines from the report into a variable, I check for the existence of the word “Total”. If it is found, the string is parsed. The total from the report is then put into another variable. Finally, the two totals are compared to achieve the desired green bar.

Code Listing #2

public void SalesByYearReportResults()
{
// 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 SUM([Order Subtotals].Subtotal) FROM Orders " + 
            "INNER JOIN [Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID " + 
            "WHERE Orders.ShippedDate Between '01/01/1998' And '12/31/1998'";
      DBCommandWrapper dbCommandWrapper = db.GetSqlStringCommandWrapper(sqlCommand);
      // DataSet that will hold the returned results
      Decimal dSQLTotal = System.Convert.ToDecimal(db.ExecuteScalar(dbCommandWrapper).ToString());
      Assert.IsTrue(dSQLTotal>0, "No results was returned");      
 
      CrystalObjects.ReportObjects oCrObjs = new CrystalObjects.ReportObjects();
      oCrObjs.ServerName="(local)";
      oCrObjs.UserName="NorthWind";
      oCrObjs.UserPassWord="NorthWind";
      oCrObjs.Database="NorthWind";
                  oCrObjs.LoadReport(@"C:\Dev\UnitTestBICrystalPartII\SalesReportToTest.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")) 
      {
      // Read and display lines from the file until the end of 
      // the file is reached.
            while ((line = sr.ReadLine()) != null) 
            {
                  if (line.IndexOf("Total") > 0)
                  {
                        Assert.IsTrue(true,"Not found");
                        break;
                  }
            }
      }
      // I can't assume that line includes the total.
      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.");
      }
                  
}

Summary

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 export the report into the format needed to compare its output to data retrieved via a SQL query.  We then compared the total that is produced in the report to the total produced from a straight SQL query.

In Part 3, I’ll wrap this up to test to the scenario.  We’ll modify the report to generate totals by sales rep as well as filter the data by a given time period.  And we’ll begin with a test for this.  Happy Coding!

Test Driven Resources

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



User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 



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


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-28 10:37:35 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search