Select Formula when field has various values in CR XI 1

[Deb100]

Example spreadsheet:

Details Stock Comment
Purchase AAA Need this row
Sold AAA Need this row
Purchase BBB Do not need this row
Purchase CCC Need this row
Sold CCC Need this row
Sold DDD Need this row
Dividend AAA Do not need this row

My source data is an Excel database and I have a particular column, called Details, which holds various details, ie Purchase, Sold, Dividend. I need to select all rows where Details = Sold, and then if there is a Purchase related to a Sold stock then I also need to display that row too.

Using the Group selection and Record selection I can get all rows that have Details = Sold and all rows that have Details = Purchase, but I cannot seem to be able to get Purchase only if there is a related Sold.

I would be most grateful if you could point me in the right direction.

 

[lbass]

First insert a group on {table.stock}. Then use the following record selection formula (report->selection formula->record:

{table.details} in ["Sold","Purchase"]

Then go to report->selection formula->GROUP and enter:

maximum({table.details},{table.stock}) = "Sold"

This will return only those stock groups that contain a record for sold, and if the group contains a purchase record that will be displayed too.

-LB

 

[Deb100]

Hi lbass

This worked perfectly, I can't thank you enough.

I would never have figured this one out as I thought 'maximum' was only used to bring back the highest value within a column of numbers.

Once again, Thank you

Deb100