none
Forward dependencies are not valid - Error when trying to build cascading parameters

    Question

  • How to add cascading parameter to Reports?

    I have SQL Server 2008RS SSRS and I'm creating reports by BIDS. I'm not using OLAP qube.
    I have 2 parameters in report. CalenderKey and ParameterKey

    What wish to do with parameter is that each parameter options correspond available values.
    If end-user select CalenderKey as June 2013, then he should see only Categories that exist in that month.

    I have assumed that this is done with cascading parameters, but I get error:

    Parameter settings:
    Available Values and Defaul Values are configured for DataSets.
    Automatically determine when to refresh option is on.

    /*DataSet1 (the main SQL Query for report)*/
    WHERE Month IN (@CalenderKey) and Category IN (@CategoryKey)

    /*CalenderKey DataSet*/
    WHERE Category IN (@CategoryKey)

    /*CategoryKey DataSet*/
    WHERE Month IN (@CalenderKey)

    Error:
    An error occured during local report processing
    The definition of the report /reportname is invalid
    The report parameter 'CategoryKey' has a DefaultValue or a ValidValue
    that depends on the report parameter "CalenderKey". Forward dependencies are not valid.


    Kenny_I

    Thursday, July 25, 2013 6:20 AM

Answers

  • Hi Kenny_I,

    The issue occurs because the parameter CalenderKey depends on the parameter CategoryKey, and the parameter CategoryKey also depends on the parameter CalenderKey at the same time. That's what "forward dependencies" means. In Reporting Services, it is not allowed for two parameters to depend on each other. To avoid this issue, we must remove the forward dependencies relationship. In other words, we must either make the CalenderKey depends on CategoryKey or make the CategoryKey depends on CalenderKey. In this issue, you need to remove the WHERE clause in either CalenderKey DataSet or CategoryKey DataSet.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    • Marked as answer by Kenny_I Monday, July 29, 2013 11:01 AM
    Friday, July 26, 2013 9:02 AM

All replies

  • Hi Kenny_I,

    The issue occurs because the parameter CalenderKey depends on the parameter CategoryKey, and the parameter CategoryKey also depends on the parameter CalenderKey at the same time. That's what "forward dependencies" means. In Reporting Services, it is not allowed for two parameters to depend on each other. To avoid this issue, we must remove the forward dependencies relationship. In other words, we must either make the CalenderKey depends on CategoryKey or make the CategoryKey depends on CalenderKey. In this issue, you need to remove the WHERE clause in either CalenderKey DataSet or CategoryKey DataSet.

    Regards,
    Mike Yin

    If you have any feedback on our support, please click here


    Mike Yin
    TechNet Community Support

    • Marked as answer by Kenny_I Monday, July 29, 2013 11:01 AM
    Friday, July 26, 2013 9:02 AM
  • you can create one more intermediate parameter and pass the value to that parameter first and then for the next paramerter pass the intermediate parameter value.

    Friday, July 26, 2013 9:30 AM