BusinessObject: Query Current and Archive Tables in Single Report

Product: BusinessObject
Module: Crystal Report designer

Consultant, including myself, often design archiving solution to move several years old OLTP data to archiving table, or even to different archiving database.

It becomes a challenge for report designer to pull the data from 2 different tables, or databases. This post will indicate how to design a single rpt report to get the data from 2 tables automatically

1. Create a sub-report to query archive table, which keeps data older than 1 year
2. Create a parameter called "dateQuery"
3. In main report, modify Conditional Suppression formula to hide this year's data if dateQuery is older than 1 year

If DateDiff ("m", {?dateQuery}, CurrentDate ) > 12 Then True

4. 3. In sub-report, modify Conditional Suppression formula to hide old data if dateQuery is this year

If DateDiff ("m", {?dateQuery}, CurrentDate ) <= 12 Then True

This is another solution instead of design another report to query old data. Nonetheless, it is not a bad idea to create separate report to prevent user abusing the report, such as extracting 5 years of data, which severe degrade database, hard disk, and or even congested the network