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.
If you observe the below screenshot, It was a regular Table report with Country Name, Sales, and Number of Orders columns.
Please refer to the Table Report, Data Source, and Dataset articles to understand the creation of the Data Source, Dataset, and Table report. Here, we used the Embedded Dataset for the above SSRS 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 selecting the Insert Column option, and then selecting the right option.
Next, Drag and drop the Gauges from Toolbox to the Empty column. When you drag and Drop the Gauges from the Toolbox to the Empty column, a new window called Select Gauges Type will be opened to select the required Gauges (Radial and Linear) from the available ones.
In this example, we select Simple Linear Gauges, as shown in the screenshot below.
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 the region of the value; if you mishandle or drop in the design area will end up in a mess.
Range Properties in Linear Gauges in SSRS
The above screenshot shows that Range starts at 60 (Middle of the Scale) and ends 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 the Gauges article and the Formatting Gauges page to see the formatting options.
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 changed the end position to Sum (Total Sales). The range will start at 0 and finish at the Sum of the Total Sales of each Territory.
SSRS Linear Gauge Scale Properties
Next, we must 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 a Linear gauge scale’s default Minimum and Maximum values.
Here, we are changing the minimum value to 1500000 and the 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 gives a perfect result, but the range starts beyond the starting point (1500000).
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.
