Solved

Summing Sub Report figures

Posted on 2014-12-08
18
116 Views
Last Modified: 2014-12-10
I have a report, which comprises of 3 sub reports
I have declared values in the sub and main reports
But cannot remember how to show these values which I can then total
Have attached the rpt file

Gordon
Work-Order-Costs-V1.rpt
0
Comment
Question by:GiaHughes
  • 10
  • 7
18 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 40486693
Is this really in CR8.5?
If so then most of us can open your file but can't save it in a format you can read.


Add a formula to the main report
WhilePrintingRecords;
Shared NumberVar LabourCost;
Shared NumberVar PartsCost;
Shared NumberVar PurchaseCost;
LabourCost + PartsCost + PurchaseCost

Open in new window


For it to work you will need to add a a second GH1.
Right click GH1 in the left margin
Click INSERT SECTION BELOW
Put you formula in the new section

To get them all on the same line
Right click GH1A in the left margin
Click SECTION EXPERT
Click UNDERLAY FOLLOWING SECTIONS

The issue you are seeing is because subreports are the last thing evaluated in a section so regardless of what you try all formulas are evaluated in GH1A before the subreports so the values in the shared variables are the ones returning from the previous record.

By adding the second GH section you overcome that issue

mlmcc
0
 

Author Comment

by:GiaHughes
ID: 40486714
Hi mimcc

Sorry it is in CP XI release 2

Gordon
0
 

Author Comment

by:GiaHughes
ID: 40486718
mimcc
Do I removed the existing delared formula in the main report and replace it with your above?

Gordon
0
 

Author Comment

by:GiaHughes
ID: 40486735
Hi mimcc

Attached report, I have not gor it right yet
The report shows the sub report figures,
Need to add the totals?

Gordon
Work-Order-Costs-V1.rpt
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40486967
The declaration formulas need to be in the report header or Group 1 header.  You could also just modify them to not set the value to 0.

mlmcc
Work-Order-Costs-V1.rpt
0
 

Author Comment

by:GiaHughes
ID: 40487521
Hi mimcc

Your report seems to work, need to chech the data
I would like to create a crosstab chart which shows the cost center down the side axis, then the totals by year accross the horizonal axis. Then I would like to create a graph from the cross tab
Can this be?
If so, can you help or guide me

Gordon
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40487543
If you are trying to use the shared variable number, you can't build charts and I don't think you can build a crosstab using them.  I know charts are built early in the processing of the report before subreports are evaluated.  I believe cross tabs have the same issue.

mlmcc
0
 

Author Comment

by:GiaHughes
ID: 40487920
Hi mimcc

Thats a shame
Now how can I get the total costs by year?
Need some lateral thinking!!

Gordon
0
 
LVL 34

Expert Comment

by:James0628
ID: 40488630
If you want to build a cross-tab or chart based on the data in the subreports, the simplest thing would probably be to create another subreport that read all of the required data.  Then you could create whatever cross-tab or chart you wanted.  Whether or not that's practical, or possible, is another matter.  I haven't tried to look at where your main report or subreports are getting their data, so I have really no idea.

 If you can't use a separate subreport, it might be possible to use variables.  Many years ago, CR had some sample reports showing how you could save data in variables and then either pass the values to a subreport in parameters, or save the values in shared array variables, and the subreport would use those values.  The subreport would read some "dummy" data (some table that contained the required number of records, and, ideally, included a field with a record count), but it would not really use that data.  Instead, it would use the record number to "extract" the individual values from the data that had been passed from the main report, and then use that data in a chart.  I'm guessing that the same thing would work for a cross-tab.

 I don't know if CR (or SAP now) still has those samples tucked away on their Web site somewhere.  Any ideas mlmcc?  You probably know the reports I'm talking about (I probably found out about them from you).

 James
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 100

Expert Comment

by:mlmcc
ID: 40488948
I can find the white paper you are referring to.  Not sure if it will work with a cross tab.

What are you trying to achieve with a cross tab?

What totals do you want to display?

mlmcc
0
 

Author Comment

by:GiaHughes
ID: 40490106
Hi mimcc and James0628
Now I have to experts on the case
I am trying to see if I can create a crosstab that shows the total costs year on year
The costs are made up of the 3 elements which need to be created as the sub reports and then combined on thhe main report
From a presentation point of view it would be great to see the crosstab and then a graph for visual effects
Therefore it can be shown the trend of the cost over a few years, say 2010 to 214

Hope this makes sense

Gordon
0
 

Author Comment

by:GiaHughes
ID: 40491211
Hi

On the basis that I will probably have to go with the data only
I would like to expert the data to excel
The issue I get is that it does not exprt correctly
See the attached as a PDF and as an XLS sheet
The excel seems to put the cost center number is funny places
Any ideas?

Gordon
Crystal-Reports---Work-Order-Costs-V2.pd
Work-Order-Costs-V2.xlsx
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 40491396
Here is a version that exports well

Basic change was to have the subreports calculate the data but display nothing and to have the totals displayed by the main report.  In that way the subreports don't affect the export.

mlmcc
Work-Order-Costs-V2.xls
Work-Order-Costs-V2.rpt
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40491401
To get a cross tab, may require a complete reengineering of the report.  Looking at the data, I don't think what you need will work with the trick we mentioned above.

Can the data source be changed so there is only one source?
It may require using a command.

mlmcc
0
 

Author Comment

by:GiaHughes
ID: 40492072
Hi mimcc

I think I will have to go with what you have done for me
Cannot get all the information for a single source

Gordon
0
 

Author Closing Comment

by:GiaHughes
ID: 40492075
Always excellent support
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 40492437
Are all the tables in a single database?

mlmcc
0
 

Author Comment

by:GiaHughes
ID: 40492513
Hi mimcc
The tables are in the same database

Gordon
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
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…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

760 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

18 Experts available now in Live!

Get 1:1 Help Now