asked on

Cyrstal Reports formula to check IsNull

I can't quite get my formula to work correctly. I have a parameter where the user could choose the name of a manager. The parameter defaults to ALL. My issue is that sometimes the database does not have a manager listed and I want the report to shot the Null values too if the user selects ALL

{view_PT_Employee_Training.NextDate} = {?DateParam} and
{view_PT_Employee_Training.Status} = "Pending" and
{view_PT_Employee.Status} = "Active" and
(if {?Training ID} <> "ALL" then {view_PT_Training.TrainingID} = {?Training ID} else true and
if {?Manager} <> "ALL" then {view_PT_Employee_Manager.Manager} = {?Manager} else not IsNull( {view_PT_Employee_Manager.Manager}))
T--Custom-Reports-Past-Due-Training.rpt

Mike McCracken

You need to put ( ) around the if statements. Your second if is treated as part of the ELSE of the first one.
Try this

{view_PT_Employee_Training.NextDate} = {?DateParam} and
{view_PT_Employee_Training.Status} = "Pending" and
{view_PT_Employee.Status} = "Active" and
(
if {?Training ID} <> "ALL" then 
        {view_PT_Training.TrainingID} = {?Training ID} 
else 
         true 
) and
(
if  {?Manager} <> "ALL" then 
        {view_PT_Employee_Manager.Manager} = {?Manager} 
else  
         True
)

Open in new window

What version of Crystal?

mlmcc

Sue Taylor

ASKER

2008

Mike McCracken

CR 2008 has optional parameters. I find it easier to use optional parameters than including the ALL option in the list.

If I add the ALL option then I find it easier to use AND and OR to test for the ALL rather than an IF.

{view_PT_Employee_Training.NextDate} = {?DateParam} and
{view_PT_Employee_Training.Status} = "Pending" and
{view_PT_Employee.Status} = "Active" and
(
   {?Training ID} = "ALL"
   OR 
   {view_PT_Training.TrainingID} = {?Training ID} 
) and
(
   {?Manager} = "ALL"
    OR
   {view_PT_Employee_Manager.Manager} = {?Manager} 
)

Open in new window

mlmcc

Sue Taylor

ASKER

ok. I'll try to remember that. But now what do I do to include if the manager is blank?

Mike McCracken

Do you want blank/NULL managers always included or only for ALL?
If only for ALL then the SQL should cover that since it checks for ALL or compares the manager field to the parameter.

If you want them included when a manager is specified then try

(
if  {?Manager} = "ALL" then 
        True
else  if IsNull({view_PT_Employee_Manager.Manager}) then
        True
else if ({view_PT_Employee_Manager.Manager} = '') then
        True
else
        {view_PT_Employee_Manager.Manager} = {?Manager}      
)

Open in new window

Using the other method

(
   {?Manager} = "ALL"
    OR
   IsNull({view_PT_Employee_Manager.Manager})
    OR
   {view_PT_Employee_Manager.Manager} = ''
    OR
   {view_PT_Employee_Manager.Manager} = {?Manager} 
)

Open in new window

mlmcc

Sue Taylor

ASKER

It's still not working. I know I have an employee in the database that does not have a manager listed. (I deleted the manager's name) But I still don't see it when I run the report.

{view_PT_Employee_Training.NextDate} = {?DateParam} and
{view_PT_Employee_Training.Status} = "Pending" and
{view_PT_Employee.Status} = "Active" and
(
{?Training ID} = "ALL"
OR
{view_PT_Training.TrainingID} = {?Training ID}
) and
(
{?Manager} = "ALL"
OR
IsNull({view_PT_Employee_Manager.Manager})
or
({view_PT_Employee_Manager.Manager})
= {?Manager}
)

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sue Taylor

ASKER

Thanks, mlmcc!!!