SSRS Report Parameter with Default Value

This SSRS article shows how to use the default values in the report parameter with an example. By default, the Parameter based report preview displays a blank page and waits for the user to select something. However, it will be nice to show something at first loading. Therefore, we have to assign a default value to the report Parameter.

Right-Click on the Datasets folder to create a new DataSet. The below screenshot shows the data set that we use for this SSRS Report Parameter with a Default Value example.


The Sql query that we used above SSRS example is:

SELECT Geo.EnglishCountryRegionName AS Country, 
       Geo.StateProvinceName AS State,
       SUM(Fact.OrderQuantity) AS Orders, 
       SUM(Fact.TotalProductCost) AS ProductCost, 
       SUM(Fact.SalesAmount) AS Sales, 
       SUM(Fact.TaxAmt) AS Tax
FROM FactInternetSales AS Fact  INNER JOIN DimCustomer ON DimCustomer.CustomerKey = Fact.CustomerKey
JOIN DimGeography AS Geo ON DimCustomer.GeographyKey  = Geo.GeographyKey
WHERE EnglishCountryRegionName = @Country
GROUP BY Geo.EnglishCountryRegionName, Geo.StateProvinceName, Geo.City

If you observe the above query, we have used the where condition with @Country Parameter.

Let me create another DataSet to bring the distinct country names. Then, we can use them as the available fields of the @Country Parameter.

SELECT DISTINCT EnglishCountryRegionName FROM DimGeography
Distinct dataSet query

SSRS Report Parameter with Default Value

The below screenshot shows the available columns in both DataSets and the automatically created @Country Parameter. Next, drag the table from the toolbox to the report design.

Columns from DataSet

We have designed a simple table report of Country sales and formatted the font and colors.

Table Report

If you observe the report preview, we typed the Canadian country name manually.

Report Preview

Double-click or right-click and choose the @Country Parameter Properties option will open the Report Parameter Properties window.

Edit Parameter

Go to the Available Values tab, select Get Values from a Query option, select the CountryDataSet, and assign EnglishCountryRegionName to both the values and Label fields.

Set the Available Value to SSRS report Parameter

Now, in the report preview, you can see a blank page waiting for us to choose the parameter value.

Empty Report

Once you select something, it returns the report.


Double-click the @Country Parameter, and go to the Default Values tab.

Add Default Value to SSRS Report Parameter

Select specify values option and then click add button to add the default value to the @Country Parameter. For the demo purpose, we entered Australia as the default value.

SSRS Report Parameter with Default Value 11

Now, if you go to the SSRS report preview, you will see the Australian sales which is the default parameter value.

SSRS Report Parameter with Default Value preview

You can choose the Get Values from a Query option, select the CountryDataSet, and assign EnglishCountryRegionName as the value field. Then, it selects the first row as the Default value. 

get Default Values from Query in SSRS Report Parameter

Check the SSRS Report Parameter with the Default Value preview.

SSRS Report Parameter with Default Value Preview