parameter dependency

hi folks
I have a Report Parameters issue:
Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
Parameter# 2 is a (organization) drop-down with 'All','US','Canada',etc.
There is NO dependencies between #1 & #2 and both are SQL Queries
Parameter# 3 is a (location) drop-down based on #1+#2:
like 'All-All', 'All-US','2001-Canada',etc.
I have a (functioning) stored proc. for parameter#3, but I'm having issues
with constructing the value for the sproc call. It doesn't let me do things
like:
exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
-or-
exec sp_location @.YR,@.CNTRY
also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
'2000-US' should bring back all locations for drop-down#3, like
(2000-US-)DETROIT, etc.)
I keep getting forward dependency errors on the build.
thanks
RobTried another option:
For parameter#3 I swapped the sproc for a query like:
select 'All' as YR, CNTRY, Location from myview with (nolock)
union
select YR, 'All' as CNTRY, Location from myview with (nolock)
union
select YR, CNTRY, Location from myview with (nolock)
ORDER BY Location
AND then applied filters for YR & CNTRY as the Parameters!?.value, but this
return nothing in Parameter#3 drop-down.
Rob
(done lots of drop-down direct dependencies, but never where a drop-down
depends on more than one parameter value... :(
"tutor" wrote:
> hi folks
> I have a Report Parameters issue:
> Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
> Parameter# 2 is a (organization) drop-down with 'All','US','Canada',etc.
> There is NO dependencies between #1 & #2 and both are SQL Queries
> Parameter# 3 is a (location) drop-down based on #1+#2:
> like 'All-All', 'All-US','2001-Canada',etc.
> I have a (functioning) stored proc. for parameter#3, but I'm having issues
> with constructing the value for the sproc call. It doesn't let me do things
> like:
> exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
> -or-
> exec sp_location @.YR,@.CNTRY
> also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
> '2000-US' should bring back all locations for drop-down#3, like
> (2000-US-)DETROIT, etc.)
> I keep getting forward dependency errors on the build.
> thanks
> Rob|||"=?Utf-8?B?dHV0b3I=?=" <tutor@.discussions.microsoft.com> wrote in
news:4FD87568-A2B2-4004-86AB-215C3B2DA05A@.microsoft.com:
For Parm #3, have you tried using code to build the query string instear
of a stored proc?
as in =code.builParm3 (patameters!yr.value,Parameters!cntry.value)
> hi folks
> I have a Report Parameters issue:
> Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
> Parameter# 2 is a (organization) drop-down with
> 'All','US','Canada',etc.
> There is NO dependencies between #1 & #2 and both are SQL Queries
> Parameter# 3 is a (location) drop-down based on #1+#2:
> like 'All-All', 'All-US','2001-Canada',etc.
> I have a (functioning) stored proc. for parameter#3, but I'm having
> issues with constructing the value for the sproc call. It doesn't let
> me do things like:
> exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
> -or-
> exec sp_location @.YR,@.CNTRY
> also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
> '2000-US' should bring back all locations for drop-down#3, like
> (2000-US-)DETROIT, etc.)
> I keep getting forward dependency errors on the build.
> thanks
> Rob|||Hi Asher
Just tried it and although it runs just fine from the Dataset, it has an
issue with converting the YR value of 'All' to an int...
This parameter stuff is 'sucking out my life force...' (what I think is a 5
minute job is getting close to 5 hours now...)
Let me trying...
Rob
"Asher_N" wrote:
> "=?Utf-8?B?dHV0b3I=?=" <tutor@.discussions.microsoft.com> wrote in
> news:4FD87568-A2B2-4004-86AB-215C3B2DA05A@.microsoft.com:
> For Parm #3, have you tried using code to build the query string instear
> of a stored proc?
> as in =code.builParm3 (patameters!yr.value,Parameters!cntry.value)
>
> > hi folks
> >
> > I have a Report Parameters issue:
> >
> > Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
> > Parameter# 2 is a (organization) drop-down with
> > 'All','US','Canada',etc.
> >
> > There is NO dependencies between #1 & #2 and both are SQL Queries
> >
> > Parameter# 3 is a (location) drop-down based on #1+#2:
> >
> > like 'All-All', 'All-US','2001-Canada',etc.
> >
> > I have a (functioning) stored proc. for parameter#3, but I'm having
> > issues with constructing the value for the sproc call. It doesn't let
> > me do things like:
> >
> > exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
> >
> > -or-
> >
> > exec sp_location @.YR,@.CNTRY
> >
> > also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
> > '2000-US' should bring back all locations for drop-down#3, like
> > (2000-US-)DETROIT, etc.)
> >
> > I keep getting forward dependency errors on the build.
> >
> > thanks
> > Rob
>|||got it!
adding an 'All' - 'All' portion to the query and putting it in a view was
the ticket (ie.
select TOP 100 'All' as YR, CNTRY, Location from myview with (nolock)
union
select TOP 100 YR, 'All' as CNTRY, Location from myview with (nolock)
union
select TOP 100 YR, CNTRY, Location from myview with (nolock)
union
select TOP 100 'All' as YR, 'All' as CNTRY, Location from myview with (nolock)
ORDER BY Location)
now I get drop-down values...
this is really 'hokey' and I wish somebody could do this with multiple sp
paramters.
rob
"Asher_N" wrote:
> "=?Utf-8?B?dHV0b3I=?=" <tutor@.discussions.microsoft.com> wrote in
> news:4FD87568-A2B2-4004-86AB-215C3B2DA05A@.microsoft.com:
> For Parm #3, have you tried using code to build the query string instear
> of a stored proc?
> as in =code.builParm3 (patameters!yr.value,Parameters!cntry.value)
>
> > hi folks
> >
> > I have a Report Parameters issue:
> >
> > Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
> > Parameter# 2 is a (organization) drop-down with
> > 'All','US','Canada',etc.
> >
> > There is NO dependencies between #1 & #2 and both are SQL Queries
> >
> > Parameter# 3 is a (location) drop-down based on #1+#2:
> >
> > like 'All-All', 'All-US','2001-Canada',etc.
> >
> > I have a (functioning) stored proc. for parameter#3, but I'm having
> > issues with constructing the value for the sproc call. It doesn't let
> > me do things like:
> >
> > exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
> >
> > -or-
> >
> > exec sp_location @.YR,@.CNTRY
> >
> > also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
> > '2000-US' should bring back all locations for drop-down#3, like
> > (2000-US-)DETROIT, etc.)
> >
> > I keep getting forward dependency errors on the build.
> >
> > thanks
> > Rob
>|||"=?Utf-8?B?dHV0b3I=?=" <tutor@.discussions.microsoft.com> wrote in
news:0EF21B79-A883-40ED-95DA-A3848E74AAB1@.microsoft.com:
> Hi Asher
> Just tried it and although it runs just fine from the Dataset, it has
> an issue with converting the YR value of 'All' to an int...
>
Pass it as a string to the code. The code is a VB function that would
build a SQL stmt with string concat, so it deals with strings better. You
could also tets for the values and isert the right stmt better.
> This parameter stuff is 'sucking out my life force...' (what I think
> is a 5 minute job is getting close to 5 hours now...)
> Let me trying...
> Rob
> "Asher_N" wrote:
>> "=?Utf-8?B?dHV0b3I=?=" <tutor@.discussions.microsoft.com> wrote in
>> news:4FD87568-A2B2-4004-86AB-215C3B2DA05A@.microsoft.com:
>> For Parm #3, have you tried using code to build the query string
>> instear of a stored proc?
>> as in =code.builParm3 (patameters!yr.value,Parameters!cntry.value)
>>
>> > hi folks
>> >
>> > I have a Report Parameters issue:
>> >
>> > Parameter# 1 is a (year) drop-down with 'All','2000','2001',etc.
>> > Parameter# 2 is a (organization) drop-down with
>> > 'All','US','Canada',etc.
>> >
>> > There is NO dependencies between #1 & #2 and both are SQL Queries
>> >
>> > Parameter# 3 is a (location) drop-down based on #1+#2:
>> >
>> > like 'All-All', 'All-US','2001-Canada',etc.
>> >
>> > I have a (functioning) stored proc. for parameter#3, but I'm having
>> > issues with constructing the value for the sproc call. It doesn't
>> > let me do things like:
>> >
>> > exec sp_location Parameters!YR.Value+'''-'''+Parameters!CNTRY.Value
>> >
>> > -or-
>> >
>> > exec sp_location @.YR,@.CNTRY
>> >
>> > also, Parameter# 3's drop-down values ARE dependent on #1 & #2 (ie.
>> > '2000-US' should bring back all locations for drop-down#3, like
>> > (2000-US-)DETROIT, etc.)
>> >
>> > I keep getting forward dependency errors on the build.
>> >
>> > thanks
>> > Rob
>>