Retrieve records (1st in range + rest out of range).

[wildsharkuk]

Hi,

I'm Using CR10 + Reporting on a SQL 2000 Database.

I am reporting on a main Invoice Table which has a linking notes table so basically an Invoice can have many notes.

I want to report on the invoices where the 1st note was created in a specific date range (prompted by a parameter) but show all subsequent notes for that note (which can fall either in or out of the date range).

If i use the select criteria it only pulls back the notes in the date range + if I use subreports, how do I pull subsequent notes up for the invoice?

inv tbl
------------
inv_id
inv_dtm
inv_code
etc

notes tbl
------------
note_id
inv_id
note_dtm
etc

any ideas would be greatly appreciated.

Thanks

wildsharkuk

 

[ianoctdec]

Hi,
In the main report group by Invoice_ID and select invoices that meet criteria you want: first note in the specified date range. Supress detail section
Create a subreport and link it with the main report by Invoice_ID. Place the subreport in group footer (INvoice_ID) of the main report. This way, the subreport will print for each invoice that meets criteria from the main report, all the notes.

Dana

 

[lbass]

You could also use your parameter ONLY in the group selection formula area:

minimum({table.date},{table.groupfield}) in {?daterange}

...assuming that by "first" you mean earliest.

-LB

 

[wildsharkuk]

thanx guys, i'll try when I get back to work tomorrow morning.

cheers

wildsharkuk

 

[wildsharkuk]

Hi ianoctdec & lbass

thanks for your suggestions, is there a way to hide the invoices references in the group tree which don't fall in the date range?

cheers

wildsharkuk

 

[ianoctdec]

In the group tree you should have only those Invoice IDs that meet criteria from the record selection (or group selection) formula.
What a great idea LBass. Thank you.

Dana

 

[lbass]

Non-group selected records (and suppressed records) will still show up in the group tree. You could remove the group tree altogether if you wanted to. I also think I might have read a response recently that might have provided a solution to this, but I was unable to locate it.

-LB

 

[wildsharkuk]

thanx again guys,

i've gone down the subreport route.

thanks again

wildsharkuk