Joined: 17 Sep 2010
Location: United States
Online Status: Offline
Posts: 3
Topic: I need help!!!! Posted: 17 Sep 2010 at 7:52am
First off... I am new to Crystal Reports so bear with me... and I apologize for the length of this but I know of no other way to explain without being this specific.
I am working on a project where we actually have a couple of things happening. First, we are moving from a DB2 database to a SQL Server AND upgrading our application (Accpac) to a newer version. We use Crystal Reports for some database reporting and are migrating our existing Crystal Reports. I do not know what version they were developed in but I am using V11 for the migration.
In the old Accpac version, tables like the Order Header (OEORDH) had optional fields that could be used at the users discretion. For instance, they use a options field to store a Promise Date and another field to store a Master Customer code. In the new version of Accpac, these optional fields have been moved to another table called Order Header Options (OEORDHO). Each option has its own record where the key would be the Order Number and a option key.
Using the examples above, there would be a record for order number XXXX and the option key of PROMDATE. In this record would be a value field with a date in it. There would be another record for order number XXXX and a option key of MASTER with a value of WALM.
Now for my problem... I have reports which use these optional fields and I am having problems figuring out how to get 2 records out of one file with one read.
In the Database Expert, I can select the OEORDHO table and rename it to OEHOPROM and then add OEORDHO again and rename it OEHOMAST. I can define the links for these two tables to be outter joins but I can not tell it that in addition to using Order Number as a key, it should also use the constants 'PROMDATE' and MASTER'.
Understanding that this must be totally confusing, if anyone needs more explanation, just reply here and I will see if I can do any better.
Joined: 17 Sep 2010
Location: United States
Online Status: Offline
Posts: 3
Posted: 17 Sep 2010 at 8:33am
Thats a good question... I do not know. While I work in NC, this server is located in Mexico. I do not have any access to it other connecting with ODBC.
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 17 Sep 2010 at 9:12am
IMO
if you can make a view/sp that would be the way to go.
if not you could possible do something similar using a Crystal Command. as a warning I have found commands to have much worse perforamnce in my environment.
basically if i understand your issue you need to find maximum values from your sub tables and then join those to the main table which youc an do via a SQL view/sp or the crystal command.
Others might be able to make another suggestion...
Joined: 17 Sep 2010
Location: United States
Online Status: Offline
Posts: 3
Posted: 17 Sep 2010 at 9:25am
I appreciate your input. I looked at the Crystal Command but had not given that a try. Good to know about the performance. I'll keep the Views and sp in mind. Thanks
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
