Dealing w/ Null Values (for Numerics) in Cross Tab Report

[cinbh]

I have a situation where I need to differentiate between an actual net difference (using numerics from 2 tables)of zero vs. a null value showing as zero in the cross tab. Using Crystal 8.5 here is data for my NetDiff Cross tab report:
Row= Item, Column=Shipweek, Summarized field = CurrentInventory - CurrentCommittedtoCust
Record Selection based on Summarized Field to track RedLevel of Inventory.

Table1: CURRENTINVENTORY:
Item 07/22 07/29 08/05
TearDrops 6 22 20

Table2: CURRENTCOMMITTEDtoCUST:
Item 07/22 07/29 08/05
TearDrops 1 0 23

My Cross Tab Output WITH Record Selection of Netdiff<=6
NetDiff Crosstab report output:
Item 07/22 07/29 08/05
TearDrops 5 0 -3

The week 7/29 is a zero because it is null, but the enduser would be interpreting that we are at a critical level. Is there a way to flag this null within this crosstab?!!!
Thank you in advance for your help.
Cindy

 

[synapsevampire]

Depending upon what you mean as a flag in the crosstab...you might use a formula in lieu of the field as in the following:

If isnull((MyTable.MyField}) then
-99999
else
(MyTable.MyField}

Or you can format the font based on it's value to be a different color:

Right click the field in the crosstab and select format field.

Select the Font tab and click the X-2 next to Color.

Place something like:

if isnull({MyTable.Teardrops}) then cryellow

Or some combination of both.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[cinbh]

Well, I did try the following in the format of @Netdiff Font Tab area

If IsNull({@Netdiff}) Then crGray

But everything, zeros included stay black. Unfotunately, I can't assign a -99999 since we might actually have an occurrence of it so coloring the zeros and noting is better. Not exactly sure how everything is processed during a cystal cross tab execution, but are the nulls created before my formula execution? In which case I might have to deal with the separate fields first?! Thanks for the speedy help so far.
Cin

 

[synapsevampire]

Are you sure that your numbers are nulls? And I turst that you understand that a zero is NOT a null. To check for zero, you'd place:

If {@Netdiff} = 0 Then crGray

Since you didn't supply what's in the @netdiff formula, I can't check the logic there.

Perhaps you have convert nulls to default turned on, hence it treats nulls as zeros and won't format, because it works here.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[kenhamady]

I am not I understand what you are trying to do, but Summary values in a cross-tab will never be null, even if the fields thay are based on are null. The only time I have seen summaries return a null is on running total objects that hit a null in their evaluate formula.

Is the Null stored in the database, or generated by having a missing record? If it is stored you could replace the null values (in a formula) with a very small numeric value that would not occur in real life. It would round to zero, so it would not affect the totals, but would allow you to differentiate Nulls from zeros by looking for a remainder.

Also, you can't skip the else clause in conditional color formulas or they will return BLACK as an else. You would need:

Else NoColor Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[cinbh]

Well, the summarized field section of the crosstab is what I'm using which is:
netdiff = CurrentInventory - CurrentCommittedtoCust
In my example above that cell would be null based on my select of netdiff being less than or = to 6

So based on my SELECT there would be no record in the CurrentCommittedtoCust that would cause the 7/29 netdiff value to evaluate to a <=6. So wouldn't that cause it to be null? Hope this all makes sense, man nothing is ever easy.

FYI - we need to show true zeros for our availability people to know when they should go in crisis mode, so highlighting the null &quot;0's&quot; would be a great fix for me.

 

[cinbh]

Ken, I also redid the formula based on your else condtion:
If {@Netdiff}=0 Then crGray
Else nocolor
and there was no change to the output which really makes me wonder what exactly is showing in that particular cell.
Cindy

 

[kenhamady]

If you are formatting a cross-tab cell you can't refer to the formula field itself, you have to refer to the 'CurrentValue' which is in the function list.

How are these tables joined?
Is there a one-many relationship between them?
Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[cinbh]

Thank you for the CurrentFieldValue tip which works, but now I have to deal with a Real 0 situation versus coloring the display of 0 filling the cell. I had forgotten that the database developer has the CurrentInventory, CurrentCommittedtoCust fields in the same table so there is no link involved except for Item description table. Thanks, for your help I have used the formulas that you have posted, but SO MUCH to learn.
Cindy

 

[cinbh]

Okay this is the last issue I need to resolve regarding this crosstab, now that I can select the color BUT I need it to print a Gray 0 if it is just a display for filler versus Red 0 when the net difference is actually a 0 so I did the followng I set up a
realzero formula:
If {SalesPlanDetail.sdcommit}={SalesPlanDetail.sdcurvalue} then
&quot;YES&quot;
ELSE &quot;NO&quot; (I know this formula works as I tested on another report)

Then formatted the print cell in the crosstab
If CurrentFieldValue=0 and {@realzero}=&quot;No&quot; Then crGray
Else crRed

Somewhere I doing something wrong since it does not evaluated the format correctly or if it's just not possible?

 

[kenhamady]

You can't format cross-tab cells based on report fields, only xtab values like the cell, row and column.

When you refer to report fields in the cross-tab it uses the values from the active record at the time the cross-tab is printed. That means that if the Xtab is in the report header, all cells in the cross-tab would be formatted based on the first record in the report, NOT the record(s) used in the cross-tab. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[cinbh]

Thank you to Kai and Ken for all your input. I've forwarded this on to my manager and he and I are rethinking other ways to approach. I guess I may have to manually set up a &quot;Cross Tab&quot; report, as our availability dept really needs this to track with.
Cindy