SSRS

SSRSS

SSR

SQL Server Reporting Services Charts

SQL Server Reporting Services Charts

This Article presents general information, best practices, and tips for designing charts within Microsoft SQL Server Reporting Services reports। It provides an overview of some Reporting Services features, answers common chart design and feature questions, and includes advanced examples of how to design better charts.

Click here to download the associated sample code, GetMoreChartsSamples.exe.
Click here to download the Word version of this article, MoreSSRSCharts.doc.
Contents
IntroductionData PreparationChart LabelsExample Charts and ReportsConclusion
Introduction
This white paper covers how to design charts in Microsoft SQL Server Reporting Services reports. The paper is divided into several sections and references specific report examples; these are included in the sample project download.
The first section, Data Preparation, covers specific information, tips, and insights about preparing the data. The second section, Chart Labels, tells you how to apply label settings to enhance your charts and control visual appearance and effects.
Example Charts and Reports shows specific and sometimes advanced examples of how to get more out of the built-in chart functionality of SQL Server Reporting Services. Some of these examples require careful study of the provided step-by-step instructions. Fully working sample reports are included for your convenience. The sample reports are based on the SQL Server 2005 AdventureWorks sample database and the Northwind sample database.
The information on data preparation and chart labels helps you to better understand the examples. You may find it useful to occasionally jump back to the specific chart label topics covered in the first sections while studying the samples.
Data Preparation
A chart provides a way to visualize data. It can more effectively convey information than can lengthy lists of data. Spending time carefully preparing and understanding your data before you create a chart will help you design your charts quickly and efficiently. Reporting Services chart data is organized into three areas: values, category groups, and series groups. For detailed information, see Working with Chart Data Regions) in the SQL Server Reporting Services section of SQL Server 2005 Books Online.
A chart is very similar to a matrix:
A chart category group is equivalent to a matrix column group.
A chart series group is equivalent to a matrix row group.
A chart value is equivalent to a static matrix row group.
A chart data value or data point is equivalent to a matrix cell.
Keep the following points in mind when preparing the dataset query for a chart:
Chart values are shown along the numeric y-axis. Make sure the fields used as values have numeric data types (as opposed to strings that contain formatted numbers).
X-axis values are determined based on the chart categories group values or the group labels if group labels are explicitly defined. The x-axis supports two modes (discussed in detail in X-Axis Category Mode and Scalar Mode). If you want to use the x-axis scalar mode, make sure that the fields and/or expressions used for the category group expression evaluate to a numeric data type or to a DateTime object.
You can have as many charts in your report as you need. A chart, like any other data region such as a matrix or table, is bound to one particular dataset. You can use joins and union in the dataset query to include all the needed data in the dataset.
If a chart is placed in a table group header or group footer, or in a matrix cell, the data passed to the chart control is restricted to the subset of data that constitutes that group. A chart cannot be placed in the detail row of a table, as only one data row is referenced.
A chart with too much data (for example, several thousand data points) may be difficult to interpret unless you use a scatter chart to show the distribution of values and clusters of data points. Consider pre-aggregating data in the dataset query if a detailed level of data granularity is not necessary or not useful.
Chart Labels
This section covers the following chart label topics. You may find it useful to occasionally jump back to the topics covered in this section when you study the samples in the next section.
X-axis category mode and scalar mode
This section explains the significant differences between the two x-axis modes. You can use the CategoryAxisSettings sample report as a starting point for experiments.
Axis labels
The axis labels section dives deep into the details of applying label settings and how they impact the visual appearance of the chart at run time.
Data point labels and legend labels
This section tells you how to improve your charts by adding data point labels and legend labels.
X-Axis Category Mode and Scalar Mode
The x-axis has two modes. The mode is set by using the Numeric or time-scale values option on the X Axis tab in the Chart Properties dialog box.
Category mode
The category group expression values determine the individual categories for the x-axis. Labels are shown for only the actual categories present in the data. The sort order within a group and explicit sort expressions are important in category mode, as the chart control will not reorder categories. The format code defined for the x-axis is applied only if the group expression (or the group label expression if explicitly defined) evaluates to a nonstring object.
Grouping spans for categories are shown if you have multiple levels of category groupings.
Scalar mode
The x-axis value range is determined by the minimum and maximum category group expression values. Consequently, the group expression values must be numeric or DateTime values in order to compare and sort. Gaps in the data (for example, you use a DateTime category grouping and you only have data for July and September) are shown on the x-axis, as the categories are scaled either to a numeric or a DateTime axis. Only one category grouping is allowed in scalar mode.
The charts in Figures 1A and 2A show the same four weeks of order data.
Figure 1A. X-axis in category axis mode and grouping spans
Figure 2A. X-axis in scalar mode
The Category Axis Mode in Figure 1A
Since there is no order data for the weekend days (Saturday, Sunday) in the underlying dataset, the categories are not present in Figure 1A. The example uses two category groupings, as shown in Figure 1B. The inner group expression uses =Day(Fields!OrderDate.Value) to group per day. The outer group expression uses =Month(Fields!OrderDate.Value) to group per month.
Note The outer group label expression is defined as
=MonthName(Month(Fields!OrderDate.Value))
, which uses the month name as the label for the grouping span.

Figure 1B. X-axis in category axis mode with multiple category groupings and spans (Click on the image for a larger picture)
The settings for the x-axis properties are shown in Figure 1C. In category mode, the semantics of minimum, maximum, and intervals are based on the category index. By not specifying any explicit axis properties, one label is shown for every category of data.

Figure 1C. X-axis settings for category axis mode (Click on the image for a larger picture)
The Scalar Axis Mode in Figure 2A
An x-axis in scalar mode shows either numeric or DateTime values. The x-axis covers the full range of values between the minimum and the maximum values. Consequently, Figure 2A contains gaps for the weekend days because they do not have order data.
Only one category grouping is allowed when using the x-axis in scalar mode. The value of the category grouping must evaluate to a numeric or DateTime value. The formatting of the x-axis labels is determined by the format string setting on the x-axis—in this example, MMM dd. The settings for the x-axis properties are shown in Figure 2B.

Figure 2B. X-axis settings in scalar mode (Click on the image for a larger picture)
For more information on numeric and DateTime format strings, see the following pages in the .NET Framework Developer's Guide on the Microsoft Developer Network (MSDN):
Standard Numeric Format Strings
Custom Numeric Format Strings
Standard DateTime Format Strings
Custom DateTime Format Strings
Axis Labels
Y-axis labels are always based on numeric values. If explicit axis settings are not specified, the y-axis uses the auto-scale mode as follows:
The y-axis minimum value is determined based on the lowest y-value of all data points. If that minimum data value is not an integer value but a double value (such as 3.75) and side margins are turned off, you may see y-axis labels that are not rounded to full numbers (for example, with an interval of one: 3.75, 4.75, 5.75, and so on).
The y-axis maximum value is automatically determined based on the highest y-value of all data points unless the maximum is explicitly specified.
The y-axis major interval is automatically determined based on the data values (in Figure 3 the automatic major interval is 20).
The y-axis minor interval divides the major interval into segments (in Figure 3 the automatic minor interval would be 4; hence 20 / 4 = 5 minor interval segments constitute one major interval segment).
Since y-axis values are always numeric, you can directly apply numeric format strings. The setting is applied to all generated y-axis labels.

Figure 3. Y-axis settings (Click on the image for a larger picture)
X-Axis Modes
As discussed in the previous section, the X-axis has several modes. Depending on the mode, different options for formatting are available and the axis settings (Minimum, Maximum, Cross at, and so on) may be interpreted differently. Following are descriptions of the different formatting options:
Scalar mode based on numeric category group values
With these settings, the x-axis is very similar to the y-axis. Axis settings such as Minimum, Maximum, Cross at, Major interval, and Minor interval are interpreted as integer or double values.
Since the x-axis values are numeric, you can directly apply numeric format strings.
Scalar mode based on DateTime category group values
Axis Minimum: If the axis minimum is set to a constant (such as 2005) or an expression with an integer result (for example, =2005), the value is interpreted as the first day in that year (such as Jan 1st, 2005).
Axis Maximum: An integer setting is interpreted as the last day in that year (such as Dec 31st 2005).
Axis Cross at: The setting is interpreted as the middle of the year.
Major interval and Minor interval: The interval settings are interpreted as days (equivalent to the OADate format). For example, 5 means an interval of 5 days and 0.5 means an interval of half a day (12 hours).
For the label formatting, you can directly apply standard DateTime format strings.
Category mode (the Numeric or time-scale values option is not selected)
Based on the category group expression values, the chart control matches categories across multiple series (for example, data for the January category in the 2006 series will be in the same cluster as data for the January category in the 2007 series).
Format string settings on the X Axis tab have no effect unless the category group expression (or label expression as in Figure 4) evaluates to a numeric or DateTime data type. Often when you use category mode, the category group expression evaluates to a string object, hence a format code applied later has no effect. You can either add or change the category group label expression or apply the formatting directly through the label expression, as shown in Figure 4.
Note In category mode, the semantics of minimum, maximum, and intervals are based on the category index. For instance, setting the x-axis minimum to 2 means the first category of data will not be shown. Setting the major interval to 5 means that labels are shown only for every fifth category on the x-axis. This can be useful if the x-axis is crowded with many categories (and labels) and the underlying semantics of the categories are actually numeric.
Note Reporting Services 2005 also allows expressions in all the input fields shown in the X Axis and Y Axis tabs: Title, Minimum, Maximum, Major interval, Minor interval, and so on.
Figure 4. If the label expression is explicitly defined, the result is shown on the x-axis (category axis) instead of the result of the group expression.
Axis Label Formatting Q&A
Question (Y-axis): How can I enforce "nice" integer-based labels on the y-axis?
Answer: If no axis settings are specified, the chart control automatically determines the values based on the data point y-values. If the minimum/maximum values of the data points are not integers, the y-axis labels may use double values.
If, however, at least one of the axis settings (for example, Minimum or Cross at) is explicitly specified as an integer value by the report author, the chart control rounds the automatically detected values to the nearest integer value and then shows "nice" labels. For instance, you could dynamically set the y-axis minimum value and apply rounding like this: =Floor(Min(Fields!Freight.Value)).
Question (Scalar x-axis): Turning on Numeric or time-scale values results in the chart not showing any data points at runtime. What is wrong?
Answer: Most likely the category group expression evaluates to a string instead of to numeric values. Change the category group expression accordingly. If you don't want to change the query to fetch scalar data values instead of string values, you can also perform the type conversion in the report by using Microsoft Visual Basic functions such as CInt(), CDbl(), or CDate().
Question (Category x-axis): If the number of categories increases, the x-axis becomes crowded and eventually axis labels are no longer drawn. How can I control the number of labels in the category mode of the x-axis?
Answer: The chart control tries to automatically position x-axis labels to avoid overlapping the label text. By default, every category has a label on the x-axis. You can explicitly set the x-axis major interval setting to override this default behavior. For instance, setting the major interval to 5 shows labels for every fifth category only.
Question (X-axis): How does automatic x-axis label positioning work?
Answer: Currently, built-in Reporting Services charts only allow automatic positioning in order to avoid overlapping the x-axis labels. The label direction (horizontal/vertical) of the axis labels depends on the label string sizes and the available space. X-axis labels are either shown horizontally in one line, horizontally in multiple lines with line breaks, or vertically. Showing x-axis labels at an angle, or explicit manual control over individual x-axis label positions is currently not supported.
Note There are several third-party chart add-ins that enable more control over axis labels. These add-ins can be installed on top of Reporting Services 2005.
Data Point Labels and Legend Labels
Data point labels can be used to specifically point out certain values (such as the overall minimum or maximum value) among all visible data points in the chart.
To turn on data point labels, edit the chart value in the Chart Properties dialog box. This opens the Edit Chart Values dialog box, which contains a Point Labels tab with the Show point labels option.
Positioning Data Labels
When you turn on data point labels, by default one label per data point is shown. The data point label is positioned automatically to avoid overlapping the labels. If data point labels overlap, the chart control moves overlapping labels into a free space of the chart plot area (and draws outlines to connect data point labels to data point values). If too many labels overlap, the chart control removes individual data point labels until there is enough space to fit the remaining labels without overlapping.
Besides automatic positioning, you can use explicit manual label positioning (top, left, center, and so on). However, depending on the data values and the length and size of the data point labels, this may result in overlapping labels.
By default, the data point label shows the y-value of the data point. It is also possible to specify an explicit data point label expression and numeric or DateTime format strings to customize the label. In general, you would perform data point label calculations using expressions similar to those used to calculate the y-value in the data point value expression. For instance, to show only data point labels if the relative contribution of that segment is larger than 5 percent of the total amount, you could use a data point label expression similar to the code in the following procedure.
1. Use the following expression for the data point label expression:
Copy Code=Code.GetLabel(Sum(Fields!Sales.Value), Sum(Fields!Sales.Value,"SalesChart"))
2. Open the Report Properties dialog box and click the Code tab. Add the following GetLabel(…) custom code function in the Custom code option.
Copy CodePublic Function GetLabel(ByVal currentValue As Double, ByVal totalValue As Double) As String If currentValue / totalValue < 0.05 Then Return " " Else Return Format(currentValue / totalValue, "P1") End IfEnd Function
Explanation of the Code
The GetLabel() function takes two arguments. The first argument provides the current value for that particular data point. The second argument provides the calculation of the total amount. The function calculates the relative percentage. If it is lower than 5 percent (0.05), a string with a blank is returned.
Note Returning a null or an empty string shows the auto-generated default label. If the relative percentage is at least 5 percent, a percentage formatted string (format string: P1) is returned.
An example for applying this kind of formatting can be found in the PiePercentage sample report included with this white paper.
Pie and Doughnut Charts Data Label Positions
For pie and doughnut charts there are only two data point label positions: inside (set the data point label position to Auto or Center), and outside (any other label position). An example of outside labels is shown in Figure 5 (and in the PieSimplePercentage sample report).
Figure 5. Data point labels outside the chart in a pie chart
The position of pie segment labels can be specified as shown in Figure 6.
Figure 6. Setting data point labels outside the chart in a pie or doughnut chart by selecting any position except the center one
Legend Labels
In general, legend labels are determined based on dynamic series group values (or labels if explicitly specified on the group) and the names of (static series) values. Since the chart is essentially a flattened representation of grouping hierarchies, legend labels are generated based on that hierarchy.
For example, if a chart has two series groupings (the outer defined as OrderYear, the inner as OrderQuarter) and only one chart value (for example, Actual), the legend labels are generated by concatenating the group values and chart values as shown in table 1.
Table 1
OrderYear label
OrderQuarter label
Chart value series label
GENERATED LEGEND LABEL
2006
Q1
Actual
2006 – Q1 – Actual
2006
Q2
Actual
2006 – Q2 – Actual
Suppose we add a second chart value called Budget. With the same data as the previous example, the generated labels look like those in table 2.
Table 2
OrderYear label
OrderQuarter label
Chart value series label
GENERATED LEGEND LABEL
2006
Q1
Actual
2006 – Q1 – Actual
2006
Q1
Budget
2006 – Q1 – Budget
2006
Q2
Actual
2006 – Q2 – Actual
2006
Q2
Budget
2006 – Q2 – Budget
Note You can hide individual inner levels in the hierarchy by setting the group label expression to return an empty string (
=""
). This removes that group level from the generated legend labels.
Empty Data Points and Labels
The following situation may sound familiar. You build a chart with one data series, data point labels are turned on, and the chart looks great. You decide to add a dynamic series group so that the chart shows multiple data series. Suddenly the chart has additional labels (for empty data points).
Empty data points occur when the underlying dataset does not contain data values for every series/category combination. The chart is essentially equivalent to a (sparse) matrix with empty cells.
You can remove labels for empty data points. Instead of turning on the data point labels and using the default label, use the approach shown in the EmptyDataPointLabels sample report included with this white paper (see also Figure 7). Following is sample code that does this.
1. Use the Count(…) function to determine how many underlying dataset rows are aggregated for this data point. If the count equals zero, this is an empty data point. Pass in the count to a custom code function with the actual label value:
2. =Code.GetLabel(Avg(Fields!UnitsInStock.Value), Count(Fields!UnitsInStock.Value))
3. Open the Report Properties dialog box and click the Code tab. Add the following GetLabel(…) custom code function in the Custom code option.
Public Function GetLabel(ByVal datapointValue As Double, ByVal count As Integer) As String If count = 0 Then Return " " Else Return Format(datapointValue, "N1") End IfEnd Function
Figure 7. Sample report that has empty data points without labels
Data Point Label Formatting Q&A
Question: What's the purpose of the gray lines (called outlines) near data point labels if the chart is crowded with data points and labels?
Answer: If the data point label position is set to Auto, the chart control moves labels into areas of free space to avoid overlapping data point labels. The outlines connect the data point label with the data point location.
You can use the manual positioning to avoid this. By using expressions, you can dynamically hide most data point labels by providing an evaluation result of a string with a blank (=" "). Otherwise, the default label is shown if data point labels are turned on.
Question: Is it possible to use Dundas keywords for label formatting?
Answer: Yes, you can use built-in Dundas keywords for the data point label. However, in general it is recommended that you not combine RDL expressions and Dundas keywords at the same time (RDL expressions are evaluated first, Dundas functions are interpreted by the chart control later). Table 3 contains a list of useful Dundas keywords.
Table 3
Dundas keyword
Replaced with
#VALX
X-value of the data point
#VAL
Y-value of the data point
#VALY, #VALY2, #VALY3, etc.
First y-value, second y-value, third y-value, and so on
#INDEX
Data point index within series
#TOTAL
Total of all y-values in the current series
#VALY{C2}
Y-value of the data point formatted with the C2 format string (currency formatting)
Example Charts and Reports
This section contains examples of creating different types of charts and reports. You may find it useful to occasionally jump back to the chart label topics covered in the previous sections when you study these examples. Following are the examples covered in this section.
Column and Line Hybrid Charts
Describes combinational charts in general and the SalesCostTarget sample report.
Pareto Charts
Implements a Pareto visualization for a chart (ParetoChart sample report).
Moving Average Calculations
Calculation and visualization of time-series trends in charts (MovingAverage sample report).
Custom Chart Color Palettes and Legends
How to customize the colors in your chart (CustomColorPalette sample report).
Pie and Doughnut Charts
Specific information to keep in mind when working with pie or doughnut charts.
Adding Chart Data Tables
Shows how to link aggregated chart data to detailed data (PiePercentage sample report).
Scatter and Bubble Charts
Important tips for designing scatter and bubble charts (BubbleChart, StepFunctionChart).
Table Inline Charts
Maybe you don't need complex chart visualizations or you have to deal with an unknown amount of data at runtime but still want useful and nice visualizations. This section provides ways to achieve this goal (TableInlineCharts).
Chart Extensibility and Creating Charts Manually
Discusses options if the built-in charts are not sufficient.
Sample reports, based on the SQL Server 2005 AdventureWorks sample database and the Northwind sample database, are included in the download file with this white paper.
Column and Line Hybrid Charts
Charts that show several data series as columns and other data series as lines are often used to show overall trends, target values, or to further analyze the data within the chart. This section provides general information on how to design this kind of chart in Reporting Services.
To create a column and line hybrid chart:
1. Add a chart to the report, setting the chart type to Column.
2. Design the chart by adding category groups and/or series groups and data values.
3. For the data values to be shown as lines, perform the following steps in Report Designer:
a. Open the Chart Properties dialog box.
b. Click the Data tab.
c. Select the data value to show as line and click Edit.
d. In the Edit Chart Value dialog box, click the Appearance tab and select Plot data as line (see Figure 8).
Figure 8. Drawing a data series as line in a column chart
To add a constant or dynamic target value to the chart:
1. Design the chart.
2. On the Data tab in the Chart Properties dialog box, add a new data value (for example, Target).
3. Set the target value (the example in Figure 9 uses a constant target value of 100000 across all categories). Make sure to use an expression starting with = (equals). Otherwise, the value is not interpreted as a numeric value.
Figure 9. Adding a target value
The SalesCostTarget sample report (see Figure 10) uses this approach to add a simple sales target line to the chart.

Figure 10. Target value (red line) (Click on the image for a larger picture)
Note Since lines for line chart series are drawn by connecting data points of multiple categories, the line is only visible if the category grouping has at least two distinct group instance values at run time.
Note If the chart contains one or more series groups, the target data value is repeated for every series group instance. This may be useful if you have specific target values for every group instance.
If you only want one global target value for all series, you can dynamically set the target data value like this:
=iif(Fields!.Value = First(Fields!.Value, ), , Nothing)
A specific expression example could look like this:=iif(Fields!Year.Value = First(Fields!Year.Value, "SalesChart"), 100000, Nothing)
Pareto Charts
A Pareto chart summarizes and displays the relative importance of differences between groups of data. Pareto charts distinguish the "vital few" from the "useful many." A Pareto chart can also be defined as a column chart with the columns sorted in descending order to identify the largest opportunity for improvement.
While Pareto charts are currently not directly supported in the built-in Reporting Services charts, you can create a Pareto chart by using Reporting Services 2005 features and writing some code. This section provides an in-depth explanation of the ParetoChart sample report included with this white paper.
Following is the scenario description for the ParetoChart sample report.
The SQL Server 2005 AdventureWorks database contains data about sales employees. In particular, we are interested in analyzing the following information about our sales employees:
What does the Pareto analysis look like based on the sales employees with the biggest previous year's bonuses? (See the orange line in Figure 11.)
Which sales employees received the biggest bonuses last year and how does this compare to their current year's total sales? (See the blue and green columns in Figure 11.)
Are there significant changes in sales performance based on the previous year's bonus vs. the current year's sales? While this can be answered by comparing the blue and green columns, the huge gap between the orange and the red Pareto lines in Figure 11 make it more obvious.
For a particular sales employee, we would like to drill into the past and present sales performance and analyze historical trends over several years of data.
This is achieved by adding drill-through actions on the sales data values (green columns in Figure 11) to provide a detailed analysis on individual salesperson's data with a trend analysis. The drill-through report (MovingAverage sample report) is discussed in detail in the next section.

Figure 11. Pareto chart sample report (Click on the image for a larger picture)
To build the Pareto chart report
1. Define the query to retrieve the necessary sales data.
The query pre-sorts the data for each salesperson based on the bonus values. It does not retrieve the individual sale orders because they are not needed in this report. This also keeps the dataset size small.
2. Design the overall chart layout.
Add a column chart to the report. To analyze the data per salesperson, add a category group based on the salesperson (group by =Fields!SalesPersonID.Value). For the category label, show the first name and the last name of the salesperson. This is achieved by setting the category group label expression to the following.
=Fields!FirstName.Value & " " & Fields!LastName.Value
3. Prepare the chart for the Pareto calculations.
We use the RunningValue(…) function for the calculations.
Note The RunningValue function is only supported in charts starting with Reporting Services 2005.
Similar to a matrix, the RunningValue function needs an explicit grouping scope in a chart to determine if the running value should run across all categories of a particular data series (essentially the direction is horizontal) or if the running value should run across all data series of a particular category (less common).
For the Pareto calculations, we need to use a RunningValue function that takes a series group name as its "reset" scope (and thereby runs across all categories). Since we don't have yet a series group for this particular chart, we can just add a fake series group based on a constant value (such as 1).
Group expression: 1
Label expression: ="" (to hide the series label from the generated legend labels)
This results in one data series and provides an explicit series scope name at the same time.
4. Add the bonus and sales data values as columns.
We can add data values for Bonus and for SalesYTD to the chart by dragging the corresponding dataset fields onto the chart.
Note This example uses the Sum() aggregate function for bonus and sales values.
We want to place the legend in the top left corner inside the chart plot area. Hence, we want the y-axis to scale so that the maximum data point value that is shown in the chart never exceeds 75 percent of the total height of the y-axis.
We achieve this by scaling the bonus calculations by a factor of 75 percent:
=0.75 * Sum(Fields!Bonus.Value) / Max(Fields!Bonus.Value, "SeriesGroup")
We do the same for the sales calculation:
=0.75 * Sum(Fields!SalesYTD.Value) / Max(Fields!SalesYTD.Value, "SeriesGroup")
5. Set up the y-axis as a percentage axis.
In the previous step, we set up the bonus and sales calculations as percentage calculations (relative amount compared to the maximum value).
Setting the format string of the y-axis to P0 applies percentage formatting (the actual y-axis will be scaled between 0.0 and 1.0). To get nice intervals, we set the y-axis major interval to 0.2 to set up 20 percent intervals.
6. Add the bonus and sales Pareto calculations as lines.
The RunningValue() function provides a cumulative calculation until it resets. We want it to never reset. Since we didn't have an explicit series group initially, we added one in step 3.
The Pareto calculations are the cumulative sum divided by the total values. For the bonus Pareto calculation, we use the following expression. =RunningValue(Fields!Bonus.Value, Sum, "SeriesGroup") / Sum(Fields!Bonus.Value, "SeriesGroup")
We do the same for the sales Pareto calculation: =RunningValue(Fields!SalesYTD.Value, Sum, "SeriesGroup") / Sum(Fields!SalesYTD.Value, "SeriesGroup")
7. Add a drill-through action on the sales data value.
To enable a drill-through analysis for the sales data of an individual salesperson, in the MovingAverage sample report, we add a drill-through action (see Figure 12) on the sales data point. Since we are only interested in one particular salesperson, we set the SalesPersonID drill-through parameter to the value of the current category group. In this example that is the current salesperson ID: =Fields!SalesPersonID.Value.
Figure 12. Adding a drill-through action
8. Finish the chart by adding formatting, data point labels, and legend.
Moving Average Calculations
A moving average is one of a family of similar statistical techniques used to analyze time series data. A moving average series can be calculated for any time series.
While moving average calculations are not directly supported through the built-in Reporting Services charts, you can often write code to perform this kind of calculation. This section provides an in-depth explanation of the MovingAverage sample report.
The sample report is related to the scenario described in the previous section about Pareto charts. For a particular sales employee we would like to analyze the past and present sales performance and analyze historical trends over several years of data. Moving averages are used to smooth out short-term fluctuations, thus highlighting long-term trends or cycles.
The MovingAverage sample shows how to calculate a simple moving average (the unweighted mean of the previous n data points). In this particular example, we use the sales data for the previous three months. See Figure 13.

Figure 13. Moving average calculation (Click on the image for a larger picture)
To build the report
1. Define the query so that it retrieves the necessary sales detail data.
The query is parameterized to retrieve the data for only one particular salesperson. The query parameter is set based on a report parameter, which is populated with a dataset based a list of valid values.
2. Design the overall chart layout.
Add a column chart to the report. For the x-axis, use category mode so that you can have two grouping levels: grouped by months at the inner level and grouped by year with grouping spans at the outer level. The month group uses the following explicit group label expression to format the month as an abbreviated month name. =Format(Fields!OrderDate.Value,"MMM")
3. Prepare the chart for the moving average calculation.
As in step 3 of the Pareto calculations, we use the RunningValue function. The moving average should not reset across the categories, hence we add a series grouping based on the SalesPersonID. Since the query is parameterized based on the salesperson, there will be only one salesperson series. The series group label expression is set to =Fields!FullName.Value so the chart legend items will contain the salesperson's full name.
4. Add the sales calculation as columns.
Drag the TotalDue dataset field onto the chart values drop zone to add a sales data value based on the Sum() aggregation. To concatenate the word "Sales" to the series group label (the group label is the salesperson's full name as defined in step 3), we explicitly set the data value label to Sales.
5. Add the moving average custom code functions.
The following table shows an example of a moving average calculation by using a queue. The Queue Contents column shows the current queue contents for a particular month. The last column of the table shows a RunningValue calculation based on aggregating added and removed items from the queue. The code sample below the table shows an implementation of this algorithm.
Table 4
Month
Sales
Moving average (2 mon.)
Regular running value
Queue contents
Removed queue value
Running value based on queue
Jan
20
n/a
20
20
n/a
0
Feb
10
15
35
20, 10
n/a
0+ 15 = 15
Mar
24
17
59
10, 24
-20
15+ (24-20) /2 = 17
Apr
16
20
75
24, 16
-10
17+ (16-10) /2 = 20
May
12
14
87
16, 12
-24
20+ (12-24) /2 = 14
…






To implement the queue-based RunningValue, add the following code to the Custom code section on the Code tab in the Report Properties dialog box. Private queueLength As Integer = 3 Private queueSum As Double = 0 Private queueFull As Boolean = False Private queue As New System.Collections.Generic.Queue(Of Double) Public Function MovingQueue(ByVal currentValue As Double) As Object Dim removedValue As Double = 0 If queue.Count >= queueLength Then removedValue = queue.Dequeue() End If queueSum += currentValue queueSum -= removedValue queue.Enqueue(currentValue) If queue.Count < queueLength Then Return Nothing ElseIf queue.Count = queueLength And queueFull = False Then queueFull = True Return queueSum / queueLength Else Return (currentValue - removedValue) / queueLength End If End Function
6. Add the moving average sales value as a line.
The data value calculation for the moving average will use the RunningValue function over the values returned by the MovingQueue custom code function. The MovingQueue function calculates the adjustment values for the cumulative RunningValue calculation. This is done by using the following code.
=RunningValue(Code.MovingQueue(Fields!TotalDue.Value), Sum, "SalesPerson")
Note To perform multiple moving average calculations within one chart, you must either determine a way to reset the queue at the end of a series, or use multiple queues. For example, you could use a hash table of queues that are indexed based on the series group value, and passed to the MovingQueue function as an additional argument.
Note A chart cannot span multiple pages. Therefore, declaring the variables as private nonshared is valid.
To use the moving average calculation in another data region (such as a list, table, or matrix) that spans multiple pages, the variables must be declared as shared (that is, static) in order to maintain state across pagination. However, because this uses static variables, if two people run the report at the same moment, there's a slim chance that one will smash the other's variable state. If you need to be 100 percent certain that you avoid this, you can make each of the shared variables a hash table based on the requesting user's ID (=Globals!UserID).
Custom Chart Color Palettes and Legends
Charts use built-in predefined color palettes with 10 to 16 distinct colors. Starting with Reporting Services 2000 Service Pack 1 (SP1), you can override the default colors. To specify color values as constant or expression-based values, click the Series Style button on the appearance properties for the data value in the Edit Chart Value dialog box. You could use this, for instance, to highlight values based on a certain condition such as a minimum or maximum value within the current series.
Note If you don't want to define a full custom color palette, you can override the color for individual data points. Use an expression that either returns a specific color value (in order to override) or returns "Nothing," which will pick the current color from the underlying built-in color palette.
For example, you want to highlight in red all data point values with negative y-values. For all the other data points, you want to apply the default colors. To do this, select Edit the data value and click the Appearance tab. Click the Series Style button, which opens the Style Properties dialog box. Click the Fill tab. Enter the following expression in the fill color style properties.=iif(Sum(Fields!Sales.Value - Fields!Cost.Value) < 0, "Red", Nothing))
Note If you set the fill color to a constant value, this color is applied to all the data points for that data series.
The chart legend uses color fields to match the legend items to the visible data points. The legend can only show one color field per legend item (data series); hence, it shows the color of the first data point within that series. Remember this when you use expressions to dynamically determine the color of individual data points within a series; the legend item always shows the actual color of the first data point.
While the legend built into Reporting Services charts is easy to use, it lacks flexibility. For example, the legend consumes space within the chart. If the legend is placed outside the plot area and the legend grows, the chart plot area size shrinks accordingly.
You can get more flexibility and control over the legend by generating your own custom legend by using a table or a matrix. The easiest way to synchronize the colors in the chart with your custom legend is to define your own custom chart color palette. The CustomColorPalette sample report implements a custom color palette and a custom legend. See Figure 14.
Figure 14. A bar chart report with a custom color palette and a custom legend
To build a custom color palette
1. Define the chart series groups and category groups.
By default, every chart data series has a color assigned to it. This color is based on the selected chart palette. In this example, we want to override these colors based on the series group instance values.
2. Define the custom color palette and add custom code.
The colorPalette variable stores the definition of our custom color palette, which has 15 distinct colors. The count variable keeps track of the total count of distinct grouping values in order to wrap around once we exceed the number of distinct colors in the custom color palette. The mapping hash table keeps track of the mapping between grouping values and colors. This ensures that all data points within the same data series have the same color. Later it is used to synchronize the custom legend colors with the chart colors. The following code goes into the custom code window of the report. Private colorPalette As String() = {"Green", "Blue", "Red", "Orange", "Aqua", "Teal", "Gold", "RoyalBlue", "MistyRose", "LightGreen", "LemonChiffon", "LightSteelBlue", "#F1E7D6", "#E16C56", "#CFBA9B"} Private count As Integer = 0 Private mapping As New System.Collections.Hashtable() Public Function GetColor(ByVal groupingValue As String) As String If mapping.ContainsKey(groupingValue) Then Return mapping(groupingValue) End If Dim c As String = colorPalette(count Mod colorPalette.Length) count = count + 1 mapping.Add(groupingValue, c) Return c End Function
3. Call the GetColor() function to assign colors to data points.
The GetColor function is called from the fill color style properties. Edit the data value to open the Edit Chart Value dialog box and click the Appearance tab (Figure 15). Click the Series Style button and click the Fill tab. The current series group value is passed as an argument to the GetColor function, which is needed to map the internal group instance value to the color value.
Figure 15. Specifying explicit data series styles
Note If there are multiple chart series groups, you can concatenate the series group values to create a unique identifier that is used inside the GetColor function. The following code is an example. =Code.GetColor(Fields!Country.Value & "" & Fields!City.Value)
4. Add a chart legend.
You can use the built-in chart legend. Or, turn off the built-in chart legend and follow the steps in the next procedure to build your own custom chart legend with a table or a matrix data region.
To build a custom legend
1. Add a table data region to the report.
Place the table next to the chart and bind it to the same dataset as the chart.
2. Mirror the chart grouping structure in the table by adding table groups.
If the chart uses series groupings, add them to the table by adding table groups that are based on the same group expression as the one in the chart series groupings. Then add chart category groupings (if present) as inner table groups.
In general, if the chart has m series grouping and n category grouping, you add m+n table groups for your custom legend.
For the individual table groups, make sure to show only the group header (which will contain the legend description). Also, remove the table detail row unless you want to use the table detail rows to simulate a chart data table.
3. Design the custom legend.
Add a rectangle for the color field of the custom legend. For example, you might add it to the first table column. As indicated in step 2, you should only have group header rows in the table. The rectangle goes into the innermost group header level.
Set the rectangle BackgroundColor property to the equivalent expression used on the chart data point's fill color. In the most trivial case, the expression would just contain one grouping value as in the following code. =Code.GetColor(Fields!Country.Value)
For the legend text, use either the same expression as in the category and series group/label expressions, or experiment until you achieve the legend description text that you want.
Pie and Doughnut Charts
The Data Point Labels and Legend Labels section describes how to set up inside and outside data point labels for pie slices. This section covers a few additional properties of pie and doughnut charts.
Unlike other chart types, a pie or doughnut chart has only one "dimension of groupings" (that is, one data series). To have two dimensions in pie and doughnut charts, the charts would need to be stacked on top of each other.
Note During report publishing, Reporting Services automatically converts the series groups of a pie or doughnut chart into category groups to show the data as one data series.
Pie charts are often used to show the relative percentages of data points. In general, the percentage value (which can be used for example, for displaying as a data point label) can be calculated by dividing the data point value expression by the total sum for the entire chart. The following code is an example of this.=Sum(Fields!Sales.Value) / Sum(Fields!Sales.Value, "SalesChart")
This expression is added as a data label expression as shown in Figure 16.
Figure 16. Data label percentage calculation
By default, pie slices have black borders that increase the visibility of the slices. However, as shown in Figure 17 in the next section, you can override the border with specific settings for the data point appearance. If you want the border color to match the pie slice color, you must use a custom color palette as discussed in the previous section. Using a custom color palette you can set the same color for the data point (pie slice) as for the border by making a call into a custom code function that assigns colors based on the category grouping value.
Adding Chart Data Tables
You might want to add detail data to your chart. Adding data directly into the chart may make the chart more difficult to interpret. Instead, add the information in a data table.
A chart is a very effective way to visualize the overall distribution of values and to identify interesting areas (for example, very small and very large values). A reader might want to analyze the information in more detail based on the underlying detail data.
Reporting Services provides three ways of adding interactivity on chart data points as follows:
Use a jump to report action to show detail data by drilling to another report based on the current series/category group values by adding these as drill-through parameter values.
Use a jump to bookmark action to jump to a section (such as a data table) within the same report.
Use a jump to URL action to generate a hyperlink to an external navigation target outside the report.
Figure 17 shows a simplified version of the PiePercentage sample report included with this white paper.

Figure 17. Pie chart with custom formatting, custom color palette, and a data table (Click on the image for a larger picture)
To build a data table
1. Create a pie chart with a custom color palette.
The pie chart in Figure 17 has two category groupings. The outer grouping is based on the order year. The inner grouping is based on the product category. The custom color palette is defined as described in step 2 of the steps for building a custom color palette.
Since there are two category groupings, we use the following expression that generates a composite key, which is passed into the GetColor function. =Code.GetColor(Fields!OrderYear.Value & Fields!ProdCat.Value)
If we apply the same GetColor function call to the data point fill color property and to the data point border color property, the pie slices won't show the default black border.
2. Show data point labels for only those pie slices that represent a share of the pie that is greater than 4 percent of the total pie.
To achieve this, add the following function to the report custom code section. The important part is to return a string with a blank label for the pie slices that do not have labels—otherwise the chart control will show the default label for that slice. (The default label is the underlying data point value.) Public Function GetLabel(ByVal currentValue As Double, ByVal totalValue As Double) As String If currentValue / totalValue < 0.04 Then Return " " Else Return Format(currentValue / totalValue, "P1") End IfEnd Function
The label expression for the data point calls the GetLabel function to calculate the percentage value/label. =Code.GetLabel(Sum(Fields!Sales.Value), Sum(Fields!Sales.Value, "SalesChart"))
3. Create a data table (matrix) for the chart.
The chart only shows aggregated sales data per year and product category. In the data matrix, we would like to group the data in the same way.
We add a matrix data region and bind it to the same dataset as the chart. We then add the OrderYear and ProdCat fields as row groups on the matrix, and aggregate the Sales values in the matrix cell. To add subtotals, right-click the group header cells in the matrix and select Subtotal from the context menu.
Note Clicking the small green triangle in a matrix heading selects the Subtotal properties where you can explicitly set style properties for the subtotal cells. For example, you could show subtotal cells with a different background color to visually distinguish them from other data.
The underlying dataset provides finer data granularity than what is shown in the chart. We can take advantage of that in the data matrix by adding another (inner) row group based on the subcategory, and the quarter and month as matrix column groups.
4. Add toggle drilldowns to the data matrix.
To add a drilldown effect for a particular group, right-click the group header and edit the group properties. On the Visibility tab in the Group Properties dialog box, select the report item name that should toggle the visibility of the current group. Typically you select the report item name of the text box in the parent group. If there is no parent group, you could add a text box to the matrix corner and use it to toggle the outermost grouping level in the matrix.
The Initial visibility option, which sets the initial toggle state is also set on the group Visibility tab. Setting this option to Visible means that it is initially expanded and Hidden means it is initially collapsed.
5. Adjust the initial toggle image and the toggle state.
If the initial toggle visibility state of a group is set to Visible, the toggle image on the report item that toggles this group may show a plus sign (+). To display the minus sign (-) toggle image instead, right-click on the report item that toggles the group; this is usually the text box in the parent group's header. Select Properties from the context menu. In the Textbox Properties dialog box, select the Visibility tab and set the initial appearance of the toggle image. Since the group toggle visibility is set to Visible, the initial appearance of the text box should be set to Expanded (-) as shown in Figure 18.
Figure 18. Adjusting the initial appearance of the toggle image
6. Add bookmarks to connect the chart and the data matrix (optional).
Enabling bookmark navigation from chart data points to the data matrix involves two steps. First, define the bookmark IDs within the data matrix. Second, define the jump to bookmark navigation action on the chart data point.
To add bookmarks in the data matrix, right-click the group header of the order year row group and select Properties from the context menu. In the Textbox Properties dialog box, select the Navigation tab and define the Bookmark ID expression by using an expression that generates a string: =CStr(Fields!OrderYear.Value). This provides our bookmark jump targets.
Open the Chart Properties dialog box and edit the data point properties. On the Action tab, select Jump to bookmark, and use the same expression as was used for the bookmark ID.
Scatter and Bubble Charts
Scatter and bubble charts are different from other chart types because, instead of using the category grouping values as x-values, they have explicit x-values for the data points. Consequently, the data can be grouped (and aggregated) into a different category than the value that is shown on the x-axis. For example, to show last year's sales for individual salespersons along the x-axis, you would not want to aggregate y-values if two salespersons have identical x-values. The BubbleChart sample report (Figure 19) has a category grouping based on the salesperson so that it aggregates sales data per salesperson. However, the value of last year's sales is shown on the x-axis.
Note It is very important that you understand the distinction between the x-value property of the data point and the grouping of the chart data based on series and category groups. If you design a scatter or bubble chart and it only shows one data point in preview, but you expected many different points to be shown, the most likely explanation is that no category groups or series groups have been defined. If no category groups and series groups are defined, the underlying dataset rows are aggregated up into one data point with a specific x-value and y-value. In some cases, you could define a category or series group expression that is identical to the x-value expression, but in a scatter or bubble chart you rather want to add a category or series grouping based on what the data represents. If the chart aggregates sales values for each salesperson, the category or series grouping should be based on the salesperson's ID or name as shown in the BubbleChart sample report.
Figure 19. Bubble chart with series data grouped by salesperson
Another useful scenario for scatter charts is in the StepFunctionChart sample report. The scatter line chart uses a category grouping based on measurement IDs. For certain days, shown as x-values, there are multiple measurements in the dataset, shown along the y-axis, resulting in vertical steps.
Figure 20. Scatter chart based on, for example, sensor measurements
Table Inline Charts
Sometimes you have an unknown amount of data at runtime and you want to dynamically "grow" the charts in size. One way to achieve this is to embed a chart in another data region's group. For instance, you could use a list data region with a detail group based on the following expression.
=Ceiling(RowNumber(Nothing)/20)
This results in one group for every twenty detail rows. Embedding a chart inside that list creates chart instances at runtime for every twenty rows.
To create an inline bar visualization of data, you can use two different implementation approaches:
Embed an image that has dynamically calculated right padding values (see Figure 21). Adjusting the right padding of a static image achieves a bar chart effect as the result of dynamically stretching the image.
Embed a bar chart that has a calculated y-axis maximum value (see Figure 23).
Both approaches are implemented side-by-side in the TableInlineCharts sample report.
Figure 21. Table inline chart simulated by an embedded image and dynamic padding
To implement inline visualizations based on embedded images
1. Design an embedded image that will be used for the "bar" visualization.
A simple gradient image usually looks nice. Add the image as an embedded image to the report.
2. Add a table to group and visualize the data.
Design the grouping structure of the table. You can either place the image visualization into a group header or into the table detail row. Add an embedded image in a new table column. Select the embedded image from step 1 as source of the image.
3. Calculate the right (or left) image padding.
Create an expression for the right (or left) padding property of the image report item. The expression divides the numeric value to be visualized by the maximum value. Then multiply the relative size by the width of the table column as defined in step 2. You may also want to consider using the Math.Min or Math.Max functions to restrict the padding to a certain range if needed.
In the TableInlineChart sample report, the table column has a width of 2 inches. For the padding calculation we use the points measurement unit; there are 72 points per inch. Hence, assuming we set the left padding to 0 points, there is a 144-point range for the Right Padding option. The following code sets up the padding. =144 * (1.0 - Fields!UnitsInStock.Value / Max(Fields!UnitsInStock.Value, "DataSet1")) & "pt"
Figure 22. Dynamically calculating the image right padding size
4. Set the image sizing property to Fit.
The padding defined in the previous step determines how much space is available for the image to stretch, thus generating the bar visualization effect.
One drawback of the embedded image approach is that the image might stretch if you use fonts or thin lines in the image. Using a chart for the inline visualization (Figure 23) provides more control over the visualization and often better results.
Figure 23. Table inline chart based on a chart embedded in the table group header
To use a chart for inline visualization
1. Add a table to group the data.
Design the grouping structure of the table. Keep in mind that charts can only be placed into the table header or footer or into the group header or footer.
2. Add a new table column and place the chart into a group header.
To maximize the chart plot area for the bar visualization apply the following chart properties settings.
General settings: Set to bar chart, turn off the legend.
Data: Bind the chart to the same dataset as the parent table; add a data point value based on the value to be visualized.
X-axis: Turn off axis labels, turn off gridlines, set the tick marks to None.
Y-axis: Turn off axis labels, set the tick marks to None, set the maximum to the maximum value calculated in the scope of the containing table or the dataset. This is needed to achieve correct bar sizes; otherwise every chart instance would just auto-scale the y-axis based on the data values for that particular group.
3. Refine the chart visualization (optional).
Experiment with chart plot area style settings, y-axis major gridlines, 3D effects, or dynamic color settings to further refine the inline chart visualization.
Chart Extensibility and Creating Charts Manually
This white paper provides information on tweaking chart settings and extending the existing chart capabilities with expressions and custom code functions. Beyond that, there are additional approaches to integrate more advanced chart functionality into Reporting Services:
Integrate chart images generated by custom assemblies.
Implement chart extensibility based on the new custom report item feature of Reporting Services 2005.
Use third-party Reporting Services 2005 add-in components, which provide enhanced chart functionality. These are based on the custom report item feature.
To integrate images generated by custom assemblies
1. Design and implement a custom assembly to generate images.
The custom assembly must retrieve the data on its own, take care of grouping/sorting the data, and generating the chart image.
Note The custom assembly has to return the image as byte[]. It cannot return it as a System.Drawing.Image. You can often convert a System.Drawing.Image object with code similar to the following. System.IO.MemoryStream renderedImage = new MemoryStream(); myChart.Save(renderedImage); renderedImage.Position = 0; return renderedImage.ToArray();
2. Add an image to the report.
Set the image type to Database. If the generated image is a bitmap in the PNG image format, set the image mimetype property to "image/png." For the image value property, use an expression like the following. =MyCustomAssembly.GenerateChart()
3. View the report in Report Designer Preview view to verify that the report is working correctly.
Note In a default configuration, custom assemblies run in FullTrust in Report Designer preview. Hence, operations that require certain code access security permissions (such as file input/output, data provide access, etc.) are automatically granted these permissions in FullTrust.
4. Deploy the custom assembly on a report server.
Make sure that the security policy configuration of the report server grants sufficient permissions to your custom assembly at runtime; otherwise the image generation will fail. For more information, see Understanding Code Access Security in Reporting Services in SQL Server 2005 Books Online.
Charts Based on a CustomReportItem Compared to Custom Assemblies
There are several benefits of using the custom report item feature over the custom assembly approach. First, you can build your own design-time support component that will integrate directly into Report Designer. Second, at runtime you can take advantage of the Reporting Services processing engine to retrieve the data and apply grouping/sorting and filters. The CustomReportItem runtime control will access the processed data and generate a chart image with an interactive image map and associated actions.
Carefully study and review the documentation and samples before building your own CustomReportItem. More information is available at the following sites:
Custom Report Item Sample in SQL Server 2005 Books Online
The information on custom report items on Chris Hays's Reporting Services Sleazy Hacks Weblog on MSDN.
Conclusion
This white paper provides tips and insights on the charting functionality of Reporting Services that have not been previously covered in books, other articles, papers, or presentations. It also covers how and when to use certain functionality options.
The white paper presents and thoroughly explains a set of report examples that demonstrate how to get more out of the built-in Reporting Services charts when working in particular scenarios. For instance, it describes how to add a Pareto analysis or calculate moving averages.
Finally, the paper briefly discusses Reporting Services extensibility that you can use to integrate external (charting) functionality into your reports.

Custom Assemblies in SQL SERVER Reporting Services(SSRS)

Create custom assembly
1) Open Visual Studio 2008 and create a new Project.
2) The New Project window will open; Select the Visual C# node; from the right pane select Class Library. Complete the following:
In the Name field enter "rsCustomAssembly"
In the Location field enter "C:\Visual Studio 2008\Projects"
In the Solution field enter "rsCustomAssembly"
Click OK when you are done
3) Delete the default class.cs file created. When prompted click OK.
4) Add a new class by right clicking on the solution name and selecting Add >> Class...
5) Name the new class Utilities.
6) Enter the code below into the class file and save it.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace rsCustomAssembly
{
public class Utilities
{
///

/// Sample concatenate string method; not intended for production use.
/// Note: the string object already provides a concatenate method - string.concat(object o)
///

///
///
///
public static string Concat(string str1, string str2)
{
return str str1+ " " + str2;
}

public static string HelloReportingServices()
{
return "Reporting Services";
}
}
}
Your screen should look similar

7) From the top menubar you will find Build; Click this and select rsCustomAssembly from the list. The bottom status bar should presenet Build succeeded. If not, check your code again to make sure it follows the description above.
Copy the assembly to the Visual Studio Private assemblies and Report Server bin
You must copy you assembly (.dll) to the report designer and report server folders before you can use the assembly in Reporting Services. You can find your assembly by locating the bin folder in your project. For example: C:\Visual Studio 2008\Projects\rsCustomAssembly\rsCustomAssembly\bin\Debug
Note: the location is dependent on your install
For Report Designer: C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies
For Report Server: C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin
You have completed with this step.
Create a new Report
This step we will perform the following:
Create a new report
Reference the custom assembly
Add a new textbox to the report designer with a custom expression
1) Open BIDS and create a new project, like you did in 1) under "Create custom assembly".
2) Under the Project Type pane, select Business Intelligence Projects; from within the Templates pane, select Report Server Project and complete the following:
For the Name field enter SampleReport
For the Location field enter C:\Visual Studio 2008\Projects
For the Solution field enter SampleReport
Click OK
3) From the Solution Explorer, right click on the Reports folder and select Add >> New Item
The Add New Item window will open

4) From within the Templates pane, select Report and leave the default name of Report1.rdl
5) Click Add
Reference the Assembly
1) From the top menu bar select Report >> Report Properties
Note: if you don't see the Report menu item, try placing your cursor anywhere in the design view; you should then see the menu item.
2) The Report Properties window opens. From within the left pane, select References.
3) Click Add under "Add or remove assemblies".
4) Click the ellipsis right of the text box. The Add Reference window will open.
5) Select the Browse tab and navigate to the Report Server bin location, where you copied your .dll, as described earlier in this article under Copy the assembly to the Visual Studio Private assemblies and Report Server bin
you should see a similar value in the reference text box
6) Click OK to save and close the Report Properties window.
Add Expression and run report
1) Drag and drop a Text Box from the Toolbox pane on the left of your design pane. Strech the text box so you have some room for text.
2) Right click the text box you just added and select Expression
3) For the expression value enter :
=rsCustomAssembly.Utilities.Concat("Reporting", "Services")
4) Click OK to save and close the Expression window.
5) To the right of the Design tab, click Preview. You should see the following:
Save your work. You have completed referencing a custom assembly in SQL Server Reporting

SQL SERVER Reporting Services: Create and Call a Custom Assembly

namespace MyAssembly
{
public class Colors
{
public string PercentageToColor(double percentage)
{
string returnValue = string.Empty;

if (percentage < 20)
{
returnValue = "red";
}
else if (percentage < 80)
{
returnValue = "blue";
}
else
{
returnValue = "green";
}

return returnValue;
}
}
}
Once you’ve compiled your class you need to copy the resulting assembly to the directory in which it is accessible from within your report:
· To use it in the report designer, you need to copy it to: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
· To make it available for reports that have been deployed to the report server, you need to copy it to: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

Before you can access your custom assembly you have to reference the assembly: Open the report properties (Menu: Report – Report Properties) and select the References tab.
Browse to your assembly and define a Class Name and an Instance Name. (The Class Name and the Instance Name or only for non-static methods). Make sure to prefix your class name with the assembly name.
Now you can call the methods in your assembly from your report, using an expression:
· To call a static method: =..
· To call an instance method: =Code..
So in our example this would be: =Code.TestColor.PercentageToColor(Fields!Percentage.Value)
That's it! You've just created and called a custom assembly from your report...
In one of my future articles I will explain how to pass parameters to the class constructor and I will also tell something about SSRS and .Net Permissions.

Tablix Data Region in SSRS

Tablix Data Region in SSRS The Tablix data region is a generalized layout report item that displays report data in cells that are organized into rows and columns. Report data can be detail data as it is retrieved from the data source, or aggregated detail data organized into groups that you specify. Each Tablix cell can contain any report item, including a text box, an image, or another data region such as a Tablix region, chart, or gauge. To add multiple report items to a cell, first add a rectangle, which acts as a container, and then add the report items to the rectangle. The table, matrix, and list data regions are represented in the Toolbox by templates for the underlying Tablix data region. When you add one of these templates to a report, you are actually adding a Tablix data region that is optimized for a specific data layout. By default, a table template displays detail data in a grid layout, a matrix displays group data in a grid layout, and a list displays detail data in a free-form layout. By default, each Tablix cell in a table or matrix contains a text box. The cell in a list contains a rectangle. You can replace a default report item with a different report item, for example, an image. As you define groups for a table, matrix, or list, Report Designer adds rows and columns to the Tablix data region on which to display grouped data. Understanding the Tablix data region requires understanding the following: 1. The difference between detail data and grouped data. 2. Groups, which are organized as members of group hierarchies on the horizontal axis as row groups and on the vertical axis as column groups. 3. The purpose of Tablix cells in the four areas of a Tablix data region: the body, the row group headers, the column group headers, and the corner. 4. Static and dynamic rows and columns and how they relate to groups. Once you understand these concepts, you can recognize the structure that Report Designer adds for you when you add templates and create groups, and modify the structure to suit your own needs. Report Designer provides multiple visual indicators to help you recognized Tablix data region structure. For more information, see Understanding Tablix Data Region Cells, Rows, and Columns। Understanding Detail and Grouped Data Detail data is all the data from a report dataset as it comes back from the data source. Detail data is essentially what you see in the query designer results pane when you run a dataset query. The actual detail data includes calculated fields that you create, and is restricted by filters set on the dataset, data region, and details group. You display detail data on a detail row by using a simple expression such as [Quantity]. When the report runs, the detail row repeats once for each row in the query results at run time. Grouped data is detail data that is organized by a value that you specify in the group definition, for example, [SalesOrder]. You display grouped data on group rows and columns by using simple expressions that aggregate the grouped data, for example, [Sum(Quantity)]. For more information, see Understanding Groups (Reporting Services)।
Understanding Group Hierarchies Groups are organized as members of group hierarchies. Row group and column group hierarchies are identical structures on different axes. Think of row groups as expanding down the page and column groups as expanding across the page. A tree structure represents nested row and column groups that have a parent/child relationship, for example, a category with subcategories. The parent group is the root of the tree and child groups are its branches. Groups can also have an independent, adjacent relationship, for example, sales by territory and sales by year. Multiple unrelated tree hierarchies are called a forest. In a Tablix data region, row groups and columns groups are each represented as an independent forest. For more information, see Understanding Groups (Reporting Services)।
Understanding Tablix Data Region Areas A Tablix data region has possible four areas for cells: the Tablix corner, the Tablix row group hierarchy, the Tablix column group hierarchy, or the Tablix body. The Tablix body always exists. The other areas are optional. Cells in the Tablix body area display detail and group data. Cells in the Row Groups area are created automatically when you create a row group. These are row group header cells and display row group instance values by default. For example, when you group by [SalesOrder], group instance values are the individual sales orders that you are grouping by. Cells in the Column Groups area are created automatically when you create a column group. These are column group header cells and display column group instance values default. For example, when you group by [Year], group instance values are the individual years that you are grouping by. Cells in the Tablix corner area are created automatically when you have both row groups and column groups defined. Cells in this area can display labels, or you can merge the cells and create a title. For more information, see Understanding Tablix Data Region Areas।
Understanding Static and Dynamic Rows and Columns A Tablix data region organizes cells in rows and columns that are associated with groups. Because group structures for row groups and columns are identical, this discussion uses row groups. You can apply the same concepts to column groups. A row is either a static or dynamic. A static row is not associated to a group. When the report runs, a static row renders once. Table headers and footers are static rows. Static rows display labels and totals. Cells in a static row are scoped to the data region. A dynamic row is associated to one or more groups. A dynamic row renders once for every unique group value for the innermost group. Cells in a dynamic row are scoped to the innermost row group and column group to which the cell belongs. Dynamic detail rows are associated with the Details group that is automatically created when you add a table or list to the design surface. By definition, the Details group is the innermost group for a Tablix data region. Cells in detail rows display detail data. Dynamic group rows are created when you add a row group or column group to an existing Tablix data region. Cells in dynamic group rows display aggregated values for the default scope. The Add Total feature automatically creates a row outside the current group on which to display values that are scoped to the group. You can also add static and dynamic rows manually. Visual indicators help you understand which rows are static and which rows are dynamic. For more information, see Understanding Tablix Data Region Cells, Rows, and Columns। Understanding Tablix Data Region Areas A Tablix data region has four areas that contain Tablix cells: the corner, the row group area, the column group area, and the body. Cells in each area have a distinct function. You add cells to the Tablix body area to display detail and grouped data. Report Designer adds cells to the row group or column group area when you create a group in order to display group instance values. Report Designer creates Tablix corner cells when both row groups and column groups exist. On the design surface, dotted lines denote the four areas of a selected Tablix data region। The following figure shows the areas for a Tablix region with nested row groups based on category and subcategory, nested column groups based on geography and country/region, and an adjacent column group based on year.
The following list describes each area: Tablix corner area. (Optional) A Tablix corner is located in the upper-left corner, or upper-right corner for right-to-left (RTL) layouts. This area is automatically created when you add both row groups and column groups to a Tablix data region. In this area, you can merge cells and add a label or embed another report item. In the figure, merged cells in the corner display the label Sales by Area and Year. Tablix column groups area. (Optional) Tablix column groups are located in the upper-right corner (upper-left corner for RTL layout). This area is automatically created when you add a column group. Cells in this area represent members of the column groups hierarchy, and display the column group instance values. In the figure, the cells that display [Geography] and [CountryRegion] are nested column groups, and the cell that displays [Year] is an adjacent column group. The column [Total] displays the aggregated totals across each row. Tablix row groups area. (Optional) Tablix row groups are located on the lower-left corner (lower right for RTL layout). This area is automatically created when you add a row group. Cells in this area represent members of the row groups hierarchy, and display row group instance values. In the figure, the cells that display [Category] and [Subcat] are nested row groups. The Total row below Subcat repeats with each category group to show the aggregated subtotals for each column. The grand total row shows the totals for all categories. Tablix body area. The Tablix body is located in the lower right corner (lower left for RTL layout). The Tablix body displays detail and grouped data. In this example, only aggregated data is used. The scope for the expression is determined by the innermost groups to which the text box belongs. Cells in the Tablix body display detail data when they are members of a detail row and they represent aggregate data when they are members of a row or column associated with a group. By default, cells in a group row or column that contain simple expressions that do not include an aggregate function, evaluate to the first value in the group. In the figure, the cells display the aggregate totals for line totals for all sales order. When the report runs, column groups expand right (or left, if the Direction property of the Tablix data region is set to RTL) for as many columns as there are unique values for a group expression. Row groups expand down the page. For more information, see Understanding Tablix Data Region Cells, Rows, and Columns। The following figure shows the Tablix data region in Preview. The row group area displays two category group instances for Clothing and Components. The column group are displays a geography group instance for North America, with two nested country/region group instances for Canada (CA) and the United States (US). In addition, the adjacent column displays two year group instances for 2003 and 2004. The Total column row displays the row totals; the totals row that repeats with the category group shows subcategory totals, and the grand total row displays the category totals once for the data region. Understanding Tablix Data Region Cells, Rows, and Columns To control how the rows and columns of a Tablix data region display data in a report, you must understand how to specify rows and columns for detail data, for group data, and for labels and totals. In many cases, you can use the default structures for a table, matrix, or list to display your data. For more information, see Adding a Table (Reporting Services), Adding a Matrix (Reporting Services), or Adding a List (Reporting Services). A Tablix data region displays detail data on detail rows and detail columns and grouped data on group rows and group columns. When you add row groups and column groups to a Tablix data region, rows and columns on which to display the data are automatically added. You can manually add and remove rows and columns to customize a Tablix data region and control the way your data displays in the report. To understand how to customize a Tablix data region, you should first understand how to interpret the visual cues you see when you select a Tablix data region on the design surface. Understanding Tablix Visual Cues Visual cues on a Tablix data region help you work with a Tablix data region to display the data you want. Row and Column Handles When you select a Tablix data region, the row and column handle graphics indicate the purpose of each row and column. Handles indicate rows and columns that are inside a group or outside a group. The following table shows a variety of handle displays. Icon Description Only the details group on the row group hierarchy One outer group and the child details group One outer group, one inner group; no details group One outer group, one inner group, and the child details group One outer group with a footer row for totals and one inner group One outer group with a footer row for totals, one inner group with a footer row for totals, and one details row One outer group with a header for labels and a footer for totals, and an inner group; no details group Group Rows Rows inside a group repeat once per unique group value and are typically used for aggregate summaries. Rows outside a group repeat once with respect to the group and are used for labels or subtotals. When you select a Tablix cell, row and column handles and brackets inside the Tablix data region show the groups to which a cell belongs. This figure displays the following visual cues: Row and column handles that indicate group associations. Highlighted group indicators that show the innermost group membership for a selected cell. Group indicators that show all group memberships for a selected cell. Total Rows After you add row and column groups, you can add a row to display totals for columns and a column to display totals for rows. The following figure shows a matrix with both row and column groups, and a total row and a total column. Grouping Pane The Grouping pane displays the row and column groups for the currently selected Tablix data region on the design surface. The following figure shows the Grouping pane for this Tablix data region. The Row Groups pane shows the parent group Category and child group Subcat. The Column groups pane shows the parent group Geography and child group CountryRegion, and also the Year group, which is an adjacent group to the Geography group. When you select the Subcat group in the Row Groups pane, the group bar turns a darker shade of orange, and the corresponding row group member cell is selected on the design surface. Displaying Data on Rows and Columns Rows and row groups and columns and column groups have identical relationships. The following discussion describes how to add rows to display detail and group data on rows in a Tablix data region, but the same principles apply to adding columns to display detail and grouped data. For each row in a Tablix data region, a row is either inside or outside each row group. If the row is inside a row group, it repeats once for every unique value of the group, known as a group instance. If the row is outside a row group, it repeats only once in relation to that group. Rows outside all row groups are static and repeat only once for the data region. For example, a table header or footer row is a static row. Rows that repeat with at least one group are dynamic. When you have nested groups, a row can be inside a parent group but outside a child group. The row repeats for every group value in the parent group, but displays only once in relation to the child group. To display labels or totals for a group, add a row outside the group. To display data that changes for every group instance, add a row inside the group. When you have detail groups, each detail row is inside the detail group. The row repeats for every value in the dataset query result set. For more information about group hierarchies, see Understanding Groups (Reporting Services). The following figure shows a Tablix data region with nested row groups and a details group. For a Tablix data region that displays detail data, the details group is the innermost child group. Rows that you add to a details group repeat once per row in the result set for the query for the dataset linked to this Tablix data region. The following figure shows the last page of the rendered report. In this figure, you can see the last detail rows and the subtotal row for the last order. For each column in a Tablix data region, the same principles apply. For example, a column is either inside or outside each column group; to display totals, add a column outside the group. To remove rows and columns associated to a group, you can delete the group. When you delete a group, you have the choice between deleting the group definition only or deleting the group and all its associated rows and columns. By deleting just the group, you preserve the row and column layout on the data region. When you delete the group and its related rows and columns, you are deleting all static rows and columns (including group headers and footers) and dynamic rows and columns (including group instances) that are associated with that group. For step-by-step instructions about adding or deleting rows and columns, see How to: Insert or Delete a Row (Reporting Services) and How to: Insert or Delete a Column (Reporting Services). Understanding Tablix Cells Tablix cells belong to one of four Tablix areas: the Tablix body, Tablix row or Tablix column group areas, or the Tablix corner. Although each cell can potentially display any value in the dataset, the default function for each cell is determined by its location. For detailed information about Tablix areas, see Understanding Tablix Data Region Areas. By default, cells in Tablix row and column group areas represent group members. Group members are organized into multiple tree structures in the report definition. The row group hierarchy expands horizontally. The column group hierarchy expands vertically. These cells are added automatically when you create a group, and display the unique values for a group at run time. Cells in the Tablix corner are created when there are both row and column group areas. You can merge cells in this area to create a label or embed another report item. Cells in the Tablix body area can display detail data when the cell is in a detail row or column and aggregated group data when the cell is in a group row or column. The scope for the data in a cell is the intersection of the innermost row group and innermost column group to which the cell belongs. Merging and Splitting Cells Inside a Tablix area, you can merge multiple adjacent cells together. For example, you can create cells for labels that span multiple columns or rows. In the Tablix corner area, cells can be combined in only one direction at a time: horizontally across columns or vertically down rows. To merge a block of cells, merge the cells horizontally first. After all cells have been merged into a single cell in each row, select adjacent cells (you can select all adjacent cells in a column) and merge them. In the Tablix body area, cells can only be merged horizontally. Merging cells vertically is not supported. For more information, see How to: Merge Cells in a Data Region (Reporting Services). You can split a cell that was previously merged. You can split cells horizontally across columns or vertically down rows. To split a cell into a block of cells, split the cell horizontally first, and then split vertically as many times as necessary. Flexibility of a Tablix Data Region When you add a table, matrix, or list data region from the Toolbox, you start with an initial template for a Tablix data region, but you are not limited by that template. You can continue to develop how your data displays by adding or removing any Tablix data region feature such as groups, rows, and columns. When you delete a row or column group, you have the option of deleting the rows and columns that are used to display group values. You can also add or remove rows and columns manually. To understand how rows and columns are used to display detail and group data, see Understanding the Tablix Data Region. After you change the structure of the Tablix data region, you can set properties to help control the way the report renders the data region; for example, you can repeat column headers at the top of every page, or keep a group header with the group. For more information, see Controlling the Tablix Data Region Display on a Report Page (Reporting Services). Changing a Table to a Matrix By default, a table has detail rows that display the values from the report dataset. Typically, a table includes row groups that organize the detail data by group, and then includes aggregated values based on each group. To change the table to a matrix, add column groups. Typically, you would remove the details group when the data region has both row and column groups so that you can display only the summary values for the groups. For more information, see How to: Add or Delete a Group in a Data Region (Reporting Services). By definition, when you create a matrix, you add a Tablix corner cell. You can merge cells in this area and add a label. For more information, see How to: Merge Cells in a Data Region (Reporting Services). Changing a Matrix to a Table By default, a matrix has row groups and column groups and no detail group. To change a matrix to a table, remove column groups and add a details group to display on the details row. For more information, see How to: Add or Delete a Group in a Data Region (Reporting Services) and How to: Add a Details Group (Reporting Services). Changing a Default List to a Grouped List By default, a list has detail rows and no groups. To change the list to use a group row, rename the details group and specify a group expression. For more information, see How to: Add or Delete a Group in a Data Region (Reporting Services) Creating Stepped Displays By default, when you add groups to a Tablix data region, cells in the row group header area display group values in column. When you have nested groups, each group displays in a separate column. To create a stepped display, remove all group columns except one, and format the remaining column to display the group hierarchy as an indented text display. For more information, see How to: Create a Stepped Report. Adding an Adjacent Details Group By default, the details group is the innermost child group in a group hierarchy. You cannot nest a group under the details group. You can create additional adjacent details groups, to display the top 5 products and the bottom 5 products by sales, for example. Because you can add filter and sort expressions on each group, you can show two views of detail data from the same dataset in one Tablix data region. For more information, see Understanding Groups (Reporting Services), How to: Add or Delete a Group in a Data Region (Reporting Services), and How to: Add a Filter (Reporting Services). Controlling the Tablix Data Region Display on a Report Page (Reporting Services) After you add a Tablix data region to a report, you can continue to develop your design as you work between the Design view and Preview in Report Designer. This topic describes properties for a Tablix data region that you can modify to change the way a Tablix data region appears when you view it in a report. Controlling the Appearance of Data The following features help control the appearance of a Tablix data region: Formatting data. To format data in a table, matrix, or list, set the format properties of the text box in the cell. You can set properties for multiple cells at the same time. To format data in a chart, set formatting properties on the series. For more information, see Formatting Reports and Report Items and Formatting a Chart. You can also format data by writing expressions. For more information, see Adding Conditional Formatting (Reporting Services), Using Expressions (Reporting Services), and Expression Examples (Reporting Services). Controlling sort order. To control the sort order, you must define sort expressions on the data region. To control sort order for rows and columns that are associated with a group, you must define sort expressions on the group, including the details groups. You can also add interactive sort buttons to enable the user to sort a Tablix data region or its groups. For more information, see Working with Group Features (Reporting Services) and How to: Sort Data in a Data Region (Reporting Services). Displaying a message when there is no data. When no data exists for a report dataset at run time, you can write your own message to display in place of the data region. For more information, see How to: Set a No Data Message for a Data Region. Conditionally hiding data. To conditionally control whether to show or hide a data region or parts of a data region, you can set the Hidden property to True or to an expression. Expressions can include references to report parameters. You can also specify a toggle item, so that user can decide to display detail data. For more information, see Hiding Report Items Conditionally. Merging cells. Multiple contiguous cells within a table can be combined into a single cell. This is known as a column span or a cell merge. Cells can only be combined horizontally or vertically. When you merge cells, only the data in the first cell is preserved. Data in other cells is removed. Merged cells can be split into their original columns. For more information, see How to: Merge Cells in a Data Region (Reporting Services). Controlling Tablix Data Region Position and Expansion on a Page The following features help control the way a Tablix data region displays in a rendered report: Controlling the position of a Tablix data region in relation to other report items. A Tablix data region can be positioned above, next to, or below other report items on the report design surface. At run time, Reporting Services expands the Tablix data region as needed for the data retrieved for the linked dataset, moving peer report items aside as needed. To anchor a Tablix next to another report item, you must make the report items peers and adjust their relative positions. For more information, see Understanding Rendering Behaviors. Changing the Expansion Direction. To control whether a Tablix data region expands across the page from left-to-right (LTR) or from right-to-left (RTL), use the Direction property, which can be accessed through the Properties window. For more information, see Rendering Data Regions. Controlling How a Tablix Data Region Renders The following features help control how cells in row and column group areas appear in a rendered report. For more information about row and column groups areas, see Understanding Tablix Data Region Areas. For more information about how exporting a report to different formats affects the way a Tablix data region renders on a page, see Understanding Rendering Behaviors. Controlling paging. To control the amount of data that displays on each report page, you can set page breaks on data regions. You can also set page breaks on groups. Page breaks can affect the on-demand rendering performance by reducing the amount of data that needs to be processed on each page. For more information, see Understanding Pagination in Reporting Services and How to: Add a Page Break (Reporting Services). Displaying data on either side of row headers. You are not limited to displaying row headers on the side of a Tablix data region. You can move the row headers between columns, so that columns of data appear before the row headers. To do this, modify the GroupsBeforeRowHeaders property for the matrix. You can access this property through the Properties window. The value for this property is an integer; for example, a value of 2 will display two groups instances of data region column data before displaying the column containing the row headers. Controlling Row and Column Headings. You can control how row and column headings repeat or freeze when you view a report. For more information, see Controlling Row and Column Headings. For step-by-step instructions, see the following topics: · Repeating row and column headers on multiple pages. You can display row and column headers on each page that a Tablix data region spans. For more information, see How to: Display Row and Column Headers on Multiple Pages (Reporting Services). · Keeping row and column headers in view when scrolling. You can control whether to keep the row and column headers in view when you scroll a report using a browser. For more information, see How to: Keep Headers Visible When Scrolling Through a Report (Reporting Services)।