Analyzing Work Hours

[Previous] [Next]

After you have saved all the work hours from all the submitted timesheets, you can let users analyze those work hours across the three dimensions: Time, Employee, and Project. There is naturally one measure or total: Hours Worked. You should not be surprised that the most appropriate control for analyzing this data is the PivotTable control.

You will likely want to experiment with using this part of the Timesheet solution, so after you have submitted a few timesheets, click the Analyze Hours button on the navigation bar. You will be prompted to find the Timesheets.mdb file. This is a natural drawback of using the Jet database engine in this solution. The Jet database engine is not a client/server database system. Instead, it requires file share access to the MDB file, and by default, the page cannot know where that file is if the page came from a web server. If you use SQL Server as the database store, the page can directly connect to the instance of SQL Server by name and not require any explicit file share path. Alternatively, you can use RDS as described in Chapter 5, but SQL Server is still a far better engine to use in a real multiuser and highly concurrent system.

After finding the Timesheets.mdb file, I initialize the PivotTable control using the following code from the Window object's onLoad event:

     ' Get the active view     Set view = ptHours.ActiveView      view.AutoLayout      view.DataAxis.InsertTotal _         view.AddTotal("Total Hours", view.FieldSets("Hours").Fields(0), _             c.plFunctionSum)     view.TotalBackColor = "Cornsilk"     ptHours.DisplayFieldList = True     ' Sort by work date, showing most recent data first     view.Fieldsets("Work Date").Fields(0).SortDirection = _         c.plSortDirectionDescending              view.DataAxis.RemoveFieldset view.FieldSets("Full Name")     view.Titlebar.Caption = "Work History" 

Because this is a tabular data source, no predefined totals exist. However, it makes perfect sense for this solution to automatically create a total based on the Hours column in the WorkHours table. The code accomplishes this by using the active view's AddTotal method, passing the name of the new total, the field on which to base the total, and the summary function for the total (in this case, Sum). I also set the total's background color to "Cornsilk" to make it stand out from the detail data.

The code continues by sorting the data in descending order by the Work Date column so that the most recent work hours are shown first. It also removes the Full Name column from the view, leaving the Short Name column. I created these two columns for the case in which you are using Windows NT Challenge/Response Security. In such a case, the user names returned from the LOGON_USER server variable are in the form of NT Domain Name\NT Logon Name. This is not the most attractive or easy-to-read name, so I created an additional column that contains only the NT Logon Name part. This is the column left in the view by default. To see how I extracted only the second part of the name, see the QueryWorkHours stored query in the Timesheets.mdb file.

After the PivotTable is loaded, the user can filter, sort, and drag fields to the row or column axes to group the data and see subtotals for each group. Any programming technique I described in Chapter 7 (such as saved reports or top N filtering) can also be applied here, but for simplicity's sake, I left this page rather meager. To try it out, drag the Project field over the row area and drop it. Then drag the Work Date By Week fieldset from the PivotTable Field List window to the column area to view a cross tabulation of Hours Worked by Project by Week.



Programming Microsoft Office 2000 Web Components
Programming Microsoft Office 2000 Web Components (Microsoft Progamming Series)
ISBN: 073560794X
EAN: 2147483647
Year: 1999
Pages: 111
Authors: Dave Stearns

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net