SSRS 2008: error message saying my parameter doesn't exist but it clearly does?

Question

I have a report that has about 10 parameters and about 10 datasets and one datasource. I typed alot but I really need help so I figured I would try and provide as much info about the problem as I could right from the start.

Let's simplify it all and pretend these are the relevant names of everything and that the report gets basic employee information:

• Parameters
  • facilityID: text in the format of a guid or uniqueidentifier, this is hidden and passed in so that the end user of the report can only view info for their own facility; Single valued parameter, not blank not null, hidden, text
  • startDate: start date range, let's say I want my report to include data starting Jan 1 this year; Single valued parameter, not blank not null, visible, datetime
  • endDate: end of the date range, let's say it's today/now; Single valued parameter, not blank not null, visible, datetime
  • viewOption: three hard-coded values (label, value) ("Current Employees",1) and ("Past Employees",2) and ("Employees that moved facilities",3); Multi-valued Parameter (so select all is an option), not blank, not null, visible, text
  • personID: based on the dataset getListOfNames where the label is the employee name and the value is their personID (guid/uniqueidentifier); Single select, not null, not blank, text and visible
    
• My Data Sets
  • getListOfNames: this gets a list of people that were at the company in the provided date range at the given facility and where their employee status is in the viewOption parameter value (so it can be just one number or up to three). This is a stored procedure but I have set up it to handle the multi-valued parameter by passing it join(parameter, "~") and then separating it out in the stored proc. This works on 5 other reports.
  • getReportInfoOnSelectedPerson: main body query; after the user selects the employee they want data on, this takes that personID and the date range and fills the main table.

The problem:
SSRS says viewOption doesn't exist. But I see it, right there, in the Parameters folder on the left hand side. I see it when I go to enter an expression under Parameters. There is no squiggly red line under Parameters!viewOption.value. But, when I try and put it as a value for a parameter used by the getListOfNames dataset, it errors. If I put it in the getReportInfoOnSelectedPerson dataset and use it in the exact same way, SSRS is okay with that. Wtf? So... I have checked the rdl and everything is fine (where the actual parameter is declared, where it is used in the dataset reference, everything). It's just this one dataset. And I have a similar report that uses the same data set, same basic parameters and that report is fine. I try setting the value of the dataset parameter to 1 or something and that is fine but when I try and set it to Parameter!viewOption.value it errors..... Now, above I said i normally pass the stored proc a Join on the paramater with a tilde ~ but I am trying to keep it simple and either get it to just work in general (either by passing it the first value of the multivalue viewOption parameter or turning that parameter into a single select and just passing the .value) but the join doesnt work either. All of those things work for the other dataset, which is also a stored proc.

This is my error:
An error has occurred during report processing. (rsProcessingAborted) The Value expression for the query parameter ‘@viewOption’ contains an error: The expression referenced a non-existing parameter in the report parameters collection. (rsRuntimeErrorInExpression)

Which is clearly saying my parameter doesn't exist but I can SEE it... everywhere. And if I assign one of the other datasets' parameter values to the viewOption parameter it works without an error. I've checked the rdl.

I've had this problem before and it was fixed by deleting both the parameter and dataset and creating them again (for safety I renamed both of them). That didn't work this time.

I am so frustrated. Please help....

Code?

      <DataSet Name="getListOfNames">
      <Fields>
          <Field Name="personID">
              <DataField>PersonId</DataField>
              <rd:TypeName>System.Guid</rd:TypeName>
          </Field>
          <Field Name="name">
              <DataField>name</DataField>
              <rd:TypeName>System.String</rd:TypeName>
          </Field>
      </Fields>
      <Query>
          <DataSourceName>myDataSource</DataSourceName>
          <CommandType>StoredProcedure</CommandType>
          <CommandText>getListOfNames</CommandText>
          <QueryParameters>
              <QueryParameter Name="@fac">
                  <Value>=join(Parameters!fac.Value,"~")</Value>
              </QueryParameter>
              <QueryParameter Name="@bldg">
                  <Value>=join(Parameters!bldg.Value,"~")</Value>
              </QueryParameter>
              <QueryParameter Name="@unit">
                  <Value>=join(Parameters!unit.Value,"~")</Value>
              </QueryParameter>
              <QueryParameter Name="@station">
                  <Value>=join(Parameters!station.Value,"~")</Value>
              </QueryParameter>
              <QueryParameter Name="@startDate">
                  <Value>=Parameters!startDate.Value</Value>
              </QueryParameter>
              <QueryParameter Name="@endDate">
                  <Value>=Parameters!endDate.Value</Value>
              </QueryParameter>
              <QueryParameter Name="@viewOption">
                  <Value>=Join(Parameters!viewOption.Value, "~")</Value>
              </QueryParameter>
          </QueryParameters>
          <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
      </Query>
  </DataSet>



<ReportParameter Name="viewOption">
  <DataType>String</DataType>
  <DefaultValue>
    <Values>
      <Value>1</Value>
    </Values>
  </DefaultValue>
  <Prompt>View</Prompt>
  <ValidValues>
    <DataSetReference>
      <DataSetName>viewOptionQuery</DataSetName>
      <ValueField>value</ValueField>
      <LabelField>label</LabelField>
    </DataSetReference>
  </ValidValues>
  <MultiValue>true</MultiValue>
</ReportParameter>



<DataSet Name="viewOptionQuery">
  <Fields>
    <Field Name="label">
      <DataField>label</DataField>
      <rd:TypeName>System.String</rd:TypeName>
    </Field>
    <Field Name="value">
      <DataField>value</DataField>
      <rd:TypeName>System.Int32</rd:TypeName>
    </Field>
  </Fields>
  <Query>
    <DataSourceName>flamingo</DataSourceName>
    <CommandText>select 'Other Facility' as label, 3 as value union select 'Past' as label, 2 as value union select 'Current' as label, 1 as value order by value</CommandText>
    <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
  </Query>
</DataSet>

This is how it is set up and getListOfNames is why the error is throw, if I change

              <QueryParameter Name="@viewOption">
                  <Value>=Join(Parameters!viewOption.Value, "~")</Value>
              </QueryParameter>

to

              <QueryParameter Name="@viewOption">
                  <Value>="1~2"</Value>
              </QueryParameter>

then it works.... or the value can be just 1 or 1~2~3

However... when I try and put join(Parameters!viewOption.value,"~") as the value for another dataset's query parameter it works and there is no error.

  <DataSet Name="getReportInfoOnSelectedPerson">
  <Fields>
    <Field Name="name">
      <DataField>name</DataField>
      <rd:TypeName>System.String</rd:TypeName>
    </Field> 
    <Field Name="Building">
      <DataField>Building</DataField>
      <rd:TypeName>System.String</rd:TypeName>
    </Field>
    <Field Name="Unit">
      <DataField>Unit</DataField>
      <rd:TypeName>System.String</rd:TypeName>
    </Field>
    <Field Name="desc">
      <DataField>desc</DataField>
      <rd:TypeName>System.String</rd:TypeName>
    </Field> 
  </Fields>
  <Query>
    <DataSourceName>myDataSource</DataSourceName>
    <CommandType>StoredProcedure</CommandType>
    <CommandText>Reports_BasicInfo</CommandText>
    <QueryParameters>
      <QueryParameter Name="@personID">
        <Value>=Parameters!personID.Value</Value>
      </QueryParameter>
      <QueryParameter Name="@numberINeedToAggregateData">
        <Value>=Join(Parameters!viewOption.Value,"~")</Value>
        <rd:UserDefined>true</rd:UserDefined>
      </QueryParameter>
    </QueryParameters>
    <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
  </Query>
</DataSet>

And I already said this, but the stored proc/dataset that throws the error uses a parameter with the same set up in about 5 other reports (I have tried copying and pasting the relevant code sections from working reports but I still get the error). So, what's wrong with this one?

Solution 1

I found in my situation that it was actually the parameters name casing was slightly changed and it was throwing this same error.

I had to change the name to the correct casing, and then search in the code behind to anything that refers to that parameter and correct its casing. Deployed fine after that.

Solution 2

Try changing the order of the parameters using the up/down arrows.

They should appear in the order of dependence.

Solution 3

Re-ordering the report parameters based on their dependency fixed the issue for me. I had the report parameter at the bottom of the list but the one above it was dependent on this bottom one. So when you are creating / re-creating the report parameter make sure the order is right.

Solution 4

This could mostly be due to the parameter name not being updated in the dataset.

This article helped me resolve it

When editing Parameters (names or case) for a Dataset within SSRS you may encouter the previous error message when you preview the report. While on the surface it may appear that the parameter has the same case in the Parameter settings and the Dataset query – there is another place where a change is required.

1. Open the Dataset properties for the Dataset (s) that use the Parameter in the error message.
2. Select the Parameters property in the left list pane.
3. Click the Expression Editor button for the specified Parameter.

4. Here you will notice that the Expression is underlined in red. This is where you will need to correct the case of the Parameter name.

   

Solution 5

Had the same problem. Check the Parameters tab of the Dataset Properties and click the expression (fx) button. The parameters don't seem to refresh automatically here, one of my parameters still had uppercase instead of lowercase spelling

View more solutions