Crystal XI - Sharing variable between a main and subreport 1

[ReillyC]

The report I have works great it just I need to know how to combine sites information using the shared variable technique or may I should set up my report differently.

I have a main report and a subreport. The main report contains the list of employees along with the sites they belong to. I have the main report grouped by site then employee. The subreport contains the # of applications submitted by each employee for the month.
Using information I found on this site. I did the following:

In the sub report which is grouped by site then employee
I placed the following formula:
whileprintingrecords;
shared numbervar applic := sum({@applic}, {table.employee});
//where {@applic} equals:
if isnull({table.applicationID}) then 0 else 1

Then I placed this on the subreport instead of the summary currently displayed. Then in the main report, create the following formulas:

//{@resetsubtot} to be placed in the Group #1 (site) header then suppressed the field to prevent the zeros from showing:
whileprintingrecords;
numbervar subtot := 0;

//{@totals} to be placed in the Group #2 (employee) footer the suppressed the section because the running total was showing for each employee:
whileprintingrecords;
shared numbervar applic;
numbervar subtot := subtot + applic;
numbervar grtot := grtot + applic;

//{@displsubtot} to be placed in the Group #1 (site) footer:
whileprintingrecords;
numbervar subtot;

//{@displgrtot}:
whileprintingrecords;
numbervar grtot;

Everything works I need to know how to combine sites information using the shared variable technique or may I should set up my report differently.
Current report

Beijing China
Xiango 0
Li 7

Paris France
Smith 1
Lee 4

Normandy France

Poe 5
Jung 8


California, USA
Opar 6
Lun 2



Desired Totals at the bottom
Sub total Europe + Asia 25
Total Asia 7

Total Europe 18

Total US 8

Total WW 33

What is the best way to accomplish this?

Thanks for the advice.

Cheers
R

 

[lbass]

Add additional variables to your {@totals} formula. Your grtot variable already calculates the "WW" total, so add:

shared numbervar applic;
numbervar subtot := subtot + applic;
numbervar grtot := grtot + applic;
numbervar Asia;
numbervar Eur;
numbervar US;

if {table.site} in ["France","Germany", etc.] then
Eur := Eur + applic;
if {table.site} in ["Japan","China", etc.] then
Asia := Asia + applic;
if {table.site} in ["California", etc.] then
US := US + applic;

Then reference them in the report footer, like:

whileprintingrecords;
numbervar Asia;

For Europe/Asia, add the two variables in the display formula:

whileprintingrecords;
numbervar Asia;
numbervar Eur;
Asia + Eur

-LB

 

[ReillyC]

Each of the region counts works beautifully but the final count ww is doubling.

I placed this formula in the totals formula but now the report footer ww total is doubling the amount. Should I place this total formula in a separate formula.



//{@totals} to be placed in the Group #2 (employee) footer:
whileprintingrecords;
shared numbervar applic;
numbervar subtot := subtot + applic;
numbervar grtot := grtot + applic;


shared numbervar applic;
numbervar subtot := subtot + applic;
numbervar grtot := grtot + applic;
numbervar Asia;
numbervar Eur;
numbervar US;

if {table.site} in ["France","Germany", etc.] then
Eur := Eur + applic;
if {table.site} in ["Japan","China", etc.] then
Asia := Asia + applic;
if {table.site} in ["California", etc.] then
US := US + applic;

 

[lbass]

No, it should be:

whileprintingrecords;
shared numbervar applic;
numbervar subtot := subtot + applic;
numbervar grtot := grtot + applic;
numbervar Asia;
numbervar Eur;
numbervar US;

if {table.site} in ["France","Germany", etc.] then
Eur := Eur + applic;
if {table.site} in ["Japan","China", etc.] then
Asia := Asia + applic;
if {table.site} in ["California", etc.] then
US := US + applic;

-LB

 

[ReillyC]

Ohh!1 How stupid am I. I just fixed it. It works great.

Once again thanks!!!

Cheers
R

 

[ReillyC]

I have one more question.

I have 13 other subreports to link to this main report. Unforunately each report has a separate and unique criteria. Anyway, am I correct to say that I will have to create a separate formula for each subreport in the main report to display subtotals group totals etc.

Cheers
R

 

[lbass]

If you want to summarize the specific results of the subreport in the main report, yes. I think you might be using the subreport as a result of some recommendation made in a previous thread, but you could probably be doing this report without all these subreports.

-LB

 

[ReillyC]

I don't see how I can have one report.
I have a total of 14 subreports/columns counting the number of application based on very different criteria for each column.
Snapshot
The data for the first 4 columns stem from three tables and the remaining stem from completely different tables.
My main report and subreports are grouped by the site then by employee
Here is a sample of the first four columns:
Table.app, table.events, table.site

For Year 2005

Apps Apps Pending Total
Rec'd Clsd Apps Pending with the event TBD,

US
Smith 0 5 4 0
Rose 0 3 1 2
Kim 1 0 0 0
Lin 0 0 0 0

Total 1 8 5 2


France
Sith 0 1 2 0
Rost 0 7 0 2
Kimb 1 0 0 0
Lout 0 0 0 0

Total 1 8 2 2

Summary of Totals
France 1 8 5 2
US 1 8 2 2
WW 2 16 7 4

Column Definitions:
App Rec'd = all apps in year 2005

Apps Clsd = apps in year 2005 and status = napp, clsd, combined

Pending Apps = apps in year 2005 and archived = 0

Total Pending with the event TBD and FIFI = apps in year 2005 and status = TBD, FIFI

Open to suggestions

Cheers
R

 

[lbass]

I didn't mean to second guess you. It may be that subreports are the best solution in this case.

-LB

 

[synapsevampire]

It would depend WHERE in the main report you have these subreports, I couldn't see where you posted such essential basic information to design, nor what type of database.

Given that you don't understand the importance of such information, I would guess that you might be better served to use subreports as the complexity of building data soruces is greater than plopping in multiple subreports, however subreports are less efficient, especially if they are at the group or detail level, and don't promote reusability.

As for the subreports using different data, that's where subqueries, joined Views, SPs, or UNIONS come in, and the database will be much more efficient than Crystal, and coding will be greatly reduced within Crystal

I would suggest speaking with your dba about this, or when posting in the future, include the basics.

-k

 

[ReillyC]

I appreciate the advice. I was just trying to be thorough.