1000 formulas vs 1 crosstab report

[cmpgeekinGa]

I am writing a report that needs to list whether or not there was a case going on in a particular room at a particular time. I am hoping that i can write it as a cross tab report, but the more i try to get this cross tab to work, the more frustrated i get.

i am basing the rows on {case.cas_roomstart} and have gone in and set the specified order at 15 min intervals as this is how my boss wants to see the information. the problem is, because i have to base the cross tab on the room start time, when the next 15 min interval is listed - the room appears to be empty because a case did not START in that 15 minutes... the only time options that i have are room start and room stop. i also get an elapsed time, but i can run this off of that figure as she wants it in a time line...

the only other option i can think of is to "fake" a crosstab format. however, because my boss wants this report to list activity 15 minute intervals, i would start with the 96 separate groupings (that i already have in the specified order), then you have to consider the 12 rooms that will be looked at... when you take the number of groupings and the number of formulas i would need for each grouping, i end up with a total over 1100!

if that is the only way to do the report, then i will get started right away, but i have a feeling i am overlooking something rather obvious because i can not imagine having to have that many formulas...

i am using Crystal 8.5 on an Oracle database...

any suggestions would be greatly appreciated...


Thanks for everything...

I run Crystal 8.5 on an Oracle 8 database...
cmpgeek

 

[synapsevampire]

Create a period table which has all times for every 15 minutes and join that to your data using a left outer.

Posting what doesn't work, and what you think might isn't as important as posting technical information, so always try to include:

Crystal version
Database and connectivity
Example data
Expected output

I know that you handled the CR and database, but the rest is all speculative based on text descriptions instead of examples.

-k

 

[cmpgeekinGa]

i do not know how to create a period table... is that difficult?

im sorry i did not explain the data and expected output as cleary as i should have - i thought trying to create a crosstab format here would look awful so i tried to just describe...

sample data and expected outcome:

there are three cases that i want to show up on this report with the following information. I want a "1" to show up in every time interval that the case is actively taking place - from start to finish... if there is no case going on, i want a "0" to show up...

case A Rm 2 start - 0737 finish - 0918
case B Rm 4 start - 0805 finish - 0837
case C Rm 5 start - 0711 finish - 0923

| Rm 1 | Rm 2 | Rm 3 | Rm 4 | Rm 5 | Rm 6 |
0700 | 0 | 0 | 0 | 0 | 1 | 0 |
------------------------------------------------------
0715 | 0 | 0 | 0 | 0 | 1 | 0 |
------------------------------------------------------
0730 | 0 | 1 | 0 | 0 | 1 | 0 |
------------------------------------------------------
0745 | 0 | 1 | 0 | 0 | 1 | 0 |
------------------------------------------------------
0800 | 0 | 1 | 0 | 1 | 1 | 0 |
------------------------------------------------------
0815 | 0 | 1 | 0 | 1 | 1 | 0 |
------------------------------------------------------
0830 | 0 | 1 | 0 | 1 | 1 | 0 |
------------------------------------------------------
0845 | 0 | 1 | 0 | 0 | 1 | 0 |
------------------------------------------------------
0900 | 0 | 1 | 0 | 0 | 1 | 0 |
------------------------------------------------------

etc...

i hope that helps some, and again - i am sorry i did not include this the first time
thanks again


Thanks for everything...

I run Crystal 8.5 on an Oracle 8 database...
cmpgeek

 

[synapsevampire]

A Period table is relatively easy, and standard fare in data warehousing.

So a table which contained every 15 minutes still wouldn't lend itself well to this layout as you get one row per detail, whereas your report is deriving multiple counts per row.

I would create a query in SQL Server to prepopulate a table with the appropriate counts for each 15 minute interval, and then use the table directly in CR.

Creating Running Totals might make the most sense if the SQL side makes you nervous, or you don't have good database programming on staff.

-k

 

[cmpgeekinGa]

thank you for your help and suggestions... <smile>

Thanks for everything...

I run Crystal 8.5 on an Oracle 8 database...
cmpgeek