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.