CODEWITHSUNDEEP
reporting-services
SherlockSpreadsheets
SherlockSpreadsheets

Reputation: 2370

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

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:

Remove/Add the parameters:

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

Screenshot - Param Dropdown (wrong oder):

enter image description here

Screnshot - XML:

enter image description here

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

Upvotes: 1

Views: 9497

Answers (1)

StevenWhite
StevenWhite

Reputation: 6034

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.

  1. In the Report Data section, under the Parameters folder, click on paramLocCode to select it.
  2. 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.

Upvotes: 1

Related Questions