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?
