In this article, we will show you how to create and configure the Linear Gauges in SSRS. For this, We are going to use the below-shown report.
Please refer to the SSRS Table Report, Data Source, and Dataset articles to understand the creation of Data Source, Dataset, and Table report. If you observe the below screenshot, It was a regular Table report with Country Name, Sales and Number of Orders columns.
We used the Embedded Dataset for the above report, and the SQL Command for the Dataset is:
-- Data to create Linear Gauge in SSRS
SELECT [SalesTerritoryCountry]
,SUM ([OrderQuantity])AS ORDERS
,SUM ([SalesAmount])AS [TOTAL SALES]
FROM [DimSalesTerritory]
INNER JOIN FactInternetSales ON
[DimSalesTerritory].SalesTerritoryKey = FactInternetSales.SalesTerritoryKey
GROUP BY [SalesTerritoryCountry]
ORDER BY [SalesTerritoryCountry]Creating Linear Gauges in SSRS
In this example, We will create and add the SSRS Linear Gauges on the right-hand side of the Orders column So, First, create an Empty column by right-clicking on the Orders column and select the Insert Column option and then select the Right option.
Next, Drag and drop the Gauges from Toolbox to the Empty column. When you drag and Drop the Gauges from Toolbox to the Empty column, a new window called Select Gauges Type will be opened to select required Gauges (Radial and Linear) from the available once.
In this example, we are selecting Simple Linear Gauges, as shown in the below screenshot.
Click Ok to add Linear Gauge to a newly created empty cell. Now, we have to configure the Gauges Data. First, click on the Linear Gauge will open the Gauge Data window. In this example, we want to show the Linear Pointer for Total Sales so, Please drag and drop or select the orders as the Indicator Value.
NOTE: Please be careful when dragging the columns to values region, if you mishandled or dropped in the design area will end up in a mess.
Range Properties in Linear Gauges in SSRS
If you observe the above screenshot, Range is starting at 60 (Middle of the Scale) and end at 100. Let us make it more meaningful by Right Click on the Range will open the context menu. Please select the Range Properties.. option from it as shown in the below screenshot.
To add the Range, Please refer to Adding the Ranges section in Gauges in the SSRS article.
Once you select the Range Properties.. option, a new window called Linear Scale Range Properties will be opened to configure the Range scale. We have already explained the properties and their Default values of a Range in Gauges article.
Here, we kept the starting position to 0 and are changing the end position to Sum (Total Sales). It means Range will start at 0 and finish at Sum of Total Sales of each Territory.
SSRS Linear Gauge Scale Properties
Next, we have to configure the Scale Properties of a Linear Gauge in SSRS. So, Right Click on the Scale will open the context menu. Please select the Scale Properties option from it.
Once you choose the Scale Properties option, a new window will be opened to configure the SSRS Linear Gauge Scale. From the General tab, you can notice the default Minimum and Maximum values of a Linear gauge scale.
Here, we are changing the minimum value to 1500000 and maximum value to 9500000 because our values are between these two values. You also use the fx button for custom expressions.
NOTE: You can also customize the intervals between Minimum and Maximum value using the Interval property.
Next, We formatted the Number to currency, removed the decimal values, and asked the reporting server to display values in thousands. It means 1500000 will show as $1500
Click Ok to finish configuring the SSRS Linear gauge properties. Let’s see the Report preview by clicking on the preview tab
If you observe the above screenshot, It is giving a perfect result, but the range is starting beyond the starting point (1500000).
It is because we set the Range Starting value to 0 at the beginning. Let us change the range starting value to 1500000 as shown below
Click Ok to finish and click on the preview tab to see the SSRS Linear Gauges report
If you observe the above screenshot, It looks neat and clean.
