We are using Crystal Reports to generate Phoenix reports.
At this stage we experience the following issue that makes live very difficult for us:
We have 30 Service Points / Branches and at this stage we have to create and schedule a Crystal report instance for each one separately in order to generate a branch based report e.g. a report is dropped with the individual branch info only.
When we try to schedule one report all branches information is part of that one report and each branch has to search and select its on info for printing etc.
This results in an overhead on the bandwidth as well as unnecessary steps on the user side, hence the scheduling of individual reports per branch.
The individual scheduling of reports results in a maintenance and admin nightmare since report changes have to be done 30 times and if a report has to be rescheduled 30 instances have to be changed.
In effect we only have 60 reports but due to the above this results in 1800 Crystal report, which puts a lot of stress on the server resources since multiple queries have to be run (each one doing table scans etc.) instead of one only.
Our requirement is that we want to create/schedule and maintain one Crystal Report instance that generates one database query and result set, but that generates separate reports (.rpt’s) per branch.
Can anyone provide us with suggestions or a solution how we can resolve this problem.
Your assistance and feedback regarding this matter will be appreciated.
The suitable solution for your 30 reports/branches is actually a small data mining solution.
The proper way is to create a separate table, which holds the information. The Crystal Report retrieve data from this table instead of the source table.
Certainly this will add several extra tables into your database, however, the information is only inserted once a day for all branches. If each reporting table is very different, the most you will need to add 30 extra tables. These tables should cover all branches.
Data mining solution has some disadvantage compare to real time report. For example:
1) Create extra reporting tables
2) Extra management on reporting tables
3) Extra space for reporting tables
4) Data correction done on old data will not be reflected
5) Need to add extra schedule job to insert data into reporting table
6) Extra troubleshooting on reporting tables, when data not available
Anyway, the availability of reporting table will speed things up, and reduce server load. Each branches only extra data from reporting tables, which are summarized with the exact data they need. The performance improvement is impressive.