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.

• Shared Data Source.
• Embedded Data Source.
• Embedded DataSet.
• Table Report

The Sql query that we used above SSRS example is:

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

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

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.

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.

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

Once you select something, it returns the report.

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

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.

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

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. 

Check the SSRS Report Parameter with the Default Value preview.