It has been seen many times developer’s who are new to
SSRS experience this issue (Forward dependencies is invalid).
Let’s reproduce the issue
In this example I have used the AdventureWorksDW database
.
DataSource =AdvDw
DateSet=fwdDep
Lets create a report ForwardDepandencies.rdl in your
BIDS, In which your user want to see the following information about
employee in Sale territoryRegion wise based
on SaleterritoryRegionWise
First Name , Last Name ,Phone number, SalesTerritoryRegion
To create this report you
need write some SQL query which will display the information about
employee
SELECT
EMP.FirstName,
EMP.LastName,
EMP.Phone ,
ST.SalesTerritoryRegion,
ST.SalesTerritoryKey
FROM dbo.DimEmployee Emp WITH (NOLOCK)
INNER JOIN dbo.DimSalesTerritory
ST with (NoLOCK)
on EMP.SalesTerritoryKey=ST.SalesTerritoryKey
WHERE ST.SalesTerritoryKey=@saleTerritoryKey
2. Create a Data source as you wish
3. Create a Dataset as you wish
4. Right click on your dataset put you SQL in Query and
click Ok
5. When you will expand parameter folder in BIDS green
circled 2 in the below Image
In the above example we need to display the information a
few columns
First Name,Last Name,Phone number,SalesTerritoryRegion
but our Sql consist one more column SalesTerritoryKey to which we are passing as parameter value to display the employee information in SalesTerritoryRegion wise . When user will pass the saleterritoryKey in employee information for that SalesTerritoryRegion should display in your report
but our Sql consist one more column SalesTerritoryKey to which we are passing as parameter value to display the employee information in SalesTerritoryRegion wise . When user will pass the saleterritoryKey in employee information for that SalesTerritoryRegion should display in your report
Usually developer who is new to SSRS tries to map the Parameter value from resultset dataset value
As in this example resultSet dataset (FwdDep) SaleterritoryKey value
As in this example resultSet dataset (FwdDep) SaleterritoryKey value
When you will Right Click on parameter as in this example
Saleterritory
It will ask you to fulfill the following
fields
Name : name of the parameter
Prompt: parameter text which you wish to
display in report
Now you click on Available values see below image
Select get values from query , See
carefully on below image red circled field. Here we are passing the value to parameter from resultset Dataset
to result Dataset which is creating Forward Dependencies.
Hence we are getting
below error
To resolve this issue we need the
following step
1. Go to thie BIDS , Right Click on datasource Add another dataset as you wish in this
example we have created FwdPP
2. Write the below sql in query window see below image
SELECT DISTINCT ST.SalesTerritoryRegion,ST.SalesTerritoryKey
from dbo.DimSalesTerritory ST with (NoLOCK)
Click OK , now you will see another
Dataset in your BIDS
Now go to you parameter folder right
click on your Saleterritory parameter .
Click on available values
Pass the parameter like
Dataset: FwdPP . the vales from this
dataset will be passed to resultdataset
FwdDep.
Value field : Which will be passd to resultset
parameter i.e (WHERE ST.SalesTerritoryKey=@saleTerritoryKey) in
resultset datset sql. SaleTerritoryKey from FwdPP will be passed to FwdDep.
Label Field which you need to display in
your report
Now you preview your report select the
value you will get report as your selected parameter.
