June 2, 2009 at 4:48 am
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
June 2, 2009 at 5:19 am
Something like if @level = 1 then select bla bla 1
or if @level = 2 then select bla bla 2
Rog
June 2, 2009 at 9:26 am
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
June 3, 2009 at 6:21 am
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.
June 3, 2009 at 7:57 am
Brilliant, that's what it was! I won't be making that mistake again, cheers for you help!:-)
December 21, 2009 at 2:19 am
Brilliant, thanks for that 🙂
December 21, 2009 at 2:49 am
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