Search This Blog

Tuesday, 16 September 2014

SSRS Forward Dependencies is invalid

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
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
                EMP.Phone ,
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
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
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.

No comments:

Post a Comment