Group and sum records based on Nth Largest Summary fields

[barrattp]

Hello, I am trying to group records based on a set of “Nth Largest (N=1)” Summary fields.

I have to report on an equipment table (of which I have no control of reconstructing) where there are multiple records for the same piece of equipment. For a pipe asset the equipment attributes are listed in the ‘ASSETTATTRID’ field such as ‘Material’, ‘Diameter’, ‘Pressure Type’ and ‘Length’. The results are identified under either one of two other fields called ‘NUMVALUE’ or ‘ALNVALUE’

I have created 4 formulas so as to establish the results for each item. I have created a group on each equipment number and use Summary to consolidate the individual detail line on one row.

However I now need to Group on the ‘Nth Largest summary fields and the get a sum of the lengths for simular pipe types and diameters.
EQNUM ASSETATTRID NUMVALUE ANLVALUE Nth Largest formula's

Detail Lines MA00005105989 BOOK_LEN 2.00 2 0
Detail Lines MA00005105989 DIAMETER 100.00 0 100
Detail Lines MA00005105989 MATERIAL SP 0 SP 0
Detail Lines MA00005105989 PRESSLEV L 0 0 L


Group Footer MA00005105989 2 SP 100 L SP 100.00 L(Concatenation)


I have even created a field to concatenate the Nth Largest fields and tried to do grouping from this but with no success.

Basically I need to do a grouping on the Nth Largest fields. Any help would be appreciated please.

 

[lbass]

What you are trying to do here is not very clear. I think it would help if you showed a sample of how you would like your results to look.

If you inserted a summary on each field to arrive at the NthLargest, then you should be able to go to report->topN/group sort and select the "NthLargest of {table.field}" you want to sort your groups on. But it sounds like you want to sort on multiple NthLargest values, and I can't quite picture what you mean by that. I think you can nest topN's, if you have multiple groups, but that's about the best you can do.

As far as the "sum of the lengths for similar pipe types and diameters," it's unclear what criteria you would use for "similar" or even what fields would be involved. Maybe you are talking about summing one type of field across groups? If that is the case, you can use a formula like:

if {table.ASSETTATTRID} = "diameter" then {table.numvalue}

Then insert summaries on this at group and/or report levels.

-LB