Create drop down lists for user inputs in SSRS.

Let's say you have a stored procedure that takes two input parameters to generate results. What if, when a user enters values for the sproc in an SSRS report, you wished to restrain their values to a series of options. You could do so like this:

<ReportParameters>
   <ReportParameter Name="DoomDate">
      <DataType>String</DataType>
      <AllowBlank>true</AllowBlank>
      <Prompt>What day will the world end?</Prompt>
      <ValidValues>
         <DataSetReference>
            <DataSetName>DarkDates</DataSetName>
            <ValueField>dtLabel</ValueField>
            <LabelField>dtLabel</LabelField>
         </DataSetReference>
      </ValidValues>
   </ReportParameter>
   <ReportParameter Name="SkyColor">
      <DataType>String</DataType>
      <Prompt>What color is the sky?</Prompt>
      <ValidValues>
         <ParameterValues>
            <ParameterValue>
               <Value>0</Value>
               <Label>Black</Label>
            </ParameterValue>
            <ParameterValue>
               <Value>1</Value>
               <Label>Blue</Label>
            </ParameterValue>
         </ParameterValues>
      </ValidValues>
   </ReportParameter>
</ReportParameters>

 
 

The second DataType above has its selectable values hard-coded. The first gets its values populated by data like so:

<DataSet Name="DarkDates">
   <Query>
      <DataSourceName>Whatever</DataSourceName>
      <CommandText>
         SELECT DISTINCT CONVERT(VARCHAR(10),PaymentDate,111) AS dtSort,
               CONVERT(VARCHAR(10),PaymentDate,101) dtLabel
         FROM FutureEvents
         ORDER BY CONVERT(VARCHAR(10),PaymentDate,111) DESC
      </CommandText>
      <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
   </Query>
   <Fields>
      <Field Name="dtSort">
         <DataField>dtSort</DataField>
         <rd:TypeName>System.String</rd:TypeName>
      </Field>
      <Field Name="dtLabel">
         <DataField>dtLabel</DataField>
         <rd:TypeName>System.String</rd:TypeName>
      </Field>
   </Fields>
</DataSet>

 
 

The DataTypes will create drop down lists. Their values get associated to the stored procedure like so:

<Query>
   <DataSourceName>Whatever</DataSourceName>
   <QueryParameters>
      <QueryParameter Name="@DoomDateInSproc">
         <Value>=Parameters!DoomDate.Value</Value>
      </QueryParameter>
      <QueryParameter Name="@SkyColorInSproc">
         <Value>=Parameters!SkyColor.Value</Value>
      </QueryParameter>
   </QueryParameters>
   <CommandType>StoredProcedure</CommandType>
   <CommandText>sp_SomethingErOther</CommandText>
</Query>

 
 

The three blobs of XML here are three different bits of one SSRS report using the sp_SomethingErOther stored procedure.