ASP.NET 3.5 Enterprise Application Development with Visual Studio® 2008: Problem - Design - Solution

9.3. Solution

Now that a pattern is defined for generating reports, let's create a report in the Paid Time Off solution that enables users to print a listing of their requests. The My Requests report, shown in Figure 9-32, will display a list of all requests for the current user.

Figure 9.32. Figure 9-32

This is similar to the View My Requests page created in Chapter 7, and in fact that is the page from which users should be able to print this report. Start by creating the stored procedure that will retrieve this data. You might assume that the query already exists because a page already displays this data but the object that populates the grid is not denormalized, so a new stored procedure needs to be created that denormalizes the data in order for it to be used as a data source for the report. The stored procedure must accept a user account ID, and must know which workflow to join to because the query has to display the current state and current owner of the request. Remember that an application can have more than one workflow and the object name is unique across workflows, so you can use that to determine which records in the ENTWFItem table belong to this workflow:

CREATE PROCEDURE ReportMyPTORequests ( @ENTWorkflowObjectName varchar(255), @ENTUserAccountId int ) AS SET NOCOUNT ON SELECT ENTUserAccount.LastName, ENTUserAccount.FirstName, PTORequest.RequestDate, PTODayType.PTODayTypeName, ...