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. Writing the below code inside the SSMS shows the data set that we use for this SSRS Report Parameter with a Default Value example.

The Sql query that we used in this SSRS example is shown below. If you observe the below query, we have used the where condition with @Country Parameter.

SELECT Geo.EnglishCountryRegionName AS Country, 
       Geo.StateProvinceName AS State,
	   Geo.City,
       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

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

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.

We have designed a simple table report of Country sales and formatted the font and colors. If you observe the report preview, we typed the Canadian country name manually.

Report Preview

Within the Report Data window, double-click or right-click and choose the @Country Parameter Properties option will open the Report Parameter Properties window. 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. Please refer to the Report Parameters article to understand the properties.

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.

Report Preview

Double-click the @Country Parameter, and go to the Default Values tab. Select the Specify values option and then click the 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, instead of the empty page, 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. It shows the Australian sales because Australia is the first country name within the dataset. If your DataSet is sorted in sorted in descending order, the US may appear.

SSRS Report Parameter with Default Value Preview