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.