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

Modify Report and Stored Procedure

To achieve the scenarios specification allowing Jay to see the total for each sales rep, we need to either create a new stored procedure or modify an existing one.  Looking through the stored procedures available in the Northwind database, I encountered a stored procedure called “Employee Sales by Country” that closely fits the data we need.  Let’s base the new report on this stored procedure.  We need to alter this stored procedure a little to add the month and year. 

View Code Listing 1 to see the altered stored procedure.  We added ShipMonth and ShipYear to allow grouping and sorting by that date grouping.  This also could be accomplished by adding formulas in the report, or through coding in the unit test.  But I prefer to have that data in our stored procedure to make maintenance easier.

Code Listing 1

SET QUOTED_IDENTIFIER ON 
GO SET ANSI_NULLS ON 
GO ALTER  procedure "Employee Sales by Country" 
@Beginning_Date DateTime, @Ending_Date DateTime AS
SELECT Employees.Country, Employees.LastName,
Employees.FirstName, Orders.ShippedDate,
Orders.OrderID, "Order Subtotals".Subtotal
AS SaleAmount, month(Orders.ShippedDate)
as 'ShipMonth', year(Orders.ShippedDate) as 'shipyear'
FROM Employees INNER JOIN 
      (Orders INNER JOIN "Order Subtotals" ON
Orders.OrderID = "OrderSubtotals".OrderID) 
      ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.ShippedDate Between @Beginning_Date And @Ending_Date
order by employees.lastname, employees.firstname, shipyear, shipmonth 
GO SET QUOTED_IDENTIFIER OFF 
GO SET ANSI_NULLS ON 
GO


View Entire Article

User Comments

No comments posted yet.




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


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-09-21 9:57:24 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search