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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply