Speaking in Code


One of the features of Reporting Services that gives it a tremendous amount of power and flexibility is its ability to speak in code—Visual Basic .NET code, that is. Valid Visual Basic .NET expressions can be used to control many of the properties of report items. It can even be used to control the query you are using to create your dataset.

For more complex tasks, you can embed whole Visual Basic .NET functions in your report. If that isn’t enough, you can access methods from .NET assemblies. These assemblies are not limited to Visual Basic .NET. They can be written in any .NET language, such as C#.

Let’s write some

-.-. --- -.. .

and have some

..-. ..- -. 
Note

For those of you who may not be familiar with it, the previous sentence contains two words in Morse Code. If you want to know what it says, do what I did: Look it up on the Internet.

The Delivery Status Report

Features Highlighted

  • Using Visual Basic .NET expressions to control properties

  • Using multiline headers and footers

  • Specifying scope in aggregate functions

Business Need The customer service department at Galactic Delivery Services would like a report to check on the status of deliveries for a customer. The customer service representative should be able to select a customer and a year and see all the deliveries for that customer in that year. The hubs each package went through as it was in transit should be listed.

The status for packages that have been delivered should show up in green. The status for packages still en route should be blue. The status for packages that have been lost should be red. In case of a problem, the name and e-mail address of the person to be contacted at that customer site should appear below the entry for each lost package.

Task Overview

  1. Create the Chapter08 Project, a Shared Data Source, a New Report, and Two Datasets

  2. Set Up the Report Parameters and Place the Titles on the Report Layout

  3. Add a Table to the Report

  4. Add the Expressions

Delivery Status Report, Task 1: Create the Chapter08 Project, a Shared Data Source, a New Report, and Two Datasets

  1. Create a new Reporting Services project called Chapter08 in the MSSQLRS folder.

  2. Create a shared data source called Galactic for the Galactic database.

  3. Create a new report called DeliveryStatus using the GDSReport template.

  4. Create a new dataset called DeliveryStatus that calls the stp_DeliveryStatus stored procedure.

  5. Run the stored procedure using 263722 for @CustomerNumber and 2003 for @Year.

  6. Create a second dataset called Customers that uses the following query:

    SELECT CustomerNumber, Name FROM Customer ORDER BY Name

Task Notes You probably noticed that the instructions are a bit sketchy here. Now that you have reached the level of advanced report authoring, you can handle these basic tasks on your own. If you have any trouble with these steps, refer to the previous chapters for a refresher.

Delivery Status Report, Task 2: Set Up the Report Parameters and Place the Titles on the Report Layout

  1. Select the Layout tab.

  2. Use the main menu to open the Report Parameters dialog box.

  3. Configure the report parameters as follows:

    Property

    Value

    For the CustomerNumber parameter:

    Prompt

    Customer

    Available values

    From query

    Dataset

    Customers

    Value field

    CustomerNumber

    Label field

    Name

    For the Year parameter:

    Available values

    (Enter the values from the following table)

    Default values

    Non-queried

    Default values text area

    2003

  4. Set the Available Values property for the Year parameter as follows:

    Label

    Value

    2001

    2001

    2002

    2002

    2003

    2003

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

  6. Place a 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

    0in

    Size: Width

    3.5in

    Size: Height

    0.375in

    Value

    =“Delivery Status for ” & Parameters!Year.Value

  7. Place a list onto the body of the report immediately below the text box. Modify the following properties of this list:

    Property

    Value

    Location: Left

    0in

    Location: Top

    0.375in

    Size: Width

    5.25in

    Size: Height

    0.5in

  8. Right-click the list and select Properties from the context menu. The List Properties dialog box will appear.

  9. Select the Filters tab.

  10. Type =Fields!CustomerNumber.Value for Expression.

  11. From the Operat… drop-down list, select “=”.

  12. Type =Parameters!CustomerNumber.Value for Value.

  13. Click OK to exit the List Properties dialog box.

  14. Select the Customers dataset in the Fields window and drag the Name field onto the list. Modify the following properties of the text box that results:

    Property

    Value

    Font: FontSize

    16pt

    Font: FontWeight

    Bold

    Location: Left

    0in

    Location: Top

    0in

    Size: Width

    5.125in

    Size: Height

    0.375in

Task Notes We have two parameters for this report. The CustomerNumber parameter is selected from a drop-down list that is created by a dataset. The customer names are displayed in the drop-down list because Name was chosen as the Label field. However, the customer number is the value assigned to this parameter because CustomerNumber is chosen as the Value field. The Year parameter is selected from a drop-down list that is created by a static list of values that we entered. The Label and Value are the same for each entry in this list.

The items we have placed on the report thus far were put there to provide a heading for the report and to indicate which parameters were selected to create the report. This is pretty straightforward for the Year parameter. All we need is a text box that displays the value of this parameter, with a little explanatory text thrown in for good measure.

The CustomerNumber parameter presents a bit of a problem, though. The parameter contains the customer number of the selected customer. However, it will make more sense to the user if the customer’s name is displayed at the top of the report. To accomplish this, we placed a list data region at the top of the report. When we drag the Name field from the Customers dataset onto the list, the list is immediately linked to the Customers dataset. (The DataSetName property of the list is set to Customers.)

If nothing else were done, the list would display the names of all the customers in the Customers dataset at the top of the report. Instead, we set the filter property of the list so it will only display the name of the customer whose customer number matches the customer number selected in the CustomerNumber parameter.

This represents a good application of a filter. In this case, we could not have replaced the filter with a WHERE clause in SQL, because we needed all the customers in the dataset to populate the drop-down list. By using the filter, we were able to reuse the Customers dataset to do two things: to populate the drop-down list for the CustomerNumber parameter and provide us with the name that goes with the selected customer number.

Delivery Status Report, Task 3: Add a Table to the Report

  1. Add a table to the body of the report immediately below the list.

    Note

    Be sure the table ends up below the list and not in the list.

  2. Select the DeliveryStatus dataset in the Fields window and drag the Hub field onto the leftmost cell in the details row of the table.

  3. Drag the TimeIn and TimeOut fields onto each of the two remaining cells in the details row of the table.

  4. Right-click the gray square to the left of the details row and select Insert Group from the context menu. The Grouping and Sorting Properties dialog box will appear.

  5. Select “=Fields!DeliveryNumber.Value” from the Expression drop-down list. We are now grouping the information in the table by the values in the DeliveryNumber field.

  6. Select the Sorting tab.

  7. Select “=Fields!DeliveryNumber.Value” from the Expression drop-down list.

  8. Click OK to exit the Grouping and Sorting Properties dialog box.

  9. We need to move the labels in the table header row to the group header row. Select the text box that contains the word “Hub.” Do this by clicking once in this text box. If you can see a text-editing cursor blinking in this cell, you clicked too many times. If you see the blinking cursor, click elsewhere, then try again.

  10. Press CTRL-X to cut the text box from this table header cell. Click in the group header cell immediately below it and press CTRL-V to paste the text box there.

  11. Repeat this for the text boxes containing “Time In” and “Time Out.”

  12. Right-click any gray square to the left of the table and select Table Header from the context menu. This will turn off the table header option for this table. The table header row will disappear. Do the same for the table footer.

  13. Right-click the gray square to the left of the group header row and select Insert Row Above from the context menu. An additional group header row will appear. This is not a new grouping but rather an additional row for the current grouping.

  14. Drag the ServiceType field to the leftmost cell in the new group header row.

  15. Drag the StatusName field to the next cell in the new group header row.

  16. Right-click the gray square to the left of the new group header row and select Insert Row Above from the context menu. Another new group header row will appear.

  17. Right-click the gray rectangle at the top of the first column in the table and select Insert Column to the Left from the context menu. A new column is added to the table.

  18. Drag the DeliveryNumber field to the leftmost cell in the new group header row. Modify the following property of the text box in this cell:

    Property

    Value

    TextAlign

    Left

  19. Double-click the next cell in the new group header row and type Pickup:.

  20. Drag the PickupPlanet field to the next cell in the new group header row.

  21. Drag the PickupDateTime field to the rightmost cell in the new group header row.

  22. Double-click in the group footer cell below “=Fields!Hub.Value” and type Delivery:.

  23. Drag the DeliveryPlanet field to the next cell in the group footer row.

  24. Drag the DeliveryDateTime field to the rightmost cell in the group footer row.

  25. Right-click the gray square to the left of the group footer row and select Insert Row Below from the context menu. A new group footer row will appear.

  26. Double-click in the group footer cell below “Delivery” and type Problem Contact:.

  27. Drag the ProblemContact field to the next cell in the new group footer row.

  28. Drag the ProblemEMail field to the rightmost cell in the new group footer row.

  29. Right-click the gray square to the left of the new group footer row and select Insert Row Below from the context menu. A new group footer row will appear. This row will be left blank.

  30. Click in the leftmost cell of the top group header row and hold down the mouse button. Drag the mouse to the rightmost cell of the bottom group header row and release the mouse button. You have selected all the cells in the group header.

  31. Modify the following property for these cells:

    Property

    Value

    Font: FontWeight

    Bold

  32. Repeat steps 30 and 31 for all the cells in the group footer. Your report layout should appear similar to Figure 8-1.

    click to expand
    Figure 8-1: The Delivery Status Report layout after Task 3

  33. Select the Preview tab. Select “Bolimite, Mfg” from the Customer drop-down list. Select “2003” from the Year drop-down list. Click View Report. Your report should appear similar to Figure 8-2.

    click to expand
    Figure 8-2: The Delivery Status Report preview after Task 3

Task Notes If you are observant, you will notice that the expressions created for all but one of the fields placed in the group header and footer rows include the First() aggregate function. Only the expression for the DeliveryNumber field does not include this function. This aggregate function must be used because there are many records in each group. Somehow, we need to specify which of these records will be used to supply the values for these fields. By default, the first record in the group is used by including the First() aggregate function with each field expression. However, you could replace the First() aggregate function with any of the other Reporting Services aggregate functions if you desired.

Why is there no aggregate function included in the DeliveryNumber field expression? In step 5, we selected the DeliveryNumber field as the grouping field. Because the table is grouped on this field, the value of the DeliveryNumber field must be the same for each record in the group. In short, it does not really make a difference which record the DeliveryNumber field comes from because it will be the same value for each record in the group.

Note

As it turns out, each of the fields we placed in the group header and footer is directly related to the DeliveryNumber field. Therefore, all these fields have the same value for all the records in the group. Visual Studio cannot determine this at the time you are designing the report, so it puts the aggregate functions in these expressions.

We were able to add rows to both the group header and footer. This allowed us to create more complex group header and footer layouts. In the same fashion, you can add rows to the table header, table footer, or detail line, as needed.

We now have the proper layout for our report, but we do not have the proper behavior of some of the report items. The delivery status is supposed to appear in color. The problem contact information is only supposed to be displayed with lost deliveries. Some additional formatting lines would also make the report more readable. All of this will be accomplished in the next task with the aid of expressions.

Delivery Status Report, Task 4: Add the Expressions

  1. Select the Layout tab.

  2. Enter the following expression for the Color property of the cell containing the StatusName field:

    = IIF(Fields!StatusName.Value = "Delivered", "Green",        IIF(Fields!StatusName.Value = "In Route", "Blue", "Red"))

    Note

    When entering each of the expressions, you will probably want to select <Expression…> from the drop-down list and enter this expression in the Edit Expression dialog box. Also, remember that you can get the expressions for fields and parameters from the Fields area on the left side of the Edit Expression dialog box. Remember, expressions involving the Globals, Parameters, and Fields collections are case sensitive.

  3. Click the gray square to the left of the top group header row, so the entire row is selected.

  4. Enter the following expression for the BorderStyle: Top property:

    = IIF(Fields!DeliveryNumber.Value =    FIRST(Fields!DeliveryNumber.Value, "DeliveryStatus")                                       , "Solid", "None")
    Note

    “DeliveryStatus” is case sensitive in this expression.

  5. Click and hold down the left mouse button in the cell containing the word “Hub.” Continue to hold down the left mouse button and drag the cursor through the “Time In” cell to the “Time Out” cell. All three cells should now be selected. Modify the following property for these cells:

    Property

    Value

    BorderStyle: Bottom

    Solid

  6. Select the following three cells using the same method as in step 5: “Delivery:”, “=First(Fields!DeliveryPlanet.Value)”, “=First(Fields!DeliveryDateTime.Value)”. Modify the following property for these cells:

    Property

    Value

    BorderStyle: Top

    Solid

  7. Click the gray square to the left of the top group footer row, so the entire row is selected. Enter the following expression for the BorderStyle: Bottom property for this row:

    = IIF(Fields!StatusName.Value = "Lost", "None", "Solid")
  8. Click the gray square to the left of the middle group footer row, so the entire row is selected. Enter the following expression for the BorderStyle: Bottom property:

    = IIF(Fields!StatusName.Value <> "Lost", "Solid", "None")
  9. Enter the following expression for the Visibility: Hidden property for this same row:

    = IIF(Fields!StatusName.Value = "Lost", false, true)
  10. Select the Preview tab. Select “Bolimite, Mfg” from the Customer drop-down list. Select “2003” from the Year drop-down list. Click View Report. Your report should appear similar to Figure 8-3.

    click to expand
    Figure 8-3: The Delivery Status Report preview after Task 4

  11. Select Save All in the toolbar.

Task Notes If you scroll through the pages of the report, you will see that the report now meets the business needs specified. Let’s look at what each expression is doing. The expression entered in step 2 returns green when the status is “Delivered,” and blue when the status is “In Route.” Otherwise, it returns red.

The expression in step 4 is a bit more complex. It checks whether the current value of the DeliveryNumber field is equal to the first value of the DeliveryNumber field in the DeliveryStatus dataset. As you saw in Chapter 7, aggregate functions act within a scope. By default, the First() aggregate function would return the value for the first record in the current scope. Because this expression is in the group header, by default it would return the value for the first record in each group.

However, in this expression, the First() aggregate function includes a second parameter that specifies the scope it should use. This parameter specifies that the First() aggregate function should use the scope of the entire DeliveryStatus dataset rather than just the current group. Therefore, it returns the first record in the dataset. When the current delivery number is equal to the first delivery number in the dataset, a solid border is created across the top of these text boxes. When the current delivery number is not equal to the first delivery number in the dataset, no border is created.

The expressions in step 7 and step 8 use the value of the StatusName field to control the border across the bottom of each grouping. If the row with the problem contact is displayed, the border should appear across the bottom of this row. However, if the row with the problem contact is not displayed, the border should appear across the bottom of the row above it. The expression in step 9 controls whether the grouping row containing the problem contact is displayed. This is also based on the value of the StatusName field.

As you can see, expressions can be very useful when the formatting or even the visibility of a report item needs to change depending on some condition in the report. Expressions can also be used to calculate the values to appear in a text box, as you will see in the next report.

The Lost Delivery Report

Features Highlighted

  • Using Visual Basic .NET expressions to calculate values in a text box

  • Adding static columns to a matrix

  • Adding totals to a matrix

  • Formatting total cells in a matrix

Business Need The quality assurance department at Galactic Delivery Services would like a report to help them analyze the packages lost during delivery. The report should show the number of packages lost each year at each processing hub. It should break down these numbers by the cause for each loss. It should also show the number of losses by cause as a percentage of the total number of packages lost for each hub.

Task Overview

  1. Create a New Report, Create a Dataset, and Add a Matrix to the Report

  2. Add a Calculated Column to the Matrix

  3. Add Totals to the Matrix

Lost Delivery Report, Task 1: Create a New Report, Create a Dataset, and Add a Matrix to the Report

  1. Reopen the Chapter08 project if it has been closed.

  2. Create a new report called LostDelivery using the GDSReport template.

  3. Create a new dataset called LostDelivery that calls the stp_LostDeliveries stored procedure.

  4. Place a matrix onto the body of the report. Drag the DeliveryNumber field into the Data cell. Edit the aggregate function in the resulting expression to change it from Sum to Count.

  5. Drag the Cause field into the Rows cell. Drag the Hub field into the Columns cell.

  6. Open the Matrix Properties dialog box and select the Groups tab.

  7. Click Add in the Columns area. The Grouping and Sorting Properties dialog box will appear.

  8. Type the following for Expression to group the values by year:

    =Year(Fields!PickupDateTime.Value) 
  9. Click OK to exit the Grouping and Sorting Properties dialog box.

  10. Click Up in the Columns area.

  11. Click OK to exit the Matrix Properties dialog box.

  12. Modify the following properties of the text box in the upper-left corner of the matrix:

    Property

    Value

    BackgroundColor

    Gainsboro (A light gray, near the top of the list)

    Font: FontSize

    18pt

    Font: FontWeight

    Bold

    Size: Width

    2in

    Value

    Lost Deliveries by Cause

  13. Modify the following property of the text box in the lower-left corner of the matrix:

    Property

    Value

    BackgroundColor

    Gainsboro

  14. Modify the following properties of the text box in the upper-right corner of the matrix:

    Property

    Value

    BackgroundColor

    Gainsboro

    BorderStyle: Left

    Solid

    Font: FontSize

    14pt

    Font: FontWeight

    Bold

    TextAlign

    Center

  15. Modify the following properties of the text box in the center of the right-hand column of the matrix:

    Property

    Value

    BackgroundColor

    Gainsboro

    BorderStyle: Left

    Solid

    BorderStyle: Bottom

    Solid

    Font: FontWeight

    Bold

    TextAlign

    Center

  16. Modify the following property of the text box in the lower-right corner of the matrix:

    Property

    Value

    BorderStyle: Left

    Solid

Task Notes So far we have a fairly straightforward matrix report. Let’s see what happens when we add another column and totals to the matrix.

Lost Delivery Report, Task 2: Add a Calculated Column to the Matrix

  1. Right-click the text box in the lower-right corner of the matrix and select Add Column from the context menu. A new column and a new set of column headings appear.

  2. Modify the following properties of the new text box in the lower-right corner of the matrix:

    Property

    Value

    BorderStyle: Left

    Solid

    Format

    ###.00%

    TextAlign

    Right

    Value

    =Count(Fields!DeliveryNumber.Value) / Count(Fields!DeliveryNumber.Value,“matrix1_Hub”)

  3. Modify the following properties of the text box immediately above the text box modified in step 2:

    Property

    Value

    BackgroundColor

    Transparent

    BorderStyle: Left

    Solid

    BorderStyle: Bottom

    None

    Font: FontWeight

    Normal

    TextAlign

    Right

    TextDecoration

    Underline

    Value

    % of Column

  4. Modify the following properties of the text box immediately to the left of the text box modified in step 3:

    Property

    Value

    BackgroundColor

    Transparent

    BorderStyle: Left

    Solid

    BorderStyle: Bottom

    None

    Font: FontWeight

    Normal

    TextAlign

    Right

    TextDecoration

    Underline

    Value

    # Lost

    Your report layout should appear similar to Figure 8-4.

    click to expand
    Figure 8-4: The Lost Delivery Report layout after Task 2

  5. Select the Preview tab. Your report should appear similar to Figure 8-5.

    click to expand
    Figure 8-5: The Lost Delivery Report preview after Task 2

Task Notes In the previous report, we created a multirow group header and a multirow group footer in a table. In this report, we have now created a multicolumn detail section in a matrix. When we add the second column, a new set of headers is added so that we can identify the contents of each column.

Our new column takes the count from the current row and calculates it as a percentage of the total for the column. This is done, once again, through the magic of scope. The first Count() aggregate function does not have a scope parameter, so it defaults to the scope of the current cell. In other words, it counts the number of lost deliveries in the current cell. The second Count() aggregate function has a scope parameter of matrix1_Hub. This is the name of the column group that creates the column for each hub. Therefore, this aggregate function counts the number of lost deliveries in the entire column. We then divide and use the “##.00%” format string to create a percentage.

Lost Delivery Report, Task 3: Add Totals to the Matrix

  1. Select the Layout tab.

  2. Right-click the text box in the lower-left corner of the matrix and select Subtotal from the context menu. A “total” cell is added to the bottom of the matrix.

  3. Modify the following property of the text box in this new cell:

    Property

    Value

    BorderStyle: Top

    Solid

  4. Select the Preview tab. Notice that the border we added to the top of the text box only affects the text box with the word “Total” in it. It did not affect any of the text boxes that contain the actual totals.

  5. Select the Layout tab.

  6. Click the green triangle in the upper-right corner of the text box with the word “Total” in it. It may take a few tries to click the green triangle and not the total cell text box. When you have done it correctly, the drop-down list at the top of the Properties window will change to “Subtotal.” Modify the following properties:

    Property

    Value

    BorderStyle: Left

    Solid

    BorderStyle: Top

    Solid

  7. Select the Preview tab. We now have the desired format, with the border at the top of each text box that contains a total.

  8. Select the Layout tab.

  9. Right-click the text box in the upper-right corner of the matrix. Select Subtotal from the context menu. A “total” cell is added to the right of the matrix.

  10. Modify the following properties of the text box in this new cell:

    Property

    Value

    BorderStyle: Left

    Solid

    BorderStyle: Bottom

    Solid

    Your report layout should appear similar to Figure 8-6.

    click to expand
    Figure 8-6: The Lost Delivery Report layout after Task 3

  11. Select the Preview tab. Your report should appear similar to Figure 8-7.

    click to expand
    Figure 8-7: The Lost Delivery Report preview after Task 3

  12. Select Save All in the toolbar.

Task Notes As you just saw, adding subtotals to a row or column in a matrix involves just a couple clicks. However, formatting the text boxes that contain those totals can be a little trickier. When you add a total to a matrix row or column, the text box in the new cell is the header for the total row or total column. Changing the properties of this text box only affects the header. You have to click the green triangle to modify the properties for the text boxes that contain the totals.

You have seen how expressions can be used to control properties and to provide the calculated contents of a text box. Now let’s look at using an expression in the query definition for a dataset.

The Customer List Report—Revisited

Features Highlighted

  • Using Visual Basic .NET expressions to specify a dataset query

Business Need The Customer List Report that you developed for the Galactic Delivery Services accounting department (in Chapter 4) has proved to be very popular. Several other departments would like similar reports to help them track their own lists of e-mail contacts. Rather than create separate reports for each department, which would be hard to maintain, the IT Manager has asked for one report that allows the user to select which type of contact they would like to view.

Task Overview

  1. Copy the Report from the Chapter04 Project and Add It to the Chapter08 Project

  2. Add a Report Parameter and Modify the Dataset to Use the Report Parameter

Customer List Report—Revisited, Task 1: Copy the Report from the Chapter04 Project and Add It to the Chapter08 Project

  1. Use Windows Explorer to copy the report definition file for the Customer List Report (CustomerList1.rdl) from the Chapter04 project folder and paste it in the Chapter08 project folder. Both of these folders should be found under My Documents in the Visual Studio Project\MSSQLRS folder.

  2. In Visual Studio, reopen the Chapter08 project if it has been closed.

  3. Right-click the Reports folder in the Solution Explorer and select Add | Add Existing Item from the context menu. The Add Existing Item – Chapter08 dialog box appears.

  4. Make sure you are looking at the Chapter08 folder in the dialog box and select the CustomerList1.rdl file. Click Open to exit the Add Existing Item – Chapter08 dialog box.

  5. Double-click the CustomerList1.rdl entry in the Solution Explorer to open the report definition.

  6. Select the Preview tab to show that this report is functioning properly in the Chapter08 project.

Task Notes Because the entire definition of a report is contained within a single RDL file, it is easy to copy reports to different locations. As you saw here, we can even add them to a project other than the project within which they were originally created. The Customer List Report uses a shared data source called Galactic. We did not need to copy the shared data source because we already have a shared data source with the same name and the same properties in the Chapter08 project. If this was not the case, we could have copied the shared data source file (Galactic.rds) along with the report file and added that to our new project as well.

Customer List Report—Revisited, Task 2: Add a Report Parameter and Modify the Dataset to Use the Report Parameter

  1. Select the Data tab.

  2. Open the Report Parameters dialog box.

  3. Add a new report parameter and modify the properties for this new parameter as follows:

    Property

    Value

    Name

    ListType

    Prompt

    Select a List

    Allow blank value

    unchecked

    Available Values

    (See the following table)

  4. Set the Available Values property for the ListType parameter as follows:

    Label

    Value

    Billing Contacts

    B

    Manufacturer Contacts

    M

    Problem Contacts

    P

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

  6. Click the Generic Query Designer button in the Query Builder toolbar. The Generic Query Designer, rather than the Query Builder, is displayed on the Data tab.

  7. Replace the entire select statement with the following expression:

    =IIF(Parameters!ListType.Value="B", "EXEC stp_BillingContacts",     IIF(Parameters!ListType.Value="M",            "EXEC stp_ManufacturerContacts",           "EXEC stp_ProblemContacts"))

  8. Click the … button in the Generic Query Designer toolbar. The Dataset dialog box will appear.

  9. Select the Fields tab.

  10. Change the Fields table to match the following:

    Field Name

    Type

    Value

    Name

    Database Field

    Name

    Contact

    Database Field

    Contact

    Email

    Database Field

    Email

  11. Click OK to exit the Dataset dialog box.

  12. Select the Layout tab.

  13. Drag the Contact field and drop it on the text box that currently contains the expression for the BillingContact field.

  14. Double-click the table header cell directly above the text box from step 12 and change the text to “Contact.”

  15. Drag the Email field and drop it on the text box that currently contains the expression for the BillingEmail field.

  16. Double-click the table header cell directly above the text box from step 14 and change the text to “E-mail.”

  17. Select Save All in the toolbar.

  18. Select the Preview tab. Try selecting each of the list types. Remember to click View Report.

    Note

    The database does not contain a contact name for each manufacturer, so there are no contact names in the manufacturer list.

Task Notes Rather than specifying the query in the Query Designer, we used an expression to choose between three possible queries (in this case, three stored procedure calls). This is known as a dynamic query. The name comes from the fact that the query that is actually run depends on input from the user at the time the report is run.

Because the contents of the query are not known until run time, Visual Studio cannot “pre-run” the query to determine the fields that will result. Instead, we need to manually specify the fields that will result from our dynamic query. All the possible queries that could be run should return result sets with the same field names in order for your report to work properly.

At this point, you may be ready to suggest two or three alternative approaches to creating this report. It is certainly not unusual to come up with a number of possible ways to meet the business needs of a report. When this happens, use the following criteria to evaluate the possible solutions:

  • Efficiency of operation

  • Your comfort with implementing and debugging a given solution in a reasonable amount of time

  • Maintainability

  • Your need to illustrate a certain point in a book chapter

Well, maybe that last point won’t apply to you, but it was, in fact, the overriding reason for choosing this approach for this particular report.

Payroll Checks

Features Highlighted

  • Using Visual Basic .NET functions embedded in the report to create reusable code

  • Using a stored procedure that modifies the data

  • Grouping in the details row of a data region

  • Using nested data regions

Business Need The Galactic Delivery Services accounting department needs a report to print payroll checks for its hourly employees. The checks should have the check portion in the top one-third of the page and the check register in the bottom two-thirds of the page. The check register should list the hours worked that are included in this check. The user should be able to select a week for which there is unpaid time entered and receive the payroll checks for that week. The planetary system tax amount (25 percent) and state tax amount (5 percent) must be deducted from the amount being paid.

Task Overview

  1. Create a New Report, Create Two Datasets, Add a List to the Report Layout, and Populate It

  2. Add a Table to the Report Layout and Populate It

  3. Configure the Report Parameter and Add Embedded Code to the Report

Payroll Checks, Task 1: Create a New Report, Create Two Datasets, Add a List to the Report Layout, and Populate It

  1. Reopen the Chapter08 project if it has been closed.

  2. Create a new report called PayrollChecks. Do not use the GDSReport template.

  3. Create a new dataset called PayrollChecks that calls the stp_PayrollChecks stored procedure. Do not run the stored procedure in the Query Designer. This will mark records as having been paid.

  4. Create a new dataset called WeekNumbers that calls the stp_WeekNumbers stored procedure.

  5. Select the Layout tab.

  6. Place a list onto the body of the report. Modify the following properties of this list in the Properties window:

    Property

    Value

    BackgroundColor

    LightGreen

    BorderStyle

    Solid

    DataSetName

    PayrollChecks

    PageBreakAtStart

    True

  7. Open the List Properties dialog box. Click Edit Details Group. The Details Grouping dialog box will appear.

  8. Select “=Fields!PayrollCheckNumber.Value” from the Expression drop-down list.

  9. Click OK to exit the Details Grouping dialog box. Click OK to exit the List Properties dialog box.

  10. Add text boxes to the list to get the layout shown in Figure 8-8. You can create text boxes containing fields by dragging the fields from the Fields window. You can create a text box containing a constant string by dragging a text box from the Toolbox and typing the constant string in the new text box.

    click to expand
    Figure 8-8: The payroll check layout after Task 1

  11. Modify the following property of the text box containing the sum of the LineAmount values:

    Property

    Value

    Format

    C

Task Notes Our payroll check has two separate parts: the check itself and the check register. The check register contains a line showing the amount paid for each day worked during the selected work week. The check is essentially a summary of the information in the check register. The check amount is the sum of the amount to be paid for all the days worked.

We could use two different datasets to provide data to these two areas. However, to be a little more efficient with our database resources, we are going to use a single dataset. The dataset will include all the detail information required by the check register. It will have one row for each date worked. However, we do not want to create a check for each date worked. We only want one check for all the days worked by a given employee in the week.

To accomplish this, we need to group the detail data in order to print the check. We did this by adding the details grouping in steps 7 through 9. Because we want one check per check number, the PayrollCheckNumber field seems an obvious choice for grouping. (The number in the PayrollCheckNumber field is generated by the stored procedure.) With this details grouping, our list will receive one record for each check number; therefore, we will get one check per check number.

Payroll Checks, Task 2: Add a Table to the Report Layout and Populate It

  1. Increase the height of the report body and the list.

  2. Place a table inside the list below the signature text box.

  3. Drag the WorkDate, HoursWorked, and LineAmount fields into the cells in the details row of the table.

  4. Drag the LineAmount field into the rightmost table footer cell and set the following properties for this new text box:

    Property

    Value

    BorderStyle: Top

    Solid

    Format

    C

  5. Set the following property for the text box in the rightmost details row cell:

    Property

    Value

    Format

    C

  6. Set the following properties for the table:

    Property

    Value

    BackgroundColor

    White

    NoRows

    No Uncut Checks For This Week

    PageBreakAtEnd

    True

  7. Drag the bottom of the list and the bottom of the report body up so they are the same as the bottom of the table. Your report layout will appear similar to Figure 8-9.

    click to expand
    Figure 8-9: The payroll check layout after Task 2

Task Notes In Task 1, we created a list with a detail grouping to create the check portion of our payroll checks. In Task 2, we created a table to provide the detail information for the check register. The table data region must be nested inside of the list data region so that we get one set of detail information for each check. If the table was placed below the list, we would get all the checks first and then all the check register information at the end.

The PageBreakAtEnd property was set on the table so that there will be a page break immediately after the table. This keeps our output to one check per page.

Payroll Checks, Task 3: Configure the Report Parameter and Add Embedded Code to the Report

  1. Use the main menu to open the Report Parameters dialog box.

  2. Modify the following properties for the WeekNumber parameter:

    Property

    Value

    Prompt

    WeekNumber

    Available values

    From query

    Dataset

    WeekNumber

    Value field

    WeekNumber

    Label field

    WeekNumber

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

  4. Select Report | Report Properties from the main menu. The Report Properties dialog box will appear.

  5. Select the Code tab.

  6. Enter the following in the Custom code area:

    ' State and Planetary System Tax Deductions Public Function TaxDeductions(ByVal Amount As Double) As Double         ' Planetary System Tax = 25%         ' State Tax = 5%         TaxDeductions = Amount * .25 + Amount * .05 End Function

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

  8. Right-click the text box that is in the list, but not in the table, containing the sum of the LineAmount values and select Expression from the context menu. The Edit Expression dialog box will appear.

  9. Replace the contents of the Expression area with the following:

    =Sum(Fields!LineAmount.Value) -       Code.TaxDeductions(Sum(Fields!LineAmount.Value))
  10. Click OK to exit the Edit Expression dialog box.

  11. Repeat steps 8 through 10 with the text box in the table containing the sum of the LineAmount values.

  12. Right-click the text box in the details row of the table containing the LineAmount value and select Expression from the context menu. The Edit Expression dialog box will appear.

  13. Replace the contents of the Expression area with the following:

    =Fields!LineAmount.Value - Code.TaxDeductions(Fields!LineAmount.Value)

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

  15. Select the Preview tab.

  16. Select “10-2004” from the Week Number drop-down list and click View Report. Your report should appear similar to Figure 8-10. Remember, once checks have been run for a given week, you cannot produce checks for that week again. Each time you enter the report, the Week Number drop-down list will only contain entries for weeks that have not been run. (The check number you see on the first page in your preview may be different from the check number shown in the figure. This is normal.)

    click to expand
    Figure 8-10: The payroll check preview

  17. Select Save All from the toolbar.

Task Notes Tax calculations are more straightforward on the planets where Galactic Delivery Services operates than they are here. Everyone pays 25 percent of their pay to the Planetary System government and 5 percent of their pay to the State government. Even though this is a simple formula, we need to use it in three different places. Using the embedded code feature of Reporting Services, we are able to put this formula in one location and use it in several locations. This also makes things easier to change when one or the other of these tax amounts goes up.

The Weather Report

Features Highlighted

  • Referencing .NET assemblies in the report

Business Need The Galactic Delivery Services flight control department needs a way to quickly list the current weather conditions at each of the planets served by GDS. (After all, space transports have to go through the atmosphere to take off and land.) One of the GDS programmers has created a .NET assembly that uses a web service to get the weather from various locations. A call must be made to a method of this .NET assembly for each of the planets and the results incorporated into the report.

Task Overview

  1. Copy the .NET Assembly into the Appropriate Location and Create a Reference to the Assembly

  2. Create a New Report, Create a Dataset, Add a Table to the Report Layout, and Populate It

Weather Report, Task 1: Copy the .NET Assembly into the Appropriate Location and Create a Reference to the Assembly

  1. If you have not already done so, download the WeatherInfo.dll assembly from the website for this book.

  2. Copy this file to the Report Designer folder. The default path for the Report Designer folder is

    C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer

  3. Reopen the Chapter08 project if it has been closed.

  4. Create a new report called WeatherReport using the GDSReport template.

  5. Select Report | Report Properties from the main menu. The Report Properties dialog box appears. Select the References tab.

  6. Click … next to the References area. The Add Reference dialog box appears.

  7. Click Browse. The Select Component dialog box appears. Navigate to the Report Designer folder. (The path to this folder was given previously.) Select WeatherInfo.dll and click Open to select this file and exit the Select Component dialog box.

  8. Click OK to exit the Add Reference dialog box. Click OK to exit the Report Properties dialog box.

Task Notes In order for a custom assembly to be used in our reports, the assembly must be in a location where it can be found by Reporting Services. When you are designing reports, the assembly must be either in the Report Designer folder or in the Global Assembly Cache. We placed the WeatherInfo.dll assembly in the Report Designer folder in step 2. Consult your .NET documentation for information on placing an assembly in the Global Assembly Cache.

We are using a class from the WeatherInfo assembly called PlanetaryWeather and a method from that class called GetWeather. The GetWeather method is a shared method. This means that you do not need to create an instance of the PlanetaryWeather class in order to use the GetWeather method.

To use a method that is not a shared method, you need to use the Classes area of the Report Properties dialog box. First, create a reference in the References area, as we did in steps 6 and 7. Then, under Class name, specify the name of the class within that assembly that you wish to instantiate. Finally, provide a name for the instance of that class. Reporting Services will create an instance of the class with the name you provide when the report is run.

Once the assembly is in the correct location and you have created a reference to that assembly, you can use the methods of this assembly in your reports. When referencing a shared method in an assembly, use the following syntax:

Namespace.ClassName.MethodName(Parameters…)

For the WeatherInfo assembly, the syntax is

WeatherInfo.PlanetaryWeather.GetWeather(PlanetAbbrv)

To use a nonshared method from a class that you instantiated, use the syntax

Code.InstanceName.MethodName(Parameters…)

Weather Report, Task 2: Create a New Report, Create a Dataset, Add a Table to the Report Layout, and Populate It

  1. Create a new dataset called Planets. Use the following for the query string:

    SELECT Name, PlanetAbbrv FROM Planet ORDER BY Name
  2. Select the Layout tab.

  3. Place a text box and a table onto the body of the report. Complete your report layout so it is similar to Figure 8-11.

    click to expand
    Figure 8-11: Weather Report layout

  4. Select the Preview tab. Your report should appear similar to Figure 8-12.

    click to expand
    Figure 8-12: Weather Report preview

    Note

    Keep in mind that the GetWeather method is actually going out to the Internet and retrieving weather conditions when you run the report. Because of this, you must be connected to the Internet when you run this report. Also, the weather conditions you see in your report will vary from those shown in the report preview. Finally, some locations may show “null” for a certain condition if that condition has not been reported in the past hour.

  5. Select Save All from the toolbar.

Task Notes Remember that the WeatherInfo assembly happens to use a web service to gather its weather information. Therefore, a connection to the Internet is required when previewing this report. The preview will take a minute or so to complete because the report needs to access the web service for each planet.




Microsoft SQL Server 2000 Reporting Services
Microsoft SQL Server 2000 Reporting Services Step by Step (Pro-Step by Step Developer)
ISBN: 0735621063
EAN: 2147483647
Year: 2003
Pages: 109

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