Solved

Crystal Reports Group Summary

Posted on 2013-10-23
10
731 Views
Last Modified: 2013-10-27
I have the following dataset:

Job,Hierarchy,TotalBudget,Actual
30001666,400402,60000,5000
30001666,400402,60000,10000
30001666,400402,60000,3000
30001666,400402,60000,15000
30001666,400403,20000,18000
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.

Required Report Output
Help would be greatly appreciated.
0
Comment
Question by:Morgs77
  • 3
  • 3
  • 3
  • +1
10 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 39596268
There is probably a SQL solution to get what you want in a query.

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
0
 

Author Comment

by:Morgs77
ID: 39596286
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.
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 200 total points
ID: 39596306
You could look at the SQL Expression.

mlmcc
0
 
LVL 22

Expert Comment

by:Ido Millet
ID: 39596885
You simply need to create a Running Total and select the option to evaluate only once for each Group Level 2.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 39597264
A running total won't work because he wants it displayed in the group header.

mlmcc
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 22

Expert Comment

by:Ido Millet
ID: 39597889
Correct; Running Totals would work only if the user is willing to see the totals in the Group Footer.
0
 

Author Comment

by:Morgs77
ID: 39599757
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.
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 50 total points
ID: 39599912
As mlmcc mentioned earlier, you could try a SQL Expression.  That lets you create a sort of sub-query to gather information from the tables.  If that option is available in your report, it should be listed in the Field Explorer.  I've never used them (they weren't an option for most of my reports), but that might give you what you need.

 James
0
 
LVL 22

Accepted Solution

by:
Ido Millet earned 250 total points
ID: 39600071
If you must place the information in the header, here is the structure of the SQL Expression that would solve this:

(SELECT Sum(`TotalBudget`) FROM Budget as B WHERE (`B`.`Hierarchy ID` = `Budget`.`Hierarchy ID`) )
0
 

Author Closing Comment

by:Morgs77
ID: 39604718
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.
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query Help 12 52
select over clause 1 40
Solution for warm standby SQL server 20 34
SQL profiler equivalent in MS-Access 3 41
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now