Passing parameters between 5 subreports Crystal Reports XI

I have a report that is a collage of 5 subreports which are mixed with commands and tables internally. The report produces a result of records based on todays date minus 1.
Current Date -1=yesterday.

I need to have the report scheduled in BOE to normally run as scheduled with the default date always accepting the Current Date minus 1 so that it looks at yesterdays data. I need the option to say if the report needs a look back at the data from 10 days ago I need the report to be flexible so that the user can pick a date without changing the design of the  report just picking a different date.

I assume I would use a parameter and I could somehow set the date default to be always today? How do I do that?

If I am using SQL commands for many of the subreports in the Add Command window should I build the Parameter in the Add Command or in Crystal Reports Designer itself? The report takes seven minutes to run as is?
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Asked:
Who is Participating?
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.

wykabryanCommented:
How we handled something similar to this was creating 3 formulas.

Parameter 1: TimeFrame -
Ad - Hoc:  Allows the user to enter in their own dates in the below paramters
MTD: Automatically send dates to the report to run from the first of the month to yesterday
YTD: Automatically send dates to the report to run from the first of the year to yesterday

Parameter 2: Start Date (String)
Default is set to whatever you want, but user will have to update if they choose Ad-hoc

Parameter 3: End Date(String)
Defaults is set to whatever you want, but user will have to update if they choose ad-hoc.

In the Record Selection Formula Editor we use the following formula:
((if {?TimeFrame} ='ad' then
    {Industry_Management_BE.Fiscal Year and Month as Integer} in tonumber({?StartPeriod}) to tonumber({?EndPeriod})) or
(if {?TimeFrame} ='mtd' then
    {Industry_Management_BE.Fiscal Year and Month as Integer} = MTD (dateadd("d",-7,currentdate))) or
(if {?TimeFrame} ='ytd' then
    {Industry_Management_BE.Fiscal Year and Month as Integer} in YTDBeginning (currentdate) to YTDEnding (dateadd("d",-7,currentdate))))

There is some custom functions that we have that convert dates to fiscal periods.  But the same principle can be applied for your situation.

Hope this helps.
0
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
I wanted to see one day at a time. 10 days MTD or YTD would not help these guys. What I need is one day plucked out of say 3 weeks ago. Just one days worth.
0
wykabryanCommented:
ahh.. ok.. That is a little simpler.
create a parameter that allows users to enter in a date.  I would suggest making it a string parameter and then converting it in the report.  I have seen users get very fustrated trying to make what crystal wants for a datetime.

Formula = DateParameter
if date({dateparameter}) = currentdate then dateadd("d",-1,date({dateparameter}))
  else date({dateparameter})

In the Record Selection Formula Editor we use the following formula:
{DateParameter} = {Columndate}

Then you can link this formula to each of your subreports.
0

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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
Then this would be inside of Crystal Reports or inside of the Add Command area of CR. They act very differently I have found. If it should be done in Crystal I think I can handle if it is done in Add Command I may be in trouble?
0
wykabryanCommented:
Yes inside CR, not a command. You would set up a parameter and a formula using the above example.
0
Fletcher BurdineTableau Trainer & Consultant Sales Exec.Author Commented:
Also is there anyway to make the default date in the parameter always the current date that way when scheduled in BOE the parameter never needs changing else a user goes in and says give me the value from 3 weeks ago like March 15. In other words low maintenance reporting with the flexibility of "Pick a day"?
Thanks for your help this was fantastic!
0
wykabryanCommented:
yes.  That is where the timeframe parameter would come in..

TimeFrame Parameter

Setting:
ad - AdHoc
ye - Yesterday

Formula = DateParameter
if date({dateparameter}) = currentdate then dateadd("d",-1,date({dateparameter}))
  else date({dateparameter})

In the Record Selection Formula Editor we use the following formula:
((if {timeframe} = 'ad' then
{DateParameter} = {Columndate}) or
(if {timeframe} = 'ye' then
dateadd("d",-1,date(currentdate)) = {columndate}))

Then when you scheduled the report, set the timeframe parameter to yesterday, put what ever date you want in your date parameter(it wont matter because the report will ignore it), and schedule.

Hope this helps.

0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.