petercotting@btopenworld.com

Can anyone give me a tip on creating a report in Crystal throught the CE drivers for Baan that select data from tfgld201 and tfgld203 and references tfgld008. This is in BaanIVc4.

Selecting data on tfgld201 will be on year & period but tfgld203 is only on year

Also when printing the report no P&L data is printed as no opening balances exist!

Crystal supplied sample reports one of them an extended TB. But when looking at the tables used tfgld203 is not included.

I think someone could not work it out!!

I am not familiar with the the Crystal/Baan drivers but the problem you are running into is to do the type of sql join that you have set up.

It sounds as though you are only selecting records if the account number exists in tfgld203. For P&L accounts this will never occur.

Terry

We have the Crystal Enterprise Solution Kit for Baan and have used it for the past 5 years. It's primarily limited to the SQL that you can use with Baan tools, and that is very limited. I have found that sometimes to get around the limitations with Baan SQL has meant using Oracle's native capabilities. It has provided me with greater flexibility in combining tables. I had this same problem presented from our finance department regarding these tables. Here is the Oracle statement that I constructed for a VIEW that I created for them to use. We have Crystal Reports 10, and it lets you can construct this SQL statement directly in Crystal Reports if you have Oracle and have defined an ODBC connector to your database. You may have another database, but I hope this helps give you some other direction to try. The UNION ALL statement allows you to combine the rows of both tables rather than trying to link them, which is not practical for what you are wanting.

select a.t$cono, a.t$year, 0, a.t$leac, b.t$desc, a.t$fobh, 0, 0
from ttfgld203100 a,
ttfgld008100 b
where a.t$leac = b.t$leac
union all
select a.t$cono, a.t$year, a.t$prno, a.t$leac, b.t$desc, 0, a.t$fdah, a.t$fcah
from ttfgld201100 a,
ttfgld008100 b
where a.t$leac = b.t$leac
and a.t$ptyp = 1

Gilbert Guymer
Database Administrator
Lufkin Industries, Inc.

Also need to take care of sublevels from tfgld008 , as tfgld203 and tfgld201 contain higher level sublevels too and u wish only 0 level or particular level accounts.