Ssrs custom legend


  • Remove extra spaces in the SSRS charts
  • Fit Chart Labels Perfectly in Reporting Services using Two Powerful Properties
  • Custom Legend in SSRS Charts
  • SSRS Charts with Data Tables (Excel Style)
  • Reporting in SQL Server – Customize the visual appearance of your reports
  • SSRS chart does not show all axis labels. How do I fix it?
  • Remove extra spaces in the SSRS charts

    For example, a green arrow indicator could show you are beating your service level times, a yellow circle that you are just meeting them, and a big red X that you are failing to meet them. Figure 1 displays the indicators currently available in Report Services. Figure 1: Indicator types in SSRS For the first example, we will use red, yellow, and green circle indicators to show the number of support calls solved per calendar year; red indicates 10, or less cases solved; yellow indicates between 10, and 75, cases solved, while a green light signifies over 75, cases solved.

    Of course, the business users and management will need to help define the indicator thresholds and change the colors as needed. Matrix vs. To be consistent, I use tablix throughout this article, but it is called a matrix or table in the toolbox. Next, drag onto the tablix the ServerRegion and Priority columns, as row groups, and CalendarYear as a column group.

    To complete the tablix, add the Amount field number of support calls to the data region and finally add an indicator as a second column on the data area of the tablix. Finally, run the report. Without even looking at the raw figures, the indicator lights quickly reveal the regions and years that originated the most support tickets.

    As shown in Figure 2, though, unfortunately SSRS will stretch the aspect ratios of the indicators when other columns, the ServerRegion and Priority for this example, in the tablix use multiple rows of text to display a single data item.

    Figure 2: Using indicators in a tablix data region Although the indicators are correct, their sizing and spacing look wrong, aesthetically and proportionally. The bottom graphic shows the results, with the indicators in the left columns contained within the rectangle object and those in the right allowed to stretch. In order for these figures to display at a size large enough for a report consumer to see, the size of the rectangle and inserted indicators must be expanded to accomodate both the indicator and its related data label, as shown in Figure 4.

    To achieve this result, we first need to increase the size of our rectangle and the indicator within the rectagle from the previous step. Then, the indicator properties can be modified to show data labels, and finally, we neede to increase the font of the data labels to display these values clearly to the report consumer.

    The tooltip in Figure 5 displays the value related to the indicator. Going back to our original example, we can add the tooltip by selecting the indicator and then right clicking and selecting properties. In figure 5, I also formated the value to display as a number with two places beyond the decimal. Figure 5: Using tooltips to display indicator values Indicators make it easy and quick to compare two data points; for example, a red stop light standouts when pictured against a group of green indicators.

    Making the same comparison with just the discrete values requires a must more detailed review of each figure. In this example, we embedded our indicators within a tablix. Next, we will explore the various methods a report designer can use to embed different report objects within a table, tablix, or tablix.

    Embedded Charts Embedding charts within a tablix might seem counter-intuitive, but it means that instead of displaying numbers in the data area of the control, we can display a chart. This method allows a report developer to present many charts with minimal effort. For example, say your supervisor wants to see the support calls broken out by country, for each support region and for each calendar year.

    One solution would be to create one chart for each region and year combination, but this could quickly add up to many charts. By embedding the chart within a tablix, we automatically get a chart for each group in the table.

    In our example, shown in Figure 6, we embed within the data area of the tablix a pie chart to show the number of support calls solved by country, by region, and by year. To complete this chart, first we will want to add a tablix to our report body area. Subsequently, we drag a pie chart onto the data area of the tablix; we use the Country field for the series grouping, and the Amount field for the values of the pie segments.

    Figure 6: Embedding a pie chart in the data area of a tablix The report users would see a pie chart for each server territory group and calendar year, similar to Figure 7. Such an embedded chart provides both a details component and trending component.

    The detail component is revealed by showing the country breakout on each chart and the trend is revealed by comparing the pie charts from year to year. Also, notice that the pie chart makes use of the tooltips functionality to display the exact percentage of the total each slice contributes to the pie. We can embed within both row and column groupings of a tablix many types of charts and graphs, including sparklines and databars, which we discuss next. Sparklines and Databars Sparklines and databars are great for showing trends and changes in data over time, especially over many periods.

    A sparkline is similar to a regular line chart, while a databar is comparable to a column or bar chart. However, we generally embed both sparklines and databars within a textbox within a tablix.

    For best effect, report designers must keep the display on these charts as simple as possible, eschewing detailed legends, titles, tick marks, and even x and y axes, unless absolutely necessary.

    Furthermore, we can embed, or nest, each sparkline or databar into multiple row or column groups within a tablix. Nested sparklines and databars display the related graphic for each intersection of the column groups and row groups included in a tablix. However, the report designer must understand how such a drill down affects the resulting databars or sparklines. ServerTerritoryCountry row group.

    This nested sparkline will display the numbe rod support calls Amount field by Year and then Month. Nested graphics lend themselves perfectly to analyzing trends in data. To create this simple nested sparkline, first add a tablix to the report body and then drag ServerTerritoryCountry to the row group.

    Next, add a new column group by clicking on the column group area lower right of the window and selecting Add Column Group. This may seem confusing, but the fact is that all tablixes must have at least one column and one row group. In our example, we do not want to group on any column value so we use the dummy value of 1 for the column group, meaning we will group together all the values into a single column.

    Figure 9 shows the results. In Figure 10, we use a databar instead of a sparkline and instead of using the CalendarYear and CalendarMonth as the series, we use Priority. In the resulting chart, the color of the bar distinguishes the priority of the call, while the bars added together show the total support cases. Figure Nested Databar by Country We can also create a trended databar graph using a technique similar to the one we used for the previous sparkline example.

    In this example, I also added a second column group for the ServerRegion. This situation causes spacing, alignment, and proportionality problems for each databar or sparkline on the chart. In Figure 12, each databar represents 1 month, but there is clearly a horizontal scaling problem with the T Pacific Server region, which displays fewer, wider databars. As a result, we do not know to which period each bar belongs, making it very hard to compare between groups.

    Figure Nested databar with misaligned periods To fix this problem, we can adjust a horizontal axis property on the databar, accessed by right clicking on the databars and then selecting the Horizontal AxisProperties. We need to enable the Align Axis option, as shown in Figure Figure Horizontal Axis Properties Alignment Option Having done this, the databars will have the correct spacing and alignment from period to period, even if a period has no data, as shown in Figure We can now reliably compare support calls across periods and regions.

    Figure Databar Example with Horizontally Aligned Axis Note, however, that this graph is still visually misleading in that it looks as if the number of support calls height of the data bars are roughly comparable between, for example, the R and T Pacific regions. Just as we did for the horizontal axis, we can fix this simply by setting the Align Axis option for the vertical axis. Now, in Figure 15, we can compare individual months both within a Server Region and also between Server Regions.

    Figure Databar with both Horizontal and Vertical Alignment Sparklines and databars generally do not include axis labels or titles so, once again, it is useful to add tooltips to display the appropriate month on our databar. Simply right-click on the databar, select the Series Properties option, and enter the appropriate values for the tooltip expression. In Figure 16, we concatenate month and year in the tooltip expression property. Figure Adding Year and Month Tooltip to Databar Now the report consumer has an easy way of gaining time period perspective without having to display the month and year on the x axis, and at the same time making our chart interactive.

    Figure Year and Month Tooltip on a databar Handling Line Breaks in Sparklines For sparklines, we can use the same horizontal and vertical alignment settings as described for databars.

    However, the NULL values cause a third issue, namely line breaks in the sparklines. We set the EmptyPointValue property to Average meaning that the empty point value will be replaced by the average of the values immediately before and after the empty point. Note that we also set the Color property to match the color of the existing lines, DarkBlue in our example.

    Figure Empty Point Properties Figure 20 displays the final sparkline report. Notice how the R Server Region now has a solid line, with the empty points set to the average of the previous and successive points. For our support calls example, we could design a column chart to display the support calls solved by country, and broken out by priority. Next, we could overlay a column chart to display the total support calls per country.

    Finally, on the original chart, a secondary axis could display as a line chart the average number of support calls. The first step in this process is to drag a Bar Chart onto the report body area, then add ServerTerritoryCountry as the category, Priority as the series, and Amount as the Values.

    Figure 21 shows the result. To add this second data series, click on the chart, and then click on the green plus button in the Values area. First, we need to change the chart type for the rollup series to a range column, so right click on the rollup series and select Change Chart Type, and select the Range Column type, as shown in Figure Figure Choosing a Range Column chart type for the Rollup Series Second, we need to set the fill color for the range column overlay chart such that it does not conflict with any of the colors used by the individual priority bars.

    It also needs to display, transparently but overtly, when contrasted with the colors used for each individual priority. Right click on the rollup series again, then select Series Properties and then click on the Fill option from the left hand menu.

    For this example, I used a fill color of Khaki. Depending on your needs, it also may beneficial to turn off the legend for the Rollup series. Figure Rollup Series Fill Color Figure 25 shows our new overlaid series so we can now compare not only support call volume per priority, for each country, but also the total support calls for each country. Figure Bar Chart with Range Chart Overlay Finally, we can add a secondary axis, comprising a third series of values that will display the average number of support calls per country.

    Click on the chart, and then click on the green plus button in the Values area. Next, add a new set of values that will be based on the following formula: AVG Fields! Figure Chart Series Values for Average Number of Support Calls To add this Average Series as a secondary axis, right click on it, and in the properties area select the Axes and Chart Area option from the left hand menu and select the Secondary option for the vertical axis, as shown in Figure Figure Setting Average Series to Use Secondary Vertical Axis Finally, we need to adjust the Average series chart type to a line chart, so right click on the average values series, select change chart type and select line chart as the Chart type, as illustrated in Figure It provides three distinct views of the data all in a single chart.

    Most managers like to see multiple data points in one place, and this type of chart fits that bill. This report contains: An indicator tablix which quickly shows the breakout of support calls by region, priority, and year An indicator tablix with large data labels A pie chart tablix which shows support calls by country, region, and year Databar and sparkline tablixes which show support calls by country and server region A column chart which includes a summarized country rollup of support calls and a secondary axis which displays the average support calls for each country.

    These various charts, when pulled together, create a good starting or even ending point for a dashboard front end. Wield these charting techniques with care, and a proper appreciation of the chart detail properties, and you can give your managers and supervisors accurate information, in an easily-understood, visually appealing report.

    Happy charting!

    Fit Chart Labels Perfectly in Reporting Services using Two Powerful Properties

    October 3, by Steve Simon Introduction As we are nearing the end of the North American summer, I thought that we would take a lighter look at nifty ways of reporting information.

    We shall take things one step further and work with the color fill of the vertical bars to reflect the values that they represent. Let us begin! We are going to look at some sales data from one of my South African clients. Obviously, the numbers have been altered for confidentiality reasons. The client had requested a series of reports in bar chart format showing actual revenue while having the monthly goals display in line graph format.

    Red being definitely under target, yellow as near target and green above target. We note that there are three columns. In addition to these three fields, we also require the three character month name, which is obtained by through passing the year and month combination to a function dbo. Monthee which I created.

    Our code for this function may be found in Addenda 1. We begin by creating a stored procedure to pull this data above. At this point, we shall assume that you feel comfortable working with a Reporting Services project. We give our project a name above. We accept and return to the report design surface. As I have mentioned many times before, a data source is similar to a garden hose. This garden hose connects to the tap on the house the database and the other end of the data source empties into a watering can the dataset.

    Our next task is to develop a report. We accept and return to the report. Our next task is to add a dataset. We return to our report and we also find that the system has created the dataset screen above.

    We click on the button as may be seen above. Having a look at our modified report, we see that every month is now displayed. Altering the GOAL portion of the chart to be a line graph As a part of the original specifications, the portrayal of monthly goals was to be a line chart. In Reporting Services creating the line graph whilst simple requires knowing where the change must be made.

    We accept the change. We are returned to our report surface. Running our report, we note the following. Let us now add the data values to the bar chart. Sample label values appear in design view above. These must be formatted to appear as Dollars. Our chart looks as follows in design view see below. If Actual to Goal is less than. If Actual to Goal is between.

    Custom Legend in SSRS Charts

    This nested sparkline will display the numbe rod support calls Amount field by Year and then Month. Nested graphics lend themselves perfectly to analyzing trends in data. To create this simple nested sparkline, first add a tablix to the report body and then drag ServerTerritoryCountry to the row group.

    Next, add a new column group by clicking on the column group area lower right of the window and selecting Add Column Group. This may seem confusing, but the fact is that all tablixes must have at least one column and one row group. In our example, we do not want to group on any column value so we use the dummy value of 1 for the column group, meaning we will group together all the values into a single column.

    Figure 9 shows the results. In Figure 10, we use a databar instead of a sparkline and instead of using the CalendarYear and CalendarMonth as the series, we use Priority. In the resulting chart, the color of the bar distinguishes the priority of the call, while the bars added together show the total support cases. Figure Nested Databar by Country We can also create a trended databar graph using a technique similar to the one we used for the previous sparkline example.

    In this example, I also added a second column group for the ServerRegion. This situation causes spacing, alignment, and proportionality problems for each databar or sparkline on the chart. In Figure 12, each databar represents 1 month, but there is clearly a horizontal scaling problem with the T Pacific Server region, which displays fewer, wider databars. As a result, we do not know to which period each bar belongs, making it very hard to compare between groups.

    Figure Nested databar with misaligned periods To fix this problem, we can adjust a horizontal axis property on the databar, accessed by right clicking on the databars and then selecting the Horizontal AxisProperties. We need to enable the Align Axis option, as shown in Figure Figure Horizontal Axis Properties Alignment Option Having done this, the databars will have the correct spacing and alignment from period to period, even if a period has no data, as shown in Figure We can now reliably compare support calls across periods and regions.

    Figure Databar Example with Horizontally Aligned Axis Note, however, that this graph is still visually misleading in that it looks as if the number of support calls height of the data bars are roughly comparable between, for example, the R and T Pacific regions.

    Just as we did for the horizontal axis, we can fix this simply by setting the Align Axis option for the vertical axis. Now, in Figure 15, we can compare individual months both within a Server Region and also between Server Regions. Figure Databar with both Horizontal and Vertical Alignment Sparklines and databars generally do not include axis labels or titles so, once again, it is useful to add tooltips to display the appropriate month on our databar.

    Simply right-click on the databar, select the Series Properties option, and enter the appropriate values for the tooltip expression. In Figure 16, we concatenate month and year in the tooltip expression property.

    SSRS Charts with Data Tables (Excel Style)

    Figure Adding Year and Month Tooltip to Databar Now the report consumer has an easy way of gaining time period perspective without having to display the month and year on the x axis, and at the same time making our chart interactive. Figure Year and Month Tooltip on a databar Handling Line Breaks in Sparklines For sparklines, we can use the same horizontal and vertical alignment settings as described for databars.

    However, the NULL values cause a third issue, namely line breaks in the sparklines. We set the EmptyPointValue property to Average meaning that the empty point value will be replaced by the average of the values immediately before and after the empty point.

    Note that we also set the Color property to match the color of the existing lines, DarkBlue in our example. Figure Empty Point Properties Figure 20 displays the final sparkline report.

    Notice how the R Server Region now has a solid line, with the empty points set to the average of the previous and successive points.

    Reporting in SQL Server – Customize the visual appearance of your reports

    For our support calls example, we could design a column chart to display the support calls solved by country, and broken out by priority. Next, we could overlay a column chart to display the total support calls per country.

    Finally, on the original chart, a secondary axis could display as a line chart the average number of support calls.

    Therefor, the Inner Plot Area occupies the entire chart and leaves no room for the axis labels. Note, however, the overlapping layers with the Chart contents on top of the Inner Plot Area contents. This time we give the chart a little white space around the edges but everything inside of that belongs to the Inner Plot Area. Not surprisingly, our labels are really smashed together.

    If you need extra space for a legend or a background image, this is how to get it. We just want the axis labels to fit. After some fiddling with the numbers, here is our winner: If you were lucky, it only took you six minutes to get this right. Laserbeak and Vortex down in Accounting will be relieved!

    SSRS chart does not show all axis labels. How do I fix it?

    CustomPosition and CustomInnerPlotPosition can save you a great deal of frustration when dealing with small or dense graphs. Will these properties save you time and agony? Do you have another trick for squeezing labels into place? Complacency is a struggle we all have to fight and I would be lying if I say that I am immune to all these.

    This post is a direct result of a few forum posts where people said that charts with data tables are not possible in SSRS unless you add a tablix beneath the chart and spend umpteen hours trying to align both of them! Working with SSRS is like making love to a woman — you need to know the woman and what makes her click well for a fabulous time. For people who are familiar with excel charts, adding a data table to a chart is a one-click affair. It is nice to see the data table just beneath the chart and it also serves as a legend.


    Ssrs custom legend