Show All - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Show All

Author

Topic

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-10-02 : 10:30:36

How to display all the records on Parameterized Report?

I would like to display all the records. Then user can use the dropdown if they want for filter. Right now, it's blank and user has to pick a value from drop down, click on preview then data is displayed.

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-10-02 : 10:36:28

in reporting services?

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-10-02 : 10:37:36

yes. SSRS 2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 10:55:27

quote:
Originally posted by doran_doran

How to display all the records on Parameterized Report?

I would like to display all the records. Then user can use the dropdown if they want for filter. Right now, it's blank and user has to pick a value from drop down, click on preview then data is displayed.

add a <No Selection> option for parameter in report and make it default. then in sp do filter like

WHERE colvalue=@parameter or @parameter ='<No Selection>'

this will give you all the data by default and then you may filter using parameter

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-10-02 : 11:01:40

I went to "Report > Report Parameters" and I only see the current parameter. Are you suggesting "Add" another parameter? If yes, what will be value in Name, Data type, Prompt, etc.

Or are you suggesting modify existing parameter and under default value pick "Null",

Please suggest. Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 11:04:16

nope. what i sugested was to add a new value among current values of parameter. currently are you hardcoding values in parameter tab or populating it from query? whatever be case, you need to add the value <No Selection> also to it and change where condition of query also

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-10-02 : 11:19:21

1. this is my query
select * from logs where UserName=@rpUser or @rpUser='<No Selection>'

2.

Thats all I did. But it's not working. Sorry if I am not following your steps.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 11:23:52

first part of 1 is correct. dont do second part in dataset window. instead go to parameters tab and add the value <No Selection> also to list of values you already added for username parameter(by the way is it a free text field currently or a dropdown?)

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-10-02 : 11:26:24

It's a dropdown.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 11:28:13

ok...how are you giving values inisde it currently? through query or hardcoding?

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-10-02 : 11:37:26

via a second query "DataSet1". (second query is SELECT USERNAME FROM LOGS GROUP BY USERNAME)

Still not working... hmm

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 11:53:11

not only that. you also need to add this value among username value returned by dataset1. whats the query used for populating dataset1?

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-10-02 : 12:02:22

SELECT USERNAME FROM LOGS GROUP BY USERNAME

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 12:09:44

ok then make it


SELECT USERNAME
FROM
(
SELECT USERNAME,1 AS OrdVal FROM LOGS 
GROUP BY USERNAME

UNION ALL

SELECT '<No Selection>',0
)t
ORDER BY OrdVal,USERNAME

doran_doran
Posting Yak Master

179 Posts

Posted - 2009-10-02 : 12:15:28

O My Goodness. It worked on the first try. Thanks again for your help and be patience with me.

Thanks a LOOOOOTTTTTTTTTTTTTTTTTTTT

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-02 : 12:18:23

i know it will!
welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources