Solved

Crystal Reports - Null Values

Posted on 2010-11-09
6
577 Views
Last Modified: 2012-05-10
Hi there,

I have a long formula (snippet below) which identifies a series of codes and groups them under various headings.  These codes have a date to that is Blank and some are populated with dates.  I only want those NOT populated with a date or null but am not able to code the formula correctly to verify the results.

if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Audit Committee Chair", "Audit Committee Member"] and {CnCnstncy_1.CnCnstncy_1_Date_To} IsNull then "Audit Committee"

Can someone see what I am doing wrong with the above?  
Thank you -
0
Comment
Question by:rporter45
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 34096652
There's a quirk in crystal iisnull must be the first part of your if statement.
0
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 34096843
IsNull is a function, so you put something inside the function brackets

if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Audit Committee Chair", "Audit Committee Member"] and IsNull({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Audit Committee"
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 34097122
It doesn't need to be first so long as the field being tested isn't used.

mlmcc
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rporter45
ID: 34106877
Hi there,

I have taken the advice above regarding IsNull but something is still not working.  Attached is the code I am using.  I am trying to verify that the data is working but it's not returning any of the requested names such as the first, Audit Committee.  It also looks as though there are far too many records.

Any suggestions would be appreciated,

Thank you -


// Seletion Committe Prior to null values
if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Audit Committee Chair", "Audit Committee Member"]
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Audit Committee" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Compensation Advisory Group Chair", "Compensation Advisory Group Member"] 
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Compensation Advisory Group" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Deputy Mayor", "Mayor", "Councillor"]
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Dignitaries" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Executive Committee Chair", "Executive Committee Member"] 
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Executive Committee" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Finance Committee Chair", "Finance Committee Member"]
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Finance Committee" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Foundation Board Chair", "Foundation Board Member", "Foundation Board Vice-Chair", "Foundation Board Member at Large", 
"Foundation Board Past Chair", "Foundation Board Secretary", "Foundation Board Treasurer"]
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Foundation Board" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Governance Committee Chair", "Governance Committee Member"]
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Governance Committee" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Hospital Board Chair", "Hospital Board Member"] 
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Hospital Board" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Investment Committee Chair", "Investment Committee Member"]
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Investment Committee" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Nominating Committee Chair", "Nominating Committee Member"]
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Nominating Committee" else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Special Events Committee Chair", "Special Events Committee Member"]
and IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then "Special Events Committee" 

Open in new window

0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 125 total points
ID: 34106973
What do you want returned if ({CnCnstncy_1.CnCnstncy_1_Date_To})  is not null?

Try this one, it is a bit simmpler

mlmcc
If IsNull ({CnCnstncy_1.CnCnstncy_1_Date_To}) then
(
if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Audit Committee Chair", "Audit Committee Member"]  then 
    "Audit Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Compensation Advisory Group Chair", "Compensation Advisory Group Member"] then 
   "Compensation Advisory Group" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Deputy Mayor", "Mayor", "Councillor"] then 
   "Dignitaries" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Executive Committee Chair", "Executive Committee Member"]  then 
   "Executive Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Finance Committee Chair", "Finance Committee Member"] then 
    "Finance Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Foundation Board Chair", "Foundation Board Member", "Foundation Board Vice-Chair", "Foundation Board Member at Large", 
"Foundation Board Past Chair", "Foundation Board Secretary", "Foundation Board Treasurer"]  then 
    "Foundation Board"  
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Governance Committee Chair", "Governance Committee Member"] then 
    "Governance Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Hospital Board Chair", "Hospital Board Member"]  then 
    "Hospital Board" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Investment Committee Chair", "Investment Committee Member"] then 
    "Investment Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Nominating Committee Chair", "Nominating Committee Member"]  then 
    "Nominating Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Special Events Committee Chair", "Special Events Committee Member"] then 
    "Special Events Committee" 
)
Else
     "Date is not null"

Open in new window

0
 
LVL 1

Accepted Solution

by:
deemas earned 125 total points
ID: 34128180
mlmcc has organized well, but I do think, It would be better, If we would use "Switch-Case" instead of following part
(
if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Audit Committee Chair", "Audit Committee Member"]  then 
    "Audit Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Compensation Advisory Group Chair", "Compensation Advisory Group Member"] then 
   "Compensation Advisory Group" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Deputy Mayor", "Mayor", "Councillor"] then 
   "Dignitaries" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Executive Committee Chair", "Executive Committee Member"]  then 
   "Executive Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Finance Committee Chair", "Finance Committee Member"] then 
    "Finance Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in ["Foundation Board Chair", "Foundation Board Member", "Foundation Board Vice-Chair", "Foundation Board Member at Large", 
"Foundation Board Past Chair", "Foundation Board Secretary", "Foundation Board Treasurer"]  then 
    "Foundation Board"  
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Governance Committee Chair", "Governance Committee Member"] then 
    "Governance Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Hospital Board Chair", "Hospital Board Member"]  then 
    "Hospital Board" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Investment Committee Chair", "Investment Committee Member"] then 
    "Investment Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Nominating Committee Chair", "Nominating Committee Member"]  then 
    "Nominating Committee" 
else if {CnCnstncy_1.CnCnstncy_1_CodeLong} in["Special Events Committee Chair", "Special Events Committee Member"] then 
    "Special Events Committee" 
)

Open in new window

0

Featured Post

Webinar: MongoDB® Index Types

Join Percona’s Senior Technical Services Engineer, Adamo Tonete as he presents “MongoDB Index Types, How, When and Where Should They be Used?” on Wednesday, July 12, 2017 at 11:00 am PDT / 2:00 pm EDT (UTC-7).

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question