Crystal Reports 11 - Master-Sub report

Hi,

I have some questions on the Auto-Schedule feature in the Crystal.
I found a report with that feature and I want to replicate that in to mine.

Below are the codes used in those reports, I kinda understand whats going on, but not in detail..

Master Report
-------------------

This has 4 formulas and 3 parameters:
Formulas:
1. reporting_date

Local numbervar m:=month(CurrentDate) + 1;

Local numbervar y:=year(CurrentDate);

If m>12 then (m:=m-12; y:=y + 1);

Date(y,m,1)


2. begin_dt

IF {?AutoSchedule} = 'N' THEN {?begin_date}
ELSE IF {?AutoSchedule} = 'Y' AND Month({@reporting date}) in 1 to 3
THEN datevalue(DateAdd('yyyy',-2,Date(year({@reporting date}), 1, 1)))
else if {?AutoSchedule} = 'Y' AND Month({@reporting date}) in 4 to 12
THEN datevalue(DateAdd('yyyy',-1,Date(year({@reporting date}), 1, 1)))

3. end_dt

IF {?AutoSchedule} = 'N' THEN {?end_date}

ELSE IF {?AutoSchedule} = 'Y' THEN DateValue(DateAdd('m',-1,{@reporting date}))-1


4. qtr_display


IF {?AutoSchedule} = "Y" THEN
'Q'&ToText(DatePart('q',(DateAdd('q',-1,{@reporting date}))-1),'0')
&'/'
& Totext (Year (DateAdd('m',-1,{@reporting date})-1),'0000')


ELSE IF {?AutoSchedule} = "N" THEN
'Q'&Totext ( DatePart ( 'q' , {@end_dt}) , '0')
& '/'
& Totext (Year ({@end_dt}), '0000')

Parameters:
1.AutoSchedule -- This has 2 values --  Y, N
2. BeginDate -- Empty parameter
3. EndDate  -- Empty parameter

--------------------------------------------------------------------------

SUB-Report
--------------
This has 3 parameters -- begin_date, end_date, qtr_display

Can anyone explain me about this auto-schedule feature and whats going on in each and every part of the code above. Please also let me know if there is any website which explains this step by step with examples.

Thanks@
D-pkAsked:
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.

ShivCommented:
1. reporting_date :

 You are moving the date to 1st of the next month.

 For example :

 if your are on dec 15th 2009 ,  then

your formula gives :

m:=month(CurrentDate) + 1;  ===> 12+1 =13

If m>12 then (m:=m-12; y:=y + 1);  ====> month will be 13-12 =1 (Jan) and year 2010

=====> it implies it is moving to ist of the next month.


2. begin_dt

{? } ==> implies parameter
{@ } ==> formula

IF {?AutoSchedule} = 'N' THEN {?begin_date}
===============
means if you enter  N  in parameter {?AutoSchedule}  then this formula gives what you selected from {?begin_date}
============

ELSE IF {?AutoSchedule} = 'Y' AND Month({@reporting date}) in 1 to 3
THEN datevalue(DateAdd('yyyy',-2,Date(year({@reporting date}), 1, 1)))
==============
means if you enter  Y  in parameter {?AutoSchedule}  and month of {@reporting date} formula is in 1 to 3 (1 st quarter )  then this formula(begin date) moving year back to
2 years back of the year of {@reporting date} and to month and date to  jan 1st

==============


else if {?AutoSchedule} = 'Y' AND Month({@reporting date}) in 4 to 12
THEN datevalue(DateAdd('yyyy',-1,Date(year({@reporting date}), 1, 1)))
=========
means if you enter  Y  in parameter {?AutoSchedule}  and month of {@reporting date} formula is in 2nd quarter then this formula(begin date) moving year back to
1 year back of the year of {@reporting date} and to month and date to  jan 1st
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
ShivCommented:
in the above not second quarter (but 4 to 12 months)

3. end_dt

IF {?AutoSchedule} = 'N' THEN {?end_date}

==============
means if you enter  N  in parameter {?AutoSchedule}  then this formula gives hat you selected from {?end_date} parameter
=========
ELSE IF {?AutoSchedule} = 'Y' THEN DateValue(DateAdd('m',-1,{@reporting date}))-1
===================

means if you enter  Y  in parameter {?AutoSchedule}  this formula(end date) moving  month  of {@reporting date} to last day of before previous month.
(for example :@reporting date => 1 st day of march , so this formula gives you last day of jan

In another way : {@reporting date} gives you 1 st day of next month of currentdate
so this formula gives you last day of previous month

0
ShivCommented:
4. qtr_display:


IF {?AutoSchedule} = "Y" THEN
'Q'&ToText(DatePart('q',(DateAdd('q',-1,{@reporting date}))-1),'0')
&'/'
& Totext (Year (DateAdd('m',-1,{@reporting date})-1),'0000')

=========
means if you enter  Y  in parameter {?AutoSchedule} then

this formula gives you :
  last quarter for next month-1 /  year of  previous currentdate month

=============

ELSE IF {?AutoSchedule} = "N" THEN
'Q'&Totext ( DatePart ( 'q' , {@end_dt}) , '0')
& '/'
& Totext (Year ({@end_dt}), '0000')

============
For N
current quarter of end date/ year of endate

========

check crystal help or click F1

for checking the functions like :

TOTEXT, DATEADD,DATEPART, etc.,
0
D-pkAuthor Commented:
Thanks that was very informative...
Just curious.. Is there something like a dual table (Oracle), in crystal to check these functions?
what would be my begin_date and end_date parameter, if it is year to date, meaning,
begin_date should always be the beginning of the year and the end_date should be previous months last day... This is a quartely report...
For eg: It runs 4 times a year.. Apr 10, Jul 10, Oct 10 and Jan 10

For Apr 10 the reporting period should be from Jan 1 to Mar 31
For Jul 10 -- Jan 1 to Jun 30
For Oct 10 --  Jan 1 to sep 30
For Jan 10 -- Jan 1 to Dec 31 -- This one is kinda tricky because, even though the report is ran the next year it needs information for prev year... Lets say the above ones are 2010 and the Jan 10 2011 should pull records of Jan 1 2010 to Dec 31 2010...
Please assist me in writing the formula..
Thanks!
0
mlmccCommented:
To answer your question about the AutoSchedule feature, there is no such feature.  The report you are looking at uses a parameter by that name and the value is N or Y depending on how it is called.  I assume this report is scheduled through a tool like Crystal Server and when run that way the value passed is Y so the report knows to report appropriately.  If it is run manually then the value will be N and the report act accordingly.

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