Cross-tabs and Null Values 1

[lynchg]

I have a problem with a cross-tab report that seems similar to that found in thread149-1029079, but it is not quite the same.

I have a table that holds history records for service requests, so a set of these records for each particular request would show all of the activity during the life cycle of that request.

The critical fields for the cross-tab are the RqstNum (rows), Status (columns), and Avg of StatusAge (summarized field). The StatusAge is a calculated field that holds the amount of time that a service request spent (in days) in each particular status.

Example of the data:

RqstNum Status Age
123 Triage 2
123 Assigned .001
123 Addl Info 1
123 Coding 5
124 Entered .1
124 Triage 3
124 Assigned 5
124 Coding 7

When this data is thrown into a cross-tab, I want it to look like this:

Entered Triage Assigned AddlInfo Coding
123 <null> 2 0.0 1 5
124 .1 3 5 <null> 7

Avg of Age .1 2.5 2.5 1 6

I round off the values in the cross-tab to .1, so the .001 value becomes 0.0

My problem is with the cases where there should be null values the report displays 0.0, I would like those to be blank. Request 123 never entered into an 'Entered' status and 124 never entered into an 'AddlInfo' status, but the way it displays on my report, you can't distinguish them from 0.0 value for 123 under the Assigned status.

I have tried suppressing the display of the value when it is null or zero, no luck. The formatting options don't seem to be able to discern individual values within the grid.

I am using Crystal XI R2 on Windows XP Pro SP2.

Any help would be appreciated.

 

[IanWaterman]

Try replacing your Age field with a forumula

@Age

If Age < 0.5 then 0 else
Age


Ian

 

[lynchg]

Thanks, but the problem isn't in the actual data. The problem lies where there is no incidence of a request even entering into a particular status.

My example shows 2 requests that each entered 4 different statuses but between them there are a total of 5 statuses that will get pulled into the cross-tab, with each request not having any time spent in one of the 5 statuses (ie 124 was never in the 'Entered' status).

The problem is that the cross-tab somehow decides to insert a value of zero into the grid where it really should be null. I can't figure out a way to suppress those nulls from appearing as zero values. So someone looking at the report would wonder why the average in the AddlInfo column isn't .5 when there are two values, 1 and 0. It actually displays the correct average but displaying 0's where it should be null or blank makes the report misleading.

 

[lbass]

You could try setting the null to some unlikely value and then suppress based on that value. You would use the formula instead of the current summary field in the crosstab.

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

Then since the summary is an average, you should be able to use:

currentfieldvalue = .000001

... to suppress the summary.

-LB

 

[lynchg]

But there aren't null values in the data. There are about 25 different statuses are that possible for these requests. Every time the status of a request changes, a new history record is created. So one request could enter into 10 different statuses and another only 2 or 3.

When you display both of these requests in a crosstab report the crosstab control will create a column for every distinct status that occurs in the data source. But in almost every case, no single request will have entered into every single status that appears as a report column. So in those cases I would expect that cell in the crosstab grid to be blank, but it isn't, it displays a 0. Like in my example request 123 was never in the 'Entered' status, there is no null value in my data, there is just no history record that holds an occurrence of 123 in the "Entered' status.

 

[lbass]

You could try the obverse then:

if {table.field} = 0 then
.000001 else
{table.field}

Then use "suppress if zero". I can't really test this, but maybe the missing fields would still result in a zero while the fields with actual zero values, would now be non-zero.

-LB

 

[lynchg]

I really appreciate your rapid replies, this is a great forum that has helped me immensely over the years.

But unfortunately there are no values of zero in the data, we have timestamps on when a request enters and exits a status, and the Age field is a calculated field. If a request enters into a status for even just 1 min it will have an Age value of 0.000694 days.

The crosstab control is assigning some value to those 'nulls' but it isn't null and it isn't zero, even though it displays a zero. I tried to suppress the cell where the age is null or where it equals zero, or if it is less than 0.000001, or etc.... None of those formatting suppression formulas work.

 

[IanWaterman]

Going back to my original formula

@Age

If isnull(Age) then 0 else
Age

Us this formual instead of age field, In the cross tab right click the value display cell and select format, select number tab -> customise and the check suppress if zero.

Ian

 

[lynchg]

I'm sorry Ian but I don't think you are understanding my problem. There are no nulls or zeros in the data. The 'nulls' only occur within the crosstab grid where there is no occurrence of a request having been in a particular status.

 

[IanWaterman]

Sorry I understand now.

So why does this not work

In the cross tab right click the value display cell and select format, select number tab -> customise and the check suppress if zero.

The 123 Assigned is not really zero and should not suppress.

Ian

 

[lynchg]

All of the suppression formulas I have tried don't work, including zero, null, less than .00001 (about 1 sec), nothing works.

 

[IanWaterman]

Where are you trying this, in the common tab or the number tab.

I have just tested a cross tab and suppressing in the number tab as I mention above works fine. Where as you say the cross tab finds no data and normally brings back a zero.

Ian

 

[lynchg]

That did it, I was using suppression on the common tab. I hit the checkbox within the custom area of the number tab and it worked.

Thanks a lot!