| Author |
Topic |
|
rwlopez
Yak Posting Veteran
80 Posts |
 Posted - 2006-11-21 : 17:34:01
|
| On a report I have a drop down parameter that is populated by a query. I would like to have the option of selecting all records rather that only something on the list. How can I accomplish this? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
|
|
rwlopez
Yak Posting Veteran
80 Posts |
Posted - 2006-11-21 : 17:49:14
|
| I'm not including a WHERE clause and I know SQL, just when I run my report I would like to be able to select all records from the drop down box. |
 |
|
|
rwlopez
Yak Posting Veteran
80 Posts |
Posted - 2006-11-21 : 18:12:21
|
| I would like to re-state my previous question, since the previous question was mis-understood. I have a report in reporting services that contains a parameter lets call it Sales Person Name. When I run the report I would like to be able to select a single sales person or all sales people. Currently the parameter is populated by a query that generates a list of all sales people. However when the report runs I am forced to Select one sales person. What I would like to do is by default run the report for all sales people and then have the ability to select one or all sales people. The SQL that creates my report is fine my question is about parameters in Reporting Services not SQL. |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-21 : 18:13:06
|
I think this is what you want quote:
Adding the ALL member for multivalue parametersIn a SQL Server 2005 Reporting Services report, if you created a valid values list for a multivalue report parameter, a Select All option is automatically added to the available values list. If you upgrade to SQL Server 2005 Service Pack 1 (SP1), the Select All option is no longer automatically created because the option affects performance when used with a multidimensional data source. You can add an All member as a parameter value by modifying the query that supplies available values. Change the query for the dataset that provides available values to include a literal "All" member, change the query for the dataset for the report data to test for the parameter value or the All member, and set All as the default value for the report parameter. For more information, see Working with Parameters in Reporting Services.
Taken from herehttp://msdn2.microsoft.com/en-us/library/ms143380.aspx |
|
|
|
rwlopez
Yak Posting Veteran
80 Posts |
Posted - 2006-11-21 : 18:41:26
|
| Thanks. I am not sure why MS didn't have the option to select every item in a parameter built in. It dosen't make any sense that you would have to write extra code to accomplish this. |
|
|
|
rwlopez
Yak Posting Veteran
80 Posts |
Posted - 2006-11-21 : 19:54:35
|
| '' |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2006-11-21 : 22:16:49
|
quote:
Originally posted by rwlopez
Thanks. I am not sure why MS didn't have the option to select every item in a parameter built in. It dosen't make any sense that you would have to write extra code to accomplish this.
See my previous post "If you upgrade to SQL Server 2005 Service Pack 1 (SP1), the Select All option is no longer automatically created because the option affects performance when used with a multidimensional data source." |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-11-22 : 00:04:08
|
quote:
Originally posted by rwlopez
I'm not including a WHERE clause and I know SQL, just when I run my report I would like to be able to select all records from the drop down box.
Sorry, I didn't notice it was the Reporting Services forum.You had been asking such basic questions in you prior posts that I assumed that you were just starting to work with SQL and might benefit from a tutorial.CODO ERGO SUM |
|
|
|
rwlopez
Yak Posting Veteran
80 Posts |
Posted - 2006-11-23 : 13:17:02
|
| No, I have been doing this type of work for almost seven years. learned on Oracle and then used worked with MS Acess for a long time, but have only been working with T-SQL and Visual Studio for the past eight months. However SQL Server 2005 is a much more robust and powerfull tool and occasionally come across syntax questions and I find this form an efficient way of getting the answers I need from other professionals. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-11-23 : 14:04:48
|
| select * from yourtablewhere (option = 'all members' or option = somecolumn)Peter LarssonHelsingborg, Sweden |
|
|
|
|
SELECT ALL parameter