Blogs
|
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.
Jamie Wiseman
is a Crystal Reports demo creator for SAP - Business Objects
| Showing messages 1 through 7 of 7. | ||
|
|
||
| Titles Only | Main Topics | Oldest First |
- Thanks for the tip
2010-04-15 18:43:22 Timothy Conway Business Card [Reply]
This is a very good tip. I've often written reports with very time consuming formatting only to have the data source change in the way you describe. One particular case is having to go to a view or command object in order to have a Union Query. More than once I would finally give up and have to do all the formatting over again.
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
- Formatting of fields on the canvas is a benefit for some reports
2010-03-18 09:22:13 Michael Wilkins Business Card [Reply]
If you are required to squeeze more information onto the page than makes reasonable sense, this technique allows you to avoid losing that formatting when you make changes on the back end. I have been using stored procedures exclusively for a while now; however, I sometimes need to change an old report where a lot of effort went into formatting the fields. Using formulas, allows me to change the back end data while maintaining the formatting of the report.
- Templates
2010-02-23 09:00:06 Carol Ann Whiteman Business Card [Reply]
I frequently change databases, and use Excel and Sequel Server on a single report. But, where I can really see the value in using this process is as a template for new reports. It will give me the opportunity to set standards and simplify the creation of similar complex financial reports.
- How does this help?
2010-02-23 05:42:48 Rob Richardson Business Card [Reply]
I do not understand the purpose of this. I can just add a new data source, remove the old datasource fields I have on my report, and put new ones on. I don't have to go through the rigamarole of replacing fields with formulas.
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?- How does this help?
2010-02-23 06:09:36 Steven Anderson Business Card [Reply]
I agree completely with Mr. Richardson. Worse, using the recommended method of formulas only, the user is not able to take advantage of the wizards when creating a report.
Crystal Reports' insistence on hard-wiring its reports to a data source has been a serious Achilles heel for years.- How does this help?
2010-02-23 08:59:15 Jamie Wiseman
Business Card
[Reply]
good points. you would only need to use this under certain conditions:
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
- Don't Forget Formatting Expressions
2010-01-22 10:20:17 David Deitch Business Card [Reply]
Whenever I have a table I want to remove from a report, inevitably I find a field was used in a formatting expression that I missed. So when you are replacing data objects with formulas, don't forget to look in section as well as report object formatting expressions!
| Showing messages 1 through 7 of 7. |