Data grouping problems

[CharleyBrown]

I am trying to create a Profit & Loss Report with 3 columns of Info: This Year Actual, This Year Budget, and Last Year Actual. The info is held in 2 separate tables, Actual and Budget, which have the same table structure and field names. I have linked the tables at every field except the Amount field.
I have developed the report using a lot of grouping and suppressing by formulas referring to fields in the Actual table. The report works well with the Actual data but as soon as I put in any fields in the Budget column referring to the Budget table, the whole report goes haywire and produces totally wrong figures, even in the Actual columns.
Can I unite the two tables into one using Crystal or does this have to be done using a stored procedure on the database? Do I have to use variables in the grouping formulae? Any other ideas?

TIA

 

[AndrewBaines]

Options:
a) Stored procedure/view
b) Subreports - messy but works
c) Union Query:
select fields, "BUDGET" as accType
from tableA
UNION
select fields, "ACTUAL"
from tableB

This gives you a field called accType which you can use to identify whether it's a budget or actual figure. Andrew Baines
Chase International

http://www.chase-international.com

 

[kenhamady]

Is there one row in the budget table for each row in the actual table?
How do you know which records in the budget table go with each record in the actual table. Is it by GL Account?
What is the database that they are stored in (ie SQL Server, Oracle, etc)? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[CharleyBrown]

Thanks for the pointers Andrew. Ken, yes it is a one to one match between tables based on the four GL Account Fields - Company, Division, Department, Account. Database is Progress 9.2 which is SQL based.

Regards,

 

[kenhamady]

You should be able to link these tables using 4 arrows, one for each of the field pairs you mentioned above. That will make one table out of them with all of the fields of both tables. Does that not work for you? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[CharleyBrown]

Ken,

No, I've tried all the combinations of linking the two tables but without success.

CB

 

[kenhamady]

What exactly happens? No data? missing data? Duplicates? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[CharleyBrown]

The 'Actual' amounts, which were correct before adding the 'Budget' formulae, change to incorrect amounts, and the results of the Budget Formulae are incorrect as well. I think the problem is that I am not using the raw data for each individual account - the formulae summarise groups of accounts and when I insert the budget formulae, grouping by reference to the budget table, this conflicts with the grouping of the report based on the actual table. Does that make sense or am I missing something completely obvious?

Thanks for taking the time,
CB

 

[kenhamady]

Does incorrect mean high or low? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[CharleyBrown]

Low, e.g. Actual Sales Revenue for the month went from 98K to 3K, but I haven't analysed how it got that way.


CB

 

[kenhamady]

Does EVERY account have a matching record in the budget table? Did you try using a left outer join from actual to Budget? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[CharleyBrown]

I'll try that next time I'm on site but in previous reports where a Left Outer Join has been required I would get a database error message and get chucked out of Crystal entirely. Since that didn't happen I was assuming that the matching was one to one.

thanks,

CB