Records from a sub-report

[Indigenous]

Greetings, I'm trying to render a particular record from a sub report.

This record has to meet these conditions:
1 - starting with the most current record, is it in a box?
if yes, then go to 2
if no, ask the next most current record question 1 again (until it finds one that is in a box so it can ask question 2).
2 - while question 1 is yes, what is the minimum date & time

sample records in the sub-report:
key....date.....time...box
5.......1-1-12...1200...a
7.......1-2-12...1205
11.....1-4-12...0905...a
16.....1-8-12...1005...a

So looking at the records, the data that meets the conditions is from key 11. It wouldn't be the record from key 5 because it came out of the box during key 7.

I got as far as being able to display the records in decending order (inverted order than shown here). After that, I don't have a clue where to start.

Any assistance is greatly appreciated.

 

[lbass]

I think you should create a SQL expression {%maxnoboxdt}:

(
select max(`date`)
from table A
where A.`box` is null and
A.`groupfield`=table.`groupfield`
)

For "groupfield" use the field you are linking the subreport on.

Then create a formula:

//{@inboxdt}:
if not isnull({table.box}) and
{table.date} > {%maxnoboxdt} then
{table.date}

Then you can insert a minimum on {@inboxdt} to get the desired result. Problem is it looks like you have separate fields for date and time, and it is unclear whether they are strings, dates, or number (time). The SQL expression assumes the date field is in fact a date. Otherwise you would need to do a conversion within the SQL expression, and you should also build in the time if different times on the same date are possible.

-LB

 

[xxyyzz49]

Another option, the SQL expression is great when they can be used, is to use a running total. Summarize on your date field as minimum, use a formula for evaluate selecting box not null and don't reset. Place the running total in the report footer and it displays the record meeting criteria.

 

[lbass]

You could use an inserted running total if you sort by date ascending and follow pmsawyer's suggestion, except you would need to add a reset formula like this:

isnull({table.box}) or //reset if hits a null
{table.groupfield} <> previous({table.groupfield}) //reset for a new group

-LB

 

[Indigenous]

Thank you both for your input.

I couldn't get the SQL expression to work correctly, so I went with PM's running total for the date which is working correctly.

I'm now stuck on trying to render the time associated with that date.

I created a running total for time (almost a duplicate of the one for the date), except in the formula for evauluation I added:
{#date} = {table.date} // where #date is the running total for the date

when i do this, i receive a "Error 997: failed to retrieve error messge from print engine" window.

both time and date fields are timedate ... where I'm formating out the unwanted portion.

any thoughts?

 

[Indigenous]

I was able to resolve the error message ... apparently you can't use a RT in the evaluation formula of another RT.

I'm still stuck on rendering the correct time.

To recap:
KEY field is the link to the sub-report;
RT setup for DATE, sorted in descending order, summarize to minimum, evaluation formula= BOX is not null, no reset;

when the RT for TIME is setup in the same fashion, the result is from KEY 5 instead of KEY 11 (see original example). I've tried a number of combinations based on the info above with no luck.

Can anyone shed some light?

Also, LB -- I'm a little late in admitting, but I'm a bit lost regarding the table.groupfield concept ... I was unable to locate any further info on this.

Thanks,
imf

 

[lbass]

First, pmsawyer's solution would return the key 5 row, so I think you have to do it the way I suggested, using the running total approach.

To address the time issue, we need to know the datatype for both the date field and the time field--are they strings, numbers, or dates or times. You can run your mouse over each field and observe the datatype shown in the tooltip.

By {table.groupfield} I meant whatever field you are linking on, so I guess that would be the key field. A SQL expression goes directly to the database, so wouldn't be limited by the linking field.

-LB