Suppressing group based on record 1

[pradeep79]

I have following 3 tables

Contract(Id, Name)
Contract_Stage(Id, Contract_Id, stage_name, status, date)
Stage_Order(stage_name, order)

Each contract can have upto 5 different stages. The Stage_Order table is there to specify the chronological order of stages.

Currently I'm grouping by the Contract_Id and showing all the stages for a contract. What I want to do is find the latest stage for a group (maximum({Stage_Order.order}, {Contract.Contract_Id})) then check whether that stage status ({Contract_Stage.status}) is 'Pending'. If latest stage is pending I want to display the group if not I want to suppress the whole group.

So far I managed to use next() to check the next record's status withing a group and suppress current record if next record is the last record and the status is not 'Pending'. This works fine for only records with 2 stages. But I don't know how I can go beyond the next record if there are 5 records within a group and suppress all 5 records.

I'm suppressing the records based on the above formula because I'm not sure how to use the record selection expert to narrow the records. I'm sure you can do it both ways. I would be interesed to know how it can be done by record selection and also how to suppress the records as I'm doing above but beyond the next record.

 

[lbass]

If you can create a SQL expression {%MaxStatus}, set one up like this (but the punctuation would be specific to your datasource):

(
select max(`Status`)
from Contract_Stage A
where A.`Contract_ID` = `Contract_Stage`.`Contract_ID` and
A.`Date` =
(
select max(`Date`)
from Contract_Stage A
where A.`Contract_ID` = `Contract_Stage`.`Contract_ID`
)
)

Then in your record selection formula (NOT your group selection), use a formula like this:

{%MaxStatus} = "Pending"

This will return the contract IDs where "Pending" is the last status in the group.

-LB

 

[pradeep79]

Thanks LB, that did the trick. I wasn't sure of how to use sql expressions and now I understand it.