CRXI - Sum of Max not able to implement

[pndMaster]

Hello,
I have a table like below that stores information for each provider for each of contract by category by month. Amt allocated is set for each category when contract is setup, the users enter how much they spended each month. Now i wanted to know the over all spended amount for each categories and total amt allocated as well.

Orgid ContId ContName CatId Month AmtAllowed AmtExpended
10 1 test 3 1 100 20
10 1 test 4 1 50 5
10 1 test 5 1 500 100

10 1 test 3 2 100 10
10 1 test 4 2 50 15
10 1 test 5 2 500 150

10 1 test 3 3 100 15
10 1 test 4 3 50 10
10 1 test 5 3 500 50

10 2 xyz 3 1 50 15
10 2 xyz 5 1 150 10

11 5 abc 3 1 100 10
11 5 abc 4 1 200 50


I need to find out the total for each categories as below output is expected

CatId AmtAllowed AmtExpended
3 250 70
4 250 80
5 650 310

Ur feedback is appreciated

Regards

 

[pmax9999]

If I understand correctly, you will need to create a Group on {Table.CatID}, then use Running Totals to get the results you need, Set those RTs up as follows:

[Pre]
AmtAllowed
Field to Summarize: {Table.AmtAllowed}
Type of Summary: sum
Evaluate: On change of field {Table.ContName}
Reset: On change of group {Table.CatID}

AmtExpended
Field to Summarize: {Table.AmtExpended}
Type of Summary: sum
Evaluate: For each record
Reset: On change of group {Table.CatID}
[/Pre]


Hope this helps.

Cheers
Pete