Solved

Store total calculated duration sessions.

Posted on 2016-08-05
44
61 Views
Last Modified: 2016-08-12
I have a Crystal report with a occupation counter (+1 for Entry, -1 for Exit) and a set quota of 51. Each transaction that passes this counter mark (51) should start a timer (based on timestamp under EventTime), the timer stops as soon as the count is back to 51. It does not matter if there is 1 transaction triggered past 51 for 5 mins I want to keep track of this. At the end of the parameter selection (typical 1 month) I just want to show the total duration for the counter being past 51. I also want to show the total duration at the end of each day.

The attached report already shows the triggered transactions and I'm looking for a way to store and reflect the total duration, for example with reference to the snapshot below (on page 68 of 85) :-

Triggered # 16 :-  on 06/24/2016 08:32:00AM  off 06/24/2016 09:21:00AM Trigger 16 duration stored as (49 minutes)
Triggered # 17 :-  on 06/24/2016 09:22:00AM  off 06/24/2016 10:03:00AM Trigger 17 duration stored as (41 minutes)
Triggered # 18 :-  on 06/24/2016 11:23:00AM  off 06/24/2016 12:11:00PM Trigger 18 duration stored as (48 minutes)

Total duration for the 3 triggers in the example = Trigger 16+Trigger17+Trigger18 = 138 Minutes (2Hrs. 18Mins)

Trigger example
0
Comment
Question by:John-S Pretorius
  • 26
  • 15
  • 3
44 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 50 total points
ID: 41745106
The basic idea is

IN the report header add a formula

Name - DeclareVariables
WhilePrintingRecords;
Global NumberVar TotalMinutes;
''

Open in new window


When you calculate the time
Name - CalcTotal
WhilePrintingRecords;
Global NumberVar TotalMinutes;

If MeetsCriteria then
    TotalMinutes := TotalMinutes + {@ElapsedTime};
''

Open in new window


To display it
Name - DisplayTime
WhilePrintingRecords;
Global NumberVar TotalMinutes;
TotalMinutes 

Open in new window


mlmcc
1
 

Author Comment

by:John-S Pretorius
ID: 41745373
This is a great start, thank you  - how about the initial trigger 'If meets criteria' how do I include the first occupation count .time (running total) > 51 to when it reaches 51 again and calculate {@ElapsedTime}
0
 
LVL 34

Expert Comment

by:James0628
ID: 41745432
I think you need a couple more variables.

 Change the first formula to:

WhilePrintingRecords;
Global NumberVar TotalMinutes;
Global BooleanVar over_quota;
Global DateTimeVar over_quota_start;

over_quota := False;

''

Open in new window

over_quota will be set to True when you go over, and then back to False when you drop below again.
 The event time when you first went over the quota will be saved in over_quota_start.


 Change the second formula to:

WhilePrintingRecords;
Global NumberVar TotalMinutes;
Global BooleanVar over_quota;
Global DateTimeVar over_quota_start;

if not over_quota and {#running total} > 51 then
// If we just went over the quota, set the flag and save the starting datetime
(
  over_quota := True;
  over_quota_start := {EventTime field}
)
else
  if over_quota and {#running total} <= 51 then
// If we were over the quota and just dropped below again, clear the flag and
// add to the total "over quota" minutes
  (
    over_quota := False;
    TotalMinutes := TotalMinutes + DateDiff ("n", over_quota_start, {EventTime field});
  );
''

Open in new window

You're using a running total to count the transactions, correct?  Replace {#running total} with your running total.

 I used "n" with DateDiff to get the difference in minutes.  I think CR calculates the difference in whole numbers and ignores the fractions.  If you want a more precise figure, you could use "s" to get the difference in seconds and add those up, and then divide the result by 60 at the end (in the last formula that mlmcc posted) to convert the total to minutes.


 If you happen to have some groups on the report, like for different locations, and the quota is separate for each group, include that first formula in the group header to reset the quota variables for each new group.  You would also need to change that formula to set TotalMinutes to 0.


 Also, what if the last records in the report are over the quota, and the count doesn't drop below that at the end?  Since that last count never drops below the quota, you won't get any time added for those transactions.  Do you want to try to handle that in some way?

 James
1
 

Author Comment

by:John-S Pretorius
ID: 41745459
The counter (Quota = 51) is actually a formula consisting of multiple {running Totals} 0 see below :-

{@OCC}
{#Entry}-{#Exit}+{@AOpenSPT} AOpenSPT is a subreport calculating a baseline counter of how many was present at the beginning of the month (parameter) and is carried into the main report.

Below image show the error I'm seeing :-

Error Message
0
 

Author Comment

by:John-S Pretorius
ID: 41745693
Also, I think I understand your comment :-

" Also, what if the last records in the report are over the quota, and the count doesn't drop below that at the end?  Since that last count never drops below the quota, you won't get any time added for those transactions.  Do you want to try to handle that in some way?"

an yes I would want to handle these in some way
0
 
LVL 34

Expert Comment

by:James0628
ID: 41745849
Ah, sorry about the error.  Both parts of an if-else have to produce (end with) the same type of value.  In this case, the first part (if not over_quota ...) produces a datetime (over_quota_start), but the Else (after "if over_quota ...") produces a number (TotalMinutes), so CR gives you an error saying that the Else needs to also produce a datetime.  The simple fix is to set over_quota last in both parts, so that they both produce a logical value:

if not over_quota and {#running total} > 51 then
// If we just went over the quota, set the flag and save the starting datetime
(
  over_quota_start := {EventTime field};
  over_quota := True
)
else
  if over_quota and {#running total} <= 51 then
// If we were over the quota and just dropped below again, clear the flag and
// add to the total "over quota" minutes
  (
    TotalMinutes := TotalMinutes + DateDiff ("n", over_quota_start, {EventTime field});
    over_quota := False
  );

Open in new window



 As for the case when you are still over the quota at the end of the report, what do you want to do?  Just use the datetime from the last record (even though you were still over the quota at that point)?

 James
0
 

Author Comment

by:John-S Pretorius
ID: 41745881
Let me work with the results a little, I will give some feedback shortly. Thank you so much James.
0
 

Author Comment

by:John-S Pretorius
ID: 41745883
I still seem to have a small error, see below :-

Error
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41745886
Reverse the statements in the THEN part.  I believe you need a ; before the )

mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 41745892
I get the 'A date-time is required here' again
0
 
LVL 34

Expert Comment

by:James0628
ID: 41745896
You somehow lost a couple of characters when you switched those lines.  The previous line is missing the ); at the end.  They were there in the formula that you posted earlier.

 Just an FYI ...

 When you have a series of statements in (), the last statement does not need to have a ; at the end.  It can have one, but it's not required.  I usually leave it out, but in my first version of that second formula, I was inconsistent about that.  I did not have a ; at the end of the first set of statements in (), but I did have one at the end of the second.  Sorry if that inconsistency caused any confusion.

 Edit:
 I hadn't seen your last post before I posted this.  Just to be clear, over_quota := False should be last.  You just need to add the ); back to the end of the previous line.

 James
1
 

Author Comment

by:John-S Pretorius
ID: 41746128
That worked thank you James, the issue I see now is that when triggered and over multiple pages it will only calculate the time triggered on the last page. If you look at the same report I attached for June 1st which triggers on 08:34AM (page 2) and staying triggered until 10:47PM (Page 4) - shows 348 minutes, it should actually show 853 minutes.

I do have a group on the EventTime set to 'each day', I have tried to move the formula the the group header with exact same results.
0
 

Author Comment

by:John-S Pretorius
ID: 41746188
It seems that a good approach with a daily group in place would be to close at the end of the day and calculate the total duration up to midnight should the trigger go into the next day. The duration should then start from 0 mins on the next day again.
0
 
LVL 34

Expert Comment

by:James0628
ID: 41746492
Just to make sure that I understand ...
 You have the report grouped by day, but if a day goes over the quota and that continues onto another page, the total over quota only includes the last page?

 I assume that you put the first formula in the daily group header, to reset the over_quota and TotalMinutes (?) variables for each day.

 Do you also have the group header set to repeat on each page?  If so, that's the problem.  When the group header is repeated on a new page, that also resets the variables, so the values from the previous page are lost.

 If that's the problem, there's an easy fix.  You can use InRepeatedGroupHeader to see if this is a new group header, or the last group header being repeated on a new page, and only reset the variables when it's a new group header.  Change the first formula to:

WhilePrintingRecords;
Global NumberVar TotalMinutes;
Global BooleanVar over_quota;
Global DateTimeVar over_quota_start;

if not InRepeatedGroupHeader then
(
  TotalMinutes := 0;
  over_quota := False
);

''

Open in new window


 James
0
 

Author Comment

by:John-S Pretorius
ID: 41746535
James, thank you for the efforts, I'm attaching the report with saved data - still having the same issue.
C--Users-John-S-Pretorius-Desktop-Du.rpt
0
 
LVL 34

Expert Comment

by:James0628
ID: 41746580
OK, I see an issue that I hadn't considered before.  I had thought about the possibility of being over the quota at the end of the report, but not at the end of a group, and I see that some groups are over at the end.

 But the question remains, how do you want to handle that?  I don't think you ever said.  The obvious idea would be to use the datetime from the last record in the group/report, but that's not completely accurate, since you were still over the quota at that point.  Is there some other time that you would like to use, like the end of the business day?  Or do you want to just use the last datetime, and accept that the total will be "incomplete" in those cases?

 If you want to just use the datetime from the last record in the group/report, try changing the CalcTotal formula as follows:

//-------------------------------------
//Calculate triggered quotas
WhilePrintingRecords;
Global NumberVar TotalMinutes;
Global BooleanVar over_quota;
Global DateTimeVar over_quota_start;

if not over_quota and {@OCC} > 51 then
// If we just went over the quota, set the flag and save the starting datetime
(
   over_quota_start := {DunhillCounts.DE_RevenueCombo_Time};
   over_quota := True
)
else
  if over_quota and
   ({@OCC} <= 51 or OnLastRecord or Date ({DunhillCounts.DE_RevenueCombo_Time}) <> Date (Next ({DunhillCounts.DE_RevenueCombo_Time}))) then
// If we were over the quota and just dropped below again, or this is the last
// record in a group, clear the flag and add to the total "over quota" minutes
  (
    TotalMinutes := TotalMinutes + DateDiff ("n", over_quota_start, {DunhillCounts.DE_RevenueCombo_Time});
    over_quota := False
  );

''



//--------------------------------------

Open in new window


 What I did was change the over_quota part to also recognize when this is the last record on the report (OnLastRecord), or the last record in a group (the date in the current record is not the same as the date in the next record).

 James
0
 

Author Comment

by:John-S Pretorius
ID: 41748117
Still seeing issues with group counts not reflecting end of each day.

As for handling groups 'over at the end' I would want to calculate to midnight.

Thank you for your efforts!!
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 41748166
Just change this line

    TotalMinutes := TotalMinutes + DateDiff ("n", over_quota_start,  {DunhillCounts.DE_RevenueCombo_Time});


to
if over_quota and   {@OCC} <= 51 then

    TotalMinutes := TotalMinutes + DateDiff ("n", over_quota_start,  {DunhillCounts.DE_RevenueCombo_Time});

ELSE
     TotalMinutes := TotalMinutes + DateDiff ("n", over_quota_start, DateTime(CurrentDate,Time(23,59,59));

Open in new window


mlmcc
0
 

Author Comment

by:John-S Pretorius
ID: 41748181
I already tried this formula in my attached post on Sunday, the group totals calculate correctly but the grand totals is completely off. seem to be close to a solution.
0
 
LVL 34

Expert Comment

by:James0628
ID: 41748290
Still seeing issues with group counts not reflecting end of each day.
Exactly what issues?  Are you saying that a group total is not including the last records when it's over the quota at the end of the group?  Or are you just talking about having the total include the time up to midnight?  I haven't tried to implement the latter (midnight), so that's to be expected.  But if it's the former, and some groups are not including some records in the total, can you give a specific example in the sample report that you posted?  I looked at a couple of groups and they seemed to be OK.

 Changing the total at the end of a group to include the time up to midnight is a bit more complicated than mlmcc's suggestion, but it should be easy enough.  But I want to get the "issues" straightened out before adding something new to the formula.


 Also, you mentioned grand totals.  The formulas that I posted are just for group totals.  If you want a grand total, you'll need another variable for that, and to change the "calc" formula, or add another formula, to update the new variable.

 James
0
 

Author Comment

by:John-S Pretorius
ID: 41749566
Good evening James, can we look at 1) changing the end of the group (grouped by day) to calculate up to midnight if the last transaction is still triggered past 51 and 2) add the 'Grand total' as mentioned to reflect all the daily totals combined.
0
 

Author Comment

by:John-S Pretorius
ID: 41749596
Just a note of observation, correct me if I'm wrong but I see the same triggered count on the beginning of a new day i.e. as we close the day and calculate to midnight we should also include duration from 00:00 on a new day if triggered.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 34

Expert Comment

by:James0628
ID: 41750705
You'll need a new variable for the grand total.  Add the line below to the DeclareVariables formula.

Global NumberVar GrandTotalMinutes;

 You do not want to set it to 0 (like you do with TotalMinutes).  You just want to declare it.  If you set it to 0, it will just be a group total, like TotalMinutes.

 I noticed that in the report that you posted earlier, DeclareVariables is only in the group header, not the report header.  It should be in both places.  You put it in the report header just to declare the variables at the start of the report.  It seems that global variables sometimes don't work properly if you don't do that.  Even if that's not true, there's no reason not to do it, so why not play it safe?

 The new version of CalcTotal below should add the time up to midnight to the total minutes.  If we were over the quota and now we're below, it uses the time from the current record as the end time.  If we were over the quota and we are _not_ below, but this is the last record in a group (or the entire report), then it uses midnight.  To get midnight (which is tomorrow), it extracts the date from DE_RevenueCombo_Time, adds 1 (to get tomorrow's date), and sets the time to midnight (Time (0, 0, 0)).

 For the grand total, it just performs the same operations on the new GrandTotalMinutes variable.

 Note that this new version of CalcTotal is based on the formula that I posted earlier.  If you made some changes to it, this version does not include those.

//-------------------------------------
//Calculate triggered quotas
WhilePrintingRecords;
Global NumberVar TotalMinutes;
Global NumberVar GrandTotalMinutes;
Global BooleanVar over_quota;
Global DateTimeVar over_quota_start;

if not over_quota and {@OCC} > 51 then
// If we just went over the quota, set the flag and save the starting datetime
(
   over_quota_start := {DunhillCounts.DE_RevenueCombo_Time};
   over_quota := True
)
else
  if over_quota then
// If we were over the quota, then ...
    if {@OCC} <= 51 then
//   If we just dropped below again, clear the flag and add to the total "over quota" minutes
    (
      TotalMinutes := TotalMinutes + DateDiff ("n", over_quota_start, {DunhillCounts.DE_RevenueCombo_Time});
      GrandTotalMinutes := GrandTotalMinutes + DateDiff ("n", over_quota_start, {DunhillCounts.DE_RevenueCombo_Time});
      over_quota := False
    )
    else
      if OnLastRecord or Date ({DunhillCounts.DE_RevenueCombo_Time}) <> Date (Next ({DunhillCounts.DE_RevenueCombo_Time})) then
//     If this is the last record in a group, clear the flag and add the time up to midnight
//     to the total "over quota" minutes
      (
        TotalMinutes := TotalMinutes +
         DateDiff ("n", over_quota_start, DateTime (Date ({DunhillCounts.DE_RevenueCombo_Time}) + 1, Time (0, 0, 0)));
        GrandTotalMinutes := GrandTotalMinutes +
         DateDiff ("n", over_quota_start, DateTime (Date ({DunhillCounts.DE_RevenueCombo_Time}) + 1, Time (0, 0, 0)));
        over_quota := False
      );
''


//--------------------------------------

Open in new window



 Then you'll need a new formula for the report footer, to output the value in GrandTotalMinutes (like the DisplayTime formula for TotalMinutes).


Just a note of observation, correct me if I'm wrong but I see the same triggered count on the beginning of a new day i.e. as we close the day and calculate to midnight we should also include duration from 00:00 on a new day if triggered.

 I'm not sure that I follow.  Are you saying that a day can _start_ over the quota?  I thought that the quota was completely separate for each day (ie. you always started a new day at 0, and counted up from there).

 If the "over quota" condition can run over multiple days, would it make more sense to ignore the day breaks and just keep adding time until you eventually dropped below the quota again (or at the end of the report, if you never dropped below) ?

 James
0
 

Author Comment

by:John-S Pretorius
ID: 41750875
There has been quite some work done here - thank you. With all the formulas being updated I was thinking of starting from scratch again and should have some feedback shortly.

What I meant by my observation note was specific to each daily total :- if a day ends triggered the total duration will be reflected up to midnight..... and..... should it flow over into the next day, the next day calculation (still triggered) should calculate it's duration from midnight until the trigger falls.

It's possible that you are already covering this and I'm not keeping up with you.
0
 
LVL 34

Expert Comment

by:James0628
ID: 41750906
Like I said, I thought that the quota was separate for each day.  So, the DeclareVariables formula sets over_quota to False (and TotalMinutes to 0), and, per my instructions, you put that formula in the group header.  That means that each day is seen as starting below the quota (because over_quota has been set to False).  When CalcTotal sees the first record that is over the quota in the new day, which I guess would be the first record for the day if the "over quota" condition continued from the previous day, then it will start a new "over quota" condition and use the time in that first record as the start time.

 If you want an "over quota" condition at the end of one day to continue into the next day, that will take some changes.  But, like I said, if that's the case, would it make more sense to just keep accumulating a total until you fell below the quota again, rather than creating a total for the first day that includes up to midnight, and then adding the time starting at midnight to the first total for the next day?  IOW, would it make more sense to get a total for all of that "over quota" time as one block, rather than splitting it up between different days?

 James
0
 

Author Comment

by:John-S Pretorius
ID: 41750911
I need to show a total for each day if possible at all.
0
 

Author Comment

by:John-S Pretorius
ID: 41751036
To stay current I cleaned up the report (please sea attached) - it seems that the Total and Grand Totals are working well. Above mentioned is apparent though and to reflect true totals possibly would require to close each day out until midnight if triggered and start a new day fresh from midnight if the trigger is carried over.  

Please have a look at Sunday the 31st as an example :

C--Users-John-S-Pretorius-Desktop-Du.rpt
0
 
LVL 34

Expert Comment

by:James0628
ID: 41751082
I was working on changes and a reply when you added your last post.  I think I've got things sorted out, so I'm going to go ahead and post it.  But I have not looked at the report in your latest post, so let me know if anything seems "off".

 Change the DeclareVariables formula as follows, so that it only declares the variables and doesn't set them, and remove it from the group header.  Put it in the report header only.  Technically, you don't have to remove the lines that set the variables.  It won't do any harm (as long as the formula is only in the report header).  But it's not necessary to set the variables there.  The default starting values will be 0 and False (which is also 0).  So, it's probably better to remove those lines, so that you don't end up looking at the formula later and wondering why those lines are there.

 New DeclareVariables formula, only in the report header ...
WhilePrintingRecords;
Global NumberVar TotalMinutes;
Global NumberVar GrandTotalMinutes;
Global BooleanVar over_quota;
Global DateTimeVar over_quota_start;

''

Open in new window



 Create a new formula (below) that will be put in the group header (replacing DeclareVariables).  It resets the total for each new group, and if the count is already over the quota, it sets over_quota back to True (it would have been set to False at the end of the previous group) and sets over_quota_start to midnight today (the date in the first record in the group).

 New formula for the group header ...
WhilePrintingRecords;
Global NumberVar TotalMinutes;
Global NumberVar GrandTotalMinutes;
Global BooleanVar over_quota;
Global DateTimeVar over_quota_start;

if not InRepeatedGroupHeader then
(
  TotalMinutes := 0;

// If a group (ie. day) is over the quota at the start, set over_quota to
// True and set the start time to midnight "today" (the date in the first
// record in the new group).

  if {@OCC} > 51 then
  (
    over_quota_start := DateTime (Date ({DunhillCounts.DE_RevenueCombo_Time}), Time (0, 0, 0));
    over_quota := True
  );
);

''

Open in new window



 I made those changes in the report that you posted earlier and checked some groups and the totals seemed correct.


 Another possible issue that occurred to me ...

 Is there data for every single day, or could some days be skipped (eg. weekends or holidays)?  If you do not have data for every day, what do you want to do if you were over the quota at the end of one day, and then there was no data for one or more days?  For example, if there is no data for the weekend and you were over the quota at the end of the day on Friday.  As it stands, the Friday total would include up to midnight Friday, and the next day (Monday or Tuesday or whenever) would include from midnight on that day.  Any days in between would not be counted.

 James
0
 

Author Comment

by:John-S Pretorius
ID: 41751299
MINOR....please refer to attached, I ran the parameter to only cover (3) days 07/29 - 07/31 and calculated manually.



07/29/2016

01:49 - 02:04       15
02:50-05:54         184
05:56-06:00         4
06:04-06:17         13
06:29-06:51         22
07:22-08:05         43
08:13-09:04         45

Manually calculated = 326 VS report ( 322)

07/30/2016

14:57-15:06         9
15:25-23:57        514 (since this is a 'to midnight close while triggered it should be 517 to midnight)

Manually calculated = 523 VS report (524)  :) :) :)

07/31/2016
12:23-08:45        502

Manually calculated  = 502 VS report (525)

TOTAL Manually calculated  : 1371 VS report (1371) :) :)

Thoughts.....?


So something else came up specific to the 'mean' count per day, should i open a new question on this or can I continue .....?

C--Users-John-S-Pretorius-Desktop-Du.rpt
0
 
LVL 34

Expert Comment

by:James0628
ID: 41751344
You've got a couple of mistakes there, and some things that are either mistakes, or I misunderstood what you want.

 07/29
 The second set ends at 5:44 PM, not 5:54, so that's 174 minutes, not 184.
 And 8:13 to 9:04 PM is 51 minutes, not 45 (not sure how you managed to get 45 there :-).


 07/30
 I really don't know what you're saying in your comment.  "517 to midnight"?  Is 517 a time of day or a number of minutes?  Either way, where did 517 come from?  IAC, since it was still over quota in the last record for the day, the time should include until midnight, correct?  3:25 PM to midnight is 8:35, or 515 minutes.  Plus the other 9 gives you 524, as shown on the report.


 07/31
 Since the day started over the quota, shouldn't the starting time be midnight?  You used 12:23 AM, the time in the first record.


 I don't know what to tell you about your last question, about the "'mean' count per day", since I really have no idea what you're talking about.  If it's some kind of adjustment to the totals that we've been working on, then we should probably just continue here.  If you think it's a separate issue, then I guess start a new question.

 James
1
 

Author Comment

by:John-S Pretorius
ID: 41751906
You are correct, I'm not sure where I pulled some of those values from...must have been when my wife introduced a glass a wine :)

What's been requested is to also count what I referred to as the 'mean' (max) quota count (should it be triggered for a day. The daily duration would be used to establish a $ amount to be used with the 'mean' / max Occupancy count for that day (see rate table example below) :-

Rate table example
1-   8Hrs  = $2,50
8- 12Hrs  = $5,00
12-24Hrs = $7,50
    >24Hrs=$10,00

07/29/2016 (5h22min) :- $2.50 * 63 = $157.50
07/30/2016 (8h44min) :- $5.00 * 71 = $355.00
07/31/2016 (8h45min):-  $5.00 * 60 = $300.00

Total                                      = $812.50
0
 
LVL 34

Expert Comment

by:James0628
ID: 41753110
Sure, blame it on the wife.  I'm sure she won't mind.  :-)

 First of all, I just noticed that in the report that you posted, you have CalcTotal in the group footer.  It should not be there.  CalcTotal is in the detail section, so it's evaluated for every record.  It doesn't need to be in the group footer, and including it there forces the formula to be evaluated an extra time for the last record in the group.  In this particular case that may not be doing any harm, because of the tests in your formulas, but in other cases doing that would corrupt your results (eg. inflate a total by adding the last record in the group twice).  I've seen people post with incorrect results from a formula, and it was because they had included it in an "extra" section, forcing it to be evaluated an extra time.  If CalcTotal is still in the group footer, remove it.

 Moving on ...

 It took me a while to figure it all out, but I think I get it now.

 First, you included ">24Hrs=$10,00" in the rates.  If you're looking at the "over quota" time, and that is separate for each day, how are you going to get more than 24 hours?  Are you using some of them extra long days?  :-)  Maybe you're in the Arctic circle, where the night or "day" can last for days?  :-)

 Seriously, I would assume that you included ">24" for a reason, but it's presumably not possible, unless I misunderstood something.


 Where will those rates come from?  Are they fixed, or in a table?  If they're in a table, the simplest thing might be to use a subreport to read the rates from the table and store them in shared variables.  (They would need to be shared variables, as opposed to global, because global variables in a subreport will not be visible outside of that subreport)

 Do you want to show something for each day, or just a total figure at the end of the report?
 If you want to show something for each day, do you want to show that at the end of each day (ie. in the group footer), or do you want to show all of the daily figures together at the end of the report (like the lines in your post)?
 Do you want to show lines with calculations similar to the ones that you posted, or just the $ result (eg. $157.50)?


 FYI, I've done some testing, using the rates that you posted, and I'm getting the results that you posted.  So, the calculations, etc. seem to be working.  It's just a question of where the actual rates will come from, and how you want the results presented.

 James
0
 

Author Comment

by:John-S Pretorius
ID: 41753160
The rate table I posted was just an example, I was envisioning a simple formula that will be used only in the footer (I will suppress the details and just have a summary as a thought) so I would see the below structure (see snap) with the calculation for the $amount based on duration * peak/max/mean quota for that day. I would want to see the peak count and also the revenue amount for each day and also the total revenue for the end of the month.


Sample
0
 
LVL 34

Expert Comment

by:James0628
ID: 41753172
But where will the rates come from?  Do they need to be read from a table, or are they fixed and you'll just plug them into the formulas in the report?

 When you say "a simple formula that will be used only in the footer", do you mean the group footer or the report footer?  If you want to show all of that in the report footer, you'll have to save the figures for each day somewhere (or use a subreport).

 James
0
 

Author Comment

by:John-S Pretorius
ID: 41753182
The rate table will be fixed, we can use the example from this morning.

that 'simple formula' :) will need to be in the group footer, the report footer will need to show the total of all the $amounts and peak counts from each month totaled.
0
 

Author Comment

by:John-S Pretorius
ID: 41753195
each day totalled, I was meant to say.
0
 
LVL 34

Accepted Solution

by:
James0628 earned 450 total points
ID: 41753305
I'm attaching a version of your report with the new calculations.  The figures seem correct, so my main question at this point is about the "presentation".  Let me know if you want to change something.

 Here's a list of the changes and additions (assuming that I don't forget anything).

 Init_Rates

 A new formula in the report header that creates and fills two array variables with the rate information.

 rate_cutoffs has the end of each range (1 hour, 8 hours, 12 and 24).
 rates has, well, the rates.  :-)

 One question:
 How do the ranges work, exactly?  For example, you said that the first two were 1 - 8 and 8 - 12, but obviously you don't want to include 8 in both ranges.  So, does each range include the starting value or the ending value?  You used >24 for your last entry, which implies that the previous entry includes 24 (the ending value).  That would make the ranges:

 <=1
 >1 to 8
 >8 to 12
 >12 to 24
 >24 (not actually possible)

 That's what I used, but if you want to include the starting values instead (eg. 1 to <8), that's a simple change.


 DeclareVariables RH

 I added 3 new variables.

 MaxOCC is the maximum OCC for a group/day.
 GTMaxOCC is the grand total of the MaxOCC values.
 GT_OQ_Charge is the grand total of the charges (MaxOCC * rate) for each group/day.  "OQ" is short for "Over Quota".


 DeclareVariables GH

 MaxOCC is reset for each new group/day (just like TotalMinutes).


 OCC

 If the current OCC is > MaxOCC, then MaxOCC is set to that value.


 DisplayMaxOCC

 A new formula in the group footer that displays the MaxOCC for the group/day, along with the rate and the charge (MaxOCC * rate).  You could put those 3 things in separate formulas.  I just decided to combine them for the heck of it.  It converts the numbers to strings and combines them, producing a string like "$2.50 * 63 = $157.50".

 It uses a For loop to go through the rate_cutoffs array variable until TotalMinutes / 60 is <= the cutoff point (eg. 8), and that tells it which entry in the rates variable to use for the calculation.  That rate is put in tmp_rate, which is then used to calculate the charge.  If no matching rate is found (eg. the over quota hours is <= 1), then tmp_rate is 0.

 It just occurred to me that you might want to change the formula so that nothing is displayed if there is no charge.  We could do that easily enough.

 This formula also adds the maximum OCC for the group/day (MaxOCC) to the grand total (GTMaxOCC), and adds the charge for the group/day to the grand total (GT_OQ_Charge).


 DisplayGTMaxOCC

 Displays GTMaxOCC in the report footer.


 DisplayGT_OQ_Charge

 Displays GT_OQ_Charge in the report footer.


 I think that's it.  Let me know if you have any questions.

 James

C--Users-John-S-Pretorius-Desktop-Du.rpt
0
 

Author Comment

by:John-S Pretorius
ID: 41753644
James, this is perfect you are also a master SIR!!

Please take one last look at the attached, this will be my presentation summary format, I had to groom some of the group footer values and hope they will not cause corrupt values in this specific reporting format.

C--Users-John-S-Pretorius-Desktop-De.rpt
0
 

Author Comment

by:John-S Pretorius
ID: 41753670
I see some weird calcs on your original report when pulling a full month, please have a look at June 5th total on page 10. There are also a few other 0 values that should have something.

C--Users-John-S-Pretorius-Desktop-C-.rpt
0
 
LVL 34

Expert Comment

by:James0628
ID: 41754145
First of all, you had CalcTotal in the detail section twice.  I don't think that's actually a problem, because a formula that's repeated in the same section seems to only be evaluated once, so you shouldn't get figures added twice or anything like that.  But there's also no reason to include it twice, and it could cause confusion down the line, so you should delete one of them.


 You said "June 5th".  I assume that you meant July 5th.

 What's wrong with it?  The total time seems correct.  If you're talking about the $0 charge, anything up to an hour has a 0 rate.  When you posted your rate table, the first range was 1 - 8, implying that the rate for anything less was 0.  So that's what I used in the rates variable.

 If that wasn't correct, what's the rate for the first hour?  Is it the same as 1 - 8 (so the range is really 0 - 8), or is there a different rate for the first hour?

 And if there are other issues with other days, can you give me some specific examples?  I didn't feel like checking the whole month for unspecified problems.  :-)

 James
0
 

Author Comment

by:John-S Pretorius
ID: 41754180
the key is when the MaxOcc Quota is past 51 there should also be a charge, and you are correct the issue is with the actual rate not defined for a 0-1hr amount = $1,50. Sounds like an easy fix.
0
 

Author Comment

by:John-S Pretorius
ID: 41754221
I added below :-

//1-   8Hrs  = $2,50
//8- 12Hrs  = $5,00
//12-24Hrs = $7,50
//    >24Hrs=$10,00

WhilePrintingRecords;

Global NumberVar Array rate_cutoffs;
Global NumberVar Array rates;

Redim rate_cutoffs [ 5 ];
Redim rates [ 5 ];

rate_cutoffs [ 1 ] := 0;
rates [ 1 ] := 0;
rate_cutoffs [ 2 ] := 1;
rates [ 2 ] := 1.5;
rate_cutoffs [ 3 ] := 8;
rates [ 3 ] := 2.50;
rate_cutoffs [ 4 ] := 12;
rates [ 4 ] := 5.00;
rate_cutoffs [ 5 ] := 24;
rates [ 5 ] := 7.50;

''
0
 

Author Closing Comment

by:John-S Pretorius
ID: 41754227
Great work and a wonderful solution.
0
 
LVL 34

Expert Comment

by:James0628
ID: 41754265
For the record, your change to the rate arrays looks good.

 Glad we finally got it all sorted out.  :-)

 James
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now