Reporting Services 2000 Filtering based on a text parameter

[goofaholix]

I've looked through the reporting services help and it's very vague on how to do this.

What I want to do is set up a prompt with three values; Open, Closed, and Due for Completion.

Based on the user selection then a filter will be inserted, something like:If the user selects "Open" then Datatable.Status not in ('Closed', 'Cancelled', 'On Hold'), if the user selects "Closed" then Datatable.Status in ('Closed', 'Cancelled', 'On Hold'), if the user selects "Due for Completion" then Datatable.Completion date > getDate()-14.

In the Report Parameters screen there is a Value column for each label, but I haven't been able to work out how that value can be inserted into the where statement.

Thanks,

 

[unclerico]

are you using a stored procedure?

 

[goofaholix]

No, I'm trying to do this within the report.

With Crystal or Cognos this would be straightforward by just incuding an if/then/else statement in the report filter using the results of a text prompt.

I can't really use a stored procedure in my current setup so my fallback option will have to be creating 3 different reports.

Bruce

 

[unclerico]

the only way that I know how to do this is to use custom code...inside the custom code you setup your sql statement like so

Function GetSql(ByVal strFilterVal As String) As String
  Dim strSql, strList As String

  strSql = "SELECT field1, field2 "
  strSql += "FROM table1 "
  
  Select Case strFilterVal
    Case "Open"
      strList = "WHERE status NOT IN ('Closed','Cancelled','On Hold')"
    Case "Closed"
      strList = "WHERE status IN ('Closed','Cancelled', 'On Hold')"
    Case "Due for Completion"
      strList = "WHERE [competion date] > (DATEADD(-14,GETDATE())"
  End Select

  strSql += strList

  Return strSql
End Function

And then in the Generic Query Designer type in

=Code.GetSql(Parameters!<parametername>.Value)

Then you need to manuaually define each field to be used in the report
You can't fire this off from the Generic Query Designer, you need to actually make the layout of your report and then preview it...not sure if this is what you wanted or not but this is what I have done in the past.