Ssrs date/time parameter to default to first date from a dataset

0
votes

I have a dataset called "activities" which is written in SQL and in this dataset there is a field called "created on".

When the report is executed it currently has date to and date from parameters in order to filter and only show records based on the selected dates.

What I require is the "start date" parameter to default to the first activities "created on" date.

E.g. Report executed and has multiple activities.

  • Activity a created on= 01/01/2017,
  • Activity b created on= 02/01/2017,
  • Activity c created on = 03/01/2017

Result: The start date parameter when the report is executed should default to 01/01/2017.

1
sql-serverreporting-servicesssrs-2008ssrs-2012ssrs-tablix
In the Report Parameter properties you can set a default value for a parameter. - Mat Richardson

1 Answers

5
votes

You can't have a default parameter that uses and expression. So you can't do this =Min(Fields!MyDateField.Value, "DataSet1") as you might expect. As this dataset depends on parameters, it's not available before the report is executed.

Instead you will need to create another dataset that provides the default value.

  1. Create a new dataset (e.g. dsStartDate)
  2. Set the datset query to be something like SELECT MIN(myDateColumn) as StartDate FROM myTable
  3. In your parameter properties, go to "Default Values", choose "Get values from a query"
  4. Select dsStartDate as the dataset and StartDate as the Value field.

That's it.