mulitple server each with Crystal reports

[Former Member]

HI We have a project COO currently gets 1 reports from each of 12 locations, Financial data. It reflects to the previous day financial and distribution data.

Each is a crystal report. Same report different locations. They want to extract all the data to the HQ location.

In your experience, is there any way to run the Crystal reports from HQ and get the data in via the report, would this not save the extraction project costs and at no real gain.

if so what sort of connection is supported, via cloud perhaps, how can we code this.

[Former Member · Accepted solution]

Thanks Brian, it is a DB2 environment. Did you use SSIS with the DB2? That would be the best way imo.

[vitaly_izmaylov]

Are 12 locations withen the same network domain?

In what database the data stored at the locations?

One of the option is to store data form 12 locations in the same datasource, could be in Cloud. But there are too many variables to make a decision that requires investment. You better have consultant to help you.

[ido_millet]

At least one of the Crystal Reports 3rd-party tools listed here allows you to automate the process of:

1. running the same rpt against different data sources (or using saved data in the rpt since it sounds like you already have 10 rpt's delivered with data).

2. Exporting via ODBC and APPENDING the data from each of these reports to a single database table.

You can then use the merged data as a data source for consolidated reporting.

[Former Member]

I believe it is now called SAP BODS. If anyone can discuss how this works with multi location that would be very appreciated.

[former_member292966]

Hi Paul,

I didn't use SSIS when I worked with DB2, I worked with Data Integrator from Business Objects. It's now called something else. I am now using SSIS for my reporting databases and it works fairly well. Once you create your job it's easy to schedule. If you already have SQL Server Enterprise then it's free and you don't have to buy something else.

Good luck. I found my relationship with the DBA got better when i wasn't interfering with his DB2 tables.

Brian

[former_member292966]

Hi Paul,

What you are doing is possible from HQ and 1 report if the databases of all 12 locations are accessible from HQ. You could look at an enterprise reporting platform like Crystal Enterprise. Schedule the report 12 times, each time hitting a different database location and sending it to the COO.

Something else you can do is grab all the data from each of the 12 locations into a central reporting database then have the report formatted to group by location so everything is in one report and sent out. Having a reporting database that doesn't tie up the production database makes DBAs less angry. I worked in a DB2 environment and querying the database directly for the reports quite often locked tables and affected the performance of the production database. Extracting that data to a separate reporting database at off-peak hours removed any issues with performance.

Trying to query individual databases can get messy and managing it can get difficult. A more centralized reporting method will help minimize performance issues. If you are using SQL Server, look at using ETL tools like SQL Server Integration Services to extract the data and merge them into the reporting database.

Good luck,

Brian

[Former Member]

HI If they are in the same network domain, then one job can store them in same source? Does this make best practice to you?
I would want to use Cr then as one program and parsing out results according to location id.