Combining Data from different Databases

[ts04]

Can anybody help me with the following problem:

I have a database A (Btrieve) with the following data:

Prod.code amount date

232AB300 7 22032004
434EB900 9 23032004
...

And I have a table from database B (Access) with the following data:

Prod.code description

AB Product with specification B
EB Product with specification C
...

In my report I have a selection of each product code that exsist out of (AB or EB etc.) + amount + date. In a column next to it I want to display the discription from the table from database B that belongs to the specified code that is extracted from the production code from the table from database A.

How do I do this?

Thanx in advance,

Tanja

 

[Katy44]

I don't know what version of Crystal you are using and I have never heard of Btrieve, but you should be able to just set up a link to both data sources and select to use the two tables, linking as appropriate.
As it is, you will probably have to create a view to be able to get the first table in the right format - if the Prod code is always in that format you should be able to create a view that uses a substring, to just get the two letters in the Prod code field.

 

[ts04]

But how do I link the tables if the lookup value from table A is different then the value in Table B (232AB300 / AB) I can Extract AB from the field in my report but I can't use the extracted value to search table B for the additional data. I am not so experienced with Crystal Reports so maybe the solution is easy but not yet to me My version is CP 9 by the way.

Thanx for your help so far!

Tanja

 

[Katy44]

Is the Prod code always in this format?
Are the two digits in it always unique?
If so, you need to create some sort of view to give you the same data as the table, but with only the two digits in the Prod code column. You should then be able to link this to the prod code field in the other table in the usual way.

You can create the view in your database if you are able to do this, and then use this as your data source.

If not, you can create a Command in Crystal, which seems to me to be a Crystal view.

Choose "Database Expert" from the Database menu.

Choose your existing datasource to the BTrieve database.

You should see "Add Command" - double click.

You then need to write in this box, something like:

"
Select right(left(ProdCode,5),2) ProdCode, amount, date
from <TableName>
"

If you click OK you should then see that you get a command in you list of tables etc (it is possible to change the name).
If you then go tot he links tab, you shoudl be able to link ProdCode from the Command to ProdCode from your other table.

Please let me know if this works, I'm sorry if it's not very clear.

 

[ts04]

I can't add a command in any way with the database expert.
Your additional questions:

the prod code is always in this format.
The ANM1 is not unique. In my report I group al records with the same anm1/ am1 / eb1 (the alphanumerique part in the record). The numbers around anm1 etc. indicate further specifications which I dont require for the report.

I might have another way to add the addional information (description):
I can create a text file / ini-file in which I can search for the corresponding anm1 etc. and then I can have the lookup function to return the corresponding description.
I need an external file for the descriptions so I can always externely add extra prod codes with a seperate VB application. The problem that occurs in this solution is that I don't have the correct code for approaching ini-files of text files through Crystal Reports 9. The standard code that is used in Visual Basic doesn't work if I program in Basic syntax.
Can you maybe help me with this?

Thanx!!

 

[Katy44]

I'm sorry I really wouldn't know where to start with this!
Any ideas from soemone else?
I don't see why you don't have the Add Command functionality though - it is definitely available in 9.

 

[ts04]

Sorry Sorry!! I was beeing a little bit blind! I can see the add command.. but I can only use it for my acces database. The Btrieve database doesn't show the add command. Thanx for your help and time!

Kind regards,

Tanja

 

[lyanch]

You can't use add command when combining databases, at least not "off brands" for sure, and it is not available at all for certain connection types.

Outside of that, the easiest way to do this is to create a subreport to get the description from btreive. create your regular report to access, then create a subreport from btrieve with the description. Link on the prod.code (i know it isn't right.. we will fix it).

Then... go into the selection formula in the subreport and change it to:

{btreive.prod.code} = ("*" + {?prodCodeParams} + "*")

I would do a show SQL to make sure it is getting sent to the database.

Lisa

 

[lbass]

Using the subreport approach, I would create a formula for the Btreive data that extracts the letters:

mid({table.prodcode},4,2)

and then use this as the subreport link to the prodcode in the Access database.

-LB

 

[chelseatech]

Sorry I got into this one late.

When pervasive renamed btrieve to Pervasive/SQL they seemed to spend more money on upgrading the packaging than adding any SQL complience to the product.

I'm not a great fan of btrieve and have found it easier to extract the Btreive data into a real database and do all the reporting there. Even Access would be an improvement.

I took some large btrieve tables and imported them into SQL server. The same report went from 5 hours to 2 minutes in processing time. I suspect developers keep using it so they can keep their novell servers on site.

So my recommended solutions:
1) Set an auto transfer of your data into a "reporting database". Call it a Data Mart or Data Warehouse if anyone asks
or
2) Use CR9/10 and the ability there to join different databases. This can be quite slow to process.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[CrystalBart]

Thank you all for your help!

I haven't tried all alternatives yet but so far the tips are very usefull! About your opinion on Btrieve, Chelseatech, you are absolutely right! The low speed of the database irritates me over and over again. In this case my company also uses them to keep their novell servers on site I guess. I will follow your advise, it will probably help me with my further work in Crystal Reports with Btrieve data.

Thanx to all again!

Kind Regards,
Tanja

 

[ts04]

I see I have posted my last post under another name.

In case you all should be confused about the fact that I posted my last reply under another name is that I was working on the PC of my colleauge here at work. I forgot to log out and sign in under my own name! OOPS! So It was still me

Greetzz,

Tanja

 

[ts04]

Hey Chelseatech!

How can I import my btrieve databases into a SQL-server, I really need faster processing time on my database. Can I do this each time I restart my crystal report so the data in the "shadow database" refreshes?
The question might be a little vague but I hope you can help me.

Thanx in advance!

Tanja

 

[fredp1]

Hi Tanja,

Not sure on Btrieve, but maybe the first question is when you should import it.
If the report doesn't need to be a 'live' report, and a snapshot of the data is okay, you can setup a Job and DTS package to import the data overnight and maybe a SP to pre process the data. You should be able to do this as long as you get a odbc driver. If you have a OLE db driver, then you can make the report 'live' by using a Stored procedure (SP) in SQL Server.
Assuming then the time to import of the data is quick, you could get a SP written to copy the data from btrieve(you'll need OLEDB!) to populate a table, then do more processing in the SP to pget the results available in Crystal. In simple terms, the SP could send the exact results to Crystal.

how to do it: In SQL Server,(assume 2000), the select statement can read from a OLEDB data source. Here is a extract from the Books on Line for SQL2k
This is an example of a query against an Excel spreadsheet through the OLE DB provider for Jet.

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

Look at 'OPENDATASOURCE' in the help or 'FROM clause, FROM clause (described)'.
Get you db/programmer to write the SP for you or you could try the tek-tip forum Microsoft SQL Serverrogramming.

All my work I use a Job to run DTS packages to import data from AS/400 into Sql Server, then use SP's to pre process the data ready for the next morning so the reports can run from regular tables using a star schema... but i'm using a Datawarehouse tool which speeds up the development. This means that the data is to 24hours old but for various reports, but thats okay for us.
You'll need to decide what is possible(OLEDB available) and is the time taken practical to make it 'live' or over night.

Fred