Formula to eliminate values including nulls

[Freefall27]

I am having a challenge eliminating null values and others in a formula

I am using CR XI and pulling data by ODBC from Oracle DB


WO_NAME DESCRIPTION ACTUAL COMPL PROJECT ID
SO_11351 CONNECT 10/1/2006 05-2006
SO_11500 CONNECT 10/6/2006 13-00000
SO_11550 CONNECT 10/1/2006
SO_11750 CONNECT 10/11/2006 02-5555
SO_11770 CONNECT 10/3/2006
SO_11775 CONNECT 10/2/2006 03-1678





I want to query and eliminate any PROJECT ID that is null or equal to 05-2006 or 13-00000.


The desired result of using the formula on the above data would be as follows:

WO_NAME DESCRIPTION ACTUAL COMPL PROJECT ID


SO_11750 CONNECT 10/11/2006 02-5555
SO_11775 CONNECT 10/2/2006 03-1678


Thanks for your help

 

[BobSuruncle]

In your record selection, try this:

(
NOT IsNull({YourTable.ProjectID})
)
OR
(
NOT ({YourTable.ProjectID} in ['05-2006','13-00000'])
)

Bob Suruncle

 

[Kendel]

or you can combine them:

... not (isnull({YourTable.ProjectID}) and {YourTable.ProjectID} in ["05-2006","13-00000"])

 

[Freefall27]

BobSuruncle,

I entered the following in my report:

{WORK_ORDER_INST.ACTUAL_COMPL} in MonthToDate and
(
NOT IsNull({WORK_ORDER_INST.PROJECT_ID})
)
OR
(
NOT ({WORK_ORDER_INST.PROJECT_ID} in ['05-02006','13-00000'])

The nulls have been eliminated from the report but I now get data from September and still see 05-02006 & 13-00000 in the results.

 

[Freefall27]

Kendel.....I used your formula and it eliminates all null values but still shows 05-02006 & 1300000

Thanks

 

[Kendel]

should be OR not and

... not (isnull({YourTable.ProjectID}) OR[/b} {YourTable.ProjectID} in ["05-2006","13-00000"])

 

[Freefall27]

Kendel....Thanks for your help.

 

[Freefall27]

This report is working great now.

I have one more question that hopefully someone can help me with on the report.

I need to only select the previous whole week and run the report and distribute. I have put LASTFULLWEEK into the select expert which pulls desired records. Is there a way to take the dates that the report is querying and place in the report header or page header automatically?


Ex.

"DATE RANGE IS BETWEEN 10/15/06 and 10/21/06"

WO_NAME DESCRIPTION ACTUAL COMPL PROJECT ID
SO_11351 CONNECT 10/1/2006 05-2006
SO_11500 CONNECT 10/6/2006 13-00000
SO_11550 CONNECT 10/1/2006
SO_11750 CONNECT 10/11/2006 02-5555
SO_11770 CONNECT 10/3/2006
SO_11775 CONNECT 10/2/2006 03-1678

 

[Kendel]

Just put the 2 date variables in the report header. Something like:

DATE RANGE IS BETWEEN @From and @To

 

[Freefall27]

How do I set this up if I only have ACTUAL COMPLETE DATE on Report? Newer to Crystal and trying to learn as I go here. Thanks

 

[Kendel]

You can use the Min & Max function.

 

[lbass]

Use:

"Date range is between " + totext(minimum(lastfullweek),"MM/dd/yy")+" and " totext(maximum(lastfullweek),"MM/dd/yy")

-LB