none
Cascading Report Parameters Error Despite Order in Report Data Pane

    Question

  • I am trying to adjust a somewhat detailed report created by another person.  Currently there are only 2 parameters.  I use the arrows in the Report Data pane to change the cascading dependencies but get the error The report AppID has a DefaultValue or a ValidValue that depends on the report parameter Status. Forward dependencies are not valid. After researching this error, it seems like all I am supposed to do is change the order of the parameters in the Report Data pane.  I feel like there is some other aspect involved that links the parameters together.  I'm guessing the dataset queries are supposed to be adjusted but I am not sure.

    Thanks in advance!
    Friday, August 23, 2013 10:04 PM

Answers

  • Yes Hellbox you are correct. You need to change the dataset queries. Previously for applicationID dataset, you were having the where clause on Reportstatus. Now you want first to pick the applicationID and based on that status IDs should populate. So first remove the where clause from applicationID dataset query and put the where clause on reportstatus dataset query as below

    select report_status from table where application id in (@ApplicationID)

    and param order will be first ApplicationID and then Report status.

    Hope this is helpful to you.

    Thanks,

    Nilima


    Nilima

    • Marked as answer by Hellbox Tuesday, August 27, 2013 1:05 PM
    Tuesday, August 27, 2013 6:15 AM

All replies

  • Hi Hellbox,

    According to your description, the error accrued when we use the arrow in the Report Data pane to change the order of the cascading parameters. And when you change the order of the cascading parameters once again to make the parameter Status above the parameter AppID, that will make the error go away.

    With cascading parameters, the list of values for one parameter depends on the value chosen in preceding parameter. Order is important for cascading parameters because the dataset query for a parameter later in the list includes references to parameters earlier in the list. So when you have a parameter higher in the list that depends on the value of another parameter which is lower in the list, this error will happens.

    Reference:
    http://myitforum.com/cs2/blogs/jnelson/archive/2011/08/18/158901.aspx 

    If you have any more questions, please feel free to ask.

    Thanks,
    Katherine Xiong

    Monday, August 26, 2013 10:03 AM
  • Thanks for the reply Katherine.  I understand the importance of the report parameter order.  I read your link, but it does not tell you what is required to fix the datasets to reflect a new report parameter order.  I may be misunderstanding something if the link did address this.  This report is supposed to show a person's application status.  Currently it requires the Status parameter to have a value then it cascades the available values down to the ApplicationID parameter to show only valid values.  If I want to reverse the process, where I first pick the Application ID, and then see only a list of valid values for the Status parameter, it gives me the error I mentioned in my original post.  Am I supposed to changes something on the dataset quires or maybe change the parameter property on the dataset properties?

    Thanks

    Monday, August 26, 2013 1:20 PM
  • Hi Hellbox,

    Thanks for your post.

    Just as you post, when you change the order of the cascading parameters, it will get an error. When the error accrues, they will not change anything in the dataset query and in the parameter property in the dataset properties.

    Besides, if the two parameters are cascading parameters, create the dataset query maybe to like this:

    SELECT   DISTINCT  ApplicationID, Status
    FROM     table_name
    WHERE    (Status IN (@Status))

    Reference:
    http://technet.microsoft.com/en-us/library/aa337498(v=sql.105).aspx 

    Thank you for your understanding.

    Thanks,
    Katherine Xiong

    Tuesday, August 27, 2013 3:14 AM
  • Yes Hellbox you are correct. You need to change the dataset queries. Previously for applicationID dataset, you were having the where clause on Reportstatus. Now you want first to pick the applicationID and based on that status IDs should populate. So first remove the where clause from applicationID dataset query and put the where clause on reportstatus dataset query as below

    select report_status from table where application id in (@ApplicationID)

    and param order will be first ApplicationID and then Report status.

    Hope this is helpful to you.

    Thanks,

    Nilima


    Nilima

    • Marked as answer by Hellbox Tuesday, August 27, 2013 1:05 PM
    Tuesday, August 27, 2013 6:15 AM