MicrosoftВ® SQL Server(TM) 2005 Reporting Services Step by Step (Step by Step (Microsoft))

Now that we have charted up a storm, it is time to turn our attention to two other methods for adding color to a report. One way is through the use of borders and background colors. Almost all report items have properties you can use to specify borders and background colors.

The other way to add color to your reports is through the use of images. Images can be placed on a report using the image report object. They can serve as a background for other report objects. They can even serve as the background to the main body of the report itself.

In addition to determining where an image is placed on the report, you also have to determine where the image will come from. Images can be stored in the report project, embedded in the report itself, pulled from a binary field in a database, or obtained from the Web using a URL. Each image location has its own benefits and drawbacks.

Images stored in the report project are, of course, saved as separate files. They are not stored as part of the report definition. This means when the report is rendered, the renderer must find each of these image files to render the report correctly.

Images stored in the report project are easier to update if they have to be changed in the future. You can simply modify the image file because it is not embedded in a report definition file. They can also be shared among several reports. However, because the report and its required images exist as separate files, some care has to be taken to ensure that the renderer can always locate the images when it is rendering the report.

Embedded images are stored right in the report definition file. With embedded images, only one file is required for rendering the report. There is no risk of the renderer being unable to find a required image. The downside of embedded images is it is more difficult to update an image. To change an embedded image, you need to modify the source image, re-embed the modified image, and redeploy the report. Also, it is impossible to share an embedded image between reports. It can only be used by the report in which it is embedded.

Images stored in a database file can be shared among reports and are easy to track down when a report is rendered. In addition, when images are stored with the data in the database, it is possible to use a different image in your report for each row in the dataset. This is more difficult to do with project or embedded images.

Images in the database do pose two concerns. First, retrieving images from the database puts an additional load on your database server. Care must be taken to make sure your server can handle this additional load without degradation in response time. And, second, managing large binary objects, such as images, in database records is not always a trivial task.

Images obtained through the Internet have a number of advantages. They can be easily shared among tens or even hundreds of reports; all the reports simply reference the same URL. They can be easily updated; just post a new image to the web server and all the reports are referencing this new version. In addition, web servers are designed for serving images, so there should not be an issue with additional load on the web server, unless it is extremely busy already.

The downside to obtaining images from a web server is this: the renderer must take the time to make an HTTP request for each image it needs to put in the report. If the image’s URL points to the report server itself or if it points to another server on the same internal network, this may not be a big deal. If, on the other hand, the URL points to a server across the Internet from the report server, the time required for rendering will increase. You also need to insure that the report server can always connect to the web server hosting the image.

As a rule of thumb, images to be shared among many reports, such as company logos, should be kept either in the report project or accessed through a URL. These shared images should be put in one central location, so they can be accessed by the reports when they are needed. Images that have a strong association with data in a particular record in a database table should be stored in the database itself. For example, a picture of a particular employee has a strong association with that employee’s record in the Employee table. We are only interested in displaying the picture of a particular employee when the row in the dataset for that employee is being processed. Any images that do not fall into these two categories should be embedded in the report to ease deployment issues.

Conference Nametags

Features Highlighted

Business Need   Galactic Delivery Services is preparing for its annual customer conference. The billing contact for each customer has been invited to the conference. As part of the preparations, the GDS art department must create nametags for the conference attendees. Because the names of all the billing contacts are available in the Galactic database, and this database can easily be accessed from Reporting Services, the art department has decided to use Reporting Services to create the nametags.

The conference nametags should include the name of the attendee and also the name of the company they work for. The art department would like the nametags to be bright and colorful. They should include the GDS logo.

Task Overview

  1. Create a New Report, Create a Dataset, and Place the Report Items on the Report

Conference Nametags, Task 1: Create a New Report, Create a Dataset, and Place the Report Items on the Report

  1. Reopen the Chapter06 project if it was closed. Close the DaysInMaint report.

  2. Add a blank report called Nametags to the Chapter06 project. (Do not use the Report Wizard.)

  3. Select <New Dateset...> from the Dataset drop-down list. The Dataset dialog box appears.

  4. Enter BillingContacts for the name in the Dataset dialog box.

  5. Galactic (shared) is selected for the data source by default. Click OK. You return to the Data tab, which now displays the Generic Query Designer.

  6. Type the following in the SQL pane:

    SELECT BillingContact, Name FROM Customer ORDER BY BillingContact

  7. Run the query to make sure no errors exist. Correct any typos that may be detected.

  8. Switch to the Layout tab.

  9. Select the List report item in the Toolbox window and drop it onto the report layout. Modify the following properties of the list:

    Property

    Value

    BackgroundColor

    DarkOrange

    Location: Left

    0.125in

    Location: Top

    0.125in

    Size: Width

    4.75in

    Size: Height

    2.125in

    We are using a list here because this report is going to have a freeform layout, rather than the rows and columns of a table or matrix.

  10. In the Datasets window, expand the BillingContact dataset. Drag the BillingContact field from the Datasets window and drop it onto the list. Modify the following properties of the text box that results:

    Property

    Value

    BackgroundColor

    Gold

    BorderColor

    DarkBlue

    BorderStyle

    Solid

    BorderWidth

    4pt

    Color

    DarkBlue

    Font: FontSize

    20pt

    Font: FontWeight

    Bold

    Location: Left

    0.125in

    Location: Top

    0.125in

    Size: Width

    4.5in

    Size: Height

    O.5in

    TextAlign

    Center

    VerticalAlign

    Middle

  11. Drag the Name field from the Datasets window and drop it onto the list. Modify the following properties of the text box that results:

    Property

    Value

    BackgroundColor

    Gold

    BorderColor

    DarkBlue

    BorderStyle

    Solid

    BorderWidth

    4pt

    Color

    DarkBlue

    Font: FontSize

    16pt

    Font: FontWeight

    Bold

    Location: Left

    0.125in

    Location: Top

    0.875in

    Size: Width

    4.5in

    Size: Height

    0.375in

    TextAlign

    Center

    VerticalAlign

    Middle

  12. Drag a text box from the Toolbox and drop it onto the list. Modify the following properties of this text box:

    Property

    Value

    Font: FontSize

    23pt

    Location: Left

    1in

    Location: Top

    1.375in

    Size: Width

    3.625in

    Size: Height

    0.625in

    TextAlign

    Center

    Value

    GDS Conference 2006

    VerticalAlign

    Middle

  13. Drag a line from the Toolbox and drop it onto the list. Modify the following properties of the line:

    Property

    Value

    EndPoint: Horizontal

    4.75in

    EndPoint: Vertical

    2.125in

    LineColor

    DarkBlue

    LineWidth

    10pt

    Location: Left

    Oin

    Location: Top

    2.125in

  14. Drag an image report item from the Toolbox and drop it onto the list. The Image Wizard appears. Click Next.

  15. Click the Project radio button. This will be an external image. The Image Wizard places it in the Chapter06 project for us. Click Next.

  16. Click New Image. The Import Image dialog box appears.

  17. Navigate to the GDS.gif image file and select it. Click Open.

    Note 

    The image files used in the reports in this chapter are available on the website for this book. If you have not done so already, go to http://www.osborne.com, locate the book’s page using the ISBN 0072262397, and follow the instructions to download the image files.

  18. Click Next, and then click Finish.

  19. Modify the following properties of the image in the Properties window:

    Property

    Value

    Location: Left

    0.125in

    Location: Top

    1.375in

    Sizing

    AutoSize

  20. Check to make sure the list is still the correct size. Change the dimensions to match the following, if necessary:

    Property

    Value

    Size: Width

    4.75in

    Size: Height

    2.125in

  21. Click in the report layout area. This causes the report body to be selected in the Properties window. Modify the following properties of the report body:

    Property

    Value

    BackgroundColor

    DarkBlue

    Size: Width

    5in

    Size: Height

    2.25in

  22. Click the Preview tab. The nametags are ready to be printed, cut apart, and placed in nametag holders, as shown here.

  23. Click Save All in the toolbar.

Task Notes   We used several properties of the report objects in our Conference Nametags report to add color. The BackgroundColor property controls the color in the background of the report item. This defaults to Transparent, meaning that whatever is behind the item shows through. When the BackgroundColor property is set to a color rather than Transparent, that color fills in and covers up everything behind the item.

The BorderColor property controls the color of the border around the outside of the report item. BorderColor works in cooperation with two other properties: BorderStyle and BorderWidth. The BorderStyle property defaults to None. When BorderStyle is None, the border is invisible. No matter what color you set for BorderColor, it does not show up when the BorderStyle is set to None.

To have a visible border around an object, you must change the BorderSyle property to a solid line (Solid), a dotted line (Dotted), a dashed line (Dashed), a double line (Double), or one of the other settings in the BorderStyle drop-down list. Once you select one of these visible settings for the BorderStyle property, you can set the color of the border using the BorderColor property and the thickness of the border using the BorderWidth property.

The border settings for each side of a report item can be controlled separately or altogether. If you expand any of the three border properties, you can see they have separate entries for Default, Left, Right, Top, and Bottom. The Default property is, as it says, the default value for all four sides of the report item. When the Left, Right, Top, or Bottom property is blank, the setting for that particular side is taken from the Default property. For example, if the BorderStyle: Default property is set to None, and BorderStyle: Left, BorderStyle: Right, BorderStyle: Top, and BorderStyle: Bottom are all blank, then there is no border around the report item. If the BorderStyle: Bottom property is set to Double, this overrides the default setting and a double line appears across the bottom of the item. The border on the other three sides of the item (left, right, and top) continues to use the default setting.

The Color property controls the color of the text created by a report item. You find the Color property on a text box, which is expected, because the main purpose of a text box is to create text. You also find the Color property on each of the data regions, tables, matrixes, lists, and charts. A data region can create a text message when no rows are in the dataset attached to it. The Color property specifies the color of the text in this special “no rows” message when it is displayed. (We discuss the “no rows” message more in Chapter 7.)

The final color property we used in the Conference Nametags report is the LineColor property. This property exists only for line report items. It should come as no surprise that this property controls the color of the line.

One thing you quickly notice when you begin using background colors is this: a report item with a BackgroundColor property set to Transparent is only transparent when the report is rendered. The report item is not transparent on the Layout tab. Report items that have a BackgroundColor property set to Transparent have a white background on the Layout tab. This makes it easier to select a report item as you are moving things around or changing properties in the report layout.

We used the TextAlign property to adjust the way text is placed horizontally inside a text box (left, center, or right). In this report, we also used the VerticalAlign property to adjust the way text is placed vertically inside a text box (top, middle, or bottom). The vertical alignment of text in a text box is not usually an issue unless the border of the text box is visible and you can see where the text is being placed relative to the top and bottom of the text box.

Conference Place Cards

Features Highlighted

Business Need   Galactic Delivery Services is continuing its preparations for the annual customer conference. In addition to the nametags, the GDS art department must also create place cards for the conference attendees. The place cards are going to be put on the table in front of each attendee during roundtable discussions. As with the nametags, place cards should be created for all the billing contacts.

The conference place cards should include the name of the attendee and also the name of the company they work for. The art department would like the place cards to continue the color scheme set by the nametags, but with a more intricate pattern. They should include the GDS logo.

Task Overview

  1. Create a New Report, Create a Dataset, and Place the Report Items on the Report

Conference Place Cards, Task 1: Create a New Report, Create a Dataset, and Place the Report Items on the Report

  1. Reopen the Chapter06 project if it was closed. Close the Nametags report.

  2. Add a blank report called PlaceCards to the Chapter06 project. (Do not use the Report Wizard.)

  3. Select <New Dateset...> from the Dataset drop-down list. The Dataset dialog box appears.

  4. Enter BillingContacts for the name in the Dataset dialog box.

  5. Galactic (shared) is selected for the data source by default. Click OK. You return to the Data tab, which now displays the Generic Query Designer.

  6. Type the following in the SQL pane:

    SELECT BillingContact, Name FROM Customer ORDER BY BillingContact

  7. Run the query to make sure no errors exist. Correct any typos that may be detected.

  8. Switch to the Layout tab.

  9. Select Report | Embedded Images from the Main menu. The Embedded Images dialog box appears.

  10. Click New Image. The Import Image dialog box appears.

  11. Navigate to the GDSBackRect.gif image file and select it. Click Open.

  12. Click New Image. The Import Image dialog box appears.

  13. Navigate to the GDSBackOval.gif image file and select it. Click Open.

  14. Click New Image. The Import Image dialog box appears.

  15. Navigate to the GDSBig.gif image file and select it. Click Open.

  16. Click OK to exit the Embedded Images dialog box.

  17. Click in the report layout area. This causes the report body to be selected in the Properties window. Modify the following properties of the report body:

    Property

    Value

    BackgroundColor

    DarkOrange

    BackgroundImage: Source

    Embedded

    BackgroundImage: Value

    gdsbackrect (The drop-down list shows all the images embedded in the report.)

    Size: Width

    8.875in

    Size: Height

    3.2in

  18. Drag a List from the Toolbox and drop it onto the report layout. Modify the following properties of the list:

    Property

    Value

    Location: Left

    Oin

    Location: Top

    Oin

    Size: Width

    8.75in

    Size: Height

    3.2in

  19. In the Datasets window, expand the BillingContact dataset. Drag the BillingContact field from the Datasets window and drop it onto the list. Modify the following properties of the text box that results:

    Property

    Value

    BackgroundImage: Source

    Embedded

    BackgroundImage: Value

    gdsbackoval

    Font: FontSize

    30pt

    Font: FontWeight

    Bold

    Location: Left

    2.5in

    Location: Top

    1.75in

    Size: Width

    6.125in

    Size: Height

    0.625in

    TextAlign

    Center

    VerticalAlign

    Middle

  20. Drag the Name field from the Datasets window and drop it onto the list. Modify the following properties of the text box that results:

    Property

    Value

    BackgroundImage: Source

    Embedded

    BackgroundImage: Value

    gdsbackoval

    Color

    DarkBlue

    Font: FontSize

    30pt

    Font: FontWeight

    Bold

    Location: Left

    2.5in

    Location: Top

    2.5in

    Size: Width

    6.125in

    Size: Height

    0.625in

    TextAlign

    Center

    VerticalAlign

    Middle

  21. Drag an Image report item from the Toolbox, and drop it onto the list. The Image Wizard appears.

  22. Click Next.

  23. Make sure Embedded is selected. Click Next.

  24. Select the gdsbig image. Click Next.

  25. Click Finish to exit the Image Wizard.

  26. Modify the following properties of the image:

    Property

    Value

    BorderStyle

    Double

    BorderWidth

    3pt

    Location: Left

    0.3in

    Location: Top

    1.715in

    Size: Width

    1.62 5in

    Size: Height

    1.375in

    Sizing

    Fit

  27. Drag a text box from the Toolbox and drop it onto the list. Modify the following properties of this text box:

    Property

    Value

    Font: FontSize

    9pt

    Font: FontWeight

    Bold

    Location: Left

    0.05in

    Location: Top

    1.715in

    Size: Width

    0.25in

    Size: Height

    1.375in

    TextAlign

    Center

    Value

    GDS Conference 2006

    WritingMode

    tb-rl

  28. This text box looks strange on the Layout tab. Have faith; it will look fine in the Preview tab.

  29. Drag a text box from the Toolbox and drop it onto the list. Modify the following properties of this text box:

    Property

    Value

    Font: FontSize

    9pt

    Font: FontWeight

    Bold

    Location: Left

    1.925in

    Location: Top

    1.715in

    Size: Width

    0.25in

    Size: Height

    1.375in

    TextAlign

    Center

    Value

    GDS Conference 2006

    VerticalAlign

    Bottom

    WritingMode

    tb-rl

  30. This text box also looks strange on the Layout tab.

  31. Click the Preview tab. The place cards are ready to be printed, cut apart, folded, and placed on the tables, as shown here.

  32. Click Save All in the toolbar.

Task Notes   In this report, we used embedded images instead of using external images, as we did in the previous report. Remember, the method of storing the image has nothing to do with the way the image is used in the report. External images can be used as background images. Embedded images can be used in image report items.

The Embedded Images dialog box enables you to manage the images embedded in the report. Remember, an embedded image remains in the report even if no report item is referencing it. The only way to remove an embedded image from a report is to use the Delete button in the Embedded Images dialog box. Always remove embedded images from the report if they are not being used. This way, the report definition does not become any larger than it needs to be.

Also, in this report, we used the WritingMode property to rotate the contents of two text boxes by 90 degrees. The normal writing mode for English text in a text box is left-to-right, top-to-bottom (lr-tb). We changed this default writing mode and told these two text boxes to output our text top-to-bottom, right-to-left (tb-rl). The WritingMode property was implemented to allow Reporting Services to work with languages written from top to bottom and right to left. However, that does not prevent us from using the WritingMode property to produce a fancy effect with our English text.

The Rate Sheet Report

Features Highlighted

Business Need   The Galactic Delivery Services marketing department needs to produce a new rate sheet. The rate sheet needs to include a description of each type of delivery service provided by GDS. Each type has its own image to help customers remember the three types of service. The rate sheet also includes the name of each service type with a longer description below it and the cost of each service type off to the right side of the page.

Because all the information on the three types of service is available in the database, the marketing department wants to produce the rate sheet from a report, rather than creating or updating a document each time the rates change.

Task Overview

  1. Create a New Report, Create a Dataset, and Place the Report Items on the Report

  2. Refine the Report Layout

Rate Sheet Report, Task 1: Create a New Report, Create a Dataset, and Place the Report Items on the Report

  1. Reopen the Chapter06 project if it was closed. Close the PlaceCards report.

  2. Add a blank report called RateSheet to the Chapter06 project. (Do not use the Report Wizard.)

  3. Select <New Dateset...> from the Dataset drop-down list. The Dataset dialog box appears.

  4. Enter ServiceTypes for the name in the Dataset dialog box.

  5. Galactic (shared) is selected for the data source by default. Click OK. You return to the Data tab, which now displays the Generic Query Designer.

  6. Type the following in the SQL pane:

    SELECT Description, LongDescription, Cost, PriceSheetImage FROM ServiceType ORDER BY Cost

  7. Run the query to make sure no errors exist. Correct any typos that may be detected.

  8. Switch to the Layout tab.

  9. Drag an image report item from the Toolbox and drop it onto the report layout. The Image Wizard appears.

  10. Click Next.

  11. Select Project, and then click Next.

  12. Select the gds image, and then click Next.

  13. Click Finish to exit the Image Wizard.

  14. Modify the following properties of the image:

    Property

    Value

    Location: Left

    0in

    Location: Top

    0in

  15. Drag a text box from the Toolbox and drop it onto the report layout. Modify the following properties of this text box:

    Property

    Value

    Color

    DarkBlue

    Font: FontSize

    30pt

    Font: FontWeight

    Bold

    Location: Left

    0.875in

    Location: Top

    Oin

    Size: Width

    6in

    Size: Height

    0.625in

    Value

    Galactic Delivery Services

    VerticalAlign

    Middle

  16. Drag a text box from the Toolbox and drop it onto the report layout. Modify the following properties of this text box:

    Property

    Value

    Color

    DarkOrange

    Font: FontSize

    25pt

    Font: FontWeight

    Bold

    Location: Left

    0.875in

    Location: Top

    0.625in

    Size: Width

    6in

    Size: Height

    0.5in

    Value

    Types of Service

    VerticalAlign

    Middle

  17. Drag a text box from the Toolbox and drop it onto the report layout. Modify the following properties of this text box:

    Property

    Value

    Color

    Gold

    Font: FontSize

    20pt

    Font: FontWeight

    Bold

    Format

    MMMM d, yyyy

    Location: Left

    0.875in

    Location: Top

    1.125in

    Size: Width

    6in

    Size: Height

    0.5in

    TextAlign

    Left

    VerticalAlign

    Middle

  18. Right-click the last text box added to the report and select Expression from the Context menu. The Edit Expression dialog box appears.

  19. Click Globals in the Fields area.

  20. Double-click ExecutionTime.

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

  22. Drag a table from the Toolbox and place it on the report layout.

  23. Right-click in the gray rectangle to the left of the header row. Select Table Header to unselect it on the Context menu. This removes the header row.

  24. Right-click in the gray rectangle to the left of the footer row. Select Table Footer to unselect it on the Context menu. This removes the footer row.

  25. Click the gray square in the upper-left corner of the table. This selects the table. Modify the following properties of the table:

    Property

    Value

    DataSetName

    ServiceTypes

    Location: Left

    0.875in

    Location: Top

    1.75in

    Size: Width

    6.25in

    Size: Height

    2.125in

  26. Drag an image report item from the Toolbox and drop it onto the leftmost table cell. The Image Wizard appears.

  27. Click Next.

  28. Select Database. Click Next.

  29. Select PriceSheetImage from the Image Field drop-down list.

  30. Select image/gif from the MIME Type drop-down list.

  31. Click Next.

  32. Click Finish to exit the Image Wizard.

  33. Click the center table cell. Click the gray rectangle above the center table cell. Modify the following property of the table column:

    Property

    Value

    Width

    2.45in

  34. In the Datasets window, expand the ServiceTypes dataset. Drag the Description field from the Datasets window and drop it onto the center table cell. Modify the following properties of the text box that results:

    Property

    Values

    Color

    DarkBlue

    Font: FontSize

    14pt

    Font: FontWeight

    Bold

  35. Drag the Cost field from the Datasets window and drop it onto the rightmost table cell. Modify the following properties of the text box that results:

    Property

    Values

    Font: FontSize

    14pt

    Format

    $###,##.00

    VerticalAlign

    Middle

  36. Click the Preview tab. Your report appears similar to the illustration.

Task Notes   In the Rate Sheet Report, we used image data stored in a database table. As we discussed earlier in the chapter, this allows the report to have a different image for each row in the table report object. The Next Day Delivery row, the Same Day Delivery row, and the Previous Day Delivery row each have their own unique image on the report.

Although the image data appears correctly on the report, the formatting leaves something to be desired. The images are all run together. The bottom of one image touches the top of the next. Also, the business needs specified that the long description of the service type should come below the name of that service type.

Let’s reformat our report a bit to see if we can improve the look of the images and include the long description in the report.

Rate Sheet Report, Task 2: Refine the Report Layout

  1. Click on the Layout tab.

  2. Click the leftmost table cell containing the image. This selects the image report item.

  3. Press DELETE to remove the image report item. The image report item has been removed from the table cell and, by default, a text box is created and put in its place.

  4. Drag a rectangle from the Toolbox and drop it on the leftmost table cell. A rectangle report item is now in the leftmost table cell.

  5. Drag an image from the Toolbox and drop it on the rectangle you just created. The Image Wizard appears.

  6. Click Next.

  7. Select Database, and then click Next.

  8. Select PriceSheetImage from the Image Field drop-down list.

  9. Select image/gif from the MIME Type drop-down list.

  10. Click Next.

  11. Click Finish to exit the Image Wizard.

  12. Modify the following properties of the image:

    Property

    Value

    BorderColor

    DarkBlue

    BorderStyle

    Double

    BorderWidth

    4pt

    Location: Left

    0.125in

    Location: Top

    0.125in

    Size: Width

    1.75in

    Size: Height

    1.375in

    Sizing

    Fit

  13. Click the center table cell. This selects the text box in the cell.

  14. Press DELETE to remove the text box.

  15. Drag a rectangle from the Toolbox and drop it on the center table cell. A rectangle report item is now in the center table cell.

  16. Drag the Description field from the Datasets window and drop it on the rectangle you just created.

  17. Modify the following properties of the text box that results:

    Property

    Value

    Color

    DarkBlue

    Font: FontSize

    14pt

    Font: FontWeight

    Bold

    Location: Left

    0.125in

    Location: Top

    0.125in

    Size: Width

    2.3in

    Size: Height

    0.375in

  18. Drag the LongDescription field from the Datasets window and drop it onto the same rectangle that contains the text box for the Description field. Modify the following properties of the text box that results:

    Property

    Value

    Location: Left

    0.125in

    Location: Top

    0.625in

    Size: Width

    2.3in

    Size: Height

    0.875in

  19. Click the Preview tab. Your report appears similar to the illustration.

  20. Click Save All in the toolbar.

Task Notes   Looking at our second attempt, you can see the rectangle saved the day. It solved both of our formatting problems. Having a rectangle in the leftmost cell allowed us to size the image so it did not fill the entire cell. We were able to adjust the size of the image because the Sizing property of the image was set to Fit. This, in turn, created some white space between the images, making them look much nicer. We even took advantage of that white space by adding a border to the image object.

The rectangle solved our second problem as well. The business needs specified the long description of the service type should appear below the name of the service type. We could accomplish this by putting a rectangle in the center table cell, and then putting two text boxes inside the rectangle.

The Rate Sheet Report is ready to go.

Related

Категории