Search code examples
crystal-reports

Crystal formula case statement with like

I'm trying to convert sql case statement to crystal formula.

The select in sql had this in it:

...
,pf.Status_category AS Category
,CASE WHEN p.degree LIKE '%P%' AND 
               pf.department_name LIKE 'Occ%' THEN isnull(pf.department2, '') ELSE isnull(pf.department_name, '') END AS Department, 
 CASE WHEN p.degree LIKE '%P%' AND pf.department_name LIKE 'Occ%' THEN isnull(pf.Section2, '') ELSE isnull(pf.Section_name, '') END AS Section, 
 CASE WHEN p.degree LIKE '%P%' AND pf.department_name LIKE 'Occ%' THEN isnull(pf.department3, '') ELSE isnull(pf.department2, '') END AS [Department 2], 
 CASE WHEN p.degree LIKE '%P%' AND pf.department_name LIKE 'Occ%' THEN isnull(pf.Section3, '') ELSE isnull(pf.Section2, '') END AS [Section 2], 
 pdd.DepartmentName AS DP
 ,pdv.PrivilegeDetailText AS Privilages
...
FROM  dbo.Person_PrivDtl_V AS pdv INNER JOIN
               dbo.Person_Privs_Facs_V ON pdv.M_ID = dbo.Person_Privs_Facs_V.Person_Priv_M_ID INNER JOIN
               dbo.PrivDefDepartments_PDF AS pdd ON pdd.PDDept_ID = pdv.PDDept_ID INNER JOIN
               dbo.Person AS p ON pdv.Person_ID = p.Person_ID INNER JOIN
               dbo.Person_Facilities AS pf ON pf.FacCode = dbo.Person_Privs_Facs_V.FacCode AND pdv.Person_ID = pf.Person_ID

When I convert this to crystal, I can't put this in the columns of the report design. So my idea is to use a formula for each column selected. I have this so far for the first formula, but it won't let me save it:

Formula name= Department:

select {Person_Facilities.Department_name} 
case is  like "Occ%" : {Person_Facilities.Department2}

The error seems to be the like. I looked up crystal like and it seems ok except for they use "is" for the accepted answer, but when I add "is" the error seems to be the "is" when I try to save it.

What is wrong with this formula so I can use like and the case?

Is there a better way to do this? I suppose I can use a view, but my boss doesn't want views cluttering the DB. Is using a formula the way to do this in crystal? The sql also handled null, which I'm not doing, but I'm not sure how to incorporate that at this point. I'm pretty new to crystal and my team doesn't like questions. We have Crystal Reports 2008 and SQL server 2008 R2.

Solution

  • Not sure if this is what you are looking for but try this;

    select {Person_Facilities.Department_name} 
case "Occ%" : {Person_Facilities.Department2}
default : 'Unknown';

    The default is optional.

    To use the LIKE operator, it is as follows;

    If {Command_Main.Name} LIKE '*Manager*' then Do Something

    To handle NULLS in CR, there is an option to do so in the formula editor. See below. Change it from Exception for Nulls to Default Values for Nulls. enter image description here