Summing Sub Report figures

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

Select allOpen 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

Hi mimcc

Sorry it is in CP XI release 2

Gordon

mimcc
Do I removed the existing delared formula in the main report and replace it with your above?

Gordon

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

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

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

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

Hi mimcc

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

Gordon

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

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

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

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

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

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

Always excellent support

Are all the tables in a single database?

mlmcc

Hi mimcc
The tables are in the same database

Gordon