Chart Your Course


In many cases, the best way to convey business intelligence is through business graphics. Bar charts, pie charts, and line graphs are very useful tools for giving meaning to endless volumes of data. They can quickly reveal trends and patterns to aid in data analysis. They compress lines upon lines of numbers into a format that can be understood in a moment.

In addition, charts can increase the reader’s interest in your information. A splash of color excites the reader. Whereas endless lines of black on white lull people to sleep, bars of red and blue and pie wedges of purple and green wake people up.

You create charts in Reporting Services using the chart report item. The chart report item is a data region like the table, matrix, and list report items. This means that the chart can process multiple records from a dataset. The table, matrix, and list report items allow you to place other report items in a row, a column, or a list area that is repeated for every record in the dataset. The chart, on the other hand, uses the records in a dataset to create bars, lines, or pie wedges. You cannot place other report items inside of a chart item.

In the next sections of this chapter, we will explore the many charting possibilities provided by the chart report item.

The Fuel Price Chart

Features Highlighted

  • Creating a report using the chart report item

  • Refining the look of the chart to best present the information

Business Need Galactic Delivery Services needs to analyze the fluctuations in the price of neutron fuel from month to month. The best way to perform this analysis is by creating a chart of the price over time. The user needs to be able to select the year from a drop-down list.

Task Overview

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

  2. Place a Chart Item on the Report and Populate It

  3. Refine the Chart

Fuel Price Chart, Task 1: Create the Chapter06 Project, a Shared Data Source, a New Report, and Two Datasets

  1. Create a new Reporting Services project called Chapter06 in the MSSQLRS folder. (If you need help with this task, see the section “The Transport List Report” in Chapter 5.)

  2. Create a shared data source called Galactic for the Galactic database. (Again, if you need help with this task, see the section “The Transport List Report” in Chapter 5.)

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

  4. Select <New Dateset…> from the Dataset drop-down list. The Dataset dialog box will appear.

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

  6. Galactic will be selected for the data source by default. Click OK. You will return to the Data tab, which now displays the Generic Query Designer.

  7. Type the following in the SQL pane:

    SELECT Description AS FuelType,      PriceStartDate,      Price FROM FuelPrice INNER JOIN Propulsion      ON FuelPrice.PropulsionID = Propulsion.PropulsionID WHERE (YEAR(PriceStartDate) = @Year) AND (Description = 'Neutron') ORDER BY FuelType, PriceStartDate 
  8. Click the Run button in the Generic Query Designer toolbar to run the query and make sure there are no errors. Correct any typos that may be detected. When the query is correct, the Define Query Parameters dialog box will appear. Enter 2003 for the @Year parameter and click OK.

  9. The business needs for the report specified that the user should select the year from a drop-down list. We will need to define a second dataset to populate this drop-down list. Select <New Dateset…> from the Dataset drop-down list. The Dataset dialog box will appear.

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

  11. Galactic will be selected for the data source by default. Type the following in the Query String area of the dialog box:

    SELECT DISTINCT YEAR(PriceStartDate) AS Year FROM FuelPrice
  12. Click OK. The Generic Query Designer will now display the Year dataset. Run the query to make sure it is correct. You will see a list of the distinct years that are in the FuelPrice table.

Task Notes We created two datasets in the FuelPriceChart report—one to populate the Year drop-down list and the other to provide data for the chart. Only one of these two datasets can be displayed on the Data tab at a time. You use the Dataset drop-down list to switch between the two datasets on the Data tab. In the same manner, the Fields window only shows the fields from one dataset at a time. You use the drop-down list at the top of the Fields window to change which dataset’s fields are being displayed.

You have undoubtedly noticed that both the datasets for this report were created by typing a query, either into the SQL pane of the Generic Query Designer or into the Query String area of the Dataset dialog box. The graphical tools of the Query Builder are very helpful if you are still learning the syntax of SELECT queries or if you are unfamiliar with the database you are querying. However, it is more efficient to simply type the query into the SQL pane or the Dataset dialog box. In addition, some complex queries must be typed in because they cannot be created through the Query Builder.

Throughout the remainder of this book, we will type our SELECT statements rather than create them using the Query Builder. This allows us to quickly create the necessary datasets and then concentrate on the aspects of report creation that are new and different in each report. As you create your own reports, use the interface—Query Builder or Generic Query Designer—with which you are most comfortable.

Fuel Price Chart, Task 2: Place a Chart Item on the Report and Populate It

  1. Switch to the Layout tab.

  2. Select Report | Report Parameters from the menu. The Report Parameters dialog box will appear.

  3. A report parameter called Year has been created to correspond to the @Year parameter from the FuelPrices dataset. Make sure the check box Allow Null Values is not checked.

  4. Select Available Values: From Query.

  5. In the Dataset drop-down list, select Years. Click OK to exit the Report Parameters dialog box.

  6. Click and drag the edges of the report layout area so that the layout area fills the available space on the screen.

  7. Place a chart report item on the report layout. The chart should cover almost the entire report layout because it will be the only item on the report. (If the report items are not showing to the left of the report layout, select View | Toolbox from the top menu.)

  8. The chart has three areas where you can drop fields: Drop Data Fields Here, Drop Series Fields Here, and Drop Category Fields Here. If these areas are not visible, double-click the center of the chart to display them. You may also need to scroll the layout window to see each of the drop areas.

    Note

    If you need to move the chart after you have placed it in the report layout, click the report layout so that the chart is not selected and then click the chart item so that it is selected but the three “Drop Fields Here” areas are not visible. Now you can click the edge of the chart item to drag it to the appropriate location. Click the chart item one more time to get the three “Drop Fields Here” areas to reappear.

  9. Select FuelPrices from the drop-down list in the Fields window, if it is not already selected. Drag the FuelType field and drop it on Drop Series Fields Here.

  10. Drag the PriceStartDate field and drop it on Drop Category Fields Here.

  11. Drag the Price field and drop it on Drop Data Fields Here.

  12. Right-click the chart and select Chart Type | Line | Simple Line from the context menu. The report layout should appear similar to the following illustration.

    click to expand

  13. Select the Preview tab. Select 2003 from the Year drop-down list and then click View Report. Your report will appear similar to this:

    click to expand

Task Notes You have now seen how easy it is to create a chart using the chart report item. Simply drag and drop the fields from your dataset onto the appropriate locations, select the type of chart you would like, and you have a functioning chart. In the next sections, we will explore ways to manipulate the properties of the chart to create more complex results.

The field you dropped in the Data Fields area (the Price field in this report) provides the values for the data points. The field you dropped in the Category Fields area (the PriceStartDate field in this report) provides the labels for the x-axis of the chart. This category field also groups the rows from the dataset into multiple categories. One entry is created on the x-axis for each category. In our Fuel Price Chart, we used the PriceStartDate field to create our categories. Because we are looking at data for a single year and because there is one record for each month, we get 12 distinct values for PriceStartDate in our dataset and 12 categories along the x-axis of our chart (one category for each month in the year we are charting).

One series of categories is created for each distinct value in the field you dropped in the Data Series area. Each series is usually charted in its own color; one series in green, one series in blue, and so on. The legend, located to the right in this chart, tells the reader which color has been assigned to each series. Our dataset contains only one fuel type, Neutron. Therefore, we get only one series of data points on our chart.

Now let’s use some of the properties of the chart to refine our results.

Fuel Price Chart, Task 3: Refine the Chart

  1. Select the Layout tab.

  2. Right-click the chart and select Properties from the context menu. The Chart Properties dialog box will appear.

  3. On the General tab, type Fuel Prices for the title.

    Caution

    Do not confuse Title with Name. Title contains the text that will appear above the report, whereas Name contains the name of the chart report item itself.

  4. Click the Style button (the paint brush and paint pail) next to Title.

  5. Set the following properties on the Style Properties dialog box:

    Property

    Value

    Size

    14pt

    Weight

    Bold

    Decoration

    Underline

  6. Click OK.

  7. Select the Data tab.

  8. Click Edit (next to Values). The Edit Chart Value dialog box will appear.

  9. Select the Appearance tab. Check the Show Markers check box and then select Diamond to place a diamond shape at each data point.

  10. Click OK to return to the Chart Properties dialog box.

  11. Select the X Axis tab. Set the following properties:

    Property

    Value

    Title

    =Parameters!Year.Value

    Format Code

    MMM

    Numeric or Time-Scale Values

    (checked)

  12. Select the Y Axis tab. Set the following properties:

    Property

    Value

    Title

    Price in Dollars

    Scale, Minimum

    0

    Scale, Maximum

    6

  13. Click OK.

  14. Select the Preview tab. Select 2003 from the Year drop-down list and then click View Report. Your report will appear similar to the illustration.

    click to expand

  15. Click Save All in the toolbar.

Task Notes As you have seen, the Chart Properties dialog box provides a great deal of control over the appearance and function of the chart. It is divided into seven different tabs, as shown in the following table, each controlling a different aspect of the chart.

Tab

Charting Aspect Controlled

General

Name of the report item, title, chart type, and color palette. (Chart Type can also be selected from the chart item context menu.)

Data

Dataset fields used to control the data values, categories, and series.

X Axis

Title and scale of the x-axis.

Y Axis

Title and scale of the y-axis.

Legend

If and where a legend containing information on the chart series is included.

3-D Effect

Whether the chart appears as a two-dimensional or three-dimensional object.

Filters

Filtering conditions applied to the data.

We will look at more of the settings available on the Chart Properties dialog box as we create additional charts in this chapter. You can also experiment with these settings to get exactly the chart format you need.

The format code “MMM” is a date formatting code. It causes the chart to use only the first three characters of the month name for the x-axis labels.

The Fuel Price Chart, Version 2

Features Highlighted

  • Creating a report using the chart report item with multiple series

  • Using the union operator in a SELECT statement

  • Using a WHERE clause to return records of one type or of all types

Business Need GDS now needs to analyze the fluctuations in the price of all fuel types from month to month. Allow the user to select a single fuel type or all fuel types from a drop-down list.

Task Overview

  1. Create a New Dataset for the Second Drop-down List and Revise the FuelPrices Dataset to Allow for Multiple Fuel Types

Fuel Price Chart, Version 2, Task 1: Create a New Dataset for the Second Drop-down List and Revise the FuelPrices Dataset to Allow for Multiple Fuel Types

  1. Reopen the Chapter06 project if it has been closed. Double-click the FuelPriceChart report in the Solution Explorer, if it does not open automatically.

  2. Select the Data tab.

  3. Choose <New Dataset…> from the Dataset drop-down list. The Dataset dialog box will appear.

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

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

  6. Type the following in the SQL pane:

    SELECT '_All_' AS FuelType UNION SELECT Description FROM Propulsion ORDER BY FuelType
  7. Run the query to make sure it is correct. You will see a list of the distinct fuel types in the FuelPrice table. There will also be a record with a value of “_All_”.

  8. Choose FuelPrices from the DATASET drop-down list.

  9. Change the SELECT statement to the following (the only change is in the second half of the WHERE clause):

    SELECT Description AS FuelType,      PriceStartDate,      Price FROM FuelPrice INNER JOIN Propulsion      ON FuelPrice.PropulsionID = Propulsion.PropulsionID WHERE (YEAR(PriceStartDate) = @Year)       AND ((Description = @PropulsionType)         OR (@PropulsionType = '_All_')) ORDER BY FuelType, PriceStartDate
  10. Run the query to make sure it is correct. The Define Query parameters dialog box will appear. Enter 2003 for the @Year parameter, _All_ for the @PropulsionType parameter, and click OK. Clear the query results.

  11. Select the Layout tab. (A report parameter is not created for PropulsionType until you leave the Data tab.)

  12. Select Report | Report Parameters from the main menu. The Report Parameters dialog box appears.

  13. A report parameter called PropulsionType has been created to correspond to the @PropulsionType parameter from the FuelPrices dataset. Select this parameter in the Parameters list box.

  14. Select Available Values: From Query.

  15. In the Dataset drop-down list, select FuelTypes. Click OK to exit the Report Parameters dialog box.

  16. Select the Preview tab. Select 2003 from the Year drop-down list, select _All_ from the PropulsionType drop-down list, and then click View Report. Your report will appear similar to the illustration.

    click to expand

  17. Click Save All in the toolbar.

Task Notes The query that creates the FuelTypes dataset is actually two SELECT statements combined to produce one result set. The first SELECT statement returns a single row with the constant value “_All_” in the FuelType column. The underscores are placed around the word “All” to make sure it sorts to the top of the list. The second SELECT statement returns a row for each record in the Propulsion table. Each row has a single column called Description. The two result sets are unified into a single result set by the UNION operator in between the two SELECT statements.

When result sets are “unioned,” the names of the columns in the result set are taken from the first SELECT statement in the union. That is why the FuelTypes dataset has a single column called FuelType rather than Description. When SELECT statements are unioned, only the last SELECT statement can have an ORDER BY clause. This ORDER BY clause is used to sort the entire result set after it has been unified into a single result set.

The UNION operator can be used with any two SELECT statements as long as the following is true:

  • The result set from each SELECT statement has the same number of columns.

  • The corresponding columns in each result set have the same data type.

In fact, the UNION can be used to combine any number of SELECT statements into a unified result set as long as these two conditions hold true for all the SELECT statements in the UNION.

The field we chose for our series field, FuelType, will have three distinct values in the dataset when the “_All_” option is chosen for the PropulsionType report parameter. When this option is selected, the chart will contain three series of data points. The legend tells the reader that the green series represents the data for antimatter, the blue series represents the data for fusion, and the purple series represents the data for neutron. Each series contains 12 categories. Three series multiplied by 12 categories means that we have 36 data points on our chart.

It is important that you understand categories and series as we get into more complex charting. If you are a bit fuzzy on this, review the first part of this chapter before moving on.

The Business Type Distribution Chart

Features Highlighted

  1. Creating a report using a pie chart

  2. Using the Data Label property

  3. Changing the chart palette

  4. Using the 3-D effect

Business Need The Galactic Delivery Services marketing department needs to analyze what types of businesses are using GDS for their delivery services. This information should be presented as a pie chart.

Task Overview

  1. Create a New Report and a Dataset

  2. Place a Chart Item on the Report and Populate It

Business Type Distribution Chart, Task I: Create a New Report and a Dataset

  1. Reopen the Chapter06 project if it has been closed. Close the FuelPriceChart report.

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

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

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

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

  6. Type the following in the SQL pane:

    SELECT Name AS CustomerName,      Description AS BusinessType FROM Customer INNER JOIN CustomerToBusinessTypeLink      ON Customer.CustomerNumber           = CustomerToBusinessTypeLink.CustomerNumber INNER JOIN BusinessType      ON CustomerToBusinessTypeLink.BusinessTypeCode           = BusinessType.BusinessTypeCode
  7. Run the query to make sure there are no errors. Correct any typos that may be detected.

Task Notes The CustomerBusinessTypes dataset simply contains a list of customer names and their corresponding business type. Remember that some customers are linked to more than one business type. That means that some of the customers will appear in the list more than once.

This dataset will be used to populate a pie chart in the next task. The BusinessType field will be used to create the categories for the pie chart. The items in the CustomerName field will be counted to determine how many customers are in each category.

Business Type Distribution Chart, Task 2: Place a Chart Item on the Report and Populate It

  1. Switch to the Layout tab then click and drag the edges of the report layout area so that the layout area fills the available space on the screen.

  2. Place a chart report item on the report layout. The chart should cover almost the entire report layout because it will be the only item on the report.

  3. Right-click the chart and select Chart Type | Pie | Simple Pie from the context menu.

  4. Drag the CustomerName field from the Fields window and drop it on Drop Data Fields Here.

  5. Drag the BusinessType field and drop it on Drop Category Fields Here.

  6. Right-click the chart and select Properties from the context menu. The Chart Properties dialog box will appear.

  7. On the General tab, type Customer Business Types for the title.

  8. Click the Style button (next to the Title). The Style Properties dialog box appears.

  9. Set the following properties on the Style Properties dialog box:

    Property

    Value

    Size

    14pt

    Weight

    Bold

    Decoration

    Underline

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

  11. Select Semi-Transparent from the Palette drop-down list.

  12. Select the Data tab.

  13. Click Edit (next to Values). The Edit Chart Value dialog box will appear.

  14. Select the Point Labels tab. Check the Show Point Labels check box.

  15. Click the Expression button (the button with fx on it) next to the Data Label entry area. The Edit Expression dialog box will appear.

  16. Type the following in the Expression area:

    =Fields!BusinessType.Value & vbcrlf &         "(" & CSTR(Count(Fields!CustomerName.Value)) & ")" 
  17. Click OK to exit the Edit Expression dialog box.

  18. Click Label Style. The Style Properties dialog box will appear.

  19. Select Bold from the Weight drop-down list.

  20. Click OK to exit the Style Properties dialog box.

  21. Click OK to exit the Edit Chart Value dialog box.

  22. Select the Legend tab.

  23. Uncheck Show Legend.

  24. Select the 3-D Effect tab.

  25. Check Display Chart with 3-D Visual Effect.

  26. Click OK to exit the Chart Properties dialog box.

  27. Select the Preview tab. Your report will appear similar to the illustration.

    click to expand

  28. Click Save All in the toolbar.

Task Notes By default, the pie chart uses a legend to the side of the chart to provide labels for each wedge in the pie. In the Business Type Distribution chart, we changed this default behavior. We used the Show Legend check box on the Legend tab to turn off the legend. We used the Data Label entry area on the Point Label tab to put a label for each wedge right on the pie chart itself.

We are actually using the data label to do double duty for us. It is displaying both the business type for each pie wedge and the number of companies of that business type. This provides the reader with both a graphical representation of the data, in the form of the pie wedge, and the underlying numbers for additional reference. The expression in the Data Label area concatenates the business type and the count of the number of customers with a carriage return/linefeed (vbcrlf) in between. The carriage return/linefeed causes the business type and the count to each appear on its own line.

In this chart, we are also using the 3-D effect. The 3-D effect can help to add interest to a chart by taking a flat graphic and lifting it off the page.

Now we will try one more chart before looking at incorporating images in reports.

The Days in Maintenance Chart

Features Highlighted

  • Creating a report using a 3-D, stacked, column chart

Business Need The Galactic Delivery Services transport maintenance department is looking to compare the total maintenance downtime for each year. They would also like to know how that maintenance time is distributed among the different transport types. They would like a graph showing the number of days that each type of transport spent “in for repairs.” This information should be presented as a 3-D, stacked column chart. The underlying data should be displayed as a label on each column in the chart.

Task Overview

  1. Create a New Report, Create a Dataset, Place a Chart Item on the Report, and Populate It

Days in Maintenance Chart, Task 1: Create a New Report, Create a Dataset, Place a Chart Item on the Report, and Populate It

  1. Reopen the Chapter06 project if it has been closed. Close the BusinessTypeDistribution report.

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

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

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

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

  6. Type the following in the SQL pane:

    SELECT Description AS PropulsionType,      YEAR(BeginWorkDate) AS Year,      DATEDIFF(dd, BeginWorkDate, EndWorkDate) AS DaysInMaint FROM Repair INNER JOIN Transport      ON Repair.TransportNumber = Transport.TransportNumber INNER JOIN TransportType      ON Transport.TransportTypeID = TransportType.TransportTypeID ORDER BY PropulsionType, Year

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

  8. Switch to the Layout tab.

  9. Set the following properties of the Body:

    Property

    Value

    Width

    7.5in

    Height

    4.375in

  10. Place a chart report item on the report layout. The chart should cover almost the entire report layout because it will be the only item on the report.

  11. Drag the PropulsionType field from the Fields window and drop it on Drop Series Fields Here.

  12. Drag the Year field and drop it on Drop Category Fields Here.

  13. Drag the DaysInMaint field from the Fields window and drop it on Drop Data Fields Here.

    Note

    The following uses a more abbreviated format for specifying which items need to be changed for the chart. A table will be provided for each dialog box or for each tab within a dialog box. Simply navigate to the appropriate dialog box or tab and change the items specified. Navigation hints are provided with some of the tables for certain dialog boxes and tabs that are a little harder to find.

  14. Right-click the chart and select Properties from the context menu. The Chart Properties dialog box will appear. Set the chart properties, as follows, in the General tab:

    Property

    Value

    Title

    Days in Maintenance

    Chart sub-type

    Stacked column chart

    Click the Style button (paintbrush and bucket) next to the Title on the General tab to access the Style Properties dialog box. Then set the following properties for Report Title:

    Property

    Value

    Size

    14pt

    Weight

    Bold

    Decoration

    Underline

    Click the Edit button next to the Values list on the Data tab to access the Edit Chart Value dialog box. Set the following property on the Values tab:

    Property

    Value

    Series Label =Sum(Fields!DaysInMaint.Value)

    Click the Point Labels tab in the Edit Chart Value dialog box and set these properties:

    Property

    Value

    Show point labels

    (checked)

    Data label

    =Sum(Fields!DaysInMaint.Value)

    Click the Edit button next to the Category Groups list on the Data tab to access the Grouping and Sorting Properties dialog box. Set the following property for Category Groups:

    Property

    Value

    Label

    = “Total Maint. Hours - ” & Sum(Fields!DaysInMaint.Value)

    & vbcrlf & vbcrlf & Fields!Year.Value

    Click the Edit button next to the Series Groups list on the Data tab to access the Grouping and Sorting Properties dialog box. Set the following property for Series Groups:

    Property

    Value

    Label = Fields!PropulsionType.Value & “ Total Maint. Hours (All Years)”

    Click the X Axis tab and set the following property:

    Property

    Value

    Title Year

    Click the Style button (paintbrush and bucket) next to Title on the X Axis

    tab to access the Style Properties dialog box. Set the following properties

    for X-Axis Title:

    Property

    Value

    Size

    12pt

    Weight

    Bold

    Click the Y Axis tab and set the following property:

    Property

    Value

    Title

    =“Days in” & vbcrlf & “Maintenance” & vbcrlf & “Hanger”

    Click the Style button (paintbrush and bucket) next to Title on the Y Axis tab to access the Style Properties dialog box. Set the following properties for Y-Axis Title:

    Property

    Value

    Size

    12pt

    Weight

    Bold

    Click the Legend tab and set the following property:

    Property

    Value

    Position

    (Select the square in the center of the bottom row of the Position selector.)

  15. After you have made all these modifications, click OK to exit the Chart Properties dialog box.

  16. Select the Preview tab. Your report will appear similar to this:

    click to expand

  17. Click Save All in the toolbar.

Task Notes The stacked column chart is a very good choice to fulfill the business needs for this report, because it can graphically illustrate two different pieces of information at the same time. Each colored section of the graph shows the number of maintenance days for a given propulsion type. In addition, the combined height of the three sections of the column shows the fluctuations in the total maintenance days from year to year.

Above and beyond the graphical information that is provided in the chart, several additional pieces of information are provided numerically on the chart. This includes the category labels along the x-axis, the legend at the bottom of the graph, and the detail data displayed right on the column sections themselves. The values on the columns are the result of the expression entered for the data label on the Point labels tab.

Tip

If the legend does not display completely, you may need to make the chart area wider on the Layout tab.

The expression entered for the data label uses the SUM() function to add up the values from the DaysInMaint field. It may seem that this sum should give us the total values from the DaysInMaint field for every row in the dataset. The reason this does not occur is because of the scope in which this expression is evaluated. The scope sets boundaries on which rows from the dataset are used with a given expression.

The data label expression operates at the innermost scope in the chart. That means that expressions in the data label are evaluated using only those rows that come from both the current category and the current series. For example, let's look at the column section for Star Lifters for the year 2000. This column section is part of the Star Lifter series. It is also part of the year 2000 category. When the report is evaluating the data label expression to put a label on the “Star Lifter/year 2000” column section, it uses only those rows in the result set for the Star Lifters in the year 2000. Using this scope, the report calculates the sum of DaysInMaint for Star Lifters in the year 2000 as 68 days.

Next, let's consider the summary data that appears in the column labels along the x-axis. These entries are the result of the expression entered for the label in the category groups. This expression also uses the SUM() function to add up the values from the DaysInMaint column. However, it calculates different totals because it is operating in a different scope.

In this case, the calculations are being done in the category scope, which means that the expression for the label in the category group is evaluated using all the records from the current category. For example, let's look at the column label for the year 2000 column. This column is part of the year 2000 category. When the report is evaluating the label expression to put a label below this column, it uses all the rows in the result set for the year 2000. The propulsion type of each row does not make a difference, because it is not part of this scope. Using the year 2000 category scope, the report calculates the sum of DaysInMaint for the year 2000 as 211 days.

Finally, we come to the summary data that appears in the legend below the chart. These entries are the result of two expressions that are concatenated when the report is created. The first expression comes from the label in the series groups. The second expression comes from the Series label on the Values tab. If you leave the label in the series group empty, a generic series label (Series 1, Series 2, and so on) will be created by default. If the Series label on the Values tab is left blank, it is ignored. When both the label in the series group and the Series label on the Values tab contain expressions, the results of the two expressions are concatenated with a dash in between.

The expression in the Series label on the Values tab of our current chart also uses the SUM() function to add up the values from the DaysInMaint column. Once again, it is working in a different scope, so it comes up with different results. Here, the calculations are being done in the series scope. That means that the expressions for both the Series label on the Values tab and the label in the series group are evaluated using all the records from the current series. For example, let's look at the entry in the legend for the Star Lifter series. When the report is evaluating the Series label expression from the Values tab and the label expression from the series group to put a label in the legend, it uses all the rows in the result set for Star Lifters. The year of each row does not make a difference, because it is not part of this scope. Using the Star Lifter series scope, the report calculates the sum of DaysInMaint for the Star Lifters as 332 days.

In a number of the expressions used in this chart, we are concatenating together several strings to create the labels we need. This is being done using the Visual Basic string concatenation operator (&). You may notice that several of the fields being concatenated are numeric rather than string fields. The reason these concatenations work is that the & operator will automatically convert numeric values to strings. In this way, we can take “Total Maint. Hours - ” and concatenate it with 211 to get the first line of the year 2000 column label. The 211 is actually converted to “211” and then concatenated with the rest of the string.

The final noteworthy item on this report is the expression used to create the label on the y-axis. In order to have this label fit nicely along the y-axis, we used our old friend the carriage return/linefeed to split the label onto three lines. Since the text is rotated 90 degrees, the first line of the label is farthest from the y-axis, the next line is to the right of the first line, and the last line is to the right of the other two.




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