Setting a Crystal Report to Any Datasource

Monday 12 April 2010 @ 10:26 am

It is usually pretty straightforward to move a report from one database to another.  You simply use the “Set Location” feature in the database menu.  However this can be a challenge when the table configuration is completely different.  And it gets very difficult if you are trying to set the location of the report from several tables to a single command, view or stored procedure.

This is because the set location feature was designed to keep all fields within their original table groupings.  This means that if you are using 5 fields from table X, and you want to set the location of 3 of them to table Y, you won’t be able to set the location of the remaining 2 to table Z.  This restriction has always puzzled me.  The “set location” process is sophisticated enough to allow you to map a field to a different field name and even a different data type, but for some reason not to a different table.

So I was a excited to see an article on the SAP website that shows a method for solving this problem.  Initially I was disappointed at the solution, which has you to take every field you are going to use in the report and write a corresponding formula for that field.  You use the formulas instead of the fields themselves.   That way you can change your datasource and ‘map’ the fields to any table, simply by changing the formulas.  So I filed it away.

But Mike Adel of Kaiser Permanente thought it sounded promising and asked me about it, so I decided to give it more thought.  I realized that I already do this whenever I start reports with specifications that  “evolve” over time.  I identify all the fields that will get heavily used in the formulas and placed them each in their own formula. Then I use those formulas for grouping, calculations, etc.  That way when the specification changes, it is an easy fix to change the fields used.  I guess that post is just taking the idea to it’s logical conclusion.

So if your environment is going to be fluid, or if you want to create a report that can run in many different environments, then this is a viable method.  But if you are in a stable environment I am not sure if I would take the time to do this for every report.

(For examples of my most popular formulas, please visit the FORMULAS page on my website.)

One Response to 'Setting a Crystal Report to Any Datasource'

  1. Andrew - May 10th, 2010 at 12:20 pm

    I strongly recommend using formula fields on a report for several reasons, not the least of which is the one you illustrate, of how easy it is to change the data source for the report.

    Other reasons are:

    1/ Formulae can be used to separate the visual aspects of the report from the data. They can be used to set up visual templates for reports. The layout and formatting is determined by the formula fields that remain in place on the report independent of the data. The data behind the formula fields are determined by the content of the formula that can be as simple as a copy of the field, “formula = {STS_ARRAY_LIST.ST_ID}”, or something more complicated.

    2/ The formula can be used to make final adjustments the data in the field before display, e.g.
    • ROUNDing, ROUNDUPing, etc.
    • calculating a percentage or something more complicated,
    • Scaling, currency conversion,
    • Manipulating strings to make them more meaningful, e.g. extracting the last few characters of a long serial number if the first part of the number is always the same, etc.

    3/ Copying formats between formula fields is easier. Crystal only allows you to copy formatting between like types of fields, e.g. text to text, formula to formula. If all of the fields are formulae you can copy any formatting between any fields.


    Andy Chisholm

Leave a Reply

Recrystallize Pro