How do I convert this Crystal Report IF statement for use in a WHERE clause in Reporting Services?

Question

I'm trying to translate this Crystal Reports IF Statement for use in a WHERE clause -

{@receipt_datetime_daylight} in {?DateRange} and
(if {?Call Sign} = "All Call Signs" Then
    {cacs_incident_task.resource_or_class_id} = {cacs_incident_task.resource_or_class_id}
Else If {?Call Sign} = "All Sierra Call Signs" Then
    {cacs_incident_task.resource_or_class_id} in ["S10", "S11", "S12"]
Else If {?Call Sign} = "All Whiskey Call Signs" Then
    {cacs_incident_task.resource_or_class_id} in ["W01", "W02", "W03"]
Else
    {cacs_incident_task.resource_or_class_id} = {?Call Sign}) and
(if {?OffenceType} = "All Offences" Then
    {cacs_inc_type.description} = {cacs_inc_type.description}
else
    {cacs_inc_type.description} = {?OffenceType})

CASE statements don't work in Reporting Services, so I need to find a why of translating this into a WHERE clause. Does anyone know a way?

Answer 1

you have three solutions:

1- create a query and put these conditions in its WHERE clause.

2- create an expression for this work and do this work with that. you can write with BASIC language on it.

3- you can use an assembly for this. it means that you must make a dll in your favorite language and use from its methods in your report.

Answer 2

After a bit of work, here's my solution -

/* Input Parameters */
Declare @StartDate datetime
Declare @EndDate datetime
DECLARE @CallSign varchar(50)
DECLARE @OffenceType varchar(50)

/* Local variables */
declare @CallSignClause nvarchar(1000)
declare @OffenceTypeClause nvarchar(1000)
declare @DateClause nvarchar(1000)
declare @SQLSelect nvarchar(4000)

/* Test Parameters */
Set @CallSign = 'All Whiskey Call Signs'
Set @OffenceType = 'Burglary'
Set @StartDate = { d '2010-01-01' }
Set @EndDate = { d '2010-04-01' }

if @CallSign = 'All Call Signs' OR @CallSign is null
    set @CallSignClause = ' cacs_incident_task.resource_or_class_id = cacs_incident_task.resource_or_class_id'

if @CallSign = 'All Sierra Call Signs' 
    set @CallSignClause = ' (cacs_incident_task.resource_or_class_id IN (''S10'', ''S11'', ''S12'', ''S13'', ''S14'', ''S15'', ''S16'', ''S17'', ''S18'', ''S19'', ''S20'', ''S21'', ''S22'', ''S23'', ''S24'', ''S25'', ''S26'', ''S27'', ''S28'', ''S29'', ''S30'', 
               ''S33'', ''S34'', ''S35'', ''S51'', ''S52'', ''S53'', ''S82'', ''S83''))'

if @CallSign = 'All Whiskey Call Signs' 
    set @CallSignClause = '(cacs_incident_task.resource_or_class_id IN (''W01'', ''W02'', ''W03'', ''W04'', ''W11'', ''W12'', ''W13'', ''W14'', ''W15'', ''W22'', ''W23'', ''W31'', ''W32'', ''W33'', ''W34'', ''W42'', ''W43'', ''W44'', ''W45'', 
               ''W51'', ''W52'', ''W53'', ''W54'', ''W58'', ''W62'', ''W63'', ''W64'', ''W65'', ''W68'', ''W81'', ''W82'', ''W83'', ''W84'', ''W92'', ''W93'', ''W94'', ''W95''))'

if (@CallSign <> 'All Call Signs') AND (@CallSign <> 'All Sierra Call Signs') AND (@CallSign <> 'All Whiskey Call Signs') AND (@CallSign is not null)
    set @CallSignClause = ' cacs_incident_task.resource_or_class_id = ''' + @CallSign + ''''

if @OffenceType = 'All Offences' OR @OffenceType is null
    set @OffenceTypeClause = 'cacs_inc_type.description = cacs_inc_type.description'
else
    set @OffenceTypeClause = 'cacs_inc_type.description LIKE ''%' + @OffenceType + '%'''

if @StartDate is null
    set @DateClause = ''
else
    /* set @DateClause = ' ccors_offence.committed_to_date between cast(''' +left( cast(@StartDate as varchar(20)), 12) + '''  as datetime) and cast(''' +left( cast(@EndDate as varchar(20)), 12) + '''  as datetime)' */
    set @DateClause = ' DateAdd(dd, 0, DateDiff(dd, 0, cacs_incident_header.at_scene_date)) + DateAdd(dd, 0 - DateDiff(dd, 0, cacs_incident_header.at_scene_time), cacs_incident_header.at_scene_time) between cast(''' +left( cast(@StartDate as varchar(20)), 12) + '''  as datetime) and cast(''' +left( cast(@EndDate as varchar(20)), 12) + '''  as datetime) AND ' 

set @SQLSelect = 
'SELECT
    cacs_incident_header."id", cacs_incident_header."receipt_date", cacs_incident_header."receipt_time", cacs_incident_header."receipt_daylight", cacs_incident_header."at_scene_date", cacs_incident_header."at_scene_time", cacs_incident_header."at_scene_daylight",
    cacs_incident_task."resource_or_class_id",
    cacs_resource."description" as cacs_resource,
    cacs_inc_type."description" as cacs_inc_type,
    atscene_datetime = DateAdd(dd, 0, DateDiff(dd, 0, cacs_incident_header.at_scene_date)) + DateAdd(dd, 0 - DateDiff(dd, 0, cacs_incident_header.at_scene_time), cacs_incident_header.at_scene_time) 
FROM
    { oj ((("universe_db"."dbo"."cacs_incident_header" cacs_incident_header INNER JOIN "universe_db"."dbo"."cacs_incident_header_inc_type_id" cacs_incident_header_inc_type_id ON
        cacs_incident_header."id" = cacs_incident_header_inc_type_id."id")
     INNER JOIN "universe_db"."dbo"."cacs_incident_task" cacs_incident_task ON
        cacs_incident_header."id" = cacs_incident_task."incident_header_id")
     INNER JOIN "universe_db"."dbo"."cacs_inc_type" cacs_inc_type ON
        cacs_incident_header_inc_type_id."inc_type_id" = cacs_inc_type."id")
     INNER JOIN "universe_db"."dbo"."cacs_resource" cacs_resource ON
        cacs_incident_task."resource_or_class_id" = cacs_resource."id"}
WHERE ' + @DateClause + @CallSignClause + ' AND ' + @OffenceTypeClause +
' ORDER BY cacs_incident_task."resource_or_class_id" ASC'

/* exec sp_executesql @SQLSelect */
print @SQLSelect