SSRS Dynamic Parameters

  • Hi there, I have a parameter called @level.

    In my sproc I have if @level = 1 begin .. end and @level = 2 begin .. end.

    I get the correct result sets based on the choice of this parameter in SSRS

    What I am after now though is if I choose say Level = 1 in the report, I want another parameter (@Installer) to be based on this choice ie a different subset of Installers depending on whether you have chose Level 1 or Level 2

    Is this possible?

    Regards, Roger

  • Something like if @level = 1 then select bla bla 1

    or if @level = 2 then select bla bla 2

    Rog

  • I think I have worked out the problem .. this is my original question

    I have created a dataset from a stored procedure. It has a parameter of @INST as the Installer. In the report I have created another dataset that populates the drop down for this parameter. This works no problem.

    I wanted to extend this report so that there is another parameter, @level. I have added this to the sproc in the form

    if @level = 1

    begin

    select

    various fields

    end

    if @level = 2

    begin

    select

    various other fields (different to level = 1 fields, but aliased the same so the report should still work)

    where @INST =

    end

    Again all works no problem, but what I wanted to do was dynamically change the drop down choices of @INST based on whether we choose 1 or 2 in the level parameter.

    In the report, on the definition of @INST I have tried a similar method ...

    if @level = 1

    begin

    select subset 1

    end

    if @level = 2

    begin

    select subset 2

    end

    I can hit the execute button, enter 1 or 2 in the parameter box, and get the correct data shown in the data grid, but when I try to preview I get

    "The report parameter 'INST' has a defaultValue or ValidValue that depends on the report parameter "level_1". Forward dependencies are not valid"

    --

    The problem is that although this works ok in the data grid, the @INST parameter is looking for a field that is not available when @Level = 2, so of course it will not work!

    I think my only way forward is to produce 2 reports, 1 for @level = 1 and 1 for @level = 2

    Rog

  • Rog - I have received that error message myself when setting up

    "cascading" parameters and other situations. The way I resolved it

    was to make sure that in the parameter dialog setup that the parameters

    were in the proper order (this is why I saw the "forward dependency

    not allowed" msg). If parm B depends on Parm A then Parm A must be above it in the listing in the parm dialog setup.

  • Brilliant, that's what it was! I won't be making that mistake again, cheers for you help!:-)

  • Just an FYI, I did a little blog post walking through how to create cascading parameters that you may find of interest at a new business intelligence community site, BIDN.

  • Brilliant, thanks for that 🙂

  • Looks like a great site too, just what I've been looking for, thanks again 🙂

Viewing 8 posts - 1 through 7 (of 7 total)

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