Use Arrays or Not?

[Jill50]

I am needing to produce a report from a SQL Server based Calls for Service type system that has a parameter date range on the date/time the call was received, the column headings would be the days of the week and the rows would be the time of day(in military time) in one hour increments. The data needs to be the number of calls received. In other words, for the date range entered, how many calls were received on a Monday between 0800 - 0859,etc. I have been advised that using arrays might be the best way to handle this type of report. Is that correct? And, if so, can anyone point me to some good examples and references? I am an inexperienced code writer, so I need to start from basics!
Thanks for any help you can provide!

 

[chelseatech]

The ideal would be to use a crosstab object in your report.

Create a formula for day..
DayofWeek({table.datefield})

used in this formula.....
formula for DayName
totext({@day},0) + " " +
["Sun","Mon","Tue","Wed","Thu","Fri","Sat"] [{@day}]

Formula for time
numbervar h:=hour({table.timefield})
totext(h,"00&quot + ":00 - " +
totext(h,"00&quot + ":59"

Use the DayName formula as the column,
Use the Time formula as the row and
select the call ID and count it in the summarised field in the crosstab.

Place thr crosstab object in the report header section.
Don't forget to suppress the detail section.

 

[kenhamady]

Keep in mind that this cross-tab may not inlcude all 7 days or all 24 hours. (ie, if there were no calls at 3am this week, that row would be missing.

You could build a manual cross-tab to hard code the days (See my FAQ on common formula examples). This solves the problem for columns, but if you use groups to do the hours you have the same problem.

You could create 168 formulas (one for each hour of the week) and 168 totals (one for each formula) and this would ensure that every hour and every day appears.

Last, my choice, would be to could create 24 formulas that are like this:

if Hour({your field}) = x then 1 else 0

X would be numbers from 1 - 24.
Then I would make 24 cross-tabs for the report footer, each with the column field of Day of week. NO ROW field.

In this last technique you only need one record for each day. If you have that you will get all days AND all hours. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/

 

[HowardHammerman]

I like Ken's approach and I wonder why Bruce
appended the text representation of the number of the day to the spelled out day.

For a step by step descripton of how to do a manual cross tab check out my FAQ by the same name.

Crystal Reports training, consulting, books, training material, software, and on-site support. Scheduled training in 8 cities.

http://www.hammerman.com

800-783-2269

 

[Jill50]

Thank you all for your help! ChelseaTech's solution worked great except for when there were no calls in a time interval for a week. Since this is for a Police Dept, I don't know how often that would happen, but I'm going to try it Ken's way just in case! FYI - the reason for appending the day of the week number was so that the columns would appear in order from Sunday to Saturday instead of sorting alphabetically and starting with Friday! Thanks again - this is a great forum!

 

[Jill50]

Ken, If you happen to look at this thread again, I tried your method, but the biggest problem is that the 24 crosstabs in the footer print the column names 24 times, or am I doing something wrong?
Thanks!

 

[kenhamady]

This takes a little monkeying around becuase there isn't a feature to eliminate the titles in one step. But you can:

1) suppress the column heading text
2) make the column heading boxes very short
3) Suppress the grid lines of the cross-tab

In V8.5 you can even tell it which gridlies to suppress. Ken Hamady, On-site/Phone Crystal Reports Training/Consulting
Quick Reference Guide to using Crystal in VB

http://www.kenhamady.com/