Parameter. Report Builder 3.0

1
votes

I have trouble to display parameter. I want to drop down list of State abbreviations where the policy effective date is between StartDate and End Date.

I have three parameters: StateAbbreviate, StartDate, EndDate.

In SQL, I created procedure with the following conditions for parameter:

rds.StateAbbreviate = @StateAbbreviate and PolicyEffectiveDate Between @StartDate and @EndDate

rds.StateAbbreviate is from different view.

In Report Builder, I also have these three parameters. Everything is working fine when I just type the state code. When I tried to create drop down list using "Available Values" (Report Parameter Properties) I got this error:

The report parameter ‘StateAbbreviate’ has a DefaultValue or a ValidValue that depends on the report parameter “StateAbbreviate”. Forward dependencies are not valid.
1
sqlparametersreportbuilder3.0
You have rds.StateAbbreviate = @StateAbbreviate(The report parameter) and then you are setting the Available values of the parameter as rds.StateAbbreviate thats a circular reference. Hence the error. - TMNT2014
TMNT2014, How can I fix this? - user3727778
You would need another dataset that populates the StateAbbreviate parameter - TMNT2014

1 Answers

0
votes

startdate and enddate parameters must appear in your parameter list BEFORE the stateabbreviate parameter. ReportBuilder is not good with allowing rearranging and it enters parameters sequentially, in the order which they are entered. You may have to delete the params. and re-enter them in order - StartDate, EndDate, StateAbbreviate. Then, stateabbreviate can use the previous 2 parameters to dynamically run the sp.