CR XI - Creating Sum by categories

[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]

You could use Running Totals, but that would require 1 for each CatID. They would need to be created in advance, so this approach may not be feasible if there is a large number of CatIDs.

A better alternative may be a simple sub-report in the Report Footer using the same tables and record selection, but grouped only on the CatID.

Hope this helps.

Cheers
Pete

 

[pndMaster]

can you elaborate more subreport option that yyou suggested. If i have the subreport then what should main report will have?
in Subreport do i include sum for amounts?

little more specific would help.

Regards

 

[pmax9999]

Using the sub-report approach, the main report would retain the data broken down by Orgid, ContId, ContName, CatId, Month, AmtAllowed and AmtExpended.

The sub-report would just show the summary by categories, as per the "Expected Output" from your original post.

Thinking about it further though, a Cross-Tab in the Report Footer would produce the same results, but with the efficiency of a single pass of the database.

Hope this helps

Cheers
Pete

 

[pndMaster]

If i add Sum as summaries i am getting the sum for all months for allowed amount. The allowed amount is set once at the begining or updated but we need to get Max of Allowed amount and sum it across the contracts

if i add crosstab, i will have categories as rows and allowed amt and expended amt as two columns then for summary for each cell how do i do it for allowed amt

i appreciate the feedback