A Line Chart in SSRS is very useful to connect individual dots. We can use this line chart to find the Forecast details, or trend in sales, or profit trends etc. In this article we will show you, How to create a SSRS Line Chart, How to change the Chart Title, Legend Title, Legend Position, Line Chart Font, Chart Model, and Display Data Labels on Line Chart in SQL Server Reporting Services with example.
For this example, we are going to write the Custom SQL Query against the SQL Server Data Source, and the query that we are going to use for this report is:
SQL Query
-- SQL Query that we use in SSRS Line Chart
SELECT Geo.EnglishCountryRegionName AS Country,
Geo.StateProvinceName AS State,
Geo.City,
Prod.EnglishProductName AS ProductName,
Prod.Color,
Fact.OrderQuantity,
Fact.TotalProductCost,
Fact.SalesAmount,
Fact.TaxAmt,
Fact.[Freight]
FROM DimProduct AS Prod
INNER JOIN FactInternetSales AS Fact
ON Prod.ProductKey = Fact.ProductKey
INNER JOIN DimSalesTerritory AS Terry
ON Terry.[SalesTerritoryKey] = Fact.[SalesTerritoryKey]
INNER JOIN DimGeography AS Geo
ON Geo.[SalesTerritoryKey] = Terry.[SalesTerritoryKey]
Create a Line Chart in SSRS
In this example, we are going to create a Line Chart to display the Trend in Sales amount by the Product Color. Below screenshot will show you the, Data Source and Dataset we used for this Line Chart Report.
In SSRS report design, we can add a Line chart by dragging the Chart property from the SSRS Toolbox to Design space, or we can simply right click on the report designer to open the context menu. From the context menu, please select the Insert -> Chart option as we shown below.
Once you select the Chart option, a new window called Select Chart Type will be opened to select required chart from the available once. For this example, we are selecting Line Chart as shown in below screenshot.
Once you click on Ok button, Line chart will be displayed in design region with dummy data.
Click on the empty space around the Line Chart will open the Chart Data window
- Values: Any Numeric (Metric) value such as Total Sales, Sale amount, Number of Customers, Tax etc. All these values will be aggregated using aggregate function (Sum, Avg, Count etc). This is because we are grouping them with the category group items.
- Category Group: Please specify the Column name on which you want to partition you Line Chart.
As we said before, In this example we will create Line chart to find the Sales Trend by Product color so, Drag and drop the Sales Amount column from data set to chart data values and Product Color in category group.
NOTE: Please be careful when dragging the columns to values region, if you mishandled or dropped in design area will end up in mess.
Click on the Preview button to see the report preview.
If you observe the above screenshot, It is providing perfect result but we are unable to identify the Sales at a Given point. So, let us add the Data Labels.
Add Data Labels to Line Chart in SSRS
Right click on the Line chart, and select the Show Data Labels option from the context menu to show the values
Next, we are formatting the Font, and changing the Number format from Default to Currency. To do so, Please select the Data labels, and right click on it will open the context menu. Next, please select the Series Label Properties option from the context menu as we shown below.
Here we are using comma separator for 1000, and we are allowing the SSRS report to show the data labels in Thousands
Change Axis properties of a Line Chart in SSRS
Right click on the Axis of a Line chart, and select the Horizontal Axis Properties.. option from the context menu to format the Axis values.
Here we changed the Font style, and font size of both the Horizontal, and vertical Axis
Click on the Preview button to see the Line Chart in SSRS report preview.
Change SSRS Line Chart Title
To change a Line Chart title, Please select the Chart title region as we shown in below screenshot, and change the title as per your requirement. OR, right click on it will open the context menu. Please select the Title properties.. option from the context menu as shown below.
Here we are changing the title as Line Chart Example because the report is displaying the same
Format SSRS Line Chart Title Font
Within the Font tab we can change the Font Size, Font Family, Font Style and Color of a Line Chart Title. Here we are changing the Font to Cambria, Font size to 14pt, and color to Green as shown in below screenshot
Click on the Preview button to see the report preview.
Change Axis Titles of a Line Chart in SSRS
In order to change a Axis titles, Please select the Axis title region as we shown in below screenshot, and change the title as per your requirement. OR, right click on it will open the context menu. Please select the Axis Title properties.. option from the context menu as shown below.
This will open a new window called the Axis Title Properties window. Here you can change the Title text, and also the Font style, size, and color. For now, we are changing the Color to Green, Font to Cambria, Size to 12pt
Please apply the same properties to Y-Axis as well.
Click on the Preview tab to see the report preview.
Multiple Line Charts in SSRS
Though above chart fulfilled our requirement. Let me check the sales trend for each country. In order to do so, we have to add country dimension to existing Line chart. For this, select the empty region around the Line Chart, and add the Country dimension to the Series Group.
Now you see the sales trend for each color, and country
Let me change the Lines thickness. And to do so, right click on the Lines, and select the Series properties.. from the context menu
This will open a new window called the Series Properties window. Under the Border tab, we can chnage the Line width, Color, Style (dotted, solid etc). For now, we are changing the line width from default 1pt to 2pt
Click Ok button to close the Series Properties window, and select the preview tab to see the report preview
Format Legend Position of a Line Chart in SSRS
To format the Legend region, Please Select the Legend region and right-click on it will open the context menu. From the menu, please select Legend Properties option.
This will open a new window called the Legend Properties Window. Within the General Tab we have an option called Legend Position to change the Legend Position by changing the dot positions. For now we are changing the position from default right corner to Middle.
Click Ok button to close the Properties window, and hit the preview tab to see the report preview
Change the type of a Line Chart in SSRS
SSRS allows us to change the chart type even after creating a Line chart. To do so First, select the Line chart and right-click on it will open the context menu. Please select the Change Chart Type… option from the context menu
Once you select the Change Chart Type… option, it will open new window called Select Chart Type to select the change. Here we are selecting the 3D Line chart
Click on the preview tab to see the report preview
From the above screenshot you can observe that, We successfully changed the Chart Type from 2D Line chart to 3D Line
This time we are going to change the Chart Type from 3D Line chart to Smooth Line
Click on the preview tab to see the report preview
This time we are going to change the Chart Type from Smooth Line to Line with Markers
Let me change the Marker Size, Color, Border Width, and border Color, before we see the preview
Click on the preview tab to see the report preview
This time we are going to change the Chart Type from Line with Markers to Smooth Line with Markers
Click on the preview tab to see the report preview
Thank You for Visiting Our Blog
