Hello, I have a crosstab report(

[burakcan]

Hello,

I have a crosstab report(Please see attached report file) that shows daily jobs run status. "P" Means Pass, "F" means Failed. I have also empty cells that means that job did not run that day. I would like to put "N" (Not run) instead of showing empty cell and change the background color yellow. Can anyone help me to do that?

Any help is greatly appreciated,
Thanks
Burakcan

 

[hilfy]

You'll want to use a formula instead of whatever status field you're using. You don't give any details about how your data is set up, but I'm going to assume that you have a master list of jobs in one table that is left outer joined to a list of schedules in another. You would create a formula called {@Status} which would look something like this:

If IsNull({schedule_table.status_field}) then 'N' else {schedule_table.status_field}

Place this on the report then right-click on it and select "Format Field...". Go to the "Border" tab and check the "Background" checkbox. Then click on the formula button to the right of Background and enter something like the following:

If {@Status} = 'N' the crYellow else crNoColor

This should get you at least close to what you're trying to do.

-Dell

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

http://www.decisionfirst.com

 

[burakcan]

Hi Hilfy,

Thanks for your quick response.
The status field hold only "P" or "F" values only. Empty cell means that job is not schedule to run that day. So below code
change cells' background green and red.(in Format Editor->Border->Background X2 option)

if currentfieldvalue = "P" then crGreen
else if currentfieldvalue = "F" then crred

I need to put "N" in empty cells and make bacground is Yellow.

Thanks
Burakcan

 

[hilfy]

Instead of using the field in your crosstab, use a formula similar to the one that I described above. This will give you your "N" value.

Then modify the color setting something like this:

Switch(
currentfieldvalue = 'P', crGreen,
currentfieldvalue = 'F', crRed,
currentfieldvalue = 'N', crYellow,
true, crNoColor)

-Dell

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

http://www.decisionfirst.com

 

[burakcan]

Hi,


There is no "N" value for that day. Crosstab puts blank value for that day .

My question is how can I put "N" in empty space and make background is yellow.

Thanks
Burakcan

 

[hilfy]

The answer is DO NOT USE THE FIELD! Create a formula that wil set the "N" for you when there is no value in the field and use the formula in the cross-tab instead of the field.

-Dell

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

http://www.decisionfirst.com

 

[burakcan]

How do you do that?

 

[hilfy]

Wherever you have the field in the cross tab, take it out and put the formula in the cross tab instead.

-Dell

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

http://www.decisionfirst.com

 

[burakcan]

Hi Again,

I have created formula:
if {S_CCO_JOB_STATUS;1.Status} = 'P' then 'P'
else if {S_CCO_JOB_STATUS;1.Status} = 'F' then 'F'
else 'N'

then replaced with crosstab field but still geting null value.

Please advise...

Thanks
Burakcan

 

[hilfy]

The field will be null when there is no value. When comparing anything to null, the answer is null - not true or false, so you have to check for null first. I would change your formula based on the very first formula I posted for you. It would look like this:

If IsNull({S_CCO_JOB_STATUS;1.Status}) then 'N' else {S_CCO_JOB_STATUS;1.Status}

For a more thorough discussion of how null works see the note that I wrote for the SAP Crystal Reports and Dashboard Solutions Facebook page:

http://on.fb.me/O1mrgc

-Dell

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

http://www.decisionfirst.com

 

[burakcan]

it did not work still showing null value because there is no record in table for that job for that day.

Thanks anyway.

 

[lbass]

Create this formula EXACTLY as shown in the field explorer:

If IsNull({S_CCO_JOB_STATUS;1.Status}) or
trim({S_CCO_JOB_STATUS;1.Status}) = "" then
"N" else
{S_CCO_JOB_STATUS;1.Status}

Remove your current summary field from the crosstab and replace it with this formula. Then use a color formula like this:

if currentfieldvalue = "P" then
crGreen else
if currentfieldvalue = "F" then
crred else
if currentfieldvalue = "N" then
cryellow

-LB