Parameter Prompt List & Filter selection

I have created a parameter for a report that displays a drop-down list of
values to pick from. The parameter values are defined as being "loaded from
a query." The problen is that I need to have the option of blanks as a valid
value in this parameter so that the report can be generated for "all" values
too. Note: The query is the result of a dataset which executes an MDX
statement .
Part II of my problem is that I want to be able to filtermy results to say
either "part number = parameter value", or part number <> " ". I can't seem
to conditionally set the filter. Perhaps I am using the wrong syntax. Note:
I have tried placing the filter on both the dataset and the matrix object.
Any help would be greatly appreciated!
PBOn Mar 8, 12:37 pm, ppbedz <ppb...@.discussions.microsoft.com> wrote:
> I have created a parameter for a report that displays a drop-down list of
> values to pick from. The parameter values are defined as being "loaded from
> a query." The problen is that I need to have the option of blanks as a valid
> value in this parameter so that the report can be generated for "all" values
> too. Note: The query is the result of a dataset which executes an MDX
> statement .
> Part II of my problem is that I want to be able to filtermy results to say
> either "part number = parameter value", or part number <> " ". I can't seem
> to conditionally set the filter. Perhaps I am using the wrong syntax. Note:
> I have tried placing the filter on both the dataset and the matrix object.
> Any help would be greatly appreciated!
> PB
If you install Service Pack 2, you will have 'Select All' as an
available option for multi-select parameters. For the most part,
filters can only do and-ing, not or-ing. Hope this helps.
Regards,
Enrique Martinez
Sr. SQL Server Deveoper

Parameter drop-down

I've got two reports that are almost identical. They both allow clicking on
the column headers to sort the report so there is a parameter called SortBy
which appears as a drop-down list of fields that are available for sorting.
Both reports have the same default value defined for this parameter because
they both use the same datasource, however, one report shows this default
value in the drop-down list by default, the other report shows <Select a
Value> and the user must select a value before the report can be generated.
Any ideas? I've even compared the .rdl files and they appear identical with
regards to this parameter.
Help, I'm losing it over this one.
Thanks in advance.Hi Danno,
perhaps you need a second pair of eyes. I assume you have set up values as
Non-Queried and that the Value in the list that you want as the default is
exactly the same (case and all) as what you have set as your default value -
a space or a capital in the odd place may throw it off - I'm not sure how
exact this has to be.
A question of my own, if I may, how do you set up the report such that the
user can sort by a column by clicking on the column header?
thanks
Matt
"Danno" <Danno@.discussions.microsoft.com> wrote in message
news:CF5CB8D2-56A1-4C85-ADCF-AEBF6F53715B@.microsoft.com...
> I've got two reports that are almost identical. They both allow clicking
on
> the column headers to sort the report so there is a parameter called
SortBy
> which appears as a drop-down list of fields that are available for
sorting.
> Both reports have the same default value defined for this parameter
because
> they both use the same datasource, however, one report shows this default
> value in the drop-down list by default, the other report shows <Select a
> Value> and the user must select a value before the report can be
generated.
> Any ideas? I've even compared the .rdl files and they appear identical
with
> regards to this parameter.
> Help, I'm losing it over this one.
> Thanks in advance.|||Thanks Danno,
Bit disappointing that it requires two reports to achieve this.
regards
Matt
"Danno" <Danno@.discussions.microsoft.com> wrote in message
news:D320BE8F-0F79-4BBA-90E6-97174E704B09@.microsoft.com...
> Never mind, I fixed it. I just deleted the report in Report Manager and
> re-added it and now it works...go figure.
> To allow column-header clicking, check out this sample:
>
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=6a347b53-5594-40f9-861d-876beabeda16
> "Matt" wrote:
> > Hi Danno,
> >
> > perhaps you need a second pair of eyes. I assume you have set up values
as
> > Non-Queried and that the Value in the list that you want as the default
is
> > exactly the same (case and all) as what you have set as your default
value -
> > a space or a capital in the odd place may throw it off - I'm not sure
how
> > exact this has to be.
> >
> > A question of my own, if I may, how do you set up the report such that
the
> > user can sort by a column by clicking on the column header?
> >
> > thanks
> >
> > Matt
> >
> >
> > "Danno" <Danno@.discussions.microsoft.com> wrote in message
> > news:CF5CB8D2-56A1-4C85-ADCF-AEBF6F53715B@.microsoft.com...
> > > I've got two reports that are almost identical. They both allow
clicking
> > on
> > > the column headers to sort the report so there is a parameter called
> > SortBy
> > > which appears as a drop-down list of fields that are available for
> > sorting.
> > > Both reports have the same default value defined for this parameter
> > because
> > > they both use the same datasource, however, one report shows this
default
> > > value in the drop-down list by default, the other report shows <Select
a
> > > Value> and the user must select a value before the report can be
> > generated.
> > > Any ideas? I've even compared the .rdl files and they appear
identical
> > with
> > > regards to this parameter.
> > >
> > > Help, I'm losing it over this one.
> > >
> > > Thanks in advance.
> >
> >
> >|||Never mind, I fixed it. I just deleted the report in Report Manager and
re-added it and now it works...go figure.
To allow column-header clicking, check out this sample:
http://www.gotdotnet.com/Community/UserSamples/Details.aspx?SampleGuid=6a347b53-5594-40f9-861d-876beabeda16
"Matt" wrote:
> Hi Danno,
> perhaps you need a second pair of eyes. I assume you have set up values as
> Non-Queried and that the Value in the list that you want as the default is
> exactly the same (case and all) as what you have set as your default value -
> a space or a capital in the odd place may throw it off - I'm not sure how
> exact this has to be.
> A question of my own, if I may, how do you set up the report such that the
> user can sort by a column by clicking on the column header?
> thanks
> Matt
>
> "Danno" <Danno@.discussions.microsoft.com> wrote in message
> news:CF5CB8D2-56A1-4C85-ADCF-AEBF6F53715B@.microsoft.com...
> > I've got two reports that are almost identical. They both allow clicking
> on
> > the column headers to sort the report so there is a parameter called
> SortBy
> > which appears as a drop-down list of fields that are available for
> sorting.
> > Both reports have the same default value defined for this parameter
> because
> > they both use the same datasource, however, one report shows this default
> > value in the drop-down list by default, the other report shows <Select a
> > Value> and the user must select a value before the report can be
> generated.
> > Any ideas? I've even compared the .rdl files and they appear identical
> with
> > regards to this parameter.
> >
> > Help, I'm losing it over this one.
> >
> > Thanks in advance.
>
>

Parameter Does Not Work When Deployed

This is my first attempt at using a second dataset to fill a drop-down
parameter. This works fine inside of the designer but when I deploy to the
report server, there are no values in the drop down.
I thought perhaps it was because the data source name on the server is
different. In the past I would just update it on the server after I deploy
and it worked fine. After the deploy and after chaning the DS name, I edited
the server RDL and found that not all of the DS names had been updated. I
then edited the names in the RDL and uploaded manually thinking this was the
problem but the DD list still does not work.
Thanks for any help!Are you using a shared data source? This could be a credentials problem. I
always use a shared datasource. Personally, I don't see much of a reason to
ever not use a shared data source.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:OJhRIuNjFHA.3256@.TK2MSFTNGP12.phx.gbl...
> This is my first attempt at using a second dataset to fill a drop-down
> parameter. This works fine inside of the designer but when I deploy to the
> report server, there are no values in the drop down.
> I thought perhaps it was because the data source name on the server is
> different. In the past I would just update it on the server after I deploy
> and it worked fine. After the deploy and after chaning the DS name, I
> edited the server RDL and found that not all of the DS names had been
> updated. I then edited the names in the RDL and uploaded manually thinking
> this was the problem but the DD list still does not work.
> Thanks for any help!
>|||Bruce
Thanks for your reply
Yes, I have deployed other reports to the same folder, reset them to the
same data source and it always has worked fine. What is different is this is
the first time I used a second dataset in a report (to drive the lookup drop
down). It may not be the datasource, that was just a guess on my part. Are
there othe issues that could affect a query driven lookup such that it works
in the designer but not when deployed?
Thanks
Mike
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23oQhumSjFHA.576@.TK2MSFTNGP15.phx.gbl...
> Are you using a shared data source? This could be a credentials problem. I
> always use a shared datasource. Personally, I don't see much of a reason
> to ever not use a shared data source.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:OJhRIuNjFHA.3256@.TK2MSFTNGP12.phx.gbl...
>> This is my first attempt at using a second dataset to fill a drop-down
>> parameter. This works fine inside of the designer but when I deploy to
>> the report server, there are no values in the drop down.
>> I thought perhaps it was because the data source name on the server is
>> different. In the past I would just update it on the server after I
>> deploy and it worked fine. After the deploy and after chaning the DS
>> name, I edited the server RDL and found that not all of the DS names had
>> been updated. I then edited the names in the RDL and uploaded manually
>> thinking this was the problem but the DD list still does not work.
>> Thanks for any help!
>|||It could be a security issue on the database side of things. I've been
burned by that. I use one ID in development and then production is using
another one and the table/store procedure does not have the correct rights
for the production credentials. Take a look on the database at the table the
query is going against and see what the security access rights are set to.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:ulgIyGijFHA.3012@.TK2MSFTNGP12.phx.gbl...
> Bruce
> Thanks for your reply
> Yes, I have deployed other reports to the same folder, reset them to the
> same data source and it always has worked fine. What is different is this
> is the first time I used a second dataset in a report (to drive the lookup
> drop down). It may not be the datasource, that was just a guess on my
> part. Are there othe issues that could affect a query driven lookup such
> that it works in the designer but not when deployed?
> Thanks
> Mike
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:%23oQhumSjFHA.576@.TK2MSFTNGP15.phx.gbl...
>> Are you using a shared data source? This could be a credentials problem.
>> I always use a shared datasource. Personally, I don't see much of a
>> reason to ever not use a shared data source.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
>> news:OJhRIuNjFHA.3256@.TK2MSFTNGP12.phx.gbl...
>> This is my first attempt at using a second dataset to fill a drop-down
>> parameter. This works fine inside of the designer but when I deploy to
>> the report server, there are no values in the drop down.
>> I thought perhaps it was because the data source name on the server is
>> different. In the past I would just update it on the server after I
>> deploy and it worked fine. After the deploy and after chaning the DS
>> name, I edited the server RDL and found that not all of the DS names had
>> been updated. I then edited the names in the RDL and uploaded manually
>> thinking this was the problem but the DD list still does not work.
>> Thanks for any help!
>>
>|||Bruce
The security seems ok and the main query for dataset-1 works fine. The same
data source is used for dataset-1 and dataset-2 which drives the param
drop-down list. Maybe it is not the data source since I am not getting any
erorrs. This is what happens: the the drop down list is horizontally
truncated and there are no values in displayed when you select it. It just
displays 'Select Value'.
Thanks
Mike
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:%23fTmvMijFHA.3608@.TK2MSFTNGP12.phx.gbl...
> It could be a security issue on the database side of things. I've been
> burned by that. I use one ID in development and then production is using
> another one and the table/store procedure does not have the correct rights
> for the production credentials. Take a look on the database at the table
> the query is going against and see what the security access rights are set
> to.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:ulgIyGijFHA.3012@.TK2MSFTNGP12.phx.gbl...
>> Bruce
>> Thanks for your reply
>> Yes, I have deployed other reports to the same folder, reset them to the
>> same data source and it always has worked fine. What is different is this
>> is the first time I used a second dataset in a report (to drive the
>> lookup drop down). It may not be the datasource, that was just a guess on
>> my part. Are there othe issues that could affect a query driven lookup
>> such that it works in the designer but not when deployed?
>> Thanks
>> Mike
>>
>> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
>> news:%23oQhumSjFHA.576@.TK2MSFTNGP15.phx.gbl...
>> Are you using a shared data source? This could be a credentials problem.
>> I always use a shared datasource. Personally, I don't see much of a
>> reason to ever not use a shared data source.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
>> news:OJhRIuNjFHA.3256@.TK2MSFTNGP12.phx.gbl...
>> This is my first attempt at using a second dataset to fill a drop-down
>> parameter. This works fine inside of the designer but when I deploy to
>> the report server, there are no values in the drop down.
>> I thought perhaps it was because the data source name on the server is
>> different. In the past I would just update it on the server after I
>> deploy and it worked fine. After the deploy and after chaning the DS
>> name, I edited the server RDL and found that not all of the DS names
>> had been updated. I then edited the names in the RDL and uploaded
>> manually thinking this was the problem but the DD list still does not
>> work.
>> Thanks for any help!
>>
>>
>

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
>>