Highlighting Expert 1

[shannonlp]

Hi,

I am trying to highlight the null values of a field in my report. Is it possible to use the highlighting expert to do this? I set the value of my field to " " but it didn't highlight the null values.

I'm using CR 2008 ver 12.3.

Any help would greatly appreciated.

 

[hilfy]

What I would do is something like the following:

1. Right-click on the object that I want to use the highlighting on and select Format Object...
2. Go to the Borders tab. Turn on the Background and click on the formula button for the background.
3. In the formula I would enter something like this:

If IsNull({myTable.MyField}) or {myTable.MyField} = "" then crYellow else crNoColor

Do this for each field you want to highlight.

-Dell


DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[lbass]

You can't highlight a null field because it doesn't exist or a field that = "", since it has no space, so you either need to drop the field into a text box that you can format or replace the field with a formula like this:

if isnull({table.field}) then
" " else
{table.field}

Then you can conditionally format this formula.

-LB

 

[shannonlp]

Thanks LB - this works for what I need. Unfortunately I need to create a lot of formula fields.

 

[shannonlp]

I'm running in to a problem when trying to do this for a number field. Here is an example of what I've done for the string fields:

Created a formula field

if isnull({nps_initv_proj.proj_process_code}) then
" " else {nps_initv_proj.proj_process_code}

Placed that formula field in the details section of the report.

Formatted that field

If {@Process Code} = " " then crRed else crNoColor

This works great. However, I have a number field where I'm not able to replicate this.

I tried creating a formula field

if isnull({nps_info_tech_proj.cmtee_pri_no}) then
" " else {nps_info_tech_proj.cmtee_pri_no}

But I get the error "A string is required here". There are definitely null values the database so I'm not sure where my syntax is wrong.

I also tried to just format the field by going to Format -> Border -> Background and adding

If isnull {nps_info_tech_proj.cmtee_pri_no}
then crRed else crNoColor

but I'm getting "the keyword then is missing".

 

[IanWaterman]

" is a string so you cannot mix with a number, change formula to

if isnull({nps_info_tech_proj.cmtee_pri_no}) then
0 else {nps_info_tech_proj.cmtee_pri_no}

And then change your highlight for to use 0 as control

If you want to use strings only then convert number to text

if isnull({nps_info_tech_proj.cmtee_pri_no}) then
" " else totext({nps_info_tech_proj.cmtee_pri_no})

Default number format in text is #,###.## If you want to format number without separators or decimals use

totext({nps_info_tech_proj.cmtee_pri_no}, 0, "")

Ian

 

[hilfy]

In your border formula, it looks like you forgot the parentheses for IsNull. It should look like this:

If isnull({nps_info_tech_proj.cmtee_pri_no})
then crRed else crNoColor

-Dell

DecisionFirst Technologies - Six-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[lbass]

As Ian said, new formula for number field:

//{@numberfield}:
if isnull({nps_info_tech_proj.cmtee_pri_no}) then
0 else {nps_info_tech_proj.cmtee_pri_no}

Then use conditional formatting like this:

if {@numberfield}=0 then
crRed else
crNocolor

-LB

 

[shannonlp]

Thank you for your responses. They were very helpful.

One last question - I also have some text fields which are appearing in Crystal Reports as memo fields. There are null values in the database and I’d like to highlight those with a color.

I created a formula field:

if isnull({nps_initv_proj.stakeholder_text}) then
" " else {nps_initv_proj.stakeholder_text}

And then attempted to format it so that if there is a null value the background would display a color:

If {@Stakeholders} = " " then crRed else crNoColor

It’s not displaying the color. Any idea what I’m doing wrong?

Thanks again.

 

[shannonlp]

After looking at this further it looks like there are spaces, not null values in this particular field which is likely why I'm having an issue with this. No need to respond. Thanks again.

 

[lbass]

if isnull({nps_initv_proj.stakeholder_text}) or
{nps_initv_proj.stakeholder_text}="" then
" " else {nps_initv_proj.stakeholder_text}

-LB

 

[shannonlp]

Thanks LB - I've got everything working now.