User-selected Group by _OR_ Top N?

[MVisconte]

Guys (and dolls),

I have a problem that is baffling me: Can I make a parameter-driven grouping that includes Top-N or typical group name?

I.e.
We have circuit data:
Trunks and circuits on the trunks, Trunk bandwidth, and circuit bandwidth.

I would like have user-parameter input that will change the grouping from the Trunk Identifier or Percent Utilization where % utilization is
sum(circuit BW, Trunk ID) / trunk BW * 100

Paramter set grouping by different fields is easy, but I don't know how to achieve grouping by FIELD _or_ Top N (all).

Because the % util is a sum and needs to be based on an existing group (Trunk ID), formula editor won't let me use it as an option.

Any ideas?

This may not be problem solvable from w/in Crystal alone.

-Marc

--
Marc Visconte
CSC
Lead RMS Developer
Crystal Reports

 

[lbass]

Try calculating the sum of circuit BW in a command. Something like:

select sum(`table`.`circuit BW`) as sumcircBW, `table`.`trunk ID`
from `table`
group by `table`.`trunk ID`

Adjust this to the syntax appropriate to your datasource. Link the command to the main table on trunk ID. To add in the parameter to sort by Trunk ID or topN sort,
create a parameter {?sort} with options "percent" and "trunk ID" and then create a formula:

if {?sort} = "percent" then
{command.sumcircBW} % trunk BW

You can then right click on this formula in the detail section and insert a maximum at the group level. Then add the formula as the topN sort field. Because the default is 0, if "Trunk ID" is chosen, the report will automatically be sorted by your existing group field: trunk ID.

-LB