SSRS reports using Report Parameter in two datasets

Question

I'm fixing some really old SSRS reports. I'm doing it by editing the rdl files directly in notepad++ which so far has been working well since the format is easily readable XML.

However, I have run into a problem trying to use the same ReportParameter in two datasets.

I have this in my rdl file:

  <ReportParameters>
    <ReportParameter Name="Fromdate">
      <DataType>DateTime</DataType>
      <Prompt>From date:</Prompt>
    </ReportParameter>
    <ReportParameter Name="Todate">
      <DataType>DateTime</DataType>
      <Prompt>To date:</Prompt>
    </ReportParameter>
  </ReportParameters>

This makes it so that before the report is opened, the user enters two dates that are then used in the queries that show data in the report.

The first query is working well with this. It's an Oracle query and looks like this:

    <DataSet Name="OracleDS">
      <Query>
        <DataSourceName>Oracle</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="Fromdate">
            <Value>=Parameters!Fromdate.Value</Value>
            <rd:UserDefined>true</rd:UserDefined>
          </QueryParameter>
          <QueryParameter Name="Todate">
            <Value>=Parameters!Todate.Value</Value>
            <rd:UserDefined>true</rd:UserDefined>
          </QueryParameter>
        </QueryParameters>
        <CommandText>SELECT myColumn FROM myTable WHERE myDate BETWEEN :Fromdate AND :Todate</CommandText>
      </Query>
      <Fields>
        <Field Name="myColumn">
          <DataField>MyColumn</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>

Getting the :Fromdate and :Todate works in this query. However, when I try to do the same in my other query, which is an MSSQL query, I can't open the report because SSRS tells me there's a problem with the query. Here's what the second dataset looks like:

<DataSet Name="MSSQLDS">
    <Query>
       <DataSourceName>MSSQL</DataSourceName>
       <CommandText>SELECT myColumn FROM myOtherTableOnAnotherDatabase WHERE myDate BETWEEN :Fromdate AND :Todate</CommandText>
      </Query>
      <Fields>
        <Field Name="myColumn">
          <DataField>myColumn</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>

Seems pretty straight forward. I have even tried switching places between the two datasets in the file, but the Oracle dataset works while the MSSQL dataset doesn't.

I thought maybe I need to add ' around the dates, so I tried some stuff like:

[...]
WHERE myDate BETWEEN ':Fromdate' AND ':Todate'
[...]
WHERE myDate BETWEEN '&amp; :Fromdate &amp;' AND '&amp; :Todate &amp;'

DECLARE @startdate datetime DECLARE @enddate datetime
SET @startdate = CAST(:Fromdate as datetime)
SET @enddate = CAST(:Todate as datetime)
SELECT myColumn FROM myTable
WHERE myDate BETWEEN @startdate AND @enddate

But SSRS keeps telling me that there's an error getting the data for this dataset. It works if I hard code the dates in, like so:

<CommandText>SELECT myColumn FROM myOtherTableOnAnotherDatabase WHERE myDate BETWEEN '2020-01-01' AND '2020-01-31'</CommandText>

How can I use the same report parameters in the command texts of two datasets?

doosh doosh · Accepted Answer · 2020-02-25T14:37:35

Just as I was about to post my question I realized that I was assuming that the report parameter should be passed with :ParameterName since that's how it was done for the Oracle part (and I'm not super familiar with Oracle syntax since I've been working mostly with MSSQL in the past). So I changed : to @ for the MSSQL query and it worked:

WHERE myDate BETWEEN @Fromdate AND @Todate

D'oh!

SSRS reports using Report Parameter in two datasets

1 Answers