Parameters and Formula's

[bj1]

i have a parameter called MinVisitLength
and a formula field called VisitDuration.
VisitDuration is the amount of time that is spent in one place - this is in hours and minutes.
What i need to do is to not include the MinVisitLength value in the report.
for example if the MinVisitLength = 1 min, then the report should not contain any visits <= 1 min.

How do i do this?

Thanks,
B

 

[GJParker]

@VisitDuration <= ?MinVisitLength

Place this in the selection formula

HTH

Gary Parker
Systems Support Analyst
Manchester, England

 

[bj1]

I don't seem to be able to do this because the @VisitDuration isn't in the selection formula list.
Why would it not be in there?

Thanks,
B

 

[lbass]

Please provide the contents of {@visitduration}. If you are using previous or next functions or summaries, it will not appear for use in the record selection formula. However, it might be available for use in the group selection formula section, in which case you could add the formula there:

{@VisitDuration} > {?MinVisitLength}

However, because records not included in the group select are still in the report (though not displayed), you need to use running totals for additional calculations instead of the more usual summaries, like &quot;count,&quot; so that your summaries are not inflated.

-LB

 

[bj1]

Here is the contents of VisitDuration:
@VisitDuration is available in the group selection - do i need to add the formula there?


Shared numbervar TotalBreaks;

local Stringvar HoursText;
local StringVar MinsText;
local StringVar SecsText;
local numbervar nHours;
local numbervar nMinutes;
local numbervar nSeconds;

if {@ExcessBreakHours} > 0 then
TotalBreaks:=TotalBreaks+{@ExcessBreakHours};

nHours:=truncate({@ExcessBreakHours},0);
nMinutes:=truncate(({@ExcessBreakHours} - nHours) * 60,0);
nSeconds:=truncate(({@ExcessBreakHours} - nHours - nMinutes) *60 *60,0);

if nSeconds>=30 then
nMinutes:=nMinutes+1;

if nMinutes > 60 then
(
nMinutes:=0;
nHours:=nHours+1;
);

HoursText:=ToText(nHours,0);
if nHours < 10 then
HoursText:='0' + HoursText;

MinsText:=ToText(nMinutes,0);
if nMinutes<10 then
MinsText:='0' + MinsText;

HoursText+':'+MinsText;

 

[lbass]

Just put the following in the group selection area:

{@VisitDuration} > {?MinVisitLength}

This will work fine as long as you have {?MinVisitLength} set up as a string in a format that corresponds to the format of your variable results: &quot;HH:MM&quot;

-LB

 

[bj1]

i tried this:

{@BreakDuration} > {@DisplayVisitMinimum}

where {@DisplayVisitMinimum} is

ToText({?MinVisitLength},0) + &quot; min&quot; + iif({?MinVisitLength}<>1,&quot;s&quot;,&quot;&quot

and it came up with this error:
'This formula cannot be used because it must be evaluated later'

Any ideas???

 

[lbass]

Please note that the results of your formula {@visitduration} are in this format &quot;HH:MM&quot;, or for an example, 02:15. Your formula above does not return that format. Assuming that {?minvisitlength} is provided as a number with two decimals, e.g., 1.75, the following should work:

{@visitduration} >= totext(truncate({?minvisitlength}),&quot;00&quot&&quot;:&quot;& totext(val(right(totext({?minvisitlength},2),3))*60,0)

Why have you changed the formula name to {@breakduration}? Anyway, assuming that the formula you provided is the one you mean (with the formula results in that format) this should work. Please be sure you are entering the formula in the correct area--go to report->edit selection formula->GROUP (not record). Also, just add in the formula above--do not create a new formula for the second part of the formula--sometimes doing that requires an additional pass which Crystal doesn't tolerate, while using the formula &quot;in the raw&quot; works.

-LB

 

[bj1]

i tried the above suggestion - but it asked for a number???

this is what i have tried and works for some cases and not others.

Local numbervar ONE_MIN := 1 / (60 * 24);
if {@VisitLength} < ONE_MIN * {?Visitmin} then //suppress visit length less than minimum
true

the only time it doesn't seem to work is when the visitduration is exactly 2mins and the minvisitlength is 2.
I want to be able to see the visitduration of 2 mins - but it is taking them out.

Any suggestions?

 

[lbass]

Please review your posts and notice that you keep changing the names of your formulas and parameters--this is very confusing. You also have switched to suppression of results instead of using the group select?

I don't know why your last formula would work since {@visitduration} results in a string.

My last post assumed that {?minvisitlength} was a number with two decimals. If that is true it should work in the group select (or you could change the sign and use in suppression). Where exactly was the cursor when it asked for a number? Did you copy and paste the formula so that it was exact?

Please provide specific feedback and more information for further help.

-LB

 

[bj1]

this is what i am puting in the suppress section (it didn't work in the group select):

Whileprintingrecords;
if {@VisitLength} <= totext(truncate({?VisitMinimum}),&quot;00&quot&&quot;:&quot;& totext(val(right(totext({?VisitMinimum},2),3))*60,0) then
true

and it says 'a number is required here' and the cursor is at the start of totext.

i am using @VisitLength to calculate the length and am also using @DisplayVisitLength (which is what appears on the report.
VisitDuration - which is shown above - is now called DisplayVisitLength.

The parameter - ?VisitMinimum - is of type Number.

Hopefully this makes a bitmore sense... sorry for the confusion.

 

[lbass]

Well, that explains why the formula didn't work--you substituted a number field {@VisitLength} for what had been a string field now called {@DisplayVisitLength}. If {@DisplayVisitLength} is the same formula as {@visitduration}, just renamed, then try this in my last formula. Otherwise, please provide the contents of the formula {@VisitLength}--can't help you get this to work without knowing the exact result you are getting. You also need to tell me what users are entering for {?VisitMinimum}--providing some samples would help. The formula will not work correctly unless it is in the exact format of the user entry and I assumed it was formatted like: 1.75 for one hour and 45 minutes. Or are they entering only an integer like 1, 2 etc., and does it refer to minutes only?

-LB

 

[bj1]

if i use this formula in the suppress section:

Whileprintingrecords;
if {@DisplayVisitLength} <= totext(truncate({?VisitMinimum}),&quot;00&quot&&quot;:&quot;& totext(val(right(totext({?VisitMinimum},2),3))*60,0) then
true

it says 'a time is required here' - the cursor is in front of totext.

@VisitLength :

whileprintingrecords;
if {@VisitIn2Shifts} then//subtract the time between shifts
({@StartTime}-{@StopTime})-(datetime(date(0,0,0),{?Shift Start Time})-datetime(date(0,0,0),{?Shift End Time}))
else
{@StartTime}-{@StopTime}


@StartTime :

whileprintingrecords;
global datetimevar StartTime;


@StopTime :

whileprintingrecords;
global datetimevar StopTime;


@VisitIn2Shifts :

whileprintingrecords;
evaluateafter({@SetStartTime});
evaluateafter({@SetStopTime});
global datetimevar StartTime;
global datetimevar StopTime;
{@UsingNightShift}
and
(StopTime<datetime(date({@LocalGPSDATETIME}),{?Shift End Time}))
and
(StartTime>datetime(date({@LocalGPSDATETIME}),{?Shift Start Time}))

?Shift Start Time :

the user will enter in a time of day - e.g 12:00:00AM


?Shift End Time :

the user will enter in a time of day - e.g 11:59:59PM


?VisitMinimum :

users will only be entering whole numbers, e.g 1, 2, etc. which represents 1 or 2 minutes. this is to represent minutes only.


Maybe this will help some more...
Thanks for your patience

 

[bj1]

i have solved this by using this formula in the suppress section.

whileprintingrecords;
Local numbervar ONE_MIN := 1 / (60 * 24);
if {@VisitLength} < ONE_MIN * ({?Visitminimum} - 0.01)then
true

thank for all your help