Some help using the filter - SQL 2005 1

[dianemarie]

Hello,

Can I use a parameter in a filter? I have a field called CallRecoCode. It is populated with either an R (reco) or a C (call) - never null. I allow the user via a parameter to select either Calls, or Recos, or All on the report. In Crystal, this was accomplished in the Select Statement as below:

(if {?Call Reco Code} = 'Calls Only' then
{CALL_RECO_MASTER.CallRecoCode} = 'C' else
if {?Call Reco Code} = 'Recos Only' then
{CALL_RECO_MASTER.CallRecoCode} = 'R' else true)

Instead of doing some sort of Case statement, I am wondering if I can use the filter tab in the Dataset dialog box but I'm struggling with it. Any help in the right direction would be appreciated. Thank you.

 

[RiverGuy]

Yes you can. Make sure your parameter is set to "Multi Value". This allows the user to choose more than one.

In your filters tab of your DataSet, Expression = the name of your field. The operator should be "In." The value should be "=Parameters!YourParameter.Value." Make sure there is no "(0)" after the word value--or it will only pick up the first selected item.

 

[dianemarie]

Awesome! Thank you so much. One more question. Can I default the parameter to the Select All option?

 

[dianemarie]

OK, ONE more question. Almost every report we create runs by a date range. I have been doing it in SSRS by creating two parameters, StartDate and EndDate, and putting this in the Where statement. Is this the best way to do this?

WHERE
calldatetime >= @StartDate and calldatetime <= @EndDate

 

[RiverGuy]

For question #1, I'm not sure. It's usually easy enough to click the "select all" box. A quick search turned up this:

http://blogs.msdn.com/bimusings/arc...i-value-parameters-in-reporting-services.aspx

For question #2, yes, that method is fine. I try to filter out my date range in the query as opposed to bringing over a whole transactional table and filtering in SSRS.