Report Builder 3.0 Forward Dependency Report Parameter

Question

I have a report that I am working on that will do the following:

• Return results based first on the community selected by the user.
• Filter to find alike addresses within the community, based on the number of square feet at each address.
• Set the end date (a column within the data table) to a user defined parameter for use in a WHERE at the end of the query.

The relevant information is stored in the following places:

• Community: ub_subdivision.descr
• Address: ub_serv_loc_addr.location_addr
• SqFt: arp_ops.dbo.vw_ub_serv_loc_classifications.SqFt

I have setup the query with 3 parameters:

• @Community
• @Months
• @Address

When the user is running the report, the following should happen (in this order):

1. The community parameter should populate the values stored in ub_subdivision.descr and allow the user to select the community they want from that list.
2. The address parameter should populate the values within the selected community from step 1, and allow the user to select the address they want from that list.
3. Based on the selected address, the query should store the value of the SqFt related to this address and use that in the WHERE statement as follows: WHERE (arp_ops.dbo.vw_ub_serv_loc_classifications.SqFt = @Address)
4. The months parameter should allow for user input to define how many months of data they want. This parameter is called in the query in the WHERE statement: WHERE (ub_bill_run.def_end_dt > DATEADD(m, -@Months, GETDATE())).

If I save the dataset and create a "table report" in Report Builder 3.0 it does the job of recognizing the various parameters and loading them into the Parameters folder and into the Datasets' parameters.

The problem I have is that I am not able to change the parameter properties to display Available Values and select "get from a query". If I go this route, and try to run the query I get an error that I am using "forward dependencies".

I need the @Address parameter to display the address field as the label, but store the sqft field as the value. This is the way I know how to do this and, unfortunately, it doesn't seem to work.

I would appreciate any insight anyone may have.

Thanks! John

Answer 1

You cannot have parameters based on your main data set. The forward dependency error is caused because your data set is to be filtered by your parameter, yet it is depending on the same data set to find its' set of values. This is a sort of paradox. When using queries to define the set of values for your parameters, make sure you create a new data set for each parameter.

Next, make sure the parameters are listed in the order you want them to run. Within the data sets for your parameters, you may use where clauses to make them dependent on one another in the order that they run.

In this example:

Parameter data set for Community:

SELECT DISTINCT ub_subdivision.descr
FROM  [YOUR JOINED TABLES]

Parameter data set for addresses:

SELECT DISTINCT ub_serv_loc_addr.location_addr
FROM  [YOUR JOINED TABLES]
WHERE ub_subdivision.descr IN (@Community)

Parameter data set for SqFt:

SELECT DISTINCT SqFt
FROM  [YOUR JOINED TABLES]
WHERE ub_subdivision.descr IN (@Community)
AND ub_serv_loc_addr.location_addr IN (@Address)

You should also make a month data set for your @month parameter, however it is not dependent on the other parameters so I will leave that to you.

Hope this helps!

Answer 2

There is one way to solve this make sure the order should be in the order of

• @Community
• @Months
• @Address

change order to:

• @Community
• @Address
• @Months

just delete existing @month and again add it manually and save it.

i hope it will work for you.