Post reply

Dynamically change report parameter dataset based on prior parameter value

  • October 19, 2010 at 9:40 pm

    #239989

    Having an initial parameter of type Boolean, is it possible to test for the value of this parameter, and thereby change the dataset of a second parameter at report runtime?

    That is, parameter 1 prompt is "Generate List?", with a default value of false.

    Parameter 2 takes its available values from a dataset, fed by a stored procedure, when the parameter 1 value is false.

    However. If parameter 1 changes to be true, I want to alter the available values for parameter 2 (in this case just to Null) - and, of course, to do the converse if the parameter 1 changes back to false again.

    No fancy application overlaying this - user views the reports solely through the report manager.

    Am I tilting at windmills? (And have I provided enough information?)

    Any thoughts appreciated.

  • October 20, 2010 at 9:53 am

    #1239784

    Hi in SSRS this is called cascaded parameter. ie the available values of a parameter changes based on the values of previously selected parameter.

    To do this, the SP which provides the available values of the second parameter (P2) should have an input parameter and this will be the values selected for first parameter (P1).

    So that the P2 will give you out put based on P1 = 1 or P1 = 0

    To do this for the dataset corresponding to P2 (say DS2) , you have to pass the parameter as Parameter!P1.value

    And in report parameter section, for P2 make the option as from query then select the dataset DS2, and the correct value field and label field.

    Thanks & Regards,
    MC

  • October 20, 2010 at 2:04 pm

    #1239910

    Thanks for your response - I'll give this a go.

  • October 20, 2010 at 2:48 pm

    #1239948

    So you want to control the visibility of a parameter based on an expression/query? I don't think this is possible, but if it is I would love to learn about it too.

  • October 20, 2010 at 2:57 pm

    #1239955

    Yes, effectively. You are correct, we cannot control the actual visibility of the parameter based on the selection in a previous one, but being able to change the source of the parameter's data, depending on choices made in earlier parameters, would overcome this problem (for me anyway).

  • October 21, 2010 at 7:28 am

    #1240299

    Ok, that presents an interesting problem. I would think it could be overcome but it is definitely a challenge in query writing.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply