In this article we will show you, How to create an Area Chart in SSRS, How to change the Chart Title, Legend Position, Legend Title, Area Chart Font style, Area Chart Model, and 3D Area Chart in SQL 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 that we use in SSRS Area Chart
SELECT Cat.[EnglishProductCategoryName] AS ProductCategory,
SubCat.[EnglishProductSubcategoryName] AS ProductSubCategory,
Prod.EnglishProductName AS ProductName,
FROM FactInternetSales AS Fact
INNER JOIN DimProduct AS Prod
ON Fact.ProductKey = Prod.ProductKey
INNER JOIN DimProductSubcategory AS SubCat
ON Prod.ProductSubcategoryKey = SubCat.ProductSubcategoryKey
INNER JOIN DimProductCategory AS Cat
ON SubCat.ProductCategoryKey = Cat.ProductCategoryKey
and the Data that it’s going to return is:
Create an Area Chart in SSRS
In this example, we are going to create an Area Chart to display the Sales Amount, and Total Product Cost grouped by product Name. Below screenshot will show you the Data Source, and Dataset that we used for this Area Chart Report.
In SSRS report design, we can add an Area 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 an Area Chart as shown in below screenshot.
Once you click on Ok button, Area chart will be displayed in design region with dummy data.
Click on the empty space around the 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 the Area Chart.
- Series Group: Please specify the Dimension here. This will further group the individual category
Drag and drop the Sales Amount column from data set to chart data values, Product Name to 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 tab to see the report preview.
If you observe the above screenshot, It is providing perfect result. Let me add one more metric value Total Product Cost to the Values section.
Now you can see that our Area chart is showing the difference between the Product Cost, and it’s sales Amount
Format an Area Chart in SSRS
Following list of examples will show you the steps involved in formatting the Area Chart in SSRS
Change Axis properties of an Area Chart in SSRS
Right click on the Axis of an Area 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 the Horizontal Axis.
Next, Right click on the Vertical Axis of an Area Chart, and select the Vertical Axis Properties.. option from the context menu to format the Axis values.
Please use the above specified technique to change the font of the vertical Axis. Next, format the Numbers in the vertical axis by selecting the Number tab.
Change Axis Titles of an Area Chart in SSRS
In order to change an 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. Under the General section, we can change the Title text as per our requirements. For now, we are changing it to PRODUCT NAME
Under the Font tab, we can change the Font style, size, and color. For now, we are changing the Color to Dark Green, Font to Century Gothic, Size to 12pt, and style to Bold
Please apply the same properties to Y-Axis as well. Once you are done, Click on the Preview tab to see the report preview.
Change SSRS Area Chart Title
To change the title of an Area Chart, 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 SSRS Area Chart Example because the report is displaying the same
Format SSRS Area Chart Title Font
Within the Font tab we can change the Font Size, Font Family, Font Style and Color of an Area Chart Title. Here we are changing the Font to Centuri Gothic, Font size to 14pt, and color to Brown as shown in below screenshot
Format Legend of an Area Chart in SSRS
To format the Area chart 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.
Under the Font tab, we can change the Font style, size, and color of a Legend. For now, we are changing the Font to Century Gothic, and Size to 10pt
Click OK button to close the Properties window, and hit the preview tab to see the report preview
Different Area Charts in SSRS
You have an option to add one more dimension to the Series group to further divide the Area Chart. For now, we are adding Color to the Series group
Let me show you the report preview
SSRS allows us to change the chart type even after creating an Area Chart. To do so First, select the Area 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 Area 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 Area Chart to 3D Area Chart
Thank You for Visiting Our Blog