CODEWITHSUNDEEP
sql-servert-sqlreporting-servicessql-server-2008-r2ssrs-2008
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3303

Can SSRS dataset have a default value?

My SSRS report has one dataset (datasetMacys) that calls usp_GetStoreSales @Rundate, @StoreName.

Can the dataset have a default value? That way the report will only ask for one parameter (@Rundate) and the dataset has the string value "Macys" embedded?

In other words, the user will not have to select a value for the second parameter because it's already the default value of that dataset.

I would then add a 2nd dataset (datasetSears) where the default value for @StoreName is "Sears", which means it will only use the @Rundate that the user selected.

The stored procedure looks like this, even though it's not really needed for the question:

create procedure usp_GetStoreSales 
(
    @RunDate date,
    @StoreName varchar(10)
)
as

select * from [Sales] 
where RunDate = @RunDate and StoreName = @StoreName

I can easily fix this problem by creating two different stored procedures (ie. usp_GetMacysSales @RunDate and usp_GetSearsSales @RunDate), but that's exactly what I want to avoid.

Upvotes: 1

Views: 1528

Answers (2)

Hannover Fist
Hannover Fist

Reputation: 10860

Yes - you can have a default value for the parameter of a dataset.

In the Parameters tab of the Dataset Properties, you can type in (hard code) a value in the Parameter Value expression box.

enter image description here

Of course the next question would be WHY? There may be better ways to do it.

If you are going to have both sets of data, why not make a query that combines the data into one so you only have one dataset?

Upvotes: 2

Alan Schofield
Alan Schofield

Reputation: 21683

You just need to create your two datasets and then, for each dataset, righ click the dataset name, choose properties, click the parameters tab and overwrite the parameter value for the StoreName parameter.

Upvotes: 2

Related Questions