Formula to Display a Specific Year 1

[ReillyC]

Thank goodness I found this site. I need some assistance.

In my select expert I've indicated I need all the dates in the database until the end of 2003. I need this data to determine the earliest ticket date. However I just want to display the tickets issued in 2003 along with their earliest issue date.

For example:

Ticket Ctry Date Issued Earliest Date Issued
123693 US 1/1/2003 6/1/2002
123693 EP 6/1/2002

125696 US 8/5/2003 8/5/2003

126937 US 7/5/2002 7/5/2002
126937 EP 7/5/2003

Note: Each ticket can have multiple tickets issued.

My problem is getting the current year issue dates and the earliest issue date on the same report.

If I change the select expert to display just 2003 my 2003 issue date displays correctly but the earliest issue date is using the data from the year 2003 and not all years from the database.

 

[woolade]

ReillyC,

Try grouping by Ticket and then use the following in the Group Selection part of the select expert; {date issued} = minimum({date issued}, {ticket no}). Then place all issue dates in details field and suppress if < 01/01/2003.

 

[lbass]

Do not select records by date in the record select statement. Instead, first group on {table.ticketno} and then go to report->edit selection formula->GROUP and enter:

year({table.date}) = 2003 or
{table.date} = minimum({table.date},{table.ticketno})

This will give you all dates in 2003 and the minimum date for that ticket.

To create a field to display the minimum on the same line as other ticket dates, as shown in your example, use the following formula {@earliest date issued}:

minimum({table.date},{table.ticketno})

-LB

 

[shaleen7]

Thanks for the reply I going to try and I'll let you know. Thanks!

 

[ReillyC]

?

Okay I tried it. I used the formula minimum({table.date},{table.ticketno})to display the results but instead of returning the date it's returning the words False or True and it doesn't suppress the data less than 2003. The good thing the report looks a bit closer to what I want but not quite yet. If I can get the True words to return the actual date and remove all the False words, the report would be perfect.

I'm going to try again tomorrow when things are fresh.

Thanks for your help.

 

[lbass]

You must use GROUP selection (report->edit selection formula->GROUP and enter the formula:

year({table.date}) = 2003 or
{table.date} = minimum({table.date},{table.ticketno})

This is NOT a suppression formula, but a select formula, and will show only those records in 2003 or the earliest record.

The other formula:

minimum({table.date},{table.ticketno})

...should be created in the formula editor (insert->field->formula->new). In this case, leave out:

{table.date} =

...since this would return a true or false.

I suggested this so that you would be able to display the minimum as a separate field {@Earliest Date Issued} in any detail line or a group header/footer.

-LB

 

[ReillyC]

Thanks for the reply. My problem is getting the current year issue dates and the earliest issue date on the same report. I need all the dates in the database until the end of 2003. I need this data to determine the earliest ticket date. You're suggestion worked. However I just want to display the tickets issued in 2003 along with their earliest issue date. How do I extract out the 2003 records I'm looking for. I would preferable want to use a parameter so that the user can determine the issue date range.


For example:
Ticket Ctry Date Issued Earliest Date Issued
123693 US 1/1/2003 6/1/2002

125696 US 8/5/2003 8/5/2003

126937 US 7/5/2002 7/5/2002
126937 EP 7/5/2003


Desired Results for the report:

Ticket Ctry Date Issued Earliest Date Issued
123693 US 1/1/2003 6/1/2002
125696 US 8/5/2003 8/5/2003
126937 US 7/5/2002 7/5/2002

Any suggestions?
Thanks.

 

[lbass]

Okay, I think I get what the display issue is. In your detail section you should display:

{table.tkt} {table.ctry} {table.date} {@earliestdateissued}

First, as suggested earlier, don't limit records in your record selection statement. But, instead of my earlier group selection suggestion, use:

{table.date} in {?date} //where {?date} is a date range parameter

This will return only those records falling within your date range, but will show the earliest date (possibly outside of the range) because of the formula field {@earliestdateissued} added to the details section.

This could return more than one record per ticket if multiple records occur within the date period. Is this what you want? If not, you could sort by date descending and drag the fields into a group header section to show the most recent record within the date period (along with the earliest record), and then suppress the details.

-LB

 

[ReillyC]

It worked.

What if I wanted to group it by name then by ticket number. Each employee can have multiple tickets.

 

[lbass]

You can insert a group on {table.employeename} or {table.employeeID}, and then go to report->change group expert->and use the direction key to make the employee name (or ID) the first group. The report will still work as you wanted in previous posts.

-LB

 

[ReillyC]

Many, many thanks for your assistance. You were a great help!