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
It's an Oracle data source. If I use @ instead of : then I get an error message, ORA-00936: missing expression — SherlockSpreadsheets
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
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

StevenWhite StevenWhite · Accepted Answer · 2020-07-23T23:02:57

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.

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

1 Answers