Data Caching During Preview

You switched between layout and preview a number of times during the development of this report. If you were to look on your SQL Server, however, you would find the rather complex query that provides the data for this report was only executed once. This is because the data returned for the dataset the first time the report was run is stored in a cache file. Any time after that, when the same report is run in the Report Designer with the same query, same parameters, and same data access credentials, the cached data is used.

This data caching helps to make your report development sessions more efficient. Even if you have a report based on a query that takes a fair amount of time to run, you only have to wait for it once. Any time you preview the report after that, the data is pulled from the cache file with no delay. This caching process also substantially decreases the load on your SQL Server. This can be important if you are following the frowned-upon practice of developing reports against a production database server.

The drawback to the data-caching process comes when you are making changes to the data at the same time as you are developing a report. If you insert new records or update existing records after the first time you preview the report, and then expect to see those changes in your report the next time you preview it, you are going to be confused, disappointed, or perhaps both. The report is rendered from the cached data that does not include the changes.

To remedy this situation, you need to delete the cache file. Once the cached file is deleted, the Report Designer is forced to rerun the queries in the report and create a new cache file. The cache file is in the same folder as the report definition file and has the same name with a .data on the end. For example, MyReport.rdl has a cache file located in the same folder called MyReport.rdl.data.

Remember, this data-caching process is only used by the Report Designer during report development. A different data-caching scheme operates on the report server after the report has been put into production. We discuss that caching scheme in Chapter 11.

The Employee List Report

Features Highlighted

• Implementing user-selectable grouping

• Implementing interactive sorting

• Using explicit page breaks

• Using a floating header

Business Need   The Galactic Delivery Services personnel department wants a flexible report for listing employee information. Rather than having a number of reports for each of their separate grouping and sorting needs, they want a single report where they can choose the grouping and sort order each time the report is run. The report should be able to group on job, hub, or city of residence. The report should be able to sort by employee number, last name, or hire date. Also, each new group should start on a new page. The header information should remain visible even when the user scrolls down the report page.

Task Overview

1. Create a New Report and a Dataset

2. Create the Report Layout

3. Add Interactive Sorting and a Floating Header

Employee List Report, Task 1: Create a New Report and a Dataset

1. Reopen the Chapter07 project if it was closed. Close the Employee Time Report if it is still open.

2. Right-click Reports in the Solution Explorer and select Add | New Item from the Context menu. The Add New Item dialog box appears.

3. Single-click GDSReport in the Templates area to select it. Change the name to EmployeeList and click Add.

4. Choose <New Dataset…> from the Dataset drop-down list. The Dataset dialog box appears.

5. Enter Employees for the name in the Dataset dialog box.

6. Galactic (shared) is selected for the data source by default. Click OK. You return to the Generic Query Designer in the Data tab.

7. Type the following in the SQL pane:

    SELECT Job.Description AS Job,      Hub.Description AS Hub,      Employee.EmployeeNumber,      FirstName,      LastName,      Address1,      City,      State,      ZipCode,      HireDate,      HighestLevelOfEducation,      UnionMembership FROM Employee INNER JOIN Assignment     ON Employee.EmployeeNumber = Assignment.EmployeeNumber INNER JOIN Job     ON Assignment.JobID = Job.JobID INNER JOIN Hub     ON Assignment.HubCode = Hub.HubCode

8. Run the query to make sure it is correct.

Task Notes   Notice no ORDER BY clause is in our SELECT statement. In most cases, this would cause a problem. Users like to have their information show up in something other than a random sort order. In this case it is fine, because we are sorting the data within the report itself according to what the user selects as report parameters.

Employee List Report, Task 2: Create the Report Layout

1. Select the Layout tab.

2. Place a text box onto the body of the report. Modify the following properties of this text box:

   Property	Value
   Font: FontSize	25pt
   Font: FontWeight	Bold
   Location: Left	0in
   Location: Top	0in
   Size: Width	2.875in
   Size: Height	0.5in
   Value	Employee List

3. Place a table onto the body of the report immediately below the text box you just added.

4. In the Datasets window, expand the Employees dataset. Drag the EmployeeNumber field into the leftmost cell in the detail row of the table.

5. Drag the FirstName field into the middle cell in the detail row of the table.

6. Drag the LastName field into the rightmost cell in the detail row of the table.

7. Right-click the gray rectangle above the rightmost column in the table and select Insert Column to the Right from the Context menu.

8. Drag the Address 1 field into the detail row of the newly created column.

9. Repeat Steps 7 and 8 for the City, State, ZipCode, HireDate, HighestLevelOfEducation, and UnionMembership fields.

10. Right-click the cell in the detail row containing the HireDate expression and select Properties from the Context menu. The Textbox Properties dialog box appears.

11. Select the Format tab.

12. Click the ellipsis button (…) next to the Format Code text box. The Choose Format dialog box appears.

13. Select Date from the list on the left. Select the bottom-most date format from the list on the right.

14. Click OK to exit the Choose Format dialog box. Note, the Visual Basic formatting code that corresponds to the date format you selected is now placed in the Format Code text box.

15. Click OK to exit the Textbox Properties dialog box.

16. Size each column appropriately. Use the Preview tab to check your work. Continue switching between the Layout tab and the Preview tab until you have the table columns sized correctly.

17. Drag the right edge of the report body layout area until it is just touching the right side of the table.

18. Click the gray square for the table header row. Modify the following property:

    Property	Value
    TextDecoration	Underline

19. Click in the report layout area. From the Main menu, select Report | Report Parameters. The Report Parameters dialog box appears.

20. Click Add.

21. Type GroupOrder for Name and Group By for Prompt.

22. Uncheck the Allow Blank Value option.

23. Fill in the Available Values grid as follows:

    Label	Value
    Job	Job
    Hub	Hub
    City	City

    Note 

    Use the arrow keys rather than the Tab key to move between the cells in the Available Values grid.

24. Select Non-queried for Default Values.

25. Type Job in the text box for Default Values.

26. Click OK to exit the Report Parameters dialog box.

27. Select the entire table and bring up the Table Properties dialog box.

28. Check the box labeled Repeat Header Rows on Each Page.

29. Select the Groups tab.

30. Click Add. The Grouping and Sorting Properties dialog box appears.

31. Select <Expression…> from the Expression drop-down list. The Edit Expression dialog box appears.

32. Type the following in the Expression area:

     = IIF( Parameters!GroupOrder.Value = "Job", Fields!Job.Value,        IIF(Parameters!GroupOrder.Value = "Hub", Fields!Hub.Value,              Fields!City.Value)) 

    Note 

    Use the Parameters and Fields entries in the Edit Expression dialog box to help build expressions, such as the previous one. Double-click the desired parameter or field to add it to the expression you are building.

33. Highlight the entire expression you just entered and press CTRL-C to copy this text.

34. Click OK to exit the Edit Expression dialog box.

35. Check the box labeled Page Break at End.

36. Uncheck the box labeled Include Group Footer.

37. Select the Sorting tab.

38. Select <Expression…> from the Expression drop-down list. The Edit Expression dialog box appears.

39. Delete the equals sign from the Expression area and press CTRL-V to paste the expression into the Expression area. This should be the same expression you entered in Step 32.

40. Click OK to exit the Edit Expression dialog box.

41. Leave the sort direction as Ascending. Click OK to exit the Grouping and Sorting Properties dialog box.

42. Select the Sorting tab.

43. In the Expression area, select =Fields!EmployeeNumber.Value from the drop-down list.

44. Leave the sort direction as Ascending. Click OK to exit the Table Properties dialog box.

45. Select the three leftmost cells in the Group 1 header row. Right-click these cells and select Merge Cells from the Context menu.

46. Right-click these cells again and select Expression from the Context menu.

47. Delete the equals sign from the Expression area and press CTRL-V to paste the expression into the Expression area. This should be the same expression you entered in Step 32.

48. Click OK to exit the Edit Expression dialog box.

49. Modify the following property of the merged cells:

    Property	Value
    Font: Weight	Bold

50. Your report layout should appear similar to Figure 7–10.

    
    Figure 7–10: The Employee List Report layout after Task 2

51. Select the Preview tab. Select a grouping, and then click View Report. Your report should appear similar to Figure 7–11. Experiment with changing the grouping. Remember to click View Report each time to refresh the report.

    
    Figure 7–11: The Employee List Report preview after Task 2

52. Click Save All in the toolbar.

Task Notes   In this report, the report parameter is used to control properties within the report rather than as a parameter to a SQL query. Because of this, we needed to create these report parameters manually, rather than having them created automatically from the dataset query. We also manually constructed a list of valid values and provided a default value. We were then able to use the values selected for this parameter to change the grouping and the group sorting of the table in the report.

We are able to change the grouping and group sorting of the table because of the IIF() function. This function has three parameters. The first parameter is a Boolean expression (in other words, an expression that results in either a true or false value). The second parameter is the value returned if the Boolean expression is true. The third parameter is the value returned if the Boolean expression is false.

Let’s take a look at one of our expressions using the IIF() function:

 = IIF( Parameters!GroupOrder.Value = "Job", Fields!Job.Value,        IIF(Parameters!GroupOrder.Value = "Hub", Fields!Hub.Value,              Fields!City.Value))

This expression uses two IIF() functions, one nested inside the other. The first parameter of the outer IIF() function is

 Parameters!GroupOrder.Value = "Job"

If Job is selected for the grouping, the value of the second parameter is returned by the function. In this case, the second parameter is

 Fields!Job.Value

Therefore, if Job is selected for the grouping, the value of the Job field is used.

If Job is not selected for the grouping, the value of the third parameter is returned. The value of this third parameter is another complete IIF() function:

 IIF(Parameters!GroupOrder.Value = "Hub", Fields!Hub.Value,              Fields!City.Value)

In this second IIF() function, if Hub is selected for the grouping, the second parameter of this IIF() function is returned. Here, the second parameter is

 Fields!Hub.Value

Therefore, if Hub is selected for the grouping, the value of the Hub field is used.

Finally, if Hub is not selected for the grouping, the value of the third parameter of this IIF() function is returned. Here, the third parameter is

 Fields!City.Value

Therefore, if Hub is not selected for the grouping, the value of the City field is used.

We used the same expression for both the grouping and the group sorting. The group sorting property sorts the groups themselves, so they come out in the proper order. We also used the Sorting tab in the Table Properties dialog box. This provided a default sort order for the rows within each group.

In many cases, a report needs to start each new group on a new page. We used the Page Break at End option in the grouping properties to force the report to start a new page after each grouping. Page break options can be set before or after new groupings. Page breaks can also be set before or after a report item. For instance, you can force a page break before the beginning of a table or after the end of a table.

Employee List Report, Task 3: Add Interactive Sorting and a Floating Header

1. Select the Layout tab.

2. Select the entire table and bring up the Table Properties dialog box.

3. Check the box labeled Header Should Remain Visible While Scrolling.

4. Click OK to exit the Table Properties dialog box.

5. Click the gray square for the table header row. Modify the following property:

   Property	Value
   BackgroundColor	White

6. Right-click the Employee Number text box in the table header row and select Properties from the Context menu. The Textbox Properties dialog box appears.

7. Select the Interactive Sort tab.

8. Check the box labeled Add an Interactive Sort Action to This Textbox.

9. Select =Fields!EmployeeNumber.Value from the Sort Expression drop-down list.

10. Click OK to exit the Textbox Properties dialog box.

11. Right-click the Last Name text box in the table header row and select Properties from the Context menu. The Textbox Properties dialog box appears.

12. Select the Interactive Sort tab.

13. Check the box labeled Add an Interactive Sort Action to This Textbox.

14. Click the Expression button (the button with fx on it) next to the Sort Expression text box. The Edit Expression dialog box appears.

15. Type the following in the Expression area:

     =Fields!LastName.Value & Fields!FirstName.Value

16. Click OK to exit the Edit Expression dialog box.

17. Click OK to exit the Textbox Properties dialog box.

18. Right-click the Hire Date text box in the table header row and select Properties from the Context menu. The Textbox Properties dialog box appears.

19. Select the Interactive Sort tab.

20. Check the box labeled Add an Interactive Sort Action to This Textbox.

21. Select =Fields!HireDate.Value from the Sort Expression drop-down list.

22. Click OK to exit the Textbox Properties dialog box.

23. Select the Preview tab.

24. Click the Interactive Sort button next to the Last Name column as shown in Figure 7–12.

    
    Figure 7–12: The Employee List Report with interactive sorting

25. Page through the report and note the rows are now sorted by last name in ascending order.

26. Click the Interactive Sort button next to the Last Name column again. You return to the first page of the report. Again, page through the report and note that the rows are now sorted by last name in descending order.

27. Click the Interactive Sort button next to the Hire Date column heading. You return to the first page of the report.

28. Page through the report once more. The rows are now sorted by hire date in ascending order.

29. Go to Page 5 of the report.

30. Scroll the page up and down, and notice the table headers always remain visible at the top of the page.

31. Click Save All in the toolbar.

Task Notes   The interactive sort feature enables the user viewing the report to choose the sort order they would like to see. This could also be done using a report parameter passed as part of the query that creates the dataset. This scheme requires the query to be rerun every time the sort order is changed. Interactive sorting, on the other hand, redisplays the report in the newly selected sort order without rerunning the dataset query. The sorting is all done within the report renderer using the data already collected from the data source.

In our example, the Last Name text box in the table header row has interactive sort enabled. Therefore, when the Interactive Sort button is clicked for this item, the content of the entire table is resorted. In our example, the column header text boxes we used for interactive sorting were in the table header. Therefore, their scope was the entire table. Clicking an interactive sort button resorts the rows within all of the groups in the table. When interactive sorting is applied to column headings in a group header, you can choose whether to apply the sort to all of the groups in the table or to the current group only.

In addition to choosing the scope affected by the interactive sort, you can also choose the scope to be used when evaluating the sort expression. In most cases, you want the sort expression evaluated in the scope of the detail rows, because the detail rows are being sorted. Occasionally, you may want to evaluate the sort expression within the scope of a particular grouping, if the grouping value figures into the sort expression. Note that interactive sorting only works when applied to a text box that is serving as a group header or a data region header.

The floating header does, indeed, seem to float over the columns of the report as you scroll down the page. For this reason, the background of a floating header row should be set to something other than transparent. If this is not done, the column data shows right through the header, making it rather difficult to read.

The Employee Mailing Labels Report

Features Highlighted

• Enable multiple columns

• Put information from the database into the report header

Business Need   The Galactic Delivery Services personnel department has a new version of the employee manual. The personnel department needs mailing labels to send the new manual out to each employee. The mailing labels are to be printed on a 2 1/2-inches wide and 1-inch high label. The label sheet has three labels across the sheet and ten labels down the sheet with no margin between each label.

The labels should be sorted in ZIP code, and then last name order. It would also be helpful if the total number of labels is printed in the top margin of the first page printed. Finally, a sequence number should be printed in the lower-right corner of each label.

Task Overview

1. Create the Mailing Label Content

2. Add the Report Header and Multiple Columns

Employee Mailing Labels Report, Task 1: Create the Mailing Label Content

1. Reopen the Chapter07 project if it has been closed. Close the Employee List Report if it is still open.

2. Right-click Reports in the Solution Explorer and select Add | New Item from the Context menu. The Add New Item dialog box appears.

3. Single-click Report in the Templates area to select it. Change the name to EmployeeMailingLabels and click Add.

4. Choose <New Dataset…> from the Dataset drop-down list. The Dataset dialog box appears.

5. Enter Employees for the name in the Dataset dialog box.

6. Galactic (shared) is selected for the data source by default. Click OK. You return to the Generic Query Designer in the Data tab.

7. Type the following in the SQL pane:

    SELECT FirstName + ' ' + LastName AS Name,      Address1,      City + ', ' + State + ' ' + ZipCode AS CSZ FROM Employee WHERE TerminationDate IS NULL ORDER BY ZipCode, LastName, FirstName 

8. Run the query to make sure it is correct.

9. Choose <New Dataset…> from the Dataset drop-down list. The Dataset dialog box appears.

10. Enter EmployeeCount for the name in the Dataset dialog box.

11. Galactic is selected for the data source by default. Click OK. You return to the Generic Query Designer in the Data tab.

12. Type the following in the SQL pane:

     SELECT 'Total Employees: ' + CONVERT(varchar(4), COUNT(*))              AS EmpCount FROM Employee WHERE TerminationDate IS NULL

13. Run the query to make sure it is correct.

14. Select the Layout tab.

15. Place a List onto the body of the report. Modify the following properties of the list:

    Property	Value
    Location: Left	0in
    Location: Top	0in
    Size:Width	2.5in
    Size:Height	1in

16. Select the Datasets window and expand the Employees dataset. Drag the Name field onto the list and set the following properties:

    Property	Value
    Location: Left	0in
    Location: Top	0in
    Size:Width	2.25in
    Size:Height	0.25in

17. Drag the Address 1 field onto the list and set the following properties:

    Property	Value
    Location: Left	0in
    Location: Top	0.25in
    Size:Width	2.25in
    Size:Height	0.25in

18. Drag the CSZ field onto the list and set the following properties:

    Property	Value
    Location: Left	0in
    Location: Top	0.5in
    Size:Width	2.25in
    Size:Height	0.25in

19. Drag a text box onto the list, and set the following properties of the text box:

    Property	Value
    Font: FontSize	8 pt
    Location: Left	1.125in
    Location: Top	0.75in
    Size:Width	1.375in
    Size:Height	0.25in
    TextAlign	Right
    VerticalAlign	Bottom

20. Right-click the text box you just added and select Expression from the Context menu. The Edit Expression dialog box appears.

21. Expand the Common Functions item in the list on the left and select Miscellaneous. The list in the center contains the miscellaneous functions available in Reporting Services.

22. Double-click RowNumber in the center list to add the RowNumber aggregate to the expression.

23. Type ( after RowNumber in the expression. The parameter information for the RowNumber aggregate function appears as shown in Figure 7–13.

    
    Figure 7–13: The Edit Expression dialog box with parameter information

24. The parameter information tells you there are two different forms of the RowNumber aggregate function. Click the up and down arrows to switch between these two definitions. (The down arrow is indicated by the mouse pointer in Figure 7–13.) The first form has one parameter that specifies the scope of the aggregation. The second form has two parameters: one for the scope and a second for the name of a group to count.

25. In this report, you use the first form of the RowNumber aggregate function. To complete the expression, type “list1”) after the (.

26. Click OK to exit the Edit Expression dialog box.

27. Adjust the report body so it is exactly the same size as the list report item.

28. Select the Preview tab. Your report should appear similar to Figure 7–14.

    
    Figure 7–14: The Employee Mailing Labels Report preview after Task 1

29. Click Save All in the toolbar.

Task Notes   The Edit Expression dialog box provides assistance in building expressions. Earlier, we talked about the syntax checking done as you type an expression and the jagged red line that indicates an error. The Edit Expression dialog box enables you to add global variables, parameters, fields, and even common functions to an expression with a double-click. Finally, we saw in Steps 23 and 24 how the Edit Expression dialog box provides information on the parameters expected by a function.

The business requirements call for a sequence number on each label. To do this, we look to the functions available in Reporting Services. The RowNumber function provides just what is needed.

In the next section, you finalize the formatting of the mailing labels. One of the things the business requirements asked for was the count of the number of employees at the top of the first page of labels. The EmployeeCount dataset returns the employee count, so we have the information we need. The only place we can put this employee count without messing up the label layout is in the page header. The problem is, the page header cannot contain information from datasets. In the next task, you employ a little sleight-of-hand using a report parameter to get the job done.

Employee Mailing Labels Report, Task 2: Add the Report Header and Multiple Columns

1. Click the Layout tab.

2. From the Main menu, select Report | Report Parameters. The Report Parameters dialog box appears.

3. Click Add to add a report parameter.

4. Type EmployeeCount for Name.

5. Check the box labeled Internal.

6. Change Default Values to From Query. Select EmployeeCount from the Dataset drop-down list. Select EmpCount from the Value Field drop-down list.

7. Click OK to exit the Report Parameters dialog box.

8. From the Main menu, select Report | Page Header.

9. Place a text box on the left side of the page header. Make the text box as wide as the text box holding the Name field.

10. Right-click the text box in the page header and select Properties from the Context menu. The Textbox Properties dialog box appears.

11. Click the Expression button (the button with fx on it) next to the Value text box. The Edit Expression dialog box appears.

12. Select Parameters in the list on the left.

13. Double-click EmployeeCount in the list on the right to add it to the expression.

14. Click OK to exit the Edit Expression dialog box and return to the Textbox Properties dialog box.

15. Select the Visibility tab.

16. Change Initial Visibility from Visible to Expression.

17. Click the Expression button for Initial Visibility.

18. Expand Common Functions and select Program Flow in the list on the left.

19. Double-click IIf in the center list.

20. Type ( after IIf in the expression.

21. Click Globals in the list on the left.

22. Double-click PageNumber in the center list.

23. Type > 1, true, false) at the end of the expression.

24. Click OK to exit the Edit Expression dialog box.

25. Click OK to exit the Textbox Properties.

26. From the Main menu, select Report | Report Properties. The Report Properties dialog box appears.

27. Select the Layout tab.

28. Change Columns to 3.

29. Type 0in for Spacing.

30. Type 0.5in for each of the following: Left Margin, Right Margin, and Bottom Margin.

31. Type 0.25in for the Top Margin.

32. Click OK to exit the Report Properties dialog box. Your report layout should appear similar to Figure 7–15.

    
    Figure 7–15: The Employee Moiling Labels Report layout after Task 2

33. Select the Preview tab, and then click the Print Layout toolbar button. The report appears similar to Figure 7–16.

    
    Figure 7–16: The Employee Mailing Labels Report preview after Task 2

34. Click Save All in the toolbar.

Task Notes   You cannot place information from a dataset in the page header... directly! You can, however, put the value of a parameter in the page header. You can also use a dataset to specify the default value of a parameter. Therefore, using this two-step approach, we can put the count of employees from the EmployeeCount dataset into the page header.

When we created the EmployeeCount parameter, we specified it was an internal parameter. This means this parameter is only used inside the report. The user should never be prompted for this value. Furthermore, the user should not be able to specify a value for this parameter through any other means either. Because of this restricted access, the parameter must have a default value specified by either a query or a nonqueried (constant) source. This is the only way to set a value for an internal parameter.

The business requirements also specify that the employee count should only be displayed on the first page. The page header has properties that hide it on the first page or the last page. There is no option to have it display only on the first page. To accomplish this, you created an expression to control the visibility of the text box that contains the employee count. If the page number is less than or equal to 1, the employee count is visible. If the page number is greater than 1, the employee count is hidden.

Finally, you need to set up the report layout to match the label sheet. This is done on the Layout tab of the Report Properties dialog box. The Columns property, of course, specifies the number of columns in the report. The Spacing property specifies the amount of space in between each column. In addition, the margins need to be set appropriately. Because the labels are three across with 2 1/2 inches per label, the labels take up 7 1/2 inches. Therefore, the left and right margins must be set to 1/2 inch each to get a total of 8 1/2 inches in width. A similar set of calculations tells us that the top and bottom margins must also be 1/2 inch each, but 1/4 inch must be subtracted from the top margin to accommodate the page header.

The Overtime Report

Features Highlighted

• Implementing cascading parameters

• Using SQL stored procedures

• Using table filters

• Using the NoRows property

Business Need   The Galactic Delivery Services personnel department needs to monitor the amount of overtime put in at each of its repair and distribution hubs to determine when additional personnel must be hired. The personnel department needs a report that lists the employees with over 45 hours worked in a given week at a given hub. The report should have two sections. The first section should list employees with more than 45 hours and less than 55 hours worked for the selected week. The second section should list employees with more than 55 hours worked for the selected week.

The user should be able to select a workweek from a drop-down list, and then see a second drop-down list showing the hubs that have one or more employees with more than 45 hours for the selected week. The user selects a hub from this second list, and then sees the report for that hub.

Two stored procedures in the Galactic database should be used for retrieving data. The stp_HubsOver45 stored procedure returns a list of hubs with one or more employees who have over 45 hours worked for the selected week. The stp_EmployeesOver45 stored procedure returns a list of employees who have over 45 hours worked for the selected week at the selected hub. We discuss stored procedures in the task notes.

Task Overview

1. Create a New Report and Three Datasets

2. Create the Report Layout

Overtime Report, Task 1: Create a New Report and Three Datasets

1. Reopen the Chapter07 project if it was closed. Close the Employee Mailing Labels Report if it is open.

2. Right-click Reports in the Solution Explorer and select Add | New Item from the Context menu. The Add New Item dialog box appears.

3. Single-click GDSReport in the Templates area to select it. Change the name to Overtime and click Add.

4. Choose <New Dataset…> from the Dataset drop-down list. The Dataset dialog box appears.

5. Enter Weeks for the name in the Dataset dialog box.

6. Galactic (shared) is selected for the data source by default. Click OK. You return to the Generic Query Designer in the Data tab.

7. Type the following in the SQL pane:

    SELECT DISTINCT CONVERT(char(4) , DATEPART(yy,WorkDate) ) + +   RIGHT('0'+CONVERT(varchar(2), DATEPART(wk,WorkDate)),2) as Week FROM TimeEntry ORDER BY Week 

8. Run the query to make sure it is correct.

9. Choose <New Dataset…> from the Dataset drop-down list. The Dataset dialog box appears.

10. Enter HubsOver45 for the name in the Dataset dialog box.

11. Galactic is selected for the data source by default.

12. Select StoredProcedure from the Command Type drop-down list.

13. Click OK. You return to the stored procedure version of the Generic Query Builder in the Data tab.

14. Select stp_HubsOver45 from the Stored Procedure drop-down list in the Data tab toolbar.

15. Run the stored procedure to make sure it is functioning properly. When you run the stored procedure, the Report Designer determines what parameters are required. Type 2006–15 for the @Week parameter in the Define Query Parameters dialog box and click OK.

16. Choose <New Dataset…> from the Dataset drop-down list. The Dataset dialog box appears.

17. Enter EmployeesOver45 for the name in the Dataset dialog box.

18. Galactic is selected for the data source by default.

19. Select StoredProcedure from the Command Type drop-down list.

20. Type stp_EmployeesOver45 for the query string.

    Note 

    You can select the stored procedure from the Stored Procedure drop-down list in the Data tab toolbar or you can type in the name of the stored procedure yourself; whichever is most convenient.

21. Click OK. You return to the stored procedure version of the Generic Query Designer in the Data tab.

22. Run the stored procedure to make sure it is functioning properly. Type 2006–15 for the @Week parameter and BLNR for the @HubCode parameter in the Define Query Parameters dialog box, and then click OK.

Task Notes   For two of our three datasets, we used stored procedures rather than queries. A stored procedure is a query or a set of queries given a name and stored in the database itself. You can think of a stored procedure as a data-manipulation program created and kept right inside the database.

Stored procedures have several advantages over queries:

• Speed   A certain amount of preprocessing must be done on any query before it can be run in the database. Stored procedures are preprocessed when they are created, and this preprocessing information is saved with them. This means when you execute a stored procedure, you do not need to wait for the preprocessing. The result is faster execution time.

• Simplicity   A developer or database administrator can create a stored procedure that uses a number of intricate queries. When you execute the stored procedure, you do not need to understand, or even see, this complexity. All you need to do is execute the stored procedure to get the result set you need.

• Security   When you query a set of tables, you must be given rights to see any and all data in each of the tables. However, when a stored procedure is used, you only need rights to execute the stored procedure. You do not need rights to any of the tables being queried by the stored procedure. The stored procedure can then control which rows and which columns can be seen by each user.

• Reusability   A single stored procedure can be used by a number of reports. Therefore, complex queries do not have to be created over and over again when a number of reports need to use the same data.

• Maintainability   When changes are made to the database structure, the developer or database administrator can make the corresponding changes in the stored procedure, so the stored procedure continues to return the same result set. Without stored procedures, a change in the database structure could result in a number of reports needing to be edited.

For these reasons, it is often advantageous to use stored procedures rather than queries for your datasets.

Note 

Querying against database views has a number of the same benefits as stored procedures and is also a good choice as the source for your datasets. Because querying views is much the same as querying tables (they present fields to the Query Builder just as tables do), we will not spend time discussing views.

When you are using a stored procedure for your dataset, all you need to do is set Command Type to StoredProcedure and enter the name of the stored procedure. The Report Designer can figure out the parameters required by the stored procedure and add them to the report. Can't get much simpler than that!

Overtime Report, Task 2: Create the Report Layout

1. Select the Layout tab.

2. From the Main menu, select Report | Report Parameters. The Report Parameters dialog box appears.

3. With Week selected in the Parameters list, change Available Values to From Query. The Dataset drop-down list should be set to Weeks. Select Week from the Value Field drop-down list. Select Week from the Label Field drop-down list.

4. Select HubCode from the Parameters list.

5. Change Prompt to Hub.

6. Change Available Values to From Query.

7. Select HubsOver45 from the Dataset drop-down list. Select HubCode from the Value Field drop-down list. Select Hub from the Label Field drop-down list.

8. Click OK to exit the Report Parameters dialog box.

9. Place a text box onto the body of the report. Modify the following properties of this text box:

   Property	Value
   Font: FontSize	25pt
   Font: FontWeight	Bold
   Location: Left	0in
   Location: Top	0in
   Size: Width	2in
   Size: Height	0.5in
   Value	Overtime

10. Place a second text box onto the body of the report. Modify the following properties of this text box:

    Property	Value
    Font: FontSize	16pt
    Location: Left	0in
    Location: Top	0.5in
    Size: Width	5.25in
    Size: Height	0.375in

11. Right-click this text box and select Expression from the Context menu.

12. Type the following in the Expression area:

     = "Week: " & Parameters!Week.Value & "      Hub: " & Parameters!HubCode.Value 

13. Click OK to exit the Edit Expression dialog box.

14. Place a third text box onto the body of the report. Modify the following properties of this text box:

    Property	Value
    Font: FontSize	16pt
    Font: FontWeight	Bold
    Location: Left	0in
    Location: Top	1.125in
    Size: Width	5.25in
    Size: Height	0.375in
    Value	Employees with 45 to 55 hours for this week

15. Place a table onto the body of the report immediately below the third text box.

16. Select the Dataset window and expand the EmployeesOver45 dataset.

17. Drag the EmployeeNumber field into the leftmost cell in the detail row of the table.

18. Drag the FirstName field into the center cell in the detail row of the table.

19. Drag the LastName field into the rightmost cell in the detail row of the table.

20. Right-click the gray bar above the rightmost column and select Insert Column to the Right from the Context menu.

21. Drag the Hours Worked field into the cell in the detail row in the column you just added.

22. Right-click the gray square to the left of the footer row. Select Table Footer from the Context menu to toggle off the table footer.

23. Select the table header row. Modify the following property:

    Property	Value
    TextDecoration	Underline

24. Select the leftmost table column. Modify the following property:

    Property	Value
    TextAlign	Left

    Note 

    Remember, you can use the items in the Report Formatting toolbar to do things such as turning on underlining and changing the text alignment.

25. Select the entire table and bring up the Table Properties dialog box.

26. Select the Filters tab.

27. Type =CStr(Fields! Hours Worked. Value) for the expression.

28. From the Operat... drop-down list, select <=.

29. Type 55.00 for the value.

30. Click OK to exit the Table Properties dialog box.

31. Modify the following property for the table using the Properties window:

    Property	Value
    NoRows	No Employees

32. Select both the table and the text box with the string Employees with 45 to 55 Hours for This Week. Press CTRL-C to copy these two report items. We are going to paste a copy of these two items and use them to create the layout for the Employees over 55 Hours.

33. Drag the bar between the report body and the page footer, so the report body is larger.

34. Press CTRL-V to paste a copy of the two report items. Drag the two new items so they are below the originals.

35. Select the new text box by itself. Change the value of the text box to Employees with Over 55 Hours for This Week. (You can edit it right in the text box itself.)

36. Select the new table and bring up the Table Properties dialog box.

37. Select the Filters tab.

38. From the Operat... drop-down list, select >.

39. Click OK to exit the Table Properties dialog box. Your report layout should appear similar to Figure 7–17.

    
    Figure 7–17: The Overtime Report layout

40. Select the Preview tab.

41. Notice the Week drop-down list is enabled, but the Hub drop-down list is disabled. Select 2006-15 from the Week drop-down list.

42. Once a week is selected, the Hub drop-down list is enabled. Select Borlaron Repair Base from the Hub drop-down list. Click the View Report button. Your report should appear similar to Figure 7–18.

    
    Figure 7–18: The Overtime Report preview

43. Select 2006-10 from the Week drop-down list. Borlaron Repair Base is still selected in the Hub drop-down list. Click the View Report button. Note the text under the Employees with Over 55 Hours for This Week heading.

44. Click Save All in the toolbar.

Task Notes   In this report, we used the same dataset to populate two tables. We got different information in the two tables by applying different filters on each table. The filter for the upper table on the report says we only want records in this table where the number of hours worked is less than or equal to 55. The filter for the lower table on the report says we only want records in this table where the number of hours worked is greater than 55. In this way, we can divide the data in the dataset to fulfill the business requirements of the report.

You may have noted that we used the Visual Basic function CStr() to convert the hours worked to a string data type in our filter expressions. This is due to a quirk (some might call it a bug) in the operation of the filter expressions. Because of this quirk, filters deal much better with strings than they do with other data types. To get around this, we simply convert our numbers to strings, and everything works fine.

In addition to what you saw here, filters can be applied to data in other locations as well. A dataset can have a filter applied to it after it has been selected from the database. Individual groups within a table, matrix, or chart can also utilize filters.

Filters work well in situations like the one in this report where we want to use one dataset to provide a slightly different set of records to multiple data regions. They can also be useful for taking data from a stored procedure that provides almost, but not quite, the result set you need. It is usually best, however, to have your filtering done by your select query or stored procedure, rather than by the report. The reason is, in most cases, it is considerably faster and more efficient if the database does the filtering as it executes the query or stored procedure. It does not make sense to have your query select 1,000 records from the database if your report is going to filter out all but ten of these records. Filters are a good tool to have; just remember to use them wisely.

In the Overtime Report, we used two drop-down lists to let the user select the parameters for our report. The Week drop-down list enables the user to select the week of the year for which the report should be run. This drop-down list is populated by the Week dataset. The Hub drop-down list lets the user select the hub for which the report should be run. This drop-down list is populated by the HubsOver45 dataset. The HubsOver45 dataset requires a value from the Week drop-down list before it can return a list of the hubs with employees working over 45 hours for that week. In this way, the data that populates the Hub drop-down list is dependent on the value selected in the Week drop-down list.

Reporting Services is smart enough to recognize this dependency and act accordingly. If no value is selected in the Week drop-down list, the Hub drop-down list cannot be populated, so it is disabled. Every time the selected value in the Week drop-down list changes, the Hub drop-down list is repopulated.

Finally, in this report we used the NoRows property of each of the tables. This property enables you to define a string that is output when there are no rows to populate the table. When the filter on the lower table in the report filters out all the rows in the dataset, the content of the NoRows property is displayed. This is more helpful to the user than simply having a blank space where a table should be. The NoRows property is available on any of the data region report items.