Solved

Summing Sub Report figures

Posted on 2014-12-08
18
123 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Store code inside oracle BLOB column 7 42
Formula to get month and year from date 5 53
Crystal Reports  Formula to Command 4 73
Formatting a Crystal Report 2 47
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. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

919 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

20 Experts available now in Live!

Get 1:1 Help Now