Shared Variable Returning Only Last Record 2

[wameri]

Am having a problem with a shared variable and hope somebody can help.

I have three tables invheader,invdetails, meterreadings and departments.
The sub report takes the quantities of different photograph sizes and calculates
the total area of all photographs as follows.

whileprintingrecords;
shared numbervar printssqmetres:=
sum({@3rd},{invheader.departmentname})*0.011684+
sum({@3rr},{invheader.departmentname})*0.011684+
sum({@4rd},{invheader.departmentname})*0.01596

The 3rd, 3rr etc are the various photograph sizes which am calculating the area.
To extract the quantities, I use
if{invdetails.productid}="3rd" then{invdetails.quantity}
if{invdetails.productid}="3rr" then{invdetails.quantity} etc

The report is grouped on Departments of which I have three depts.
The formular is placed in Group1 Header, invheader.departmentname and it works fine.

On my main Report, I want to compare the calculated values from the sub report to those of
the meterreadings table, which is also grouped by departments.
I use the following in the main report.

whileprintingrecords;
shared numbervar printssqmetres;
printssqmetres

I have used "Insert Section Below" to ensure sub report is a section ABOVE the main report
so that it is evaluated BEFORE the main report but what i get is the value of the last department ie;

Sub Report Data:
Department1 100
Department2 150
Department3 400

Main Report
Department1 400
Department2 400
Department2 400.

Where am I going wrong? I have tried having the sub report in Page header and Group Header1a
but I get the same results. The linking between the sub amd main is the Department.
Please help.

 

[lbass]

What are your groups on the main report? The subreport should be placed in the next higher group above the department group and linked on that field, not on department, since you are accumulating ACROSS departments in the subreport.

-LB

 

[wameri]

Sorry, the main report is also grouped on Departments.
This is what I get..

Department1 105 400
Depaerment2 160 400
Department3 408 400

The 400 is being returned from the sub report.
There is only One Group, Department.

 

[GJParker]

If your trying to compare a department from the main report against the corresponding departments data in the subreport. you will need to place teh subreport in the department group header on the main report and link the subreport to the main report by department.

This way the subreport will run once for each department and return the data for that department only.

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[lbass]

Gary is correct--I misunderstood.

-LB

 

[wameri]

Lbass, Gary, Thanks for the hints. After placing the sub report in the department group header, the main report was still reporting the same figure ie 400.
The main problem was with the link because after putting a check mark on the "Subreport Link" form and selecting "Select Data in Subreport based on field......."
the main report now shows the correct figures.
Once again, thanks. I really dont know what the rest of us would do without people like you. By the way, I have been on that problem for the last three weeks. Cheeeers!!

 

[GJParker]

Glad I could help

Gary Parker
MIS Data Analyst
Manchester, England

 

[wameri]

Sorry am back on the same report.. The figures are reporting correctly and now I want a graph to display the parcentages.The formula is

Percentage=whileprintingrecords;
if {@actualpaperused}<>0 then
({@actualpaperused}-{@actual})/{@actualpaperused}*100

@actual=whileprintingrecords;
shared numbervar printssqmetres;
printssqmetres

I place the formular in Group header 1b and it displays the correct percentage figures. I place a chart on report footer but it displays 100% for all departments.I use the Chart Expert-->Type=Bar then DATA-->Layout-->Advanced On change of=Department, Show Values=@Percentage but on checking "Available Fields" I notice @actual is not amongst the fields listed. Why is the field not amongst the fields and how do i go round this?
Crystal 9, Access 2000 DB.

 

[lbass]

Charting on variables presents special challenges. The following article should be helpful:

http://support.businessobjects.com/...pers/cr9_charting_print_time_formulas.pdf.asp

-LB

 

[wameri]

Lbass, Thanks a million.
The article is Crystal(9?) Clear and it also explains why the field is not available.
You deserve more than a star!
Cheers!!!

 

[wameri]

Lbass or anyone who has tried the sample using the above link, I have tried it with the Xtreme DB and it works fine. Am still trying to implement it in my report. One question, the formulas on the main report, @onchangeof and @showvalue
when viewed from the sub report are now parameter fields [?]?Pm-@onchangeof and [?]?Pm-@Showvalue. How is this so cos I never created any parameter fields.
Thanks again in advance.

 

[lbass]

The parameter fields that you see are how the links to the main report are shown in the record selection statement of the subreport.

-LB

 

[wameri]

Thanks LBass. Looks like I have a long way to go.