adding to existing variable 1

[dh42891]

USing CR 8.5, ODBC, Pervasive SQL, and generally hating DBs I'm trying to consolidate ticket sales by time slot. Currently the DB has 20 minute slots but I want it by hour. The report must be grouped by date. However, the DB has ticket sales by time by workstation. So I can't group by date alone. I don't care which workstation sold the ticket, just what time the ticket was sold. So, I was thinking of writing a formula that added the ticket sales for each time slot together. Meaning, I want CR to look at the beginning time slot and if it is b/w 6am and 7am, add it to a variable, regardless of where it was sold.

Should a formula similar to this work?:

whileprintingrecords;
if {table.time} = CTime (6, 00, 00) 
 then global NumberVar sixAMsevenAM := {table.count}
  else if
 {table.time} = CTime (6, 20, 00) then
    global NumberVar sixAMsevenAM := sixAMsevenAM + {table.count}
  else if
{table.time} = CTime (6, 40, 00) then
    global NumberVar sixAMsevenAM := sixAMsevenAM + {table.count};
sixAMsevenAM;

My assumption is that you can't use a variable in the definition of that same variable. My result is always 0.00 even though I know it should be at least 20. I also tried this for the same result:

whileprintingrecords;
if {table.time} = CTime (6, 00, 00) 
 then global NumberVar sixAMsevenAMa := {table.count}
  else if
 {table.time} = CTime (6, 20, 00) then
    global NumberVar sixAMsevenAMb := sixAMsevenAMa + {table.count}
  else if
{table.time} = CTime (6, 40, 00) then
    global NumberVar sixAMsevenAMc := sixAMsevenAMb +  sixAMsevenAMa + {table.count};
sixAMsevenAMc;

I suspect that this is a ridiculous way to get the report I want but it's the first idea I came up with. Any help would be greatly appreciated,

dylan

 

[IanWaterman]

You do not need to use variables, just use a formula in details, like

if {table.time} >= CTime (6, 00, 00) and {table.time}< CTime (7, 00, 00)
{table.count} else 0

Suppress detail line and then create a summary on this formula, it should yield a total for that time range, either for report or any grouping you require.

Ian

 

[GMcNamara]

If the db field is of type DateTime, you can group on this field and in the 'Section will be printed' drop down box, select 'for each hour'. This will group your records hourly.

 

[dh42891]

Thanks Ian, that seems to be working very well. GMcNamara, haven't tried it yet but I don't have much to do today so I probably will look at it. Thanks for the help,


dylan