Cross-Tab Formula

[kmcclung]

I have a cross-tab report which I am performing a count of how many times a particular action has occurred in a half hour. This is working as expected but I need additional assistance getting...

The report looks like this:
Action A Action B Action C (and so on)00:00 1 0 2
00:30 0 1 1
01:00 0 0 1
01:30 2 0 0
.
.
. (and so on)

There is a start and stop time for each action. I'm currently only basing the count off the start time.
For example, an Action B record has a starttime of 00:31 and a stoptime of 01:31. Now it displays as the 1 under Action B at 00:30 because it started within that half hour. But 01:00 and 01:30 have a count of zero - but I need both rows to have a count of 1 because the action was still occuring until 01:28 (during the half hours of 01:00 and 01:30). So the report should look like this:
Action A Action B Action C (and so on)
00:00 1 0 2
00:30 0 1 1
01:00 0 1 1
01:30 2 1 0
.
.
. (and so on)

Any ideas would be appreciated.

Thanks!
Kathleen

 

[kenhamady]

You can't do this in a cross-tab, because a cross-tab can't put one record in two different cells.

You can do this with a manual cross-tab, but for that you would have to pivot your report so that the times are across the top and the Actions are down the side. See faq149-243 for an explanation of manual cross-tabs.

You will have to write a formula for every column (time slot). The advantage is that every time slot will now show up. In your current cross-tab, only slots with a start time will show up. 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