This SSRS article shows how to hide the Parameter in report previews so we can use this report as the reference to create linked reports. For instance, if you want to restrict the sales report to a particular country and duplicate the same report with different country sales.
Right-click on the Datasets folder to create a new DataSet. Writing the code below inside the SSMS shows the data set we use for this hide SSRS report parameter example.
The Sql query that we used for this SSRS example is:
SELECT Geo.[EnglishCountryRegionName] AS [Country]
,Geo.[StateProvinceName] AS [State],Geo.[City]
,Cust.FirstName +' '+ Cust.LastName AS [Full Name]
,Cust.EnglishOccupation AS Occupation, Cust.Gender
,SUM(Cust.YearlyIncome) AS Income, SUM(Fact.OrderQuantity) AS Orders,
,SUM(Fact.SalesAmount)AS Sales
FROM DimCustomer AS Cust
INNER JOIN FactInternetSales AS Fact ON Cust.CustomerKey = Fact.CustomerKey
INNER JOIN [DimGeography] AS Geo ON Cust.GeographyKey = Geo.GeographyKey
WHERE EnglishCountryRegionName = @Country
GROUP BY Geo.[EnglishCountryRegionName],Geo.[StateProvinceName] ,Geo.[City]
,Cust.FirstName,Cust.LastName, Cust.EnglishOccupation,Cust.Gender
ORDER BY [Country]
If you observe the above query, we have used the where condition with @Country Parameter.
How to Hide SSRS Report Parameter?
The below screenshot shows the available columns in a DataSet and the automatically created Parameter. Next, to create a table report, right-click on the empty space, select Insert, and choose the Table option. We have designed a simple table report of Employee sales and formatted the font and colors.
If you observe the report preview, we type the country name manually.
Double-click the @Country Parameter, which will open the Report Parameter Properties window.
Next, 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. We entered the United States as the default value for demo purposes.
Now, if you go to the SSRS report preview, you won’t see any text box to enter the parameter, i.e., country name, because it was hidden. It is because, by default, it displays all the employee records belonging to the United States.
Let me duplicate the above report and double-click the @Country Parameter. This time we used Australia as the default value for the @Country Parameter.
In this hide SSRS report parameter preview, you can see all the employees belonging to Australia. Please refer to the article of the linked report for further reference.
