Morgs77
asked on
Crystal Reports Group Summary
I have the following dataset:
Job,Hierarchy,TotalBudget,
30001666,400402,60000,5000
30001666,400402,60000,1000
30001666,400402,60000,3000
30001666,400402,60000,1500
30001666,400403,20000,1800
30001666,500504,10000,2000
30001666,500504,10000,3000
30001666,500504,10000,1000
30001666,500504,10000,4000
30002000,400402,65000,500
30002000,400402,65000,1500
30002000,400402,65000,2000
30002000,500604,70000,1000
30002000,500604,70000,2000
30002000,500604,70000,3000
30002000,500604,70000,2000
Using Crystal Reports 2008 I want to group by Hierarchy and then Job and include summary totals in the group headers.
The difficulty is that the TotalBudget in each record is the total for Hierarchy and Job combination so should be displayed as is, not be summed, in the Job group header. I then want the sum of the Total Budget value displayed in the Job group header to be summed to the Hierarchy group header.
The Actual value for each record needs to be summed to the Job group header and the Hierarchy group header.
The output I need in the report is as follows. The left hand column is the section structure. Hopefully it makes sense.
Help would be greatly appreciated.
Job,Hierarchy,TotalBudget,
30001666,400402,60000,5000
30001666,400402,60000,1000
30001666,400402,60000,3000
30001666,400402,60000,1500
30001666,400403,20000,1800
30001666,500504,10000,2000
30001666,500504,10000,3000
30001666,500504,10000,1000
30001666,500504,10000,4000
30002000,400402,65000,500
30002000,400402,65000,1500
30002000,400402,65000,2000
30002000,500604,70000,1000
30002000,500604,70000,2000
30002000,500604,70000,3000
30002000,500604,70000,2000
Using Crystal Reports 2008 I want to group by Hierarchy and then Job and include summary totals in the group headers.
The difficulty is that the TotalBudget in each record is the total for Hierarchy and Job combination so should be displayed as is, not be summed, in the Job group header. I then want the sum of the Total Budget value displayed in the Job group header to be summed to the Hierarchy group header.
The Actual value for each record needs to be summed to the Job group header and the Hierarchy group header.
The output I need in the report is as follows. The left hand column is the section structure. Hopefully it makes sense.
Help would be greatly appreciated.
ASKER
Thanks. I can get the Actual totals fine, it is the TotalBudget totals that are causing me trouble. I had considered using a subreport but was hoping there was another way around it.
The data is from an SQL Server 2008 database.
The data is from an SQL Server 2008 database.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You simply need to create a Running Total and select the option to evaluate only once for each Group Level 2.
A running total won't work because he wants it displayed in the group header.
mlmcc
mlmcc
Correct; Running Totals would work only if the user is willing to see the totals in the Group Footer.
ASKER
Thanks for the help so far. The running total does give me the values I need, but I really need them in the group header. I will try a subreport but if there is any other way to do it I would like to try.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for all the input. I got it to work with an SQL Expression along the lines suggested by IdoMillet. I had not used this feature before and did not know it existed so it took a while realise what was being suggested. Thank you for all the help.
In Crystal you probably need to use a subreport to get the GH1 total for budget
Actual totals can be calculated with a summary.
What database are you using?
mlmcc