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?

Answer 1

I had the same issue as several others have indicated, where I had a parameter depending on another parameter that was listed after it.

However, just to be a little more clear for anyone else reading: If you are using Visual Studio 2019 like I am, then the cryptic "list" that everyone keeps referring to is the Parameters list that is found in the Report Data pane. You can access this pane in Visual Studio by going to View > Report Data at the top of Visual Studio.

For me, though, there were no up or down arrows. I had to literally open up the report's XML code (by clicking F7 or right-clicking the report file and selecting "View Code") and cut and paste the depended-upon parameter to the top of the Parameters section in the XML.

Answer 2

For me, I had to open rdl file and add my new parameter to the order/position(xml nodes) I want.

Answer 3

Happened to me too

There was one Parameters depending on another one

But the order of the depending one was on top

Used the arrows to move the calculated parameter to the end of all parameters.

Answer 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.

   

Answer 5

I also had this issue, following the all the answers above deinetely helped.

The tricky one with mine is that the dataset was an SSAS dataset therefore it was hidden. I had to search the code for the parameter to see where it was used and thats where I found it.

To view hidden datasets right click the Datasets folder in the Report Data tab and tick the 'Show Hidden Datasets' box.

Answer 6

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

Answer 7

I experienced similar problems with SSRS. The code was correct, the SQL parameters correct but the report was throwing parameter errors. I was using a Shared Dataset. I copied the same SQL to an Embedded Data Set and the report worked perfectly. So, I agree that the SSRS software has bugs that cause strange behavior.

Answer 8

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.

Answer 9

The problem also occurs if you reference a parameter "too early", e.g. from within an overriden OnInit. In that case it's not possible anymore to use a dataset for the available values nor for the default values of any parameter, even if the dataset itself is unrelated to the parameter in question.

Answer 10

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.

Answer 11

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

They should appear in the order of dependence.

Answer 12

I made a new report and copied the code over to the new report and saved it. It works perfectly now in that new report... I deleted the old one and renamed the new one, deployed to server and it everything is good. I wish I would have thought of this a lot sooner. SSRS is so funny, I don't even know what was actually causing the problem now, though...

any ideas on that?

(I had restarted SSRS a few times, restarted my computer a few times, deleted the .data files, deleted the copy off the server even though this was happening on my computer just to be safe... during all of this, btw)