Grouping on nonexistant records

[CrystalTool]

I work in a call center and use Crystal 10 on an MS-SQL database. The end-user wants a chart with the number of calls answered and abandoned by hour, by day.

The problem: the center is closed 12:00am to 6:00am every day. I grouped the report on the hour. The report doesn’t find any records prior to 6:00am, so the first group is 6:00am. The users want to see 12:00am through 5:00am with zeros next to them. How can I do this?

I tried 24 running total fields, but I can't do 24 fields per stat (calls answered, abandoned, busy, etc.). I tried a for-while loop with isnull, but it also starts at 6:00am. Essentially, I need one formula to chart.

Mitch

 

[dgillz]

As you have discovered, crystal does not report on non-existent data. So you a couple of options:

Put dummy records into the database for those time frames, even if the value is zero, so crystal will find them and group on them, OR

Create a time table which has all 24 hours worth of data in it, and link from the time table to the call table. Then group by the time from the time table (rather than the tie from the call table). This way it will find no calls, but still find the time from the other table.



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports
askdon@srhconsulting.com

http://www.srhconsulting.com

 

[Turkbear]

Hi,
If you use the TimeTable --> Call Table
be sure to use a Left Outer Join..

 

[CrystalTool]

Hi, I appreciate the help. (And wow was it fast!) The database is on the switch, which is read-only to me. I can't create any tables.

I think a stored procedure might work, but I can't get my system developers to help with this.

Can I assume an array within Crystal wouldn't help?

Mitch

 

[synapsevampire]

I have a FAQ on this with an example script for creating a period table using MS SQL Server:

faq767-4532

-k