Formula based on the Group Level

 4 Replies
 0 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
David V
Basic Member Send Private Message
Posts: 22
Basic Member
Below is an example. I do not want to use the Summary function, because in some cases I want to manipulate the data.

Table = POLINE
Group 1 = Company
Group 2 = Vendor
Group 3 = Item

I want a single formula that can do the following...

If (Group Level 1)
Sum By Company

If (Group Level 2)
Sum By Vendor

If (Group Level 3)
Sum By Item

This way I do not have to create 3 seperate formulas.
Matthew Nye
Veteran Member Send Private Message
Posts: 514
Veteran Member
as described this seems like the basic usage of grouping. Simply creating a group based on each of those fields should do the trick. But Im guessing thats not what youre looking for. Can you provide a sample set of data and what you would be looking foir? It will help to understand better what youre looking for.
If any of my answers were helpful an endorsement on LinkedIn would be much appriciated! www.linkedin.com/pub/matthew-nye/1a/886/760/
David V
Basic Member Send Private Message
Posts: 22
Basic Member
Basicly I am asking...how do you determine what Group Header/Footer the formula is located in (Group Header #1, Group Header #2, Group Header #3, etc).

Company 1 (Current Group would be 1)
Vendor 1 (Current Group would be 2)
Item 1 (Current Group would be 3)
Detail Line 1 (Current Group would be 4)
Detail Line 2 (Current Group would be 4)
Item 2 (Current Group would be 3)
Detail Line 1 (Current Group would be 4)
Detail Line 2 (Current Group would be 4)

This way I can create a single formula that can be placed in all 4 group header/footer sections of the report, that does the following.

If (Current Group = 1) then do....
else
If (Current Group = 2) then do....
else
If (Current Group = 3) then do....

Dave Curtis
Veteran Member Send Private Message
Posts: 136
Veteran Member
Using your example
Group 1 is Company
Group 2 is Vendor
Group 3 is Item

For the example I am writing that I want to
count vendors when in Company Group
Sum payments when in Vendor Group
Average Cost when in item Group

For the example my table is SALES

IF GroupNumber = 1 Then Count({SALES.Vendor},{SALES.Company})
ELSE
IF GroupNumber = 2 Then Sum({SALES.Payments},{SALES.Vendor})
ELSE
IF GroupNumber = 3 Then Average({SALES.Cost},{SALES.Item})


This should work for what it sounds you would like to do.
David V
Basic Member Send Private Message
Posts: 22
Basic Member
I tried that, but GroupNumber is the number of Groups printed. There can be hundreds of vendors with hundreds of items per vendor.