Report Footer showin top Vaule 1

[mezcalbean]

Hi, I am trying to show four locations running late or on time. in detail, I am evaluating the time

if ({Location} = '1' and {#RTotal0} = 2 and {@Time Vs APP} <= -9 and {@Time Vs APP} <= -29) then 'Running a little behind ' else
if ({Location} = '1' and {#RTotal0} = 2 and {@Time Vs APP} <= -30 ) then 'Running Behind ' else
if ({Location} = '1' and {#RTotal0} = 2 and {@Time Vs APP} <= -45 ) then 'Please Check with Staff ' else 'On Time'

I have placed 4 separate formulas evaluating each location, in the evaluation I'm only looking at a running total id of 2

{#RTotal0} = 2

The idea is to show in the report footer what the running total of id 2 is but I'm only getting the result of id 1 as its first any ideas how to the status of {#RTotal0} = 2 only?
Thanks

 

[pmax9999]

I'm having a bit of trouble understanding what you are trying to achieve here, but one thing I would question is the 1st line of the formula that you are displaying at the bottom of your post.

The formula includes "... {@Time Vs APP} <= -9 and {@Time Vs APP} <= -29 ..." which doesn't make sense. If the formula result is less than -29, it will also always be less than -9. If you are trying to test for results between -9 and -29, the formula should be "... {@Time Vs APP} <= -9 and {@Time Vs APP} [highlight #FCE94F]>=[/highlight] -29 ...".

Not sure if this helps with the issue you raise or not.

If it isn't, please explain in more detail what you are trying to achieve and what result you are getting.

As a general rule when showing formula code, you should also include the code from any formulas referred to within them.

Cheers
Pete

 

[mezcalbean]

Hi Pete thank you for the reply back, I really appreciate it.

[pre]The formula includes "... {@Time Vs APP} <= -9 and {@Time Vs APP} <= -29 ..." which doesn't make sense. If the formula result is less than -29, it will also always be less than -9. If you are trying to test for results between -9 and -29, the formula should be "... {@Time Vs APP} <= -9 and {@Time Vs APP} >= -29 ...".[/pre]

yes I realised that after I posted, thank you.

What I am trying to do is see what locations are running late or not, and again after I posted I realised that it's evaluating every line so I'm not getting a summary for the one location, where I located it on the report footer basically it's the last one that is correct. I have then moved it to group footer sit evaluating each section.

What I am trying to achieve and to evaluate is running a total of 2 - I don't care about 1 as that's irrelevant I'm looking at person 2.

one formula which would be relevant would be [pre]DateDiff("n",CDateTime(date({@current time}),Time({@current time})),CDateTime(Date({vw_QueBro.App_DtTm}),Time({vw_QueBro.App_DtTm})))[/pre]
is where Im caulating the difference between appointment time and current time.

I have added an image

Thank you

 

[pmax9999]

Hi mezcalbean.

I'd like to try and help - it is just hard to understand the issue. The resolution of the report image posted is poor, and there is no way of identifying the fields / formulas / running totals. It is also impossible to understand the report layout, ie which sections the various report components are occupying.

I'd suggest you explain in as much detail as you can exactly what you are trying to achieve, include some real sample data, and the result you are getting. It may just be simpler to attach the report file (with saved data), assuming you can satisfactorily de-identify or remove any confidential the data.

Regards

Pete

 

[mezcalbean]

Hi Pete, I have added the report with saved data

We have 4 locations and trying to see when locations for customers are running on time or running late.

My idea was to look at the second ID so I have a running total for each location which is my first problem.

I was using the report footer to sum it all up which it won't work I just want to know each location is it on time or running late without have to have a separate report for each location

Regards

Philippe

Link

 

[pmax9999]

Hey Phillipe.

I still have a few questions:

1. Please explain what you mean when you refer to the "Second ID".

2. Where the location has more than 1 record, how is the result determined, ie is it the worst result (based on time), or is it based on some other logic. For example, Jarrah has 3 records, 2 (1st and 3rd) of which are "On Time" and 1 is "Running a little behind". Is it "Running a little behind" because that is the worst of the 3, or is it "On Time", and if so, why?

3. You say you want to see results in the Report Footer. Does that mean you want the results currently showing in the Group footer summarised in the Report Footer ie, something like:

Acacia is Running a little behind and has 2 queued;​

Banksia is On Time and has 1 queued;​

Jarrah is On Time and has 3 queued;​

Tuart is On Time and has 1 queued​

4. If these are not the results you expect to see, please explain exactly what the end result should be (based on the data currently being displayed in the report.

Once I get your response to these questions, I'd expect to be in a position to give you the answers you are looking for.

Regards

Pete

 

[pmax9999]

I kept going with this, hoping my understanding of what you want is correct.

Create the following two formulas:

[Code {@var_Result_(SET)}]
WhilePrintingRecords;
Global StringVar RESULT;
RESULT := RESULT +
GroupName ({vw_QueBro.SchLocation}) + ' is ' +
(
If DateDiff('n', Minimum({vw_QueBro.App_DtTm}, {vw_QueBro.SchLocation}), CurrentDateTime) > 45
Then 'running late - please check with staff'
Else
If DateDiff('n', Minimum({vw_QueBro.App_DtTm}, {vw_QueBro.SchLocation}), CurrentDateTime) > 30
Then 'running behind'
Else
If DateDiff('n', Minimum({vw_QueBro.App_DtTm}, {vw_QueBro.SchLocation}), CurrentDateTime) > 9
Then 'running a little behind'
Else 'on time'
) +
' and has ' + ToText(Count({vw_QueBro.SchLocation}, {vw_QueBro.SchLocation}),'#') + ' queued' + CHR(10)
;
''
[/Code]
and

[Code {@var_Result_(DISPLAY)}]
WhilePrintingRecords;
Global StringVar RESULT;
[/Code]

Place the 1st Formula in the Group Footer, and the 2nd Formula in the Report Footer, and format the 2nd formula to "Can Grow".

This should then give you the summary you want (or at least, what I think you want) in the report footer.

Let me know how it goes. If it is not what you are looking for, please provide as much detail as possible.

Hope it helps.

Cheers

Pete

 

[mezcalbean]

Hi Peter thank you for the information

With your first question not answered.

1. With the running totals I want to look at the second ID for each location

so with this screenshot

acacia is on-time
banksia is on-time
jarrah is on time - I only want to evaluate the second running total - is this on time or running late? The first I don't care about really as they are there its the second person and thereafter I'm worried about.

2. Im calculating a current time and appointment time to see how late it's running really again for the second running total only as if it's more >2 then they're all in the same boat.

3 The idea with the below screenshot with Tuart is the second running total is all Im looking for - app time is 9:45 current time is 9:53 then a negative time of -8 minutes ( I have adjusted so it looks before -5 but anything after -15,-30 and -45 etc

And wanting the results for each location in the footer so I can run vis CR server and have the results run say every 5 minutes so everyone knows how everyone is running ( everything in Italics is another story )

The report I have added and thank you for the formulas doesn't seem to be working what I had envisioned hopefully with the above information I may be able to get more guidance with the end result? I have added the report with your formulas a little bit cleaner than the other Link

Thank you

Philippe

 

[pmax9999]

OK, I think I am starting to understand what you want. I am a little confused by the different time calculations, formulas and running totals referred to within them though.

I have replicated the time criteria as used in your 4 separate formulas but consolidated them into a single assessment. You should check the code within the 1st formula carefully to ensure the time component is being calculated as required.

Firstly, your implementation of my previous suggestion did not show results because the two formulas were in the wrong sections, and the formula in the Page Footer had not been changed to 'Can Grow' (but I do accept it wasn't the result you were looking for).

Please try create the following formulas:

[Code {@Timing}]
WhilePrintingRecords;
Global StringVar T ;

If GroupName ({vw_QueBro.SchLocation}) = 'ACACIA'
Then If {#RTotal0} = 2
Then If {@Time Vs APP} <= -9
Then T := 'is running a little behind '
Else
If {@Time Vs APP} <= -30
Then T := 'is running behind '
Else
If {@Time Vs APP} <= -45
Then T := 'Please Check with Staff '
Else T := 'is on time'
Else T := T
Else
If GroupName ({vw_QueBro.SchLocation}) = 'BANKSIA'
Then If {#Banksia} = 2
Then If {@Time Vs APP} <= -15
Then T := 'is running a little behind '
Else
If {@Time Vs APP} <= -30
Then T := 'is running behind '
Else
If {@Time Vs APP} <= -45
Then T := 'Please Check with Staff '
Else T := 'is on time'
Else T := T
Else
If GroupName ({vw_QueBro.SchLocation}) = 'JARRAH'
Then If {#RTotal0} = 2
Then If {@Time Vs APP} <= -15
Then T := 'is running a little behind '
Else
If {@Time Vs APP} <= -30
Then T := 'is running behind '
Else
If {@Time Vs APP} <= -45
Then T := 'Please Check with Staff '
Else T := 'is on time'
Else T := T
Else
If GroupName ({vw_QueBro.SchLocation}) = 'TUART'
Then If {#TUART} = 2
Then If {@Time Vs APP} <= -4
Then T := 'is running a little behind '
Else
If {@Time Vs APP} <= -30
Then T := 'is running behind '
Else
If {@Time Vs APP} <= -45
Then T := 'Please Check with Staff '
Else T := 'is on time'
Else T := T
Else T := T
[/Code]
, and place the formula in the Details section. This formula can be suppressed.

[Code {@Result}]
WhilePrintingRecords;
Global StringVar R;
Global StringVar T;

R := R + GroupName ({vw_QueBro.SchLocation}) + ' ' + T + 'with ' + ToText(Count({vw_QueBro.App_DtTm}, {vw_QueBro.SchLocation}), '#') + ' queued' + CHR(10);
[/Code]
, and place the formula in the Group Footer section (suppressed) and the Report Footer section.

Format the formula in the Report Footer section to "Can Grow".

Hopefully this is getting close.

It may be the 1st of these formulas can be simplified but I did it this way because it provided flexibility to have different criteria for each Location.

Let me know how it goes.

Cheers, Pete

 

[mezcalbean]

Hi Pete,

I have added the report with the new formulas and apologies for not following your guide properly it's all me. This is exactly what I was after, thank you

With the attached report I have deleted other information not needed as I was experimenting as I went.

I need to look and add to the result if there is no one here to say "on time" that is none has been queued. I seem to be getting a double of Tuart with the result as well.

With the actual result, how do I make it that the locations are separated when exported and not one cell? I need this to separate so I can display the values.

Link

Regards

I have updated the running totals to point to #RTotal0

 

[pmax9999]

Hi Phillipe.

Apologies for the duplicated line for TUART. To fix that, remove the {@Result} formula from the Report Footer section (leaving in in the Group Footer section), and create an additional formula like the following to place in the Report Footer:

[Code {@Display}]
WhilePrintingRecords;
Global StringVar R;
R
[/Code]

The file you attached in your latest post looks to me to be the same file as previously provided. It would be helpful if you could provide the latest version for me to look at, particularly as it relates to your Running Total(s). The {@Timing} formula provided previously will need to be amended so that the results go to 4 different Variables to make it easier to extract separately.

Please also explain in more detail what you want when there is no-one in the queue

Regards
Pete

 

[pmax9999]

Assuming the answer to my final question is that you just want the result for the location to show as "Nobody waiting" or similar, and simply leaving the rest of the calculations as they are in my earlier solution (ie, without knowing what other changes you made to the running totals etc), my solution is as follows:

New formula - place in Report Header
[Code {@Variables_Set}]
WhilePrintingRecords;
Global StringVar Ra := 'Acacia has no-one waiting';
Global StringVar Rb := 'Bansia has no-one waiting';
Global StringVar Rj := 'Jarrah has no-one waiting';
Global StringVar Rt := 'Tuart has no-one waiting';
''
[/Code]

Amend {@Timing} formula - place in Group Footer
[Code {@Timing}]
WhilePrintingRecords;
Global StringVar A ;
Global StringVar B ;
Global StringVar J ;
Global StringVar T ;
Global StringVar Z ;

If GroupName ({vw_QueBro.SchLocation}) = 'ACACIA'
Then If {#RTotal0} = 2
Then If {@Time Vs APP} <= -9
Then A := 'is running a little behind '
Else
If {@Time Vs APP} <= -30
Then A := 'is running behind '
Else
If {@Time Vs APP} <= -45
Then A := 'Please Check with Staff '
Else A := 'is on time'
Else A := A;

If GroupName ({vw_QueBro.SchLocation}) = 'BANKSIA'
Then If {#Banksia} = 2
Then If {@Time Vs APP} <= -15
Then B := 'is running a little behind '
Else
If {@Time Vs APP} <= -30
Then B := 'is running behind '
Else
If {@Time Vs APP} <= -45
Then B := 'Please Check with Staff '
Else B := 'is on time'
Else B := B;

If GroupName ({vw_QueBro.SchLocation}) = 'JARRAH'
Then If {#RTotal0} = 2
Then If {@Time Vs APP} <= -15
Then J := 'is running a little behind '
Else
If {@Time Vs APP} <= -30
Then J := 'is running behind '
Else
If {@Time Vs APP} <= -45
Then J := 'Please Check with Staff '
Else J := 'is on time'
Else J := J;

If GroupName ({vw_QueBro.SchLocation}) = 'TUART'
Then If {#TUART} = 2
Then If {@Time Vs APP} <= -4
Then T := 'is running a little behind '
Else
If {@Time Vs APP} <= -30
Then T := 'is running behind '
Else
If {@Time Vs APP} <= -45
Then T := 'Please Check with Staff '
Else T := 'is on time'
Else T := T;
''
[/Code]

Amend {@Result} formula - place in Group Footer
[Code {@Result}]
WhilePrintingRecords;
Global StringVar A;
Global StringVar Ra;
Global StringVar B;
Global StringVar Rb;
Global StringVar J;
Global StringVar Rj;
Global StringVar T;
Global StringVar Rt;

If GroupName ({vw_QueBro.SchLocation}) = 'Acacia'
Then Ra := GroupName ({vw_QueBro.SchLocation}) + ' ' + A + 'with ' + ToText(Count({vw_QueBro.App_DtTm}, {vw_QueBro.SchLocation}), '#') + ' queued'
Else Ra := Ra;

If GroupName ({vw_QueBro.SchLocation}) = 'Banksia'
Then Rb := GroupName ({vw_QueBro.SchLocation}) + ' ' + B + 'with ' + ToText(Count({vw_QueBro.App_DtTm}, {vw_QueBro.SchLocation}), '#') + ' queued'
Else Rb := Rb;

If GroupName ({vw_QueBro.SchLocation}) = 'Jarrah'
Then Rj := GroupName ({vw_QueBro.SchLocation}) + ' ' + J + 'with ' + ToText(Count({vw_QueBro.App_DtTm}, {vw_QueBro.SchLocation}), '#') + ' queued'
Else Rj := Rj;

If GroupName ({vw_QueBro.SchLocation}) = 'Tuart'
Then Rt := GroupName ({vw_QueBro.SchLocation}) + ' ' + T + 'with ' + ToText(Count({vw_QueBro.App_DtTm}, {vw_QueBro.SchLocation}), '#') + ' queued'
Else Rt := Rt;

''
[/Code]

Create the following 4 formulas - place in Report Footer
[Code {Display_A}]
WhilePrintingRecords;
Global StringVar Ra;
Ra
[/Code]
[Code {Display_B}]
WhilePrintingRecords;
Global StringVar Rb;
Rb
[/Code]
[Code {Display_J}]
WhilePrintingRecords;
Global StringVar Rj;
Rj
[/Code]
[Code {Display_T}]
WhilePrintingRecords;
Global StringVar Rt;
Rt
[/Code]


Regards
Pete

 

[pmax9999]

Hi mezcalbean

Its been a while since I posted the last solution for testing.

I actually invested a bit of time and effort into understanding your situation, what you were trying to achieve and developing a solution that might work, so it would be great if you could give us an update as to the outcome.

It's also useful for anyone with a similar issue in the future if they can see that a proposed solution worked or not.

Regards
Pete.

 

[mezcalbean]

Hi Pete,

I really appreciate the work undertaken, I have had to take leave and won't be back until Monday, please bear with me then and can carry on the conversation if this suits you (I am not at work not and had to travel). I do want to add to this conversation with some extra questions on how you came up with some of your work to help others just like me

Regards

Philippe

 

[pmax9999]

Sure Philippe, happy to help further. Let me know when you're ready.

 

[mezcalbean]

Hi Pete, again thank you for your help.

I have attached the report with the changes you have made and the results are great that is the the separation of the results in the group footer. With the results, I was hoping for when Acacia for example has 2 queued it could also say "is on time"

If the running total is only 1 then "on time" with 1 queued or if there is a running total of 2 or more and 2 is not matching the criteria that is <-14 etc is on time for example

 If      {@Time Vs APP} <= -15 
                Then    A := 'is running a little behind ' 
                Else 
                If      {@Time Vs APP} <= -30 
                Then    A := 'is running behind ' 
                Else    
                If      {@Time Vs APP} <= -45 
                Then    A := 'Please Check with Staff ' 
                Else    A := 'is on time'

I have changed the running totals to match the respective locations instead of one being generic like running total 2

If      GroupName ({vw_QueBro.SchLocation}) = 'JARRAH' 
Then    If      [s]{#RTotal0}[/s]{#Jarrah} = 2 
        Then    If      {@Time Vs APP} <= -15  
                Then    J := 'is running a little behind ' 
                Else    
                If      {@Time Vs APP} <= -30
                Then    J := 'is running behind ' 
                Else
                If      {@Time Vs APP} <= -45 
                Then    J := 'Please Check with Staff ' 
                Else    J := 'is on time'
        Else    J := J;

While typing this reposnse out I could see Acacia was -7 behind so I changed to point to -6 so I would expect the [CODE --> {Display_A}][/code] to be different?

If      GroupName ({vw_QueBro.SchLocation}) = 'ACACIA'
Then    If      {#Acacia} = 2 
        Then    If      {@Time Vs APP} <= -6 
                Then    A := 'is running a little behind '

Thanks you Philippe
Link

 

[pmax9999]

Hi Phillipe

One of the challenges for me is that the {@Time Vs APP} calculation uses the Current Date/Time so the minutes calculations are hugely inflated when I see the results some hours after the report was run and saved. The Timing assessments are therefore way off when I see them.

The issue with the result showing for Acacia is that my instructions weren't correct. Apologies! Please move the (@Timing) formula to the Details section.

The issue with the results for Banksia and Tuart is that they both only have 1 record each, where the formulas rely on the 2nd record to determine the timing.

Obviously we need to code for the event that a location has only 1 record - if you can let me know what you want it to display I will help you achieve it.

Hope all this helps.

Cheers
Pete

 

[mezcalbean]

Hi Pete yes completely agree with timing much easier for me .

I have moved @timing to the details section and is working perfectly, thank you!

My theory is if only one person was queued there are on time so with the below image "banksia" when only one is queued basically is also on time, so when only one is queued it's also on time?

 

[pmax9999]

Excellent news. Glad I could help you to get it working.

Cheers, Pete.

 

[mezcalbean]

One last question

with the below code "Else A := ' is on time'" does not seem to be working as its coming up blank?

 Then    If      {#Acacia} = 2 
        Then    If      {@Time Vs APP} <= -1 
                Then    A := 'is running a little behind ' 
                Else 
                If      {@Time Vs APP} <= -30 
                Then    A := 'is running behind ' 
                Else    
                If      {@Time Vs APP} <= -45 
                Then    A := 'Please Check with Staff ' 
               [COLOR=#CC0000] Else    A := ' is on time'[/color]
        Else    A := A;

Can I ask you some questions on what you have done so I could try and learn?

Thanks