SSRS shows dropdown instead of date picker

Question

I have a report with a single parameter Date/Time. The available values are from query

select cast(getdate() as date) c1 union all
select cast((getdate() - 1) as date)

But both VS preview and SSRS 2017 show that parameter values in dropdown/combobox and no date picker is presented. I need a date picker.

Any idea what's wrong ?

I tried converting it to various date types but nothing works. It doesn't work in IE, Chrome, nothing.

This is the rdl code:

  <DataSets>
    <DataSet Name="ds">
      <Query>
        <DataSourceName>SSDB</DataSourceName>
        <CommandText>
          select cast(getdate() as date) c1 union all
          select cast((getdate() - 1) as date)
        </CommandText>
      </Query>
      <Fields>
        <Field Name="c1">
          <DataField>c1</DataField>
          <rd:TypeName>System.DateTime</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>

  <ReportParameters>
    <ReportParameter Name="Date">
      <DataType>DateTime</DataType>
      <Prompt>Date</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>ds</DataSetName>
          <ValueField>c1</ValueField>
          <LabelField>c1</LabelField>
        </DataSetReference>
      </ValidValues>
    </ReportParameter>
  </ReportParameters>

  <Language>en-US</Language>

score 2 · Accepted Answer · answered Mar 05 '19 at 13:24

From my testing and what I've done personally in SSRS, the issue is with you specifying a specific set for available dates. The answer on this link describes what happens well.

Using a dataset to assign values to a filter, you must agree, is a typical practice of single-select and multi-select filters. By specifying values, you are telling SSRS some date values are legal, while others are illegal. The only way it can forbid illegal values be entered by the user is by taking away the anything-your-heart-desires-to-choose date picker.

Basically, the point is that you won't have an option for a date picker if you add anything to the available values. You can set a default value with no problem, but with no available values provided, you will see the date picker.

SSRS shows dropdown instead of date picker

1 Answers1