sorting and displaying topN programmatically in CR 10

[bether]

Hi,

I’m building a report in CR 10 that takes the following parameters:
• GroupBy (possible values Manager, Tag, Code)
• TopN (possible values Top , Bottom)
• GroupCount (a numeric value)
• Order (descending, ascending)

The GroupBy parameter will indicate what or how groups should be displayed. Each group has a summary on Quantity.

The TopN parameter will indicate whether to display the top or bottom groups, displaying groups that have the highest summary (quantity) or lowest summary.

The GroupCount will indicate the N number of groups to be displayed.

The Order will indicate the order of the grouping, either ascending or descending.

How can I instruct the report to accomplish the above tasks? I know how to create groups/summaries, etc. and use the the topN feature of CR10, but that only works if you know offhand how many groups to display. In this case, the user will supply how many groups that want to be included in the report, what group, and set the order.

Thanks.

 

[lbass]

I'm not sure what features 10.0 has, but the following will work in all versions of CR, I think. First create a formula {@grp}:

Select {?GroupBy}
Case "Manager" : {table.manager}
case "Tag" : {table.tag}
case "Code" : totext({table.code},"000" //an example of //how to convert all to the same datatype, where the //number of zeros = the maximum length of the code field

Insert a group on {@grp}. Then create a formula {@Order}:
if {?Order} = "Asc" then {table.qty} else
if {?Order} = "Desc" then -{table.qty}

Add this to the detail section and insert a sum on it, and then suppress the result (this is used just for ordering, not for display). Then go to report->group sort and choose "All", sum of {@Order} for the group sort, ascending.

Also add {table.qty} to the detail, insert a summary on it, and then drag the summary into the group header, and then suppress the detail and group footer sections.

Finally, go to the section expert->group header->suppress->x+2 and enter:

(
{?topN} = "Top" and
groupnumber > {?GroupCount}
) or
(
{?topN} = "Bottom" and
groupnumber <= distinctcount({@grp}) - {?GroupCount}
)

-LB

 

[bether]

hello,

I didn't get to tell you, this works. thanks!