Hierarchical Summary Calculations

[dev1212]

Hierarchical Summary

Hi Guys,

I need help in calculating totals for the hierarchy node and its direct children..

I have hierarchy structure like..

Manager
Sub manager 1
John
Mike
Jim
Sub manager 2
Tom
Jugad
Steve


And values are like

Designation Sal Parent

Manager 5000 NULL
Submanager1 3000 Manager
Submanager2 3500 Manager
John 1500 Submanager1
Mike 1700 Submanager1
Jim 1500 Submanager1
Tom 1800 Submanager2
Jugad 1500 Submanager2
Steve 1200 Submanager2



I want to sum the totals across hierarchy in such a way that
i get only the sum of parent and its direct children like...

Designation Sum

Manager 11500 --> (5000+3000+3500)
Submanager1 7700 --> (3000+1500+1700+1500)
John 1500
Mike 1700
Jim 1500
Submanager2 8000 --> (3500+1800+1500+1200)
Tom 1800
Jugad 1500
Steve 1200



Can you guys please reply.....
Its literally killing me...


Thanks

 

[dev1212]

I forgot to add more details...if that helps...

There is a field in the same table "level" which gives levels of each employees...

Designation Levels
Manager 1
Submanager1 2
Submanager2 2
John 3
Mike 3
Jim 3
Tom 3
Jugad 3
Steve 3

 

[lbass]

Try a formula like this:

if {table.designation} = {table.parent} then
{table.amt}

Then insert a sum on this at various group levels.

-LB

 

[chelseatech]

Are you using a hierarchical group or an alias table to created a fixed number of group levels within your hierarchy?

If you sue the hierarchical grouping option, you can jsut select the summary field and "Summarize across hierarchy"

Works like a charm.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[dev1212]

lbass,

if {table.designation} = {table.parent} then
{table.amt}

is not working... i am getting the wrong values..

any other ideas please..?

can you think of any other ideas using 'levels'

 

[lbass]

First try Chelseatech's solution.

With regard to my suggestion, please show some sample data including detail level data and then show the results you are getting using my suggestion.

-LB

 

[dev1212]

I cannot use Chelseatech's solution "hierarchical summaries" because,
the requirement is to sum the values for immediate/direct children and
not the grandchildren.

"Hierarchical summaries" sums the values for children and even the grandchildren.


Sample values are

Designation Sal Parent

Manager 5000 NULL
Submanager1 3000 Manager
Submanager2 3500 Manager
John 1500 Submanager1
Mike 1700 Submanager1
Jim 1500 Submanager1
Tom 1800 Submanager2
Jugad 1500 Submanager2
Steve 1200 Submanager2



And the desired output is,

Designation Sum

Manager 11500 --> (5000+3000+3500)
Submanager1 7700 --> (3000+1500+1700+1500)
John 1500
Mike 1700
Jim 1500
Submanager2 8000 --> (3500+1800+1500+1200)
Tom 1800
Jugad 1500
Steve 1200


Thanks

 

[dev1212]

Sorry,

i missed lbass's second question,

When i am using
if {table.designation} = {table.parent} then
{table.amt}

and dropping this formula in detail section and inserting summary on this then i am getting all zeros (0).

 

[lbass]

I asked you to show the results when you tried my suggestion for this sample data.

-LB