Running Total Issue

[khan007]

Hi!
I am on CR 8.5, WIN 2k, SQL 2k.

I have following scenario.
Report is based on a view which has two tables linked by ID. (Need all records from table B)

Table A
ID Amount Class
111 2000 A
222 1500 A
333 4000 A

Table B
ID UniqueID Charge Class
111 w1 1000 A
111 w2 100 A
111 w3 900 A
222 q1 700 A
222 q2 800 A
333 s1 1000 A
333 s2 1200 A
333 s3 800 A
333 s4 1000 A

View
ID Charge Amount Class
111 1000 2000 A
111 100 2000 A
111 900 2000 A
222 700 1500 A
222 800 1500 A
333 1000 4000 A
333 1200 4000 A
333 800 4000 A
333 1000 4000 A

I have two groups.
Class (group 1) and ID(Group 2).
In my report I need to show the Totals of amount in Group Header(Class). But the problem i am facing is, in detail section under ID group I have multiple same records for amount field for each ID rather than only one, so when i do Summary (Sum) to display on header, i am getting wrong total, but it works fine with running total as i evaluate amount on change of group ID and reset at group class.
I have to display it only on header.
Please help.

Thanks.

 

[Madawc]

Group by amount as well.

Madawc Williams (East Anglia, UK)

 

[lbass]

You could insert a subreport in the group header that only uses Table A and is linked to the main report on {table.class}. Then you can display the correct sum for the Amount field by suppressing all other sections within the subreport. You can then insert summaries on the charge field in the main report and drag the results to the group header.

-LB

 

[khan007]

I think, I cannot use that option of using subreport based on Table A, because my report is based on Stored procedure and I pass all parameters by Using that SP. Inside that SP i call that view to select all data.

 

[Madawc]

You could access the table in the main report, and selectively access the stored procedure in subreports.

It would be better to add the amount to the stored procedure, if that's possible in your case.

Subreports make a lot of work for computers, but save time for humans. You decide which is more important for your particular circumstances.

Madawc Williams (East Anglia, UK)