Test Driven Development for a Business Intelligence Project with Crystal Reports
page 3 of 5
by Eric Landes
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 26501/ 87

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).


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



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



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

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); 

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.

View Entire Article

User Comments

Title: Sales trends   
Name: William
Date: 2005-08-24 2:10:09 AM
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-2023 ASPAlliance.com  |  Page Processed at 2023-09-25 1:46:50 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search