During the Crystal Reports development, it is very common data source (database, universe) definition modifications impact the fileds to be presented in the RPT, such as field renaming, data type changes, or even deletion of a specific data field. How can we synchronize the changes from the data source into RPT to make sure the RPT is line up with the latest definition?
Problem Description
I have a report.The report retrieves several columns of a certain entity table in data mart to be placed in the report details. One day, I am noticed by the data mart developer that he / she revised a column naming of a specific entity table and requested a regression on the report side. I open up a report RPT and try to refresh it by hitting F5 key on the keyboard. An error like below prompts up:
At the same time, I try to refresh “Database Fields” under “Field Explorer”, but it doesn’t work, either.
So, what can we do?
Verify Database
What we will need to do is always using “Verify Database” command from “Database” menu to refresh data source definition and remap data field if necessary. When you choose “Verify Database”, the program checks the active databases or universes, and reports. If it detects changes, the report must be adapted to prevent errors.
With the report active in the Design tab, choose Verify Database from the Database menu.
The Verify Database message box appears.
If the program detects no changes in the active database, or the changes do not impact the report data, the message box displays this message: “The database is up to date.” In this case, click OK and return to your work.
Crystal Reports automatically adapts the report (and does not display the Map Fields dialog box) if it detects any of these changes:
- Fields have been added to the database or universe.
- Fields that are not used in the report have been deleted from the database or universe.
- Field positions have changed in the database or universe.
- Data types have changed for fields in the database or universe.
If the program detects a change(s) in the active database impact the report data, the message box displays this message: “The database file [“table name”] has changed. Proceeding to fix up the report!”
Click OK, the program displays the Map Fields dialog box. It indicates either of these types of changes happen to the database or universe:
· The name of a field that is used in the report has changed.
· The database or universe has been upsized from a PC data source to an SQL data source.
An Example
- Create a view V_REPORTS for testing:
CREATE OR REPLACE VIEW DFRPT.V_REPORTS
(REPORT_ID, REPORT_TITLE, REPORT_TYPE, STATUS)
AS
select report_id report_id,
report_title report_title,
report_type report_type,
status status
from dfrpt.reports;
commit;
- Create a RPT based on V_REPORTS:
- Now that we change the column name from STATUS to STATUS_BK for the view in database:
CREATE OR REPLACE VIEW DFRPT.V_REPORTS
(REPORT_ID, REPORT_TITLE, REPORT_TYPE, STATUS_BK)
AS
select report_id report_id,
report_title report_title,
report_type report_type,
status status
from dfrpt.reports;
commit;
- If we refresh (F5) the RPT without reopening it, an error message will prompt:
- What we need to do is: click Database -> Verify Database from the Menu bar.
At this time Crystal report notices the mismatch and we have the opportunity to correct it.
Update the mapping in this dialog and click OK, the RPT comes back to work.
Conclusion
In this post, I illustrate a good practice when designing RPT – always remember to use “Verify Database” command to refresh the data source to make sure the report data fields are the most up-to-date according to the source changes. Fix the inconsistence timely by remapping fields to make report run successfully.
