Formula to display only certain dates

[cdennis321]

Version 8.5
Database Oracle

I am working on a report that calculates the number of misses that an employee has. This report is a letter that is kicked out of the system if they have a miss entered on the current day. They cannot have 8 or more misses. One miss can come off of their record after for every 30 days they have worked (i.e. 60 days -2 misses, or 90 - 3 misses etc). I am able to calculate the misses and get the accurate count using a subreport and variables.

My problem is with displaying the missed dates on the letter. The formula that I am currently using to display the dates are as such (This allows me to display the dates across the page):

@resetdate = WhilePrintingRecords;
StringVar chain := '';
NumberVar ChCnt := 1

@calculate = WhilePrintingRecords;
StringVar Item:= totext({@Absence},"M/dd/yy");
StringVar Chain;
NumberVar ChCnt;

if {@Absence} <> previous({@Absence})
then
if ChCnt = 1
then (ChCnt:= 2; chain := Item)
else
if Length(Chain) + Length(Item) > 254
then Chain := Chain else
chain := chain + ', ' + Item

@displaystring = WhilePrintingRecords;
StringVar Chain

I want the letter to only show the missed dates that should be counted. For example, if they have a total of 5 missed dates and 2 are subtracted, then I want the letter to show only the last three missed dates.

If anybody has any ideas I will be truly grateful as this has had me stumped for quite some time.


CD

 

[Turkbear]

Hi,
The best approach would be to alter the data model to include a flag that indicates if a missed date should still be counted..

Without some data indicating 'subtraction eligible' for a missed date, there would not be an direct way to tell which dates to count..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[cdennis321]

There is no possible way for me to alter the data model.

 

[lbass]

If you are otherwise happy with the results from your chain formula, and if you have your dates sorted in descending order, I think you could change your display formula to:

WhilePrintingRecords;
StringVar Chain;
stringvar firstx;
numbervar i;
numbervar j := minimum([3,ubound(split(chain,", "))-1]);

for i := 1 to j do(
firstx := firstx + split(chain,", ")+", ");
left(firstx,len(firstx)-2);

You would also need to add to your reset formula:
WhilePrintingRecords;
StringVar chain := "";
stringvar firstx := "";
NumberVar ChCnt := 1;

In your main formula, change the last line to:
chain := chain + Item + ', ';

-LB