SSRS - query parameter reference which does not exist in Parameters collection

Question

I have a report that works. I add a new data set (report still work). I add parameters to the new dataset (report fails). The full error message is this:

The Value expression for the query parameter ':IP_START_DATE' contains an error. The expression references the parameter 'paramStartDate', which does not exist in the Parameters collection. Letters in the names of parameters must use the correct case.

My problem is very similar to this one (SSRS 2008: error message saying my parameter doesn't exist but it clearly does?). But I have tried the suggestions there (Reorder report parameters, Copy the code to a new report, delete the ".Data" file, parameter casing is correct).

None of these work, I keep getting the error. Something that looks very odd to me is when I configure the Dataset Parameters Value to map to the Name, the Values are not in the correct order as defined in my Report Parameters section. Screenshot include below.

Does anyone have an answer as to why the drop down in my screenshot below is not in the correct order? And how to resolve this? I believe this could be the cause. Some things I have tried are included below. I have also verified all fields in the source database are in upper case, and my SQL dataset matches this.

Thanks, Brian

See Below:

Screenshot of the mapping with wrong order
Screenshot of the XML tags for "ReportParameters"
XML code for "ReportParameters"
Steps I have taken to completely delete and re-add the parameters but they are still in the wrong order.

Remove/Add the parameters:

In the Code, DELETE the "" and "" nodes (found after "/ReportSections" and before "Code").
Remove all "" nodes.
View the report designer and verify report and query parameters are gone. Close the designer file. Open the designer file.
In the Code, ADD the "ReportParameters" and "ReportParametersLayout" nodes back in (between found between "/ReportSections" and "Code").
In the Report refresh your data sets. Then map the parameters.

Screenshot - Param Dropdown (wrong oder):

Screnshot - XML:

the XML:

<ReportParameters>
    <ReportParameter Name="paramLocCode">
        <DataType>String</DataType>
        <Prompt>Location</Prompt>
        <ValidValues>
        <DataSetReference>
        <DataSetName>listLocationData</DataSetName>
        <ValueField>LOC_CODE</ValueField>
        <LabelField>fxLabel</LabelField>
        </DataSetReference>
        </ValidValues>
    </ReportParameter>
        <ReportParameter Name="paramStartDate">
        <DataType>String</DataType>
        <DefaultValue>
        <Values>
        <Value>=Format(Today(), "MMddyy")</Value>
        </Values>
        </DefaultValue>
        <Prompt>Start Date "MMDDYY"</Prompt>
    </ReportParameter>
        <ReportParameter Name="paramEndDate">
        <DataType>String</DataType>
        <DefaultValue>
        <Values>
        <Value>=Format(Today(), "MMddyy")</Value>
        </Values>
        </DefaultValue>
        <Prompt>End Date "MMDDYY"</Prompt>
    </ReportParameter>
</ReportParameters>
<ReportParametersLayout>
    <GridLayoutDefinition>
        <NumberOfColumns>2</NumberOfColumns>
        <NumberOfRows>3</NumberOfRows>
        <CellDefinitions>
            <CellDefinition>
            <ColumnIndex>0</ColumnIndex>
            <RowIndex>0</RowIndex>
            <ParameterName>paramLocCode</ParameterName>
            </CellDefinition>
            <CellDefinition>
            <ColumnIndex>0</ColumnIndex>
            <RowIndex>1</RowIndex>
            <ParameterName>paramStartDate</ParameterName>
            </CellDefinition>
            <CellDefinition>
            <ColumnIndex>1</ColumnIndex>
            <RowIndex>1</RowIndex>
            <ParameterName>paramEndDate</ParameterName>
            </CellDefinition>
        </CellDefinitions>
    </GridLayoutDefinition>
</ReportParametersLayout>

SQL Dataset Query:

--listLocationData
-- DEFINE ip_start_date =  '070120';
-- DEFINE ip_end_date = '070120';

SELECT DISTINCT 
    t1.LOC_CODE
    , t2.LOC_DESCR
    , t2.COMPANY
    , t2.STATE_CODE
    , t2.TYPE
FROM 
    VEHICLE_COST t1
    JOIN VEHICLE_LOC t2
    ON t1.LOC_CODE = t2.LOC_CODE
WHERE     TO_CHAR(INVOICE_DATE,'MMDDYY') BETWEEN :IP_START_DATE AND :IP_END_DATE

shouldn't your where statement be >> between @paramStartDate and @paramEndDate? — Harry, Jul 23 '20 at 01:37
It's an Oracle data source. If I use `@` instead of `:` then I get an error message, `ORA-00936: missing expression` — SherlockSpreadsheets, Jul 23 '20 at 01:43
what happens if you remove the : in front of the parameter names? I haven't had experience with Oracle, but logic suggests that the : is only to identify it as a parameter and not part of the parameter name itself — Harry, Jul 23 '20 at 02:16
I removed the : in the parameter name. And I get the original error message, `expression references the parameter 'paramStartDate', which does not exist in the Parameters collection`. — SherlockSpreadsheets, Jul 23 '20 at 02:21
I work with SSRS and Oracle a lot. Btw, I've never had to look at the XML to fix issues like this. Everything you showed so far looks normal. Why is `paramLocCode` not in the SQL? Does it have anything set in `Available Values`? Which parameters is DataSet1 using? Do both datasets use the same data source? The param value dropdown is alphabetical so that's OK. You mentioned that it breaks when you add parameters, but it should add those for you when you add the dataset, care to elaborate? Hopefully going through this will help isolate the issue. — StevenWhite, Jul 23 '20 at 16:12
@StevenWhite - Why is paramLocCode not in the SQL? `Because the "listLocationData" SQL should create the valid values for "paramLocCode"`. Does it [paramLocCode] have anything set in Available Values? `See previous answer`. Which parameters is DataSet1 using? `All three (paramLocCode, paramEndDate, paramStartDate)`. Do both datasets use the same data source? `Yes`. To elaborate-- Yes, when I create the params in the SQL it auto creates them for the DS, which I then map from the Report params.Then I get the error message I posted, **not in Parameters collection**. — SherlockSpreadsheets, Jul 23 '20 at 18:10

score 1 · Accepted Answer · answered Jul 23 '20 at 23:02

Based on the additional information in your comment, this appears to be an issue with the parameter dependency. The start and end dates are used to generate location codes which are then populated in a drop-down and used for the subsequent dataset. If I'm understanding this correctly, the solution is to reorder the parameters.

In the Report Data section, under the Parameters folder, click on paramLocCode to select it.
Use the blue Down arrow at the top of the Report Data section to move it below the two date parameters.

Now the parameters will be referenced in order of their dependency on each other. The order of the datasets doesn't matter.

You're absolutely right. Can't believe I didn't see that. This was a huge help since I was having this same problem with a dozen other reports I was working with on a conversion from Crystal Reports project. Thanks! — SherlockSpreadsheets, Jul 24 '20 at 02:19

SSRS - query parameter reference which does not exist in Parameters collection

1 Answers1