Grouping Based On A Maximum From A Subordinate Group 1

[horatiog]

Hi

Running CR10 on Windows XP.

I have a requirement to produce a report based on whether we can propose one or more opportunities to our clients based on whether the client/opportunity combo meets cetain a requirement which itself is determined by a cut-off level supplied as a parameter.

The client has several opportunities that he can either meet or fail the requirements for, or there may not be sufficient information recorded held against the client or opportunity to determine this.

So the data could be represented as follows:

Client Opportunity Meets Requirement
Client 1 Opportunity 1 y
Client 1 Opportunity 2 n
Client 1 Opportunity 3 ?

Client 2 Opportunity 1 n
Client 2 Opportunity 2 n
Client 2 Opportunity 3 ?

Client 3 Opportunity 1 n
Client 3 Opportunity 2 n
Client 3 Opportunity 3 n

Client 4 Opportunity 1 y
Client 4 Opportunity 2 y
Client 4 Opportunity 3 n

Client 5 Opportunity 1 ?
Client 5 Opportunity 2 ?
Client 5 Opportunity 3 ?

I have been asked to produce a report that sorts the clients by whether they have met the requirements for at least one opportunity, those that dont have enough info to make that decision for any opportunity and then those that dont meet the requirement for any of their opportunities; i.e.

Meet Requirements On At Least One Opportunity:
Client 1
Client 4

Not Enough Info On At Least One Opportunity:
Client 2
Client 5

Does Not Match Requirements On Any Opportunity:
Client 3

They then want to drill down into each client and see all their opporunities sorted by 'y', '?', 'n'.

I have started a report that runs from a view that perms all clients against all opportunities.

It seems to me that my problem is that I need the top level group to be based on the maxima of my formula ({@Meets_Requirement}) for the lower grouping column (CLIENT_ID); i.e. Maximum({@Meets_Requirement},CLIENT_ID) but, course, that is not allowed.

Secondly, {@Meets_Requirement} is a fomula based on a calculation using the a parameter input percentage figure applied against currency values held for both the Client & Opportunity.

I think that you may advise me to use a sub-report to overcome this problem but we have a fundemental problem with these as well. If a user looks back at a report instance that was run some time ago, the main report shows the data as it was at that time. However, if a user then drills into the subreport, then this re-interrogates the DB and shows data as at now. We then get calls logged at our helpdesk, saying that the report is inconsistent. We have therefore been told not to include subreports where possible.

Any help that you can give would be much appreciated.

Regards,
Harry Haines
Reports Analyst

 

[lbass]

Please clarify whether "?" is an actual formula result or whether this reflects a null. Please post the content of {@MeetsRequirement}.

-LB

 

[horatiog]

Hi LBass,

Yes it is a '?' - the result is a string from the following formula:

If IsNull({@Margin Value})
Then
    '?'
Else
    if {@Margin Value} > 0
    then
        'y'
    else
        'n'

{@Margin Value} is a numeric value that is NULL if any of the source columns that are used in its calculation are NULL.

Hope this helps?

Thanks & Regards,
Harry.

 

[lbass]

When asked to show the contents of a formula, you should show the contents of all nested formulas as well. The point is to determine whether there are any summaries contained in it or any calculations across records (sequential). Anyway, assuming that {@margin} is calculated within a specific record, then I think you could do the following. Create three formulas:

//{@y}:
if {@Meets Requirement} = 'y' then 1

//{@?}:
if {@Meets Requirement} = '?' then 1

//{@n}:
if {@Meets Requirement} = 'n' then 1

Right click on each and insert sums (not counts) on each. Then go to report->group sort->and choose summaries in this order:

sum of {@y} descending
sum of {?} descending
sum of {@n} ascending

This would order the client groups like this:

4
1
5
2
3

If this is close enough for you, I'll show you how you can create fake group headers for this approach.

Add a sort formula (descending) to report ->sort records to address the drilldown sort:

if {@Meets Requirement} = '?' then 'p' else
{@Meets Requirement}

Otherwise, you would have to use a command that tests each client group against your criteria.

-LB

 

[horatiog]

Hi LBass,

Thanks for that great reply and sorry it took so long to get back to you. I got dragged into a lot of support work for the last week or so and have only now got back to this report.

Also, apologies for not including the whole hierarchy of formulae - I now see why you would want this. You were right in your assumption that {@margin} is calculated from the values of a specific record (along with the Cutoff % which is supplied as a parmeter.)

Your group sort solution worked great; the clients are now in the right order but I would like to fake a group header for each "sort grouping" if possible. You said you have some more ideas for this? Any further help is much appreciated.

Regards,
Harry.

 

[lbass]

Insert another group header section and then drag it so that it becomes GH_a, with your regular fields in GH_b. Then create a formula {@grphdrname} and place it in the new GH_a section:

whileprintingrecords;
stringvar prevgrpname;
stringvar grpname;
prevgrpname := grpname;
grpname :=
if sum({@y},{table.client}) > 0 then
"Meets at Least One Criterion" else
if sum({@?},{table.client}) > 0 then
"Missing at Least Some Info" else
"Meets No Criteria"

Then go to the section expert->GH_a->suppress->x+2 and enter:

whileprintingrecords;
stringvar grpname;
stringvar prevgrpname;
not onfirstrecord and
grpname = prevgrpname

-LB