Deploying report that's populating DataSet based on a parameter passed into the ReportViewer

0
votes

I am new to reporting services and to using the report viewer control in an aspx (C#). I am currently working on a project where I need to populate a report based on region. I am using SQL Express 2008 R2.

I have created a region parameter in the report, which is populated with a dataset using the following query:

enter image description here

SELECT RegionGUID, RegionDescription
FROM   utRegion

This then creates a DropDownList with the available values, which I then use in my main report dataset to obtain my results based on the region selected.

enter image description here

The issue I have, is users in my application are assigned to regions and depending on who's logged into my application should restrict what regions are available via reporting services. I am able to pass parameters from my application via the report viewer to my report in reporting services, but when I try adding a parameter to my dataset that is used to populate the region DropDownList I get the following error when deploying:

The definition of the report '/Report name' is invalid

and the following error when previewing:

The report parameter 'RegionGUID' has a DefaultValue' or a ValidValue that depends on the report parameter "RegionGUID". Forward dependencies are not valid.

My query with the region parameter:

SELECT RegionGUID, RegionDescription
FROM   utRegion
WHERE  (RegionGUID = @RegionGUID)
2
c#asp.netreporting-servicesssrs-2008
If you get an error "report is invalid" while deploying, there's probably another problem. Most likely the fields in your datasets changed, but some cell or other item in the report is still referencing an edited/removed field. Doesn't it give additional details (maybe if you run a preview) about what's wrong with the definition? - Jeroen
I've updated the question with more details of the error. I don't reference the parameter anywhere in the body of the report I only use it in the datasets used to populate the RegionDropDownList and the Report Dataset - ccStars

2 Answers

2
votes

I found the solution to my issue on the following site: http://www.sqlservercentral.com/Forums/Topic306513-150-1.aspx

As stated by Jason Selburg in the above link, It seems that the order of the parameter list is important.

To make my application work I done the following:

  1. Deleted my existing RegionGUID Parameter
  2. Added a New Parameter called UserAssignedRegion with the Available Values of not specified and the visibility of hidden
  3. Re-added my RegionGUID Parameter (visibility: visible) and set it to use the following query:

Query:

 SELECT RegionGUID, RegionDescription
 FROM utRegions
 WHERE RegionGUID = @UserAssignedRegion
0
votes

You should inspect the properties of the @RegionGUID parameter, specifically the DefaultValue or ValidValue tab. Either or both may depend on a dataset that uses the parameter itself in its query. You can't query a default or valid value for a parameter while using that parameter itself.

A typical query for available values would be:

SELECT DISTINCT  RegionGUID -- For value
                ,RegionDescription -- For label
FROM utRegion
-- No where clause, you can't or need to use the parameter itself

The query for the default value may be the same (perhaps only use the First(...) value), NULL, or based on a different dataset (that also does not use the parameter itself)