Highlighting Top 3 of Calc. Field

[jcoleman]

HI Guys,

I'm using Crystal 10, on a Sql2000 database.

My report group footers are sorted by store number. What I need to do is highlight the top three stores with the highest gross margin. The gross margin field is a calculated field. I was thinking of sharing some variables to a sub-report, then sorting the sub-report by gm and some how using that to highlight the top 3. But I'm open to easier ideas.

Here's kind of what I'm after.

Store Sales GM
01 100 30
02 90 35
03 150 32
04 110 31
05 180 38

This would all be group footer information.

jc

 

[lbass]

I think you are stuck with the subreport approach. In the subreport (leave this as an unlinked sub), use a topN sort, and then create a shared variable like the following to be placed in the group footer:

shared stringvar storeID;
if instr(storeID,{table.storeID}) = 0 then
storeID := storeID + {table.storeID} + ", ";

Then in the subreport report footer, add:
shared stringvar storeID;

Place the subreport (with all sections suppressed) in a GH_a section. Then go to format section->GH_b->color tab->background->x+2 and enter:

shared stringvar storeID;
if {table.storeID} in storeID then crYellow else crNocolor

I'm guessing you are going to next say that the calculated gross margin is not available for a topN/group sort.

-LB

 

[jcoleman]

Thanks LB,

Yep, the calculated gross margin is not available for a topN/group sort, but I was thinking that if I stick it into a shared variable, I hopefully can sort it in the sub-report.

I havn't had a chance to look into yet, just looking for a easy way out.

jc

 

[lbass]

I don't think you'll be able to sort it there either, unless you use a SQL expression approach. What is the formula for GM?

-LB

 

[jcoleman]

Nope, could not sort it in the sub-report either. This is going to be much more complicated than I first expected.

The formula for GM is Sales-Cost/Sales. My Sales figure is pulled from invoiced detail lines, same as cost.

Anyways, I really don't want the users dumping the report into Excel just to highlite the top three. But right now that seems to be the easy way out.

Thanks LB

jc

 

[lbass]

If you share your actual formula instead of an explanation, I might be able to help. Are you saying that your formula looks something like:

(sum({table.sales},{table.group})-sum({table.cost},{table.group}))/sum({table.sales},{table.group})

It would make it easier if you shared the exact formula using actual table and field names. There is a way of doing this that might not be too hard depending upon the complexity of your overall report.

-LB