Charts are a powerful way to visually represent data in a report. In this article, we will explore how to create dynamic charts in SQL Server Reporting Services (SSRS) that display information in a visually appealing and informative manner.

Introduction

As we delve into the world of reporting, it is important to find innovative ways to present data. In this article, we will focus on creating charts that combine bar and line graphs into a single chart. Additionally, we will explore how to use color fill in the vertical bars to reflect the values they represent.

Getting Started

Let’s start by looking at some sales data from a fictional South African client. We will be working with a table called “MonthlyGoalvsActual” which contains sales and goal data for the financial year 2015. The table has three columns: the year and month measured, the monthly goals, and the actual month’s sales. We will also need a function called “Monthee” to obtain the three character month name.

Next, we will create a stored procedure to pull this data and a new Reporting Services project using SQL Server Data Tools. We will create a shared data source that connects to the SQLShack database and add a dataset to our project.

Creating the Chart

Now that we have our dataset, we can start designing our report. We will add a chart to the report surface and link it to the dataset. We will configure the chart to display the “Goal” and “Actual” fields as the values and set the “Category Groups” to “YearMth” and “Monthee”. We will also format the chart to display the month names correctly.

To create the line graph for the monthly goals, we will right-click on the “Goal” field in the chart and select “Change Chart Type”. We will choose the “Line” chart type and accept the change.

Next, we will add data labels to the bar chart by right-clicking on one of the bars and selecting “Show Data Labels”. We will format the labels to display as dollars.

Color Fill and Ratio Comparison

To represent the ratio of the “actuals” to the “goals”, we will use a code snippet that assigns different colors to the vertical bars based on the ratio. We will right-click on one of the bars and select “Series Properties” to open the dialog box. We will select the “Fill” option and enter the color code expression. This will change the color fill of the vertical bars based on the ratio.

Conclusion

SQL Server Reporting Services provides a powerful tool for creating dynamic charts that effectively display data. By combining bar and line graphs and using color fill to represent ratios, we can create visually appealing and informative reports. With the ability to process large amounts of data efficiently, SSRS is a valuable tool for any organization.

Thank you for joining us in this exploration of dynamic charts in SQL Server Reporting Services. We hope you found this article informative and inspiring. Happy reporting!