Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

how to set the 'Select All' option as default selected in multivalue parameter? Expand / Collapse
Author
Message
Posted Tuesday, April 8, 2008 3:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, May 25, 2012 8:08 AM
Points: 496, Visits: 508
Post #481367
Posted Wednesday, April 9, 2008 2:26 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 2, 2013 7:13 AM
Points: 44, 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))
Post #482109
Posted Wednesday, April 9, 2008 6:13 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:51 PM
Points: 704, Visits: 463
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
Post #482205
Posted Friday, April 11, 2008 3:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 5, 2009 3:06 PM
Points: 8, 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
Post #483529
Posted Friday, April 11, 2008 6:00 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:51 PM
Points: 704, Visits: 463
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
Post #483575
Posted Friday, April 11, 2008 6:34 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 5, 2009 3:06 PM
Points: 8, 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
Post #483605
Posted Friday, April 11, 2008 7:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 5, 2009 3:06 PM
Points: 8, 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
Post #483677
Posted Friday, April 11, 2008 7:58 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:51 PM
Points: 704, Visits: 463
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.
Post #483686
Posted Friday, April 11, 2008 8:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 5, 2009 3:06 PM
Points: 8, 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
Post #483720
Posted Monday, May 19, 2008 12:48 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 11:00 AM
Points: 21, 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!
Post #503070
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse