Limit a One to Many report

rbuser

The master table is equipment ID's such as "101" and the detail table holds
the associated rates and effective dates for the master ID's.

I'm using report builder now with a master report for the ID's and a
subreport to print the associated rates - works great.

Now I'd like to do 2 things more to this report:

1- Limit the rates print to only the one with the largest date
2- Print the rate on the same line as the master info (so no subreport?)


I've created a single inner-join query but can't limit the selected rates to
only one per master record.

Any ideas? Thanks!

nicocizik

Hi Jon,

This can be done in a couple ways...

1. You can filter your detail dataset before printing your report so that
it only returns the rate with the largest date.
2. You can use the AutoSearch feature in ReportBuilder to filter the
dataset on the fly as the report traverses the data.

The problem you will have with performing a caluclation on a dataset as RB
traverses the data is that in order for RB to actually traverse, the
report/subreport needs to be visible. This would prevent you from
traversing the entire detail datset using a subreport, then hiding the
contents of that subreport.

--
Regards,

Nico Cizik
Digital Metaphors
http://www.digital-metaphors.com

rbuser

Nico - Thanks for the help!

1. You can filter your detail dataset before printing your report so that
it only returns the rate with the largest date.
<<
Not sure where I do this in Reportbuilder? Is there a demo/sample
somewhere?

2. You can use the AutoSearch feature in ReportBuilder to filter the
dataset on the fly as the report traverses the data.
<<
Not sure how I would autosearch since I don't know the date ahead of time?
I'm looking for the max date based with a given ID.