Set Datasource Location from tables to an SP

Saturday 30 June 2018 @ 11:14 pm

A customer had a report that read a handful of tables. He decided to write a stored procedure to return the same data as the tables. He planned to use “Set Datasource Location” to point the tables to the stored procedure, but soon realized that it wouldn’t work.

He could set the location of any ONE of the tables to the SP, but not all of them. If he tried to set the location of the second table to the same SP, Crystal would create a second instance of the SP and link it to the first, matching the links used in the original tables. I have never found a way to to set the location of several tables to a single query/view/SP.

So what is the best solution if you have to do this? Identify the table that provides the most fields to the report, and set the location of that table to the SP. That will map the largest group of fields automatically. The other tables will have to be removed and their fields added manually.

Another thing to keep in mind is that it is usually easier to change formulas than it is to delete and replace database fields on the report. So if one table is used primarily in formulas while another table provides raw fields for display, you might want to set the location of the table that provides the raw fields. Jamie Wiseman wrote an article years ago about creating reports that could be easily set to a new datasource. His method was to create a simple formula field for every database field used, then use those formulas for everything else in the report.  I sometimes do this, and I call these “feeder” formulas.  With Jamie’s method you can delete and add tables and then simply update the feeder formulas as needed.

Comments are closed.

Recrystallize Pro

Crystal Reports Server