Accessing mult. databases in a single report

[src2]

I'm usimg CR 8.5 and I would like to access 2 databases in a single report. One database is active records and the other is archived records. Both databases are connected using ODBC and have the same structure and field names. Basically what I want to do is to be able to create reports that connect to both databases so that I can report on data no matter where it is located. Any suggestions?

Thanks

 

[synapsevampire]

Nothing prevents this, although Crystal isn't the best database engine, and joining or Unioning within crystal is very inefficient.

Try to create the datasource in a Stored Procedure or View on the database itself.

-k

 

[Pluck]

crystal doesn't like you to do this but I do it all the time. If I am NOT using stored procedures you can bring the various tables you need into the report. The ODBC will be connecting to either the current data OR the archived data.

Once you have this all set up it is as easy as going into the sql that is generated by crystal and changing the database names.

Like I said I do this all the time. BUT... best way is to use stored procedures.

Hope that helped.

 

[src2]

I haven't used stored procedures so could someone give me an example? I'm not sure how you would use a view. Isn't a view within a database? If it is and I'm trying to use 2 databases I'm not sure how that would work. An example would be good also.

Thanks

 

[Pluck]

The following is out of the SHOW SQL. The two databases on the same server but are separate databases that are mirror images of each other in structure. The only difference is one database has daily info the other archive info. I make my connection to the daily database and make my links. I then manually change the sql to point to the archive data I want.

SELECT
GROUPS."GROUP_CODE", GROUPS."GROUP_NAME"
FROM
"merrin_mic"."dbo"."GROUPS" GROUPS, // daily db
"merrin_arch"."dbo".ARCH" ARCH // archive db
WHERE
GROUPS.RECID = ARCH.RECID

 

[src2]

Pluck

Does this allow you to access and include data from both databases at the same time?

 

[synapsevampire]

src: Sure, depending upon your database, but this is database dependent, and you're asking for an example without even mntioning the type of databases involved, much less version info.

If you're not sure what type and version of databases you're using, contact someone in IT. If you have a DBA, they can probably do this for you very quickly.

Views and Stored Procedures are basically SQL. Stored Procedures generally enjoy more functions and flexibility, and are also precompiled so they execute faster.

For your purposes a View would probably be fine.

-k

 

[src2]

Pluck

I'm sorry if I'm being difficult or dense. I didn't mention the type of database because I was looking for a generic solution that could be applied to other types of databases as well.

The database I'm trying to connect to at the moment is Lotus Notes R6. I'm really connecting to a view in each of the databases.

I have a question about the sample code where it says:
WHERE
GROUPS.RECID = ARCH.RECID

I'm not sure what I'd replace this with since there isn't really a link between the two databases. Both database records use a ticket number as the primary key and there wouldn't be any corresponding records since one database contains active records and the other contains archived. I assume I'd just leave the where clause the way I found it when I looked at the SQL that was generated. What do you think?

 

[Bertrum]

Oh come on peeps - why all the technicalities when you could just use a subreport to access the other datasource? That's the simplest way of looking at separate databases you will find. If you're using any parameters then you can just pass them across from the main report to the child. Easy as milking a cow

Hope that helps

 

[FVTrainer]

Bertrum:

Of course one can use subreports, but they are not a particularly good solution in many instances. And looking at src2's situation, I doubt if subreports are a good fit. I'm quite familiar with databases where current transactions and historical transactions are in separate tables/databases, and often one wishes to combine the data and do things like group it by vendor and, perhaps, sort by order amount or product rather than order date (which is generally the driving factor on active and historical tables. In other words, the original location of the record has no bearing on how you want to display it in the report, and, in fact, gets in the way). And in the situation where you use subreports, doing totals across the subreports and main report is far more cumbersome than simply getting your data together outside of Crystal and then passing Crystal a single dataset.

So I would echo synapse's comments. I think in terms of a general approach, it is far better to depend on your database to collect the recordset for Crystal than to have Crystal itself try to do a lot of heavy lifting.

If Lotus Notes itself doesn't allow for a view across databases that would bring the two tables together (it's been a long time since I've worked with Notes so I can't say), and if src2 doesn't have access to something like ReplicAction which would be able to pump the two views into a single table in SQL or Access, then using Crystal as the query engine may be the only choice. But it should be the last choice.