Link 2 tables that have different aggregate levels

[John1Chr]

Is it possible to link two tables that have similiar data except that one is more detailed? I want to "crunch" the data and then link. In microsoft access I would run a table against a query that agreggated the similiar data at the level in the table and the link worked fine. Can you do something similiar in crystal 8.5?


I have two tables. One table has employees which is much more detailed then the department table. There are Four fields which I can link the employee table to the department table. (1.)Year, Budget Org, Budget Fund, Class org. The issue is that the employee table has many more records because it is not at the department level which contains many employees but that is not a field and it is grouped by department level.

How can I link the tables without it doubling/tripling up? I'm going after compensation amount. Is this possible in crystal using a link command or index etc. or should I use a subreport?

 

[Madawc]

You link A1 to B1a, B1b, A2 to B2, A3 to B3a, B3b, B3c etc?

The standard answer in Crystal is to group, suppress the detail line and then show the details in the group header or footer. A1, A2 and A3 would each be groups.

If you want to group the same way for two different sets of details, that needs either a Crosstab or a Subreport. Worth finding out about Crosstabs, though in 8.5 they are limited.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[John1Chr]

Mad,

I started down the path of subreport and I have the basic report functioning. I'm hoping that I'm not too limited with where I can place the formula - has to be below. I will search and post if needed if have issues hiding the subreport, etc.

 

[John1Chr]

Is there a way to hide the subreport? I cannot turn off the grow command or it won't work. What I would like to do is feed the subreport total(employee total) by Deptid which begins to explode in the GH1a but I just want it to total in the GH1b.

G1b
DeptID DeptTotal Employeetotal Diff
0001 100 90 10
0002 80 40 40

The employee total is comiing from the employee table in the subreport. I'm grouping by Employeetotal in the subreport. I just want to feed the employee total into the GH1b. Problem is that it grows in the GH1A. Maybe subreporting won't work?

 

[John1Chr]

I'm thinking that the underlay followig sections might work in this case....using that function in GH1A

 

[John1Chr]

I'm having trouble passing the variable doing this...not calculating.....darn it, too early for a drink.

 

[lbass]

What fields are you grouping on in the main report? How are you linking the sub to the main. Yes you can make the sub disappear, but first let's see whether you have this set up correctly.

-LB

 

[Madawc]

You can suppress the visible detials in a subreport using section suppression. Right-click on the section and choose Section Expert. This can also be made conditional, you may find that useful for other reports.

To pass data back, use SHARED variables. For a date, put a formula field in the subreport like

whileprintingrecords;
Shared dateVar    
V_Today := {LoadStatus.LastDLoad}

To access it in the main report, create another formula field with

whileprintingrecords;
Shared dateVar 
V_Today := V_Today

Similar for other types of data.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[Madawc]

PS. The recipient field can only be used in a section below the section that has the subreprot.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[John1Chr]

Thanks - I'm going down that route and thinking that I will have at least 4 subreports. I find that I can "compress" tables and link easily that way. Any calculations have to be below the subreport, correct? I'm using crystal 8.5.

 

[Madawc]

Yes, fine.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[John1Chr]

I have two subreports (one for employee grouped and the other for dept grouped at the same level - ie. Fund, Year, AGY) within a header of a mainreport - one subreport doesn't have a value for Budget Fund ie the employee subreport but I want to pretend it has a 0 so that I can calculate the difference between the two subreports.


What I am seeing is that if the employee subreport is null for the fund then when I calculate the difference between the two subreports the calculation where the employee subreport is null will use the value in the preceding header to calculate.

Is there a simple command if a subreport is null to make the formula see a 0 and calculate even if the grouped record is null in one of the subreports?

Currently I am using shared variable to feed to the main report:

employee subreport-
WhilePrintingRecords;
Shared CurrencyVar SubSalProj:= Sum({@Sal Projected})

dept subreport-
WhilePrintingRecords;
Shared CurrencyVar EXPBUDSAL:= Sum({@CURR BUD SAL})

 

[John1Chr]

i got it to work by placing if isnull(@value) then 0 else @value in the formula in the subreport.