Nested Groups - Group Sorting based on Formula

[dev1212]

I have 3 groups

Group 1 - Org
Group 2 - Center
Group 3 - Loc

Then detail field {amount}

Then i used 'underlay' functionality so its like

Org Center Loc amount
023 VA RN $1332

I have a need to sort the first group based on 2 different
fields {AllocID} and {AllocDet} on Ascending and Descending
based on what parameters user enters.

So basically there is only one parameter and user writes in that parameter,
for ex:
If user writes 'AllocID ASC' in parameter then --- Sort by AllocID in ascending order
If user writes 'AllocID DESC' in parameter then --- Sort by AllocID in Descending order
If user writes 'AllocDet ASC' in parameter then --- Sort by AllocDet in ascending order
If user writes 'AllocDet DESC' in parameter then --- Sort by AllocDet in Descending order


So i created 4 formulas

{@SortAllocIDASC}
==================
If
InStr({?Param},'AllocID ASC')>0
Then
{Table.AllocID}


{@SortAllocIDDESC}
==================
If
InStr({?Param},'AllocID DESC')>0
Then
{Table.AllocID}


{@SortAllocDetASC}
==================
If
InStr({?Param},'AllocDet ASC')>0
Then
{Table.AllocDet}


{@SortAllocDetDESC}
===================
If
InStr({?Param},'AllocDet DESC')>0
Then
{Table.AllocDet}


And i dropped this fields on details section
and then did insert summary (maximum) on each formula
and dropped it on GroupFooter1 i.e., Org


Now used Group sort expert and on 'org' group -> selected 'All' ->
then for formula
{@SortAllocIDASC} -> Ascending
{@SortAllocIDDESC} -> Descending
{@SortAllocDetASC} -> Ascending
{@SortAllocDetDESC} -> Descending


Now when user enters 'AllocID ASC' in parameter field, its not getting sort ascending on AllocID.
Same applies for other 3 formulas, not getting sorted as expectd.

I really need your help guys on this.


Thanks

 

[lbass]

I recreated your report, and your approach worked perfectly. I'm not sure why you are using instr() when you could just test for equality. I think the issue is that you are expecting the group sort to do something other than what it does. If you suppress all sections except the group footer #1, you will see that the sort is in fact working.

I think you probably are looking for some sort of sorting within the group, and you could add other nested group sorts--although this would give you sets of ordered groups, not just one ascending or descending list. If that is really what you want (one sorted list), then instead of nested groups, create a formula that concatenates your fields, as in:

Org+" "+Center+" "+Location

Insert a group on this and then sort this group using your formulas.

-LB

 

[dev1212]

Thats weird. let me try again..