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.

 

[CoSpringsGuy]

Try not using record or group selection to accomplish this.
In your record sort expert.. Make sure you order on the {Stage_Order.order} field. Order it ascending so that the most current record is last.
Place the fields you want displayed in the group footer. Hide the Group header and the details.
Suppress the group header footer and details using
//
{Contract_Stage.status} <> "Pending"

LBass has posted another way to do this if your report is any more than basic but this approach will work in most basic cases. The problem with this solution will be using summaries in your report footer. Crystal will use the values in your suppressed records in summaries. You will have to use running totals to get what you need for totals.

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[pradeep79]

Thanks, I never thought of putting everything in group footer.

Is {Contract_Stage.status} <> "Pending" only enough? Don't I need something like:
if {Stage_Order.order} = maximum({Stage_Order.order}, {Contract.Contract_Id}) and {Contract_Stage.status} <> "Pending" then true;

to check the status of the last stage? I'm new to Crystal reports so please bear with my questions if I'm wrong.

 

[CoSpringsGuy]

If you sort the records where the last stage prints last then
//
{Contract_Stage.status} <> "Pending"

will be all you need. That is assuming I understood your original post. You said you only wanted to see the records where the last stage is Pending. If you want to see the records if ANY stage is pending then let me know because the above wont work

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[pradeep79]

Thanks, that's correct I only need the last stage for now. Now I understand it. I will need to also do a report to check any stage pending. Could you please give me an idea.

 

[CoSpringsGuy]

will it be a seperate report that show any pending item or the same?

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[pradeep79]

It will be a seperate one.

 

[CoSpringsGuy]

Create the grouping like you had the first report

create a formula called pending
//
if {Contract_Stage.status} = "Pending" then 1

suppress your group header and/or group footer if you are going to use them with this formula
//
sum({@pending}, {Contract.Contract_Id}) =0

suppress your details with
//
{@pending}=0

Your results should be any Contract ID grouping with a pending transaction and the details of each pending transaction

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[pradeep79]

Thanks for that. Now I'm stuck with the following format which I think will not work if I move the items to group footer.

My report should look like this

Id Contract Stage Status Date
== ======== ===== ====== =====
1 Test1 Start Complete 21/01/09
Bid Complete 21/02/09
Neg Pending 21/03/09
---------------------------------------------------

I have managed to get the format like above(with all records without filtering). But don't know how I can evaluate that the last stage within this group is pending. I used next() function to get to the next record but I don't know how to go beyond this point.

I can't put this inside group footer because that only prints the last stage. I want a history of stages like above so I think it should be inside details.

I'm clueless, your help will be much appreciated.

 

[CoSpringsGuy]

in your database what is the value of stage order.... place it in the details next to date and show here what is returned

_____________________________________
Crystal Reports XI Developer Version
Intersystems Cache 5.X ODBC connection

 

[pradeep79]

Id Contract Stage Status Date Order
== ======== ===== ====== ===== ======
1 Test1 Start Complete 21/01/09 1
Bid Complete 21/02/09 3
Neg Pending 21/03/09 4
---------------------------------------------------------

There are 5 stages but a contract may not go through all 5. The highest order will be the current stage.

 

[lbass]

Instead of suppression, allow in all order records, and then use a group selection formula like this:

maximum({stage_order.order},{contract.contract_id}) = 4

-LB

 

[pradeep79]

I tried using the following formulas:

//@pending
if {Contract_Stage.Status} = 'Pending' and {Stage_Order.Order} = shared numbervar maxStgOrder then 1;

I calculated the maxStgOrder inside a subreport because the following formula won't accept if I use maximum({Stage_Order.Order}, {Contract.Contract_ID})

//@initPending
if sum({@pending}, {opportunity.Opportunity_ID}) > 0 then false;

Even after creating the maxStgOrder inside a subreport the formula fails with "This field cannot be summarized!". I read that a summary can't evaluate another formula in the same pass. I don't know how to do this. Could you please shed some light on how this can be done.

 

[pradeep79]

Thanks LB, but the lates stage isn't always 4 as in the above example. It can be anything between 1-5. I want to find the latest stage, then check whether it is pending, withing the group header if possible. If it is pending then print the details if not suppress the details.

How can formulas be used to do it? Sub reports? I'm clueless, please advice me how this can be done.

 

[lbass]

Did you see my post?

-LB

 

[pradeep79]

I haven't, could you please give me the link to it.

 

[lbass]

Are you saying that Order = 4 is not always "Pending"?

-LB

 

[pradeep79]

That's correct. Order = 4 can be complete and then we wouldn't want to know about it. A contract may go from stage 1-5 but not in a sequential order. It may start at stage 2 then goto 4 then 5. It may start at 1 and end in 3. All I want to know is the current stage (max order) and whether it is pending. If pending that means current stage is still open and the contract is live. Complete or unsuccessful then it is closed.

 

[lbass]

What are the potential values of status (the actual text)? What else can it be besides complete and pending?
-LB

 

[pradeep79]

Complete, pending, failed and no further action