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.");
      }
                  
}