?
Solved

Passing parameters between 5 subreports Crystal Reports XI

Posted on 2006-04-06
7
Medium Priority
?
3,724 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:ruavol2
  • 4
  • 3
7 Comments
 
LVL 16

Expert Comment

by:wykabryan
ID: 16393127
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
 

Author Comment

by:ruavol2
ID: 16393295
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
 
LVL 16

Accepted Solution

by:
wykabryan earned 2000 total points
ID: 16393375
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 

Author Comment

by:ruavol2
ID: 16393489
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
 
LVL 16

Expert Comment

by:wykabryan
ID: 16393519
Yes inside CR, not a command. You would set up a parameter and a formula using the above example.
0
 

Author Comment

by:ruavol2
ID: 16393526
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
 
LVL 16

Assisted Solution

by:wykabryan
wykabryan earned 2000 total points
ID: 16393576
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question