I have a report parameter: Customers
That parameter is used in main data set to pull data for the specified customer.
In the report header Iâ'd like to show that customerâ's name.
But because parameter has a customer code and not a name I dont know
the name of the customer that a user selects.
What is a solution here?Use the cutsomer code to query the customers name from the database, and
display that name in the header.
--
Brian Grant
Senior Programmer
SI International
www.si-intl.com
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:A4AFE9B4-8598-42C0-B60B-873A37904B5F@.microsoft.com...
> I have a report parameter: Customers
> That parameter is used in main data set to pull data for the specified
customer.
> In the report header I'd like to show that customer's name.
> But because parameter has a customer code and not a name I dont know
> the name of the customer that a user selects.
> What is a solution here?
>
Showing posts with label specified. Show all posts
Showing posts with label specified. Show all posts
Tuesday, March 20, 2012
Monday, March 12, 2012
parameters really only a filter?
Am I missing a trick here? When i set up my parameter in reporting
services and hit preview, the prompt apears and asks for the specified
column - however the sql generated is not refined any. it brings back
all the data and then filters on the selected column in the report.
By contrast in business objects if i used the @.prompt function the sql
generated would be refined with the input of the end user ...eg
cost_centre_code = 1234 as opposed all cost centres. What reporting
services seems to be doing is bringing back all cost centres then
filtering on 1234.
If im right it has some pretty serious implications in terms of speed
of reporting -as my query is returning all data values rather than
one...
any ideas? any input? Isnt a parameter really just a filter rather
than a condition?
thanks for any ideas
gregIt depends on what you are doing. If you use the parameter in the WHERE
clause of your SQL then you would indeed be doing the 'filtering' on the
Database Server. If you are putting your parameter in a filter expression -
then yes this means that the filtering is done in the Report Server, and all
the data is requested from the Database Server.
You are right it is better to optimise queries to have the data pre-filtered
on the Database Server where advantage can be taken of Indexes.
We cover these issues in depth in Chapter 6 of our book - "Hitchhiker's
Guide to SQL Server 2000 Reporting Services"
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> Am I missing a trick here? When i set up my parameter in reporting
> services and hit preview, the prompt apears and asks for the specified
> column - however the sql generated is not refined any. it brings back
> all the data and then filters on the selected column in the report.
> By contrast in business objects if i used the @.prompt function the sql
> generated would be refined with the input of the end user ...eg
> cost_centre_code = 1234 as opposed all cost centres. What reporting
> services seems to be doing is bringing back all cost centres then
> filtering on 1234.
> If im right it has some pretty serious implications in terms of speed
> of reporting -as my query is returning all data values rather than
> one...
> any ideas? any input? Isnt a parameter really just a filter rather
> than a condition?
>
> thanks for any ideas
> greg|||Greg, I answered this very completely at about 1:30 in your other posting.
Just to reiterate. Filters and query parameters are two different things and
both can use report parameters. You are using filters (which retrieve and
then filter) versus query parameters that are part of the where clause.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> Am I missing a trick here? When i set up my parameter in reporting
> services and hit preview, the prompt apears and asks for the specified
> column - however the sql generated is not refined any. it brings back
> all the data and then filters on the selected column in the report.
> By contrast in business objects if i used the @.prompt function the sql
> generated would be refined with the input of the end user ...eg
> cost_centre_code = 1234 as opposed all cost centres. What reporting
> services seems to be doing is bringing back all cost centres then
> filtering on 1234.
> If im right it has some pretty serious implications in terms of speed
> of reporting -as my query is returning all data values rather than
> one...
> any ideas? any input? Isnt a parameter really just a filter rather
> than a condition?
>
> thanks for any ideas
> greg|||Hi all -
Thanks for the info. I have now a parameter working on the database
server - specified in the where clause as you say - thats great.
However What I want is a prompt for the user - to enter in an
assignment id. When I pass the parameter to the where clause and hit
run in the Data tab I am asked for the Parameter value (on the Data
tab ) here I want a drop down list of values - which I would like
replicated when in the preview screen - so that the user can enter a
value from the drop down list of values box. At the moment I can only
enter in the preview panel what i have specified in the Data tab and
bring back the correct result. How can I get a drop down Lovs
todisplay when the parameter is in the where clause. I tried to point
both label and value to the specific data set but it would not allow -
saying:
"A label expression used for the report parameter ?ApplicationID'
refers to a field. Fields cannot be used in report parameter
expressions."
Any ideas
help is very much appreciated
Greg
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> Greg, I answered this very completely at about 1:30 in your other posting.
> Just to reiterate. Filters and query parameters are two different things and
> both can use report parameters. You are using filters (which retrieve and
> then filter) versus query parameters that are part of the where clause.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Greg" <greg_cochrane@.hotmail.com> wrote in message
> news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > Am I missing a trick here? When i set up my parameter in reporting
> > services and hit preview, the prompt apears and asks for the specified
> > column - however the sql generated is not refined any. it brings back
> > all the data and then filters on the selected column in the report.
> >
> > By contrast in business objects if i used the @.prompt function the sql
> > generated would be refined with the input of the end user ...eg
> > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > services seems to be doing is bringing back all cost centres then
> > filtering on 1234.
> >
> > If im right it has some pretty serious implications in terms of speed
> > of reporting -as my query is returning all data values rather than
> > one...
> >
> > any ideas? any input? Isnt a parameter really just a filter rather
> > than a condition?
> >
> >
> > thanks for any ideas
> >
> > greg|||You are missing a concept here. You only use the data tab to test out your
query. To test your report you use the preview tab. That is where you have
control over the parameters. The parameters can be free form, based on a
list or based on a dataset. In the layout tab go to Report menu, report
parameters and you'll see where you do this. The report parameters have to
be mapped to the query parameters as well (they might be already). You can
check this by clicking on the ... in the data tab and going to the
parameters tab.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411100801.51f5bf73@.posting.google.com...
> Hi all -
> Thanks for the info. I have now a parameter working on the database
> server - specified in the where clause as you say - thats great.
> However What I want is a prompt for the user - to enter in an
> assignment id. When I pass the parameter to the where clause and hit
> run in the Data tab I am asked for the Parameter value (on the Data
> tab ) here I want a drop down list of values - which I would like
> replicated when in the preview screen - so that the user can enter a
> value from the drop down list of values box. At the moment I can only
> enter in the preview panel what i have specified in the Data tab and
> bring back the correct result. How can I get a drop down Lovs
> todisplay when the parameter is in the where clause. I tried to point
> both label and value to the specific data set but it would not allow -
> saying:
> "A label expression used for the report parameter 'ApplicationID'
> refers to a field. Fields cannot be used in report parameter
> expressions."
> Any ideas
> help is very much appreciated
> Greg
>
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > Greg, I answered this very completely at about 1:30 in your other
posting.
> > Just to reiterate. Filters and query parameters are two different things
and
> > both can use report parameters. You are using filters (which retrieve
and
> > then filter) versus query parameters that are part of the where clause.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > Am I missing a trick here? When i set up my parameter in reporting
> > > services and hit preview, the prompt apears and asks for the specified
> > > column - however the sql generated is not refined any. it brings back
> > > all the data and then filters on the selected column in the report.
> > >
> > > By contrast in business objects if i used the @.prompt function the sql
> > > generated would be refined with the input of the end user ...eg
> > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > services seems to be doing is bringing back all cost centres then
> > > filtering on 1234.
> > >
> > > If im right it has some pretty serious implications in terms of speed
> > > of reporting -as my query is returning all data values rather than
> > > one...
> > >
> > > any ideas? any input? Isnt a parameter really just a filter rather
> > > than a condition?
> > >
> > >
> > > thanks for any ideas
> > >
> > > greg|||If you want the drop-down you need to do the following:
1) Create a second Dataset that returns the list of Valid Assignment ID's
and a second column for the labels you want in the drop-down
2) On the main menu, click on Report -> Report Parameters
3) Click on the Assignment ID parameter
4) In the Available Values area, click on From Query
5) Choose the new Assignment ID DataSet, Value Field, and Label Field
Now try the Preview tab.
Hope this helps
"Greg" wrote:
> Hi all -
> Thanks for the info. I have now a parameter working on the database
> server - specified in the where clause as you say - thats great.
> However What I want is a prompt for the user - to enter in an
> assignment id. When I pass the parameter to the where clause and hit
> run in the Data tab I am asked for the Parameter value (on the Data
> tab ) here I want a drop down list of values - which I would like
> replicated when in the preview screen - so that the user can enter a
> value from the drop down list of values box. At the moment I can only
> enter in the preview panel what i have specified in the Data tab and
> bring back the correct result. How can I get a drop down Lovs
> todisplay when the parameter is in the where clause. I tried to point
> both label and value to the specific data set but it would not allow -
> saying:
> "A label expression used for the report parameter â'ApplicationID'
> refers to a field. Fields cannot be used in report parameter
> expressions."
> Any ideas
> help is very much appreciated
> Greg
>
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > Greg, I answered this very completely at about 1:30 in your other posting.
> > Just to reiterate. Filters and query parameters are two different things and
> > both can use report parameters. You are using filters (which retrieve and
> > then filter) versus query parameters that are part of the where clause.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > Am I missing a trick here? When i set up my parameter in reporting
> > > services and hit preview, the prompt apears and asks for the specified
> > > column - however the sql generated is not refined any. it brings back
> > > all the data and then filters on the selected column in the report.
> > >
> > > By contrast in business objects if i used the @.prompt function the sql
> > > generated would be refined with the input of the end user ...eg
> > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > services seems to be doing is bringing back all cost centres then
> > > filtering on 1234.
> > >
> > > If im right it has some pretty serious implications in terms of speed
> > > of reporting -as my query is returning all data values rather than
> > > one...
> > >
> > > any ideas? any input? Isnt a parameter really just a filter rather
> > > than a condition?
> > >
> > >
> > > thanks for any ideas
> > >
> > > greg
>|||Hi there - sorry Im really new to reporting services and this is going
to sound like a simple question! but how do I create a new data set
within a current report' I am trying to build a list of values for a
column in my first report - see below.
Many thanks - help is appreciated.
Greg
"mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> If you want the drop-down you need to do the following:
> 1) Create a second Dataset that returns the list of Valid Assignment ID's
> and a second column for the labels you want in the drop-down
> 2) On the main menu, click on Report -> Report Parameters
> 3) Click on the Assignment ID parameter
> 4) In the Available Values area, click on From Query
> 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> Now try the Preview tab.
> Hope this helps
> "Greg" wrote:
> > Hi all -
> >
> > Thanks for the info. I have now a parameter working on the database
> > server - specified in the where clause as you say - thats great.
> > However What I want is a prompt for the user - to enter in an
> > assignment id. When I pass the parameter to the where clause and hit
> > run in the Data tab I am asked for the Parameter value (on the Data
> > tab ) here I want a drop down list of values - which I would like
> > replicated when in the preview screen - so that the user can enter a
> > value from the drop down list of values box. At the moment I can only
> > enter in the preview panel what i have specified in the Data tab and
> > bring back the correct result. How can I get a drop down Lovs
> > todisplay when the parameter is in the where clause. I tried to point
> > both label and value to the specific data set but it would not allow -
> > saying:
> > "A label expression used for the report parameter â'ApplicationID'
> > refers to a field. Fields cannot be used in report parameter
> > expressions."
> >
> > Any ideas
> >
> > help is very much appreciated
> >
> > Greg
> >
> >
> >
> >
> >
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > Just to reiterate. Filters and query parameters are two different things and
> > > both can use report parameters. You are using filters (which retrieve and
> > > then filter) versus query parameters that are part of the where clause.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > services and hit preview, the prompt apears and asks for the specified
> > > > column - however the sql generated is not refined any. it brings back
> > > > all the data and then filters on the selected column in the report.
> > > >
> > > > By contrast in business objects if i used the @.prompt function the sql
> > > > generated would be refined with the input of the end user ...eg
> > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > services seems to be doing is bringing back all cost centres then
> > > > filtering on 1234.
> > > >
> > > > If im right it has some pretty serious implications in terms of speed
> > > > of reporting -as my query is returning all data values rather than
> > > > one...
> > > >
> > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > than a condition?
> > > >
> > > >
> > > > thanks for any ideas
> > > >
> > > > greg
> >|||Q Do i "add a new item" and write the sql free hand in there? or do I
create a new report? if so how do I link the result set achieved
through to the parameter on my first report?
Greg
greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> Hi there - sorry Im really new to reporting services and this is going
> to sound like a simple question! but how do I create a new data set
> within a current report' I am trying to build a list of values for a
> column in my first report - see below.
> Many thanks - help is appreciated.
> Greg
>
>
> "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > If you want the drop-down you need to do the following:
> >
> > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > and a second column for the labels you want in the drop-down
> > 2) On the main menu, click on Report -> Report Parameters
> > 3) Click on the Assignment ID parameter
> > 4) In the Available Values area, click on From Query
> > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> >
> > Now try the Preview tab.
> >
> > Hope this helps
> >
> > "Greg" wrote:
> >
> > > Hi all -
> > >
> > > Thanks for the info. I have now a parameter working on the database
> > > server - specified in the where clause as you say - thats great.
> > > However What I want is a prompt for the user - to enter in an
> > > assignment id. When I pass the parameter to the where clause and hit
> > > run in the Data tab I am asked for the Parameter value (on the Data
> > > tab ) here I want a drop down list of values - which I would like
> > > replicated when in the preview screen - so that the user can enter a
> > > value from the drop down list of values box. At the moment I can only
> > > enter in the preview panel what i have specified in the Data tab and
> > > bring back the correct result. How can I get a drop down Lovs
> > > todisplay when the parameter is in the where clause. I tried to point
> > > both label and value to the specific data set but it would not allow -
> > > saying:
> > > "A label expression used for the report parameter â'ApplicationID'
> > > refers to a field. Fields cannot be used in report parameter
> > > expressions."
> > >
> > > Any ideas
> > >
> > > help is very much appreciated
> > >
> > > Greg
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > Just to reiterate. Filters and query parameters are two different things and
> > > > both can use report parameters. You are using filters (which retrieve and
> > > > then filter) versus query parameters that are part of the where clause.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > column - however the sql generated is not refined any. it brings back
> > > > > all the data and then filters on the selected column in the report.
> > > > >
> > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > generated would be refined with the input of the end user ...eg
> > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > services seems to be doing is bringing back all cost centres then
> > > > > filtering on 1234.
> > > > >
> > > > > If im right it has some pretty serious implications in terms of speed
> > > > > of reporting -as my query is returning all data values rather than
> > > > > one...
> > > > >
> > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > than a condition?
> > > > >
> > > > >
> > > > > thanks for any ideas
> > > > >
> > > > > greg
> > >|||go to the data tab and click on the drop down nesxt to your dataset name.
one of the options will be <new dataset> you will need to code the SQL but
for a parameter list it should be straightforward.
"Greg" wrote:
> Q Do i "add a new item" and write the sql free hand in there? or do I
> create a new report? if so how do I link the result set achieved
> through to the parameter on my first report?
>
> Greg
>
> greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> > Hi there - sorry Im really new to reporting services and this is going
> > to sound like a simple question! but how do I create a new data set
> > within a current report' I am trying to build a list of values for a
> > column in my first report - see below.
> >
> > Many thanks - help is appreciated.
> >
> > Greg
> >
> >
> >
> >
> > "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > > If you want the drop-down you need to do the following:
> > >
> > > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > > and a second column for the labels you want in the drop-down
> > > 2) On the main menu, click on Report -> Report Parameters
> > > 3) Click on the Assignment ID parameter
> > > 4) In the Available Values area, click on From Query
> > > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> > >
> > > Now try the Preview tab.
> > >
> > > Hope this helps
> > >
> > > "Greg" wrote:
> > >
> > > > Hi all -
> > > >
> > > > Thanks for the info. I have now a parameter working on the database
> > > > server - specified in the where clause as you say - thats great.
> > > > However What I want is a prompt for the user - to enter in an
> > > > assignment id. When I pass the parameter to the where clause and hit
> > > > run in the Data tab I am asked for the Parameter value (on the Data
> > > > tab ) here I want a drop down list of values - which I would like
> > > > replicated when in the preview screen - so that the user can enter a
> > > > value from the drop down list of values box. At the moment I can only
> > > > enter in the preview panel what i have specified in the Data tab and
> > > > bring back the correct result. How can I get a drop down Lovs
> > > > todisplay when the parameter is in the where clause. I tried to point
> > > > both label and value to the specific data set but it would not allow -
> > > > saying:
> > > > "A label expression used for the report parameter â'ApplicationID'
> > > > refers to a field. Fields cannot be used in report parameter
> > > > expressions."
> > > >
> > > > Any ideas
> > > >
> > > > help is very much appreciated
> > > >
> > > > Greg
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > > Just to reiterate. Filters and query parameters are two different things and
> > > > > both can use report parameters. You are using filters (which retrieve and
> > > > > then filter) versus query parameters that are part of the where clause.
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > > column - however the sql generated is not refined any. it brings back
> > > > > > all the data and then filters on the selected column in the report.
> > > > > >
> > > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > > generated would be refined with the input of the end user ...eg
> > > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > > services seems to be doing is bringing back all cost centres then
> > > > > > filtering on 1234.
> > > > > >
> > > > > > If im right it has some pretty serious implications in terms of speed
> > > > > > of reporting -as my query is returning all data values rather than
> > > > > > one...
> > > > > >
> > > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > > than a condition?
> > > > > >
> > > > > >
> > > > > > thanks for any ideas
> > > > > >
> > > > > > greg
> > > >
>|||Hi John - thanks very much!! got it working now!
Greg
"johnE" <johnE@.discussions.microsoft.com> wrote in message news:<70BEB3D8-6A8D-4609-A7D5-D387A4CFE865@.microsoft.com>...
> go to the data tab and click on the drop down nesxt to your dataset name.
> one of the options will be <new dataset> you will need to code the SQL but
> for a parameter list it should be straightforward.
> "Greg" wrote:
> > Q Do i "add a new item" and write the sql free hand in there? or do I
> > create a new report? if so how do I link the result set achieved
> > through to the parameter on my first report?
> >
> >
> > Greg
> >
> >
> > greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> > > Hi there - sorry Im really new to reporting services and this is going
> > > to sound like a simple question! but how do I create a new data set
> > > within a current report' I am trying to build a list of values for a
> > > column in my first report - see below.
> > >
> > > Many thanks - help is appreciated.
> > >
> > > Greg
> > >
> > >
> > >
> > >
> > > "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > > > If you want the drop-down you need to do the following:
> > > >
> > > > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > > > and a second column for the labels you want in the drop-down
> > > > 2) On the main menu, click on Report -> Report Parameters
> > > > 3) Click on the Assignment ID parameter
> > > > 4) In the Available Values area, click on From Query
> > > > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> > > >
> > > > Now try the Preview tab.
> > > >
> > > > Hope this helps
> > > >
> > > > "Greg" wrote:
> > > >
> > > > > Hi all -
> > > > >
> > > > > Thanks for the info. I have now a parameter working on the database
> > > > > server - specified in the where clause as you say - thats great.
> > > > > However What I want is a prompt for the user - to enter in an
> > > > > assignment id. When I pass the parameter to the where clause and hit
> > > > > run in the Data tab I am asked for the Parameter value (on the Data
> > > > > tab ) here I want a drop down list of values - which I would like
> > > > > replicated when in the preview screen - so that the user can enter a
> > > > > value from the drop down list of values box. At the moment I can only
> > > > > enter in the preview panel what i have specified in the Data tab and
> > > > > bring back the correct result. How can I get a drop down Lovs
> > > > > todisplay when the parameter is in the where clause. I tried to point
> > > > > both label and value to the specific data set but it would not allow -
> > > > > saying:
> > > > > "A label expression used for the report parameter â'ApplicationID'
> > > > > refers to a field. Fields cannot be used in report parameter
> > > > > expressions."
> > > > >
> > > > > Any ideas
> > > > >
> > > > > help is very much appreciated
> > > > >
> > > > > Greg
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > > > Just to reiterate. Filters and query parameters are two different things and
> > > > > > both can use report parameters. You are using filters (which retrieve and
> > > > > > then filter) versus query parameters that are part of the where clause.
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > > > column - however the sql generated is not refined any. it brings back
> > > > > > > all the data and then filters on the selected column in the report.
> > > > > > >
> > > > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > > > generated would be refined with the input of the end user ...eg
> > > > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > > > services seems to be doing is bringing back all cost centres then
> > > > > > > filtering on 1234.
> > > > > > >
> > > > > > > If im right it has some pretty serious implications in terms of speed
> > > > > > > of reporting -as my query is returning all data values rather than
> > > > > > > one...
> > > > > > >
> > > > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > > > than a condition?
> > > > > > >
> > > > > > >
> > > > > > > thanks for any ideas
> > > > > > >
> > > > > > > greg
> > > > >
> >
services and hit preview, the prompt apears and asks for the specified
column - however the sql generated is not refined any. it brings back
all the data and then filters on the selected column in the report.
By contrast in business objects if i used the @.prompt function the sql
generated would be refined with the input of the end user ...eg
cost_centre_code = 1234 as opposed all cost centres. What reporting
services seems to be doing is bringing back all cost centres then
filtering on 1234.
If im right it has some pretty serious implications in terms of speed
of reporting -as my query is returning all data values rather than
one...
any ideas? any input? Isnt a parameter really just a filter rather
than a condition?
thanks for any ideas
gregIt depends on what you are doing. If you use the parameter in the WHERE
clause of your SQL then you would indeed be doing the 'filtering' on the
Database Server. If you are putting your parameter in a filter expression -
then yes this means that the filtering is done in the Report Server, and all
the data is requested from the Database Server.
You are right it is better to optimise queries to have the data pre-filtered
on the Database Server where advantage can be taken of Indexes.
We cover these issues in depth in Chapter 6 of our book - "Hitchhiker's
Guide to SQL Server 2000 Reporting Services"
Peter Blackburn
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> Am I missing a trick here? When i set up my parameter in reporting
> services and hit preview, the prompt apears and asks for the specified
> column - however the sql generated is not refined any. it brings back
> all the data and then filters on the selected column in the report.
> By contrast in business objects if i used the @.prompt function the sql
> generated would be refined with the input of the end user ...eg
> cost_centre_code = 1234 as opposed all cost centres. What reporting
> services seems to be doing is bringing back all cost centres then
> filtering on 1234.
> If im right it has some pretty serious implications in terms of speed
> of reporting -as my query is returning all data values rather than
> one...
> any ideas? any input? Isnt a parameter really just a filter rather
> than a condition?
>
> thanks for any ideas
> greg|||Greg, I answered this very completely at about 1:30 in your other posting.
Just to reiterate. Filters and query parameters are two different things and
both can use report parameters. You are using filters (which retrieve and
then filter) versus query parameters that are part of the where clause.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> Am I missing a trick here? When i set up my parameter in reporting
> services and hit preview, the prompt apears and asks for the specified
> column - however the sql generated is not refined any. it brings back
> all the data and then filters on the selected column in the report.
> By contrast in business objects if i used the @.prompt function the sql
> generated would be refined with the input of the end user ...eg
> cost_centre_code = 1234 as opposed all cost centres. What reporting
> services seems to be doing is bringing back all cost centres then
> filtering on 1234.
> If im right it has some pretty serious implications in terms of speed
> of reporting -as my query is returning all data values rather than
> one...
> any ideas? any input? Isnt a parameter really just a filter rather
> than a condition?
>
> thanks for any ideas
> greg|||Hi all -
Thanks for the info. I have now a parameter working on the database
server - specified in the where clause as you say - thats great.
However What I want is a prompt for the user - to enter in an
assignment id. When I pass the parameter to the where clause and hit
run in the Data tab I am asked for the Parameter value (on the Data
tab ) here I want a drop down list of values - which I would like
replicated when in the preview screen - so that the user can enter a
value from the drop down list of values box. At the moment I can only
enter in the preview panel what i have specified in the Data tab and
bring back the correct result. How can I get a drop down Lovs
todisplay when the parameter is in the where clause. I tried to point
both label and value to the specific data set but it would not allow -
saying:
"A label expression used for the report parameter ?ApplicationID'
refers to a field. Fields cannot be used in report parameter
expressions."
Any ideas
help is very much appreciated
Greg
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> Greg, I answered this very completely at about 1:30 in your other posting.
> Just to reiterate. Filters and query parameters are two different things and
> both can use report parameters. You are using filters (which retrieve and
> then filter) versus query parameters that are part of the where clause.
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Greg" <greg_cochrane@.hotmail.com> wrote in message
> news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > Am I missing a trick here? When i set up my parameter in reporting
> > services and hit preview, the prompt apears and asks for the specified
> > column - however the sql generated is not refined any. it brings back
> > all the data and then filters on the selected column in the report.
> >
> > By contrast in business objects if i used the @.prompt function the sql
> > generated would be refined with the input of the end user ...eg
> > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > services seems to be doing is bringing back all cost centres then
> > filtering on 1234.
> >
> > If im right it has some pretty serious implications in terms of speed
> > of reporting -as my query is returning all data values rather than
> > one...
> >
> > any ideas? any input? Isnt a parameter really just a filter rather
> > than a condition?
> >
> >
> > thanks for any ideas
> >
> > greg|||You are missing a concept here. You only use the data tab to test out your
query. To test your report you use the preview tab. That is where you have
control over the parameters. The parameters can be free form, based on a
list or based on a dataset. In the layout tab go to Report menu, report
parameters and you'll see where you do this. The report parameters have to
be mapped to the query parameters as well (they might be already). You can
check this by clicking on the ... in the data tab and going to the
parameters tab.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Greg" <greg_cochrane@.hotmail.com> wrote in message
news:2dba7d00.0411100801.51f5bf73@.posting.google.com...
> Hi all -
> Thanks for the info. I have now a parameter working on the database
> server - specified in the where clause as you say - thats great.
> However What I want is a prompt for the user - to enter in an
> assignment id. When I pass the parameter to the where clause and hit
> run in the Data tab I am asked for the Parameter value (on the Data
> tab ) here I want a drop down list of values - which I would like
> replicated when in the preview screen - so that the user can enter a
> value from the drop down list of values box. At the moment I can only
> enter in the preview panel what i have specified in the Data tab and
> bring back the correct result. How can I get a drop down Lovs
> todisplay when the parameter is in the where clause. I tried to point
> both label and value to the specific data set but it would not allow -
> saying:
> "A label expression used for the report parameter 'ApplicationID'
> refers to a field. Fields cannot be used in report parameter
> expressions."
> Any ideas
> help is very much appreciated
> Greg
>
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > Greg, I answered this very completely at about 1:30 in your other
posting.
> > Just to reiterate. Filters and query parameters are two different things
and
> > both can use report parameters. You are using filters (which retrieve
and
> > then filter) versus query parameters that are part of the where clause.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > Am I missing a trick here? When i set up my parameter in reporting
> > > services and hit preview, the prompt apears and asks for the specified
> > > column - however the sql generated is not refined any. it brings back
> > > all the data and then filters on the selected column in the report.
> > >
> > > By contrast in business objects if i used the @.prompt function the sql
> > > generated would be refined with the input of the end user ...eg
> > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > services seems to be doing is bringing back all cost centres then
> > > filtering on 1234.
> > >
> > > If im right it has some pretty serious implications in terms of speed
> > > of reporting -as my query is returning all data values rather than
> > > one...
> > >
> > > any ideas? any input? Isnt a parameter really just a filter rather
> > > than a condition?
> > >
> > >
> > > thanks for any ideas
> > >
> > > greg|||If you want the drop-down you need to do the following:
1) Create a second Dataset that returns the list of Valid Assignment ID's
and a second column for the labels you want in the drop-down
2) On the main menu, click on Report -> Report Parameters
3) Click on the Assignment ID parameter
4) In the Available Values area, click on From Query
5) Choose the new Assignment ID DataSet, Value Field, and Label Field
Now try the Preview tab.
Hope this helps
"Greg" wrote:
> Hi all -
> Thanks for the info. I have now a parameter working on the database
> server - specified in the where clause as you say - thats great.
> However What I want is a prompt for the user - to enter in an
> assignment id. When I pass the parameter to the where clause and hit
> run in the Data tab I am asked for the Parameter value (on the Data
> tab ) here I want a drop down list of values - which I would like
> replicated when in the preview screen - so that the user can enter a
> value from the drop down list of values box. At the moment I can only
> enter in the preview panel what i have specified in the Data tab and
> bring back the correct result. How can I get a drop down Lovs
> todisplay when the parameter is in the where clause. I tried to point
> both label and value to the specific data set but it would not allow -
> saying:
> "A label expression used for the report parameter â'ApplicationID'
> refers to a field. Fields cannot be used in report parameter
> expressions."
> Any ideas
> help is very much appreciated
> Greg
>
>
>
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > Greg, I answered this very completely at about 1:30 in your other posting.
> > Just to reiterate. Filters and query parameters are two different things and
> > both can use report parameters. You are using filters (which retrieve and
> > then filter) versus query parameters that are part of the where clause.
> >
> > --
> > Bruce Loehle-Conger
> > MVP SQL Server Reporting Services
> >
> > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > Am I missing a trick here? When i set up my parameter in reporting
> > > services and hit preview, the prompt apears and asks for the specified
> > > column - however the sql generated is not refined any. it brings back
> > > all the data and then filters on the selected column in the report.
> > >
> > > By contrast in business objects if i used the @.prompt function the sql
> > > generated would be refined with the input of the end user ...eg
> > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > services seems to be doing is bringing back all cost centres then
> > > filtering on 1234.
> > >
> > > If im right it has some pretty serious implications in terms of speed
> > > of reporting -as my query is returning all data values rather than
> > > one...
> > >
> > > any ideas? any input? Isnt a parameter really just a filter rather
> > > than a condition?
> > >
> > >
> > > thanks for any ideas
> > >
> > > greg
>|||Hi there - sorry Im really new to reporting services and this is going
to sound like a simple question! but how do I create a new data set
within a current report' I am trying to build a list of values for a
column in my first report - see below.
Many thanks - help is appreciated.
Greg
"mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> If you want the drop-down you need to do the following:
> 1) Create a second Dataset that returns the list of Valid Assignment ID's
> and a second column for the labels you want in the drop-down
> 2) On the main menu, click on Report -> Report Parameters
> 3) Click on the Assignment ID parameter
> 4) In the Available Values area, click on From Query
> 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> Now try the Preview tab.
> Hope this helps
> "Greg" wrote:
> > Hi all -
> >
> > Thanks for the info. I have now a parameter working on the database
> > server - specified in the where clause as you say - thats great.
> > However What I want is a prompt for the user - to enter in an
> > assignment id. When I pass the parameter to the where clause and hit
> > run in the Data tab I am asked for the Parameter value (on the Data
> > tab ) here I want a drop down list of values - which I would like
> > replicated when in the preview screen - so that the user can enter a
> > value from the drop down list of values box. At the moment I can only
> > enter in the preview panel what i have specified in the Data tab and
> > bring back the correct result. How can I get a drop down Lovs
> > todisplay when the parameter is in the where clause. I tried to point
> > both label and value to the specific data set but it would not allow -
> > saying:
> > "A label expression used for the report parameter â'ApplicationID'
> > refers to a field. Fields cannot be used in report parameter
> > expressions."
> >
> > Any ideas
> >
> > help is very much appreciated
> >
> > Greg
> >
> >
> >
> >
> >
> >
> > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > Just to reiterate. Filters and query parameters are two different things and
> > > both can use report parameters. You are using filters (which retrieve and
> > > then filter) versus query parameters that are part of the where clause.
> > >
> > > --
> > > Bruce Loehle-Conger
> > > MVP SQL Server Reporting Services
> > >
> > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > services and hit preview, the prompt apears and asks for the specified
> > > > column - however the sql generated is not refined any. it brings back
> > > > all the data and then filters on the selected column in the report.
> > > >
> > > > By contrast in business objects if i used the @.prompt function the sql
> > > > generated would be refined with the input of the end user ...eg
> > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > services seems to be doing is bringing back all cost centres then
> > > > filtering on 1234.
> > > >
> > > > If im right it has some pretty serious implications in terms of speed
> > > > of reporting -as my query is returning all data values rather than
> > > > one...
> > > >
> > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > than a condition?
> > > >
> > > >
> > > > thanks for any ideas
> > > >
> > > > greg
> >|||Q Do i "add a new item" and write the sql free hand in there? or do I
create a new report? if so how do I link the result set achieved
through to the parameter on my first report?
Greg
greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> Hi there - sorry Im really new to reporting services and this is going
> to sound like a simple question! but how do I create a new data set
> within a current report' I am trying to build a list of values for a
> column in my first report - see below.
> Many thanks - help is appreciated.
> Greg
>
>
> "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > If you want the drop-down you need to do the following:
> >
> > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > and a second column for the labels you want in the drop-down
> > 2) On the main menu, click on Report -> Report Parameters
> > 3) Click on the Assignment ID parameter
> > 4) In the Available Values area, click on From Query
> > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> >
> > Now try the Preview tab.
> >
> > Hope this helps
> >
> > "Greg" wrote:
> >
> > > Hi all -
> > >
> > > Thanks for the info. I have now a parameter working on the database
> > > server - specified in the where clause as you say - thats great.
> > > However What I want is a prompt for the user - to enter in an
> > > assignment id. When I pass the parameter to the where clause and hit
> > > run in the Data tab I am asked for the Parameter value (on the Data
> > > tab ) here I want a drop down list of values - which I would like
> > > replicated when in the preview screen - so that the user can enter a
> > > value from the drop down list of values box. At the moment I can only
> > > enter in the preview panel what i have specified in the Data tab and
> > > bring back the correct result. How can I get a drop down Lovs
> > > todisplay when the parameter is in the where clause. I tried to point
> > > both label and value to the specific data set but it would not allow -
> > > saying:
> > > "A label expression used for the report parameter â'ApplicationID'
> > > refers to a field. Fields cannot be used in report parameter
> > > expressions."
> > >
> > > Any ideas
> > >
> > > help is very much appreciated
> > >
> > > Greg
> > >
> > >
> > >
> > >
> > >
> > >
> > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > Just to reiterate. Filters and query parameters are two different things and
> > > > both can use report parameters. You are using filters (which retrieve and
> > > > then filter) versus query parameters that are part of the where clause.
> > > >
> > > > --
> > > > Bruce Loehle-Conger
> > > > MVP SQL Server Reporting Services
> > > >
> > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > column - however the sql generated is not refined any. it brings back
> > > > > all the data and then filters on the selected column in the report.
> > > > >
> > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > generated would be refined with the input of the end user ...eg
> > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > services seems to be doing is bringing back all cost centres then
> > > > > filtering on 1234.
> > > > >
> > > > > If im right it has some pretty serious implications in terms of speed
> > > > > of reporting -as my query is returning all data values rather than
> > > > > one...
> > > > >
> > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > than a condition?
> > > > >
> > > > >
> > > > > thanks for any ideas
> > > > >
> > > > > greg
> > >|||go to the data tab and click on the drop down nesxt to your dataset name.
one of the options will be <new dataset> you will need to code the SQL but
for a parameter list it should be straightforward.
"Greg" wrote:
> Q Do i "add a new item" and write the sql free hand in there? or do I
> create a new report? if so how do I link the result set achieved
> through to the parameter on my first report?
>
> Greg
>
> greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> > Hi there - sorry Im really new to reporting services and this is going
> > to sound like a simple question! but how do I create a new data set
> > within a current report' I am trying to build a list of values for a
> > column in my first report - see below.
> >
> > Many thanks - help is appreciated.
> >
> > Greg
> >
> >
> >
> >
> > "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > > If you want the drop-down you need to do the following:
> > >
> > > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > > and a second column for the labels you want in the drop-down
> > > 2) On the main menu, click on Report -> Report Parameters
> > > 3) Click on the Assignment ID parameter
> > > 4) In the Available Values area, click on From Query
> > > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> > >
> > > Now try the Preview tab.
> > >
> > > Hope this helps
> > >
> > > "Greg" wrote:
> > >
> > > > Hi all -
> > > >
> > > > Thanks for the info. I have now a parameter working on the database
> > > > server - specified in the where clause as you say - thats great.
> > > > However What I want is a prompt for the user - to enter in an
> > > > assignment id. When I pass the parameter to the where clause and hit
> > > > run in the Data tab I am asked for the Parameter value (on the Data
> > > > tab ) here I want a drop down list of values - which I would like
> > > > replicated when in the preview screen - so that the user can enter a
> > > > value from the drop down list of values box. At the moment I can only
> > > > enter in the preview panel what i have specified in the Data tab and
> > > > bring back the correct result. How can I get a drop down Lovs
> > > > todisplay when the parameter is in the where clause. I tried to point
> > > > both label and value to the specific data set but it would not allow -
> > > > saying:
> > > > "A label expression used for the report parameter â'ApplicationID'
> > > > refers to a field. Fields cannot be used in report parameter
> > > > expressions."
> > > >
> > > > Any ideas
> > > >
> > > > help is very much appreciated
> > > >
> > > > Greg
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > > Just to reiterate. Filters and query parameters are two different things and
> > > > > both can use report parameters. You are using filters (which retrieve and
> > > > > then filter) versus query parameters that are part of the where clause.
> > > > >
> > > > > --
> > > > > Bruce Loehle-Conger
> > > > > MVP SQL Server Reporting Services
> > > > >
> > > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > > column - however the sql generated is not refined any. it brings back
> > > > > > all the data and then filters on the selected column in the report.
> > > > > >
> > > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > > generated would be refined with the input of the end user ...eg
> > > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > > services seems to be doing is bringing back all cost centres then
> > > > > > filtering on 1234.
> > > > > >
> > > > > > If im right it has some pretty serious implications in terms of speed
> > > > > > of reporting -as my query is returning all data values rather than
> > > > > > one...
> > > > > >
> > > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > > than a condition?
> > > > > >
> > > > > >
> > > > > > thanks for any ideas
> > > > > >
> > > > > > greg
> > > >
>|||Hi John - thanks very much!! got it working now!
Greg
"johnE" <johnE@.discussions.microsoft.com> wrote in message news:<70BEB3D8-6A8D-4609-A7D5-D387A4CFE865@.microsoft.com>...
> go to the data tab and click on the drop down nesxt to your dataset name.
> one of the options will be <new dataset> you will need to code the SQL but
> for a parameter list it should be straightforward.
> "Greg" wrote:
> > Q Do i "add a new item" and write the sql free hand in there? or do I
> > create a new report? if so how do I link the result set achieved
> > through to the parameter on my first report?
> >
> >
> > Greg
> >
> >
> > greg_cochrane@.hotmail.com (Greg) wrote in message news:<2dba7d00.0411110257.40fdc0a@.posting.google.com>...
> > > Hi there - sorry Im really new to reporting services and this is going
> > > to sound like a simple question! but how do I create a new data set
> > > within a current report' I am trying to build a list of values for a
> > > column in my first report - see below.
> > >
> > > Many thanks - help is appreciated.
> > >
> > > Greg
> > >
> > >
> > >
> > >
> > > "mlapoint" <mlapoint@.discussions.microsoft.com> wrote in message news:<31705145-03B2-45B4-96F9-4D0A2608093A@.microsoft.com>...
> > > > If you want the drop-down you need to do the following:
> > > >
> > > > 1) Create a second Dataset that returns the list of Valid Assignment ID's
> > > > and a second column for the labels you want in the drop-down
> > > > 2) On the main menu, click on Report -> Report Parameters
> > > > 3) Click on the Assignment ID parameter
> > > > 4) In the Available Values area, click on From Query
> > > > 5) Choose the new Assignment ID DataSet, Value Field, and Label Field
> > > >
> > > > Now try the Preview tab.
> > > >
> > > > Hope this helps
> > > >
> > > > "Greg" wrote:
> > > >
> > > > > Hi all -
> > > > >
> > > > > Thanks for the info. I have now a parameter working on the database
> > > > > server - specified in the where clause as you say - thats great.
> > > > > However What I want is a prompt for the user - to enter in an
> > > > > assignment id. When I pass the parameter to the where clause and hit
> > > > > run in the Data tab I am asked for the Parameter value (on the Data
> > > > > tab ) here I want a drop down list of values - which I would like
> > > > > replicated when in the preview screen - so that the user can enter a
> > > > > value from the drop down list of values box. At the moment I can only
> > > > > enter in the preview panel what i have specified in the Data tab and
> > > > > bring back the correct result. How can I get a drop down Lovs
> > > > > todisplay when the parameter is in the where clause. I tried to point
> > > > > both label and value to the specific data set but it would not allow -
> > > > > saying:
> > > > > "A label expression used for the report parameter â'ApplicationID'
> > > > > refers to a field. Fields cannot be used in report parameter
> > > > > expressions."
> > > > >
> > > > > Any ideas
> > > > >
> > > > > help is very much appreciated
> > > > >
> > > > > Greg
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message news:<uREgSGrxEHA.3572@.TK2MSFTNGP10.phx.gbl>...
> > > > > > Greg, I answered this very completely at about 1:30 in your other posting.
> > > > > > Just to reiterate. Filters and query parameters are two different things and
> > > > > > both can use report parameters. You are using filters (which retrieve and
> > > > > > then filter) versus query parameters that are part of the where clause.
> > > > > >
> > > > > > --
> > > > > > Bruce Loehle-Conger
> > > > > > MVP SQL Server Reporting Services
> > > > > >
> > > > > > "Greg" <greg_cochrane@.hotmail.com> wrote in message
> > > > > > news:2dba7d00.0411091350.72de3e0e@.posting.google.com...
> > > > > > > Am I missing a trick here? When i set up my parameter in reporting
> > > > > > > services and hit preview, the prompt apears and asks for the specified
> > > > > > > column - however the sql generated is not refined any. it brings back
> > > > > > > all the data and then filters on the selected column in the report.
> > > > > > >
> > > > > > > By contrast in business objects if i used the @.prompt function the sql
> > > > > > > generated would be refined with the input of the end user ...eg
> > > > > > > cost_centre_code = 1234 as opposed all cost centres. What reporting
> > > > > > > services seems to be doing is bringing back all cost centres then
> > > > > > > filtering on 1234.
> > > > > > >
> > > > > > > If im right it has some pretty serious implications in terms of speed
> > > > > > > of reporting -as my query is returning all data values rather than
> > > > > > > one...
> > > > > > >
> > > > > > > any ideas? any input? Isnt a parameter really just a filter rather
> > > > > > > than a condition?
> > > > > > >
> > > > > > >
> > > > > > > thanks for any ideas
> > > > > > >
> > > > > > > greg
> > > > >
> >
Monday, February 20, 2012
parameterized query that counts the number of null values in an int32 column
Using c# in the compact framework, is there a way to do a parameterized query for counting the number of records where a specified column is null. I tried all of these, but none of them work:
cmd.Add(new SqlCeParameter("@.IntColumn", SqlInt32.Null));
cmd.CommandText = "select count(*) from TableName where IntColumn is not @.IntColumn";
cmd.Add(new SqlCeParameter("@.IntColumn", DBNull.Value));
cmd.CommandText = "select count(*) from TableName where IntColumn is not @.IntColumn";
cmd.Add(new SqlCeParameter("@.IntColumn", SqlInt32.Null));
cmd.CommandText = "select count(*) from TableName where not IntColumn = @.IntColumn";
cmd.Add(new SqlCeParameter("@.IntColumn", DBNull.Value));
cmd.CommandText = "select count(*) from TableName where not IntColumn = @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Int32));
cmd.Parameters["@.IntColumn"].Value = SqlInt32.Null;
cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Int32));
cmd.Parameters["@.IntColumn"].Value = SqlInt32.Null;
cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Int32));
cmd.Parameters["@.IntColumn"].Value = DBNull.Value;
cmdGetNumRead.CommandText = "select count(*) from TableName where IntColumn is not @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Int32));
cmd.Parameters["@.IntColumn"].Value = DBNull.Value;
cmdGetNumRead.CommandText = "select count(*) from TableName where not IntColumn = @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Variant));
cmd.Parameters["@.IntColumn"].Value = SqlInt32.Null;
cmd.CommandText = "select count(*) from Meter where IntColumn is not @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Variant));
cmd.Parameters["@.IntColumn"].Value = SqlInt32.Null;
cmd.CommandText = "select count(*) from Meter where not IntColumn = @.IntColumn";
md.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Variant));
cmd.Parameters["@.IntColumn"].Value = DBNull.Value;
cmd.CommandText = "select count(*) from Meter where IntColumn is not @.IntColumn";
cmd.Parameters.Add(new SqlCeParameter("@.IntColumn", SqlDbType.Variant));
cmd.Parameters["@.IntColumn"].Value = DBNull.Value;
cmd.CommandText = "select count(*) from Meter where not IntColumn = @.IntColumn";
Whenever I use a "is not" in the query I get an exception, otherwise it returns a count of 0 which is not accurate. Any help will be appreciated.
try using this syntax:
WHERE [columnName] != DBNull.Value or WHERE [columnName] <> DBNull.Value
Darren
|||I could not get that to work. Could you provide a more detailed examle of what the c# code would actually look like?|||
I think you've found yourself a bug in the ADO provider for SQL Mobile. I tried this code on the Northwind database and it reports 0 rows (should be 22):
SqlCeConnection cn = new SqlCeConnection(@."Data Source = \program files\deviceapplication7\Northwind.sdf");
try
{
cn.Open();
SqlCeCommand cmd = new SqlCeCommand("SELECT count(*) FROM CUSTOMERS WHERE FAX = @.fax", cn);
SqlCeParameter p1 = new SqlCeParameter("@.fax", DBNull.Value);
cmd.Parameters.Add(p1);
object o = cmd.ExecuteScalar();
if ( o != null && o != DBNull.Value)
{
MessageBox.Show("There were " + ((int)o).ToString() + " null fax numbers");
}
else
MessageBox.Show("Result of query was null");
This only occurs with a parameterized query however. If you use the normal syntax (IS NULL) in your query, you'll get the right results.
Darren
|||
Thanks for your response Daren. I started a new project and ran a program a program with the very similar code as what you tried and I got the same result. I was then wondering if the limitation/bug might be related to using SqlCeCommand.ExecuteScalar with parameterized queries in general rather than specifically looking for null or not null. So I tried this program and it returned a count of 1 which is correct:
SqlCeConnection conn = new SqlCeConnection(@."Data Source= \Program Files\BugTesting\Northwind.sdf;");
conn.Open();
SqlCeCommand cmd = conn.CreateCommand();
cmd.Parameters.Add(new SqlCeParameter("@.fax", SqlDbType.NVarChar, 24));
cmd.CommandText = "select COUNT(*) from Customers where Fax = @.fax";
cmd.Prepare();
cmd.Parameters["@.fax"].Value = "030-0076545";
object o = cmd.ExecuteScalar();
if (o != null && o != DBNull.Value)
{
MessageBox.Show("There were " + ((int)o).ToString() + " null fax numbers");
}
else
{
MessageBox.Show("Result of query was null");
}
conn.Close();
conn.Dispose();
This shows that you can do parameterized queries with count and execute scalar, but I still have not found a way to count the number of null values. I don't really know how parameterized queries work under the hood, but I would guess that the code that Darren posted would translate into something like this:
SELECT count(*) FROM CUSTOMERS WHERE FAX = null
If this is true then that would explain why it returns a count of 0. Which is why in my original post I was trying things more along the lines of: cmd.CommandText = "select count(*) from Customers where Fax is @.fax"
but that generates an SqlCeException. Is this a limitation, a bug, or is there still maybe another way to do this? Anyway the only reason why I was trying to do this as a parameterized query is because in my code runs it multiple times in a row, and I thought it might run faster this way. Even if I could get this to work somehow do think that it would improve the execution speed of the query? Also where is the best place to report this as a potential bug?
Subscribe to:
Posts (Atom)