Set Default parameter in SSRS from a dataset

Question

How can i set the default parameter in SSRS using a value from Dataset?

I want the default parameter to be the previous month date that is selected from a table tblPeriod(per_id, lastDay)

tblPeriod stores the months in a set of 20 year with last day storing the last day in a month.

e.g

2000, 31-Dec-2016

1999, 30-Nov-2016

I wrote this SP getPeriod which works like this-

select per_id, lastDay , (select per_id from tblPeriod where lastDay < getDate()) as maxDate from tblPeriod

The report populates a drop down with all period values

How to make the default date as previous month end date using the maxDate value returned by the Stored Procedure?

<ReportParameter Name="period">
  <DataType>Integer</DataType>
  <Prompt>Period</Prompt>
  <ValidValues>
    <DataSetReference>
      <DataSetName>Periods</DataSetName>
      <ValueField>per_id</ValueField>
      <LabelField>lastDay</LabelField>
    </DataSetReference>
  </ValidValues>
</ReportParameter>

I would also like to know how to set the top most item in a parameter drop down as the default selected item in the report

you can play with `variable` - From Menu - Report > Report Properties > Variables and add new variable. Then set default value by simple sql function. — Pedram, Jan 01 '16 at 12:34

score 0 · Answer 1 · answered Jan 05 '16 at 20:11

Click on default value option then click on function then you can write a function to get the date you are wanting like [@parameterName] will get the entered parameter then use some of the math function to modify it like you need. Your problem is hard to solve without seeing what you are doing in the GUI and you will have to get pretty creative to solve it.

Sorry I can't be of more help. If you add some screen shots I may be able to help more.

Set Default parameter in SSRS from a dataset

1 Answers1