Setting a Crystal Report to Any Datasource
Want to make your exsting reports (or reports that you will be creating) easier to adapt to any datasource? Want to make a report more portable or more like a template?
Many of you have crystal reports that are run off of tables and want to set these reports to a stored procedure. Or they are run off of a universe and want to set them to tables. Some of these datasource migration tasks can be difficult to accomplish.
Making a report portable or adaptable to any datasource involves replacing any fields on the report canvas (or any fields in summaries, condtional formatting, cross-tabs or charts) with formulas.
Using the steps below will ensure that you are able to set your report to any datasource (tables, stored procedures, commands, universes, etc.) and ensure that none of your fields, summaries, charts or cross-tabs drop off of the report or cause errors.
This method is very handy if you are also designing a report where the database structure is frequenty changing or hasn’t been built. You can even start building a report without any datasource.
A) If you have an existing report
If you have reports that you need to be more adaptable to a new datasource this can be a bit of work depending on how many fields you use. However, in many cases the extra work could prove worth it if you have hit a wall trying to change the datasource.
1) in the Formula Workshop create a new formula for every field that you have on the report canvas…for example, if you have {Customer.Country} on the report canvas, create a formula called Country that has syntax of {Customer.Country}.
2) while still in the Formula Workshop do the above step for any fields that you will have on your charts and cross-tabs.
3) on your report canvas replace all of your fields with the applicable formulas.
4) on your report canvas edit all of your summaries to use the appicable formulas instead of the fields.
5) in your charts and cross-tabs replace all of your fields with formulas.
6) do the same for any conditional formulas on the report that use fields directly.
7) after you are done the above steps, close the Formula Workshop and ensure that you are in Design mode of crystal and not in preview mode…this will avoid a lot of records coming into the report or database errors when doing the steps below.
8) you can now add the new database tables or stored procedure or universe or command to your report using the Database Expert wizard.
**** do not use the Set Datasource Location wizard…just add the new datasource to the report
**** do not remove the old datasource yet
8) in the Formula Workshop edit all of your new formulas by replacing your old datasource fields with your new datasource fields.
9) replace any old database fields in your record or group or saved data selection formulas with your new database fields.
10) in the Field Explorer, double check that there are no old database fields with a check mark beside them…if there are, find out where they are…right click on them and use the Find In Formulas option if you are using a newer version of crystal reports.
B) If you are creating a new report
1) for any field that you want on the report canvas, create a formula that contains just the field. do not add any fields onto the report canvas, just the formulas. if you do not have a datasource yet, put a dummy value into the formula.
2) for any field that you want in a cross-tab or chart, create a formula that contains just the field or a dummy value.
3) you can still use fields directly in the selection filters as these are easy to replace when adding a new datasource to the report.
if you design all of your new reports using this method, you will make your reports more adaptable and more portable.
Could you please discuss how to use DSNs effectively with reports? It appears to me that if you specify an ODBC (DSN) connection on a report, CR XI retrieves the name of the database from the DSN and stores that name internally. This defeats the whole purpose of DSNs. If I build a report using a DSN named "ProductionDatabase" that points to a database on my computer named "Test Database", and then I move my report to my customer's system, where the DSN named "ProductionDatabase" points to a database that is named "XYZ Corporate Database", the report will not work because the name "Test Database" is embedded in it. I have been screaming about this problem for years, and nobody has ever answered. Could you please answer?
Crystal Reports' insistence on hard-wiring its reports to a data source has been a serious Achilles heel for years.
a) your database structure varies quite differently than the one that the report was designed off of...if it is just a renaming procedure then the Set Datasource Location should suffice.
*** b) you have charts and cross-tabs on the report as mentioned earlier...in these cases, if you remove your old datasource first, these objects will drop off of the report canvas...a huge pain if you spend a lot of time designing these objects. if you use formulae instead, you can remove the database completely and these objects remain on the report.
c) you are designing a report off of an xls data dump if you don't have the luxury of a dev database. setting location into a multiple table production database then becomes the issue.
d) if you wish to change your report from multiple tables to a singular object like a command.
e) you are building a template report that you want others to use that you have no idea what their database structure is.
f) you can also design a report with no database connection at all. if you are offline you can still design a report using formulae as placeholders.
if you are just doing simple design with the report creation wizards or none of the above apply then there is no need to use this technique at all.
i use cr every day and have been designing most of my reports (the more complex ones) with formulae for many years now and it has made my working life easier.
as for why the data source is hard wired i would not be able to guess. that would probably be a good post for the cr - database connectivity forum.
jamie
One thing that did work. Some years ago I had to change from single tables to union queries by a Command object. I was able to make the data source change using Crystal Enterprise where you can save the command object in another report, then using the Crystal Enterprise change the data source. Don't remember all the details exactly, but it did work.
Thanks
Tim