Report group by Person and Hour? 1

[WannaFly]

I am trying to recreate an old foxpro report and I am not familiar with the best way to do it in crystal reports. The report is grouped by "person" and "hour" to show counts per hour:

HOUR
7 8 9 10 11 12 1 2
------------------------------------
S.L. | 0 0 1 3 4 2 0 0
R.K. | 1 3 7 0 3 4 3 2

The only way I figure i can do this is group by name, then have an SQL query for each hour but i'm not familiar with howto put the group name into the expression. Can anyone provide some insight on this? Thanks.

 

[kphu]

this is not very complicated but more so tedious.

I'm not sure how experienced you are within crystal but I'll assume you know the basics.

First thing is to create a group for name. Go to Insert --> group and then select the "field that holds the names within the db".

now the tedious part. I'm assuming that there is a field within the database that stores the hour as referenced above. lets call this field "hour"

create a formula field and name it hour7.
the code will look something like this within the formula field.

whilereadingrecords;
numbervar hour7;
if {tablename.hour} = 7 then hour7 := 1

save this formula and then create a sum based on the hour7 formula field. you do this by going to insert --> summary. then selecting hour7.

Place this summary on the same line as the name grouping you did initially.

create another formula field for hour8, hour9, hour10, etc.
within the code besure to change the hour7 to what ever it should be.

ken

 

[WannaFly]

Ken,
Thank you very much that seems to have worked exactly how I wanted. I just started with CR but I seem to be getting along with it, thanks for the help

 

[kphu]

glad to help.

good luck with the learning processs. it can be challenging, frustrating but yet fun altogether at the same time.

ken

 

[lbass]

kphu has described how to create a manual crosstab, but you might first try inserting a crosstab, using {table.hour} as your column, {table.name} as your row, and a count of whatever field you are counting (it might even be just the count of {table.name}) as your summary field. This is usually much simpler than creating a manual crosstab--if it works for your particular needs.

-LB