Crystal Report Server: connect to DBs with same name and schema

[satech]

Hello world, first post here!

Plan to evaluate Crystal Report Server. Before evaluation, want to know if the product works well with multiple DBs with same name and schema.

Basically, there is an application installed on multiple sites. As it's the same application, default database name, login credentials, schema, are all the same.

My goal is to retrieve data from multiple databases, and present the data in one report. So the report will need to have multiple database connectors.

If the goal is only to generate a report, and no need for scheduled report/live office, etc, will Crystal Report alone can do the job?

Thanks.

[DonWilliams]

When you use multiple databases/Servers Crystal Reports has a Subreport function that will allow reporting off of multiple data sources, one per Subreport. So you could access those other server this way.

As for security it's all handled by the DB Client, CR doesn't use DB Security features, it expects the connection to be configured before reporting off of the dB, and The user account being used must have permissions to access the data, if it doesn't then the report will fail to connect.

If you need access then you need to get permission by the DBA, nothing CR can do to get around that.

Not sure how you would create a database on CRS, CRS has a CMS database but that's not a Reporting Database so I don't see how that is going to help, you need permissions to the dB, end of story...

[satech]

Thanks Dell for the information!

I cannot create views on existing database, as it will void the service agreement. But I can create a database on the CRS that has views accessing other databases. I will give it a try.

The challenge is, among all of these applications, one application's SQLite database is encrypted and I don't have the key. CR run-time shipped with this product can access its database, however (how?). My plan is to use one of the RPL files, duplicate the database connector multiple times, and then access all instances. As I don't have the key, I cannot query this application's database via SQL (but when editing a RPL file, I can add my own SQL and it works fine). Maybe I have to create x copies of the same report, one report per database, export data to Excel, and then run a consolidated report based on data from that Excel?

[DellSC]

Hi Minghui! Welcome to the Community!

Crystal Server will let you configure which database a report connects to in the report's Database Configuration after you publish the report, but you would have to have a separate report (design once, publish multiple times) for each database.

Crystal Reports will let you connect to multiple databases in a report and then Crystal Server will let you specify all of the connections in the Database Configuration after the report is published - you would have to design the report to pull data from all of the connections in the same run. However, reports designed this way tend to be very slow because Crystal can't push joins or filters to the database for processing when there are multiple connections - instead, it will pull ALL of the data into memory and apply the joins and filters there.

I have had similar requirements from my clients in the past and have found that the best way to avoid the slowness is to do the following:

1. Create a database link from one database, which I'll call the "master db", to each of the others.

2. In the master db, create views that pull the data from the databases that are linked - this way Crystal will see them as tables in the master instead of tables through separate connections and it can push the joins and filters to the database where they can be processed more efficiently.

3. If you want to union the data, you'll need to create a single Command (SQL Select statement) that pulls ALL of the data for the report. See this blog for more information about working with Commands: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

-Dell