Crystal report data source selection

I manage a crystal report portal, which utilizes real-time SQL connections via secured VPN tunnels and also local archived 'data-warehouse' tables. The realtime tables hold the exact same content as the data-warehouse tables - My challenge is to have a mechanism that will pull data from the real time SQL {dbo.ParkData} only if the date/time period falls within 5 days, anything else should run the report from the local data warehouse {dbo.ParkData_ARC}
John-S PretoriusSystems EngineerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
When you refer to "the date/time period", do you mean a parameter?

Does your portal allow you to observe the answer to the question (<=5 days? or more?) before you trigger the report?  What is the Crystal object model used by the portal?

Can you rely on the user to make the choice of data source?  Or does the choice must be automated based on the value of the parameter?
John-S PretoriusSystems EngineerAuthor Commented:
Thank you for your responce Ido,
Date/time period is the time parameter selection (See below), {@Date(NoTime)} = the SQL timestamp for each transaction. The parameter selection is shown before you run the report, when the report is executed the 'Ripple*****one' portal will open the ODBC connection as used by Crystal to run the report. I'm trying to make the experience as straight forward as possible for the client which is why I would like to automate the database connector behind the scenes.

//Date/time parameter selection
If {?DateRangeParam} = "Current Date" then
   {@Date(NoTime)} = CurrentDate
Else If {?DateRangeParam} = "Previous Day" then
   {@Date(NoTime)} = CurrentDate - 1
Else If {?DateRangeParam} = "Last full week" then
   {@Date(NoTime)} = LastFullWeek
Else If {?DateRangeParam} = "Month to date" then
  {@Date(NoTime)} = MonthToDate
Else If {?DateRangeParam} = "Previous month" then
 {@Date(NoTime)} = LastFullMonth
Else If {?DateRangeParam} = "6 to 6" then
{@Date(NoTime)} >= DateAdd("h",-18,CurrentDate)AND
{@Date(NoTime)} <= DateAdd("h",6,CurrentDate)
Else if {?DateRangeParam} = "Date Selection" then
Are you connecting to the data source via the tables or are you using a command?

What version of Crystal are you using?

Are you looking to have reports run from both sources at the same time?
For example, a report that reports on month to date data.  The last 5 days would be from the live data and the rest from the warehouse.

Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

John-S PretoriusSystems EngineerAuthor Commented:
I'm using Crystal Reports 2013 SP4, typically/currently when running the report a ODBC data source selection is made from Crystal - I guess this would be the tricky  part as I would manually change this from within Crystal "Database/Set Database Location" when I want to change where I'm pulling the data from. Currently I'm thinking to create 2 folders, the 1 Folder holds reports that is for realtime up to 5 days data and the other folder would be for anything data over this period. The reason I'm doing this is that the report runs much faster when data is pulled from the local data warehouse. The automated solution would make the report/function value so much more.

To answer your last question, any data selection covering more than 5 days would be from the archived local server and excluding current date :- ( <currentdate) so month to date will cover all data from 09/01/2015 thru 09/13/2015
Ido MilletProfessor of MIS at Penn State Erie and Owner, Millet SoftwareCommented:
If your report doesn't have subreports, you could use an empty main report, insert the original report as 2 subreports: one with DW as data source -- the other as Operational DB as data source, and control the suppress logic (and even the record selection criteria) of each subreport based on the date range parameter so that only one subreport gets processed.
Obviously, you would pass the parameters from the empty shell main report to both subreports.

Otherwise, your portal software would have to step in and take care of the re-targeting of the data source based on the parameter value.
Depending on your db, etc., another option might be to create a linked server in the local db that points to the remote db, then a query (eg. stored procedure in the local db, or a CR Command) could use the local or linked tables, depending on the parameters.

You might be able to use a command such as

SELECT field list
Where {?DateRangeParam}  IN ['CurrentDate', 'Previous Day', '6 to 6']
Rest of the filter

SELECT field list
WHERE {?DateRangeParam}  IN ['Last Full Week', 'Month to Date', 'Previous Month', 'Date Range']
Rest of the filter


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John-S PretoriusSystems EngineerAuthor Commented:
All, thank you for the responses I have looked into my options and what possibilities makes most sense without over complicating it. At this time I believe best approach would be to either run a report for realtime transactions (with only one selection available :- {@Date(NoTime)}=currentdate) with a database connector directly to production environment and everything else (maybe a Archive Folder) to the local SQL Data warehouse.

MLMCC, I believe you are the system administrator so if it's OK I can either accept all input (3 participants) as multiple solutions or we can just archive the question.
John-S PretoriusSystems EngineerAuthor Commented:
Sometimes not all crazy questions have answers, thank you all for the input. I believe that at this time the actual application/portal interface does not have enough functionality available to accomplish what I was asking about.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.