Problems using views directly in Crystal Reports with multiple databases

Question

Working with Crystal Reports over a multiple database environment. In the past reports have been written un-generically (go figure). Trying to clean this mess up while providing new deliverables. Clients do not understand and always pushing for the shortest possible way and do not see the need for cleaning/tuning up existing reports to make them generic for all systems.

Have come across a number of reports built using a specifically written view for each report. The drama comes when cloning a view based crystal report and then trying to run the report via crystal designer on a database different from the original database the report was built on. The report holds onto the original view object. Runs but delivers no output. At first I thought there were grants issues with the underlying views.

The solution is to update the view reference in the "Set Datasource Location" panel.

Wondering peoples opinions on using views in crystal reports. And if they have hit this situation before.

Obviously views have their uses but I do not see the point of adding another layer between a report and the sql statement(s) by making a specific view for the report when the sql can be plugged straight into the report. Only database procedures make sense to me when you are wanting to do more complex calculations and output.

Notes

The report runs fine from the application menus for the differing
systems (without changing the crystal datasource) SAP B1.
Those reports using views indirectly are NOT affected by this issue.

Pseudo screenshot: Set Datasource Location (panel)

Current Datasource:
Report
   server
      properties
         initial catalog: other database

      view
         properties
            catalog: original database

Arvo Arvo · Accepted Answer · 2011-12-06T08:14:00

From our experience using views for multiple servers/databases (or even tables with N-N relations) is preferred way. If anything changes in database layout or servers configuration, what you prefer - alter one view or multiple related reports?

Of course it can be related to fact that our reports are designed to work on single database. Our application always replaces all included datasource locations (tables, views) to current database, this allows easy reporting on different databases (including MSAccess and MSSQL ones). Such approach excludes cross-database reports, unless using views. Our reporting uses OLEDB interface too, not linked to datasources-connections, opened in application.

There are sometimes problems in designer, when it is opened directly with report file; set datasource location usually corrects them (you may need to set location for specific views too). If opened through our application, which saves report copy with corrected database information, designer works flawless.

And there are sometimes problems with underlying views, which cannot be easily transferred to different database layout. Fortunately view definitions can be altered without changing output layout :)

About customers - they of course do not want to change their working reports. You can be sure that you introduce some errors when replacing their reports with more universal ones - who wants that? No one.

Problems using views directly in Crystal Reports with multiple databases

1 Answers