SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to set the 'Select All' option as default selected in multivalue parameter?


how to set the 'Select All' option as default selected in multivalue parameter?

Author
Message
abhijeetv
abhijeetv
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 Visits: 510
Sad
Naughtycell
Naughtycell
Valued Member
Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)Valued Member (64 reputation)

Group: General Forum Members
Points: 64 Visits: 115
Hi try something similar to this:

CREATE PROC [dbo].[parListClubs] -- Parameter list
AS
SELECT
-1 AS ClubID,
' ' AS ClubName
UNION ALL
SELECT
Entity_ID AS ClubID,
Name AS ClubName
FROM dbo.Clubs
ORDER BY ClubName

--This will be your detailed extraction --

CREATE PROC [dbo].[rptClubMembershipType] (@ClubID VARCHAR(100) = -1)
AS
SELECT
MT.Description AS [Membership Type],
COUNT(*) AS #
FROM
dbo.MembershipType MT
LEFT JOIN
dbo.Members_Cycling MC
ON MT.ID = MC.Cycling_Province_MembershipType
LEFT JOIN
Members M
ON MC.Entity_ID = M.Entity_ID
WHERE
((NULLIF(@ClubID,-1) IS NULL) OR (M.ClubID IN (@ClubID)))
GROUP BY
MT.Description

Hope this helps: Give care to the where clause!
if u r not expecting nulls in ur filter column u can use
FilterColumn in (isnull(nullif(@Parameter,-1),FilterColumn))
Mike Nowill
Mike Nowill
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1226 Visits: 612
Under the 'Default Values' section of the Report parameters dialog box select 'From Query' and point it to your dataset and set the value field. Basically just set it up the same way as you did for the 'Available Values' section but you don't need to worry about the 'Label Field'

-Mike
JohnG-529958
JohnG-529958
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 87
Hi,

I could be wrong as I'm still new to RS and SQL but I don't think it is possible to have Select All set as default.

Mike - your solution is fine except that the Select All option is internally generated and so isn't one of the fields in your dataset so isn't available to be selected. I'd love to be proved wrong here though as I've been struggling with this one.

So far the only solution to this that I've come across is to effectively manually re-create the Select All functionality by having an All field that you set up in your query. Trouble is there appears to also be no way to switch off the automatic Select All entry if you are using a multivalued parameter - which means you end up with both an All option and a Select All option in your list.

Like I said - I'd love to be wrong here but I think this is correct.

John
Mike Nowill
Mike Nowill
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1226 Visits: 612
If you are looking for the physical "Select All" as an option to put a check mark next to and select everything RS automatically adds that for you to your list as long as "multi-value" is chosen. The steps I gave set the default to 'everything selected'

I am not 100% on this part but...for some reason I seem to recall a bug in RS 2005 where the 'select all' did not appear until a patch was applied. I can't remember for the life of me what patch (I remember that I had to contact MS to get it though and it wasn't publicly available) and I think it resolved this issue.

Hope this helps...

-Mike
JohnG-529958
JohnG-529958
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 87
Mike - Thanks for your reply. I've just tried this out and can confirm it works perfectly.

I'd read your previous response as setting a single value field. Completely didn't occur to me that I could just enter all of my values into the default list - far too obvious a solution.

Thanks,
John
JohnG-529958
JohnG-529958
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 87
Hmmm. I've just found that while adding all the values to the default list works fine when viewing the report from within the development studio when I deploy the report and view it on our intranet no defaults are set. Seems to make being able to set this when defining the report a bit pointless - perhaps I'm doing something wrong.

I've found an alternate means of achieving this though which is good enough for me as I don't have that many reports (yet, I guess). It's possible to open the report properties tab on the deployed version of the report and then select Parameters from the list of links on the left. I can then tick the default values for the deployed report here - it will even let me simply tick the Select All option of the list.

Best of all is that it looks so far like these defaults are retained for subsequent deployments of the report so this doesn't need to be reset if the report is tweaked.

John
Mike Nowill
Mike Nowill
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1226 Visits: 612
Hey John,

Are you using a data set to derive your parameters? Can you? It is far more effective to have a table containing your values then hard-code them into your report. Even if you have to create a separate table for them it will save you time in the long run since it can be reused across many reports and if you ever need to change them you can do so in 1 place.
JohnG-529958
JohnG-529958
SSC Rookie
SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)SSC Rookie (34 reputation)

Group: General Forum Members
Points: 34 Visits: 87
Hi Mike,

I do use datasets for parameter lists for all my other reports for precisely the reasons you give. This one though is something of a meta-report acting as an overview from which more detailed reports can be drilled to. The fields I need for this list aren't easily derivable from any existing table.

Until a couple of days ago I wouldn't have been able to create a table to store a parameter list like you suggest as the database I'm reporting on is part of a software package and their support people don't like us messing with their database. I've recently however talked our IT into letting me have a new database all of my own that I'm using for a rudimentary form of data-warehousing to improve performance on some reports so I could now do what you suggest. I'm unlikely though to be re-using or modifying this particular list so unless there are other benefits it seems to make more sense to keep it internal to the report it applies to.

Thanks for your feedback,
John
mtraphagen
mtraphagen
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 70
Are you using a data set to derive your parameters? ....

I'm not and would very much like to do this. Can you advise me or provide a sample?

Also, I have yet to get "Select All" set as my default, or even all the values in the list checked as default. I'm using VS2005. I must not understand the thread completely because I set the From Query to my dataset that populates my list, but it didn't check all the items in the list.

Any advice would be appreciated. Thank you!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search