Formula based group

[kimchavis]

I have a formula that I need to put in a group:

formula:

IF (maximum({@comp},{LOAN.LOAN_ID})) in -1 to 0 THEN 0 ELSE (maximum({@comp},{LOAN.LOAN_ID}))

comp:

if {PSCAT_LOANS_DOCUMENTS_RELATION.STATUS} = "in" then currentdate-{PSCAT_LOANS_DOCUMENTS_RELATION.STATUS_DATE}

However, its not letting me put this in a group as it is a summary formula. I have entered a section below the loan number but it lists the group every time. Is there a way for me to supress everything except the first instance of the group name?

Thanks in advance.

 

[kimchavis]

Is there more information I should provide regarding this one?

 

[lbass]

It is very unclear what you are trying to do here. You can certainly place your summary formula in the group header, if you want, although you cannot create a group based on a summary formula.

You could also I think bypass the summary formula and change {@comp} to:

if currentdate > {PSCAT_LOANS_DOCUMENTS_RELATION.STATUS_DATE} and
{PSCAT_LOANS_DOCUMENTS_RELATION.STATUS} = "in" then currentdate-{PSCAT_LOANS_DOCUMENTS_RELATION.STATUS_DATE}

Then you could insert a maximum on {@comp} and do a topN sort (report->topN/group sort), which would at least cluster like values.

If you mean you only want to display the record with the maximum {@comp} value within each group then go to report->edit selection formula->GROUP and enter:

{@comp} = maximum({@comp},{loan.loanID})

-LB

 

[kimchavis]

I thought I would put this formula in a subsection above the group of loan id to mirror what would look like a group showing only the first instance of the group and supressing all others.

There are only 3 values in the group: >211, 210 - 91 and <90

I can show only >211 in the first instance by supressing based on the section and putting in the formula Not(onfirstrecord) I was thinking of putting a subsection under that and isolating the second value and a subsection under that to isolate the third value. But Im not sure if this is even possible.

Any help would be appreciated!

 

[lbass]

Why not use a record selection statement like:

{PSCAT_LOANS_DOCUMENTS_RELATION.STATUS_DATE} < currentdate and {PSCAT_LOANS_DOCUMENTS_RELATION.STATUS} = "in"

Then create a formula {@days}:

if currentdate-{PSCAT_LOANS_DOCUMENTS_RELATION.STATUS_DATE}
>= 211 then "> 211" else
if currentdate-{PSCAT_LOANS_DOCUMENTS_RELATION.STATUS_DATE}
in 91 to 210 then "91 to 210" else
if currentdate-{PSCAT_LOANS_DOCUMENTS_RELATION.STATUS_DATE}
<= 90 then "<=90"

Then group on {@days} and choose specified order and use the drop down list to order the three groups in 90, 91-210, 211+ order. Then go to report->change group expert->and use the direction keys to make this the higher order group. Your layout would then look like:

<= 90
LoanID 3
LoanID 5
90 - 210
LoanID 1
LoanID 4
>=211
LoanID 2

-LB

 

[kimchavis]

Actually lbass, you were the one that helped me come up with that formula originally:
lbass(Technical User)
I think you could create a formula {@comp} for the detail section:

if {table.status} = "comp" then currentdate-{table.date}

Create a second formula for the group header:

"Days Old: "+totext(maximum({@comp},{table.loanID}),0)

-LB


This was on January 7th.

So I dont think that I can use that new formula to calculate my days old based on what Im pulling from. Any suggestions?

 

[lbass]

After searching and finding that old thread, I still am not sure what your current issue is. Please note that I suggested using topN in January BECAUSE you cannot group by a summary formula. I have suggested it again here, in an earlier post. It will put all your groups together that have the same order. That is really the best you can do, if it is still the case that you need to maintain records in the detail section in ascending order, and that they contain both records that are "in" and those that are "comp". By the way, in the previous thread, we were working with "comp" records, and here you are calling the formula {@comp}, but then selecting those records coded with "in"--confusing.

It would help if you could explain in a little more detail why you cannot use topN, or what you are trying to accomplish. Please provide a sample of your desired report, and try to be specific about what is not working.

-LB