April 8, 2008 at 3:52 am
🙁
April 9, 2008 at 2:26 am
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))
April 9, 2008 at 6:13 am
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
April 11, 2008 at 3:40 am
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
April 11, 2008 at 6:00 am
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
April 11, 2008 at 6:34 am
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
April 11, 2008 at 7:52 am
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
April 11, 2008 at 7:58 am
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.
April 11, 2008 at 8:31 am
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
May 19, 2008 at 12:48 pm
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!
May 20, 2008 at 8:09 am
mtraphagen:
If you want a default select for your parameters then you can set that up in the "Default values" section at the bottom of the "Report parameters" screen. If you want just one value chosen then you can use the "non-queried" option. for example, if you wanted toady's date selected then you would enter "=today()". If you want to select a bunch of stuff (or everything) from a query then you can do that with the "From query" option. It will ask you for a dataset and and a value field that matches the value field from the "available values" section.
May 20, 2008 at 9:03 am
Hi Mike,
I'm missing something really obvious. (Sorry) it sounds as if I'm doing precisely what you describe, but my check boxes are not checked by default.
In avail values: From query, a dataset I've created just for this list. Value\label fields selected from this list.
Default values; From Query. I've tried both the main dataset and the list dataset. Neither sets my check boxes. (Actually, main dataset errors out.)
SQL Server 2005 is on the server with SP2. I'm using VS2005 on my dev box. I never upgraded the server from previous. I built it last month then applied SP2.
any ideas what I'm doing wrong?
Thank you a whole bunch!
Michelle
May 20, 2008 at 9:09 am
Hey Michelle,
Your "default value" should be identical to your "available values" settings and it should work just fine. Have you tried it in both VS and SSRS?
May 20, 2008 at 9:14 am
I want to select a bunch of stuff (or everything) from a query then you can do that with the "From query" option. It will ask you for a dataset and and a value field that matches the value field from the "available values" section.
...I did that, my values display, but none are checked. I have no idea what I am missing.....
🙁
May 20, 2008 at 9:23 am
Hi,
I'm using VS2005. I'm deploying to Sharepoint so I can't use SSRS right? Or do you mean Report Builder? Report Builder won't open up my VS2005 created reports.
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply