[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Crystal Reports 11 - Master-Sub report

Posted on 2010-04-08
5
Medium Priority
?
311 Views
Last Modified: 2012-05-09
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@
0
Comment
Question by:D-pk
  • 3
5 Comments
 
LVL 9

Accepted Solution

by:
shivkasi earned 1600 total points
ID: 30157563
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
 
LVL 9

Expert Comment

by:shivkasi
ID: 30158087
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
 
LVL 9

Expert Comment

by:shivkasi
ID: 30158423
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
 

Author Comment

by:D-pk
ID: 30163451
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
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 400 total points
ID: 30171138
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

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 …
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Get the source code for a fully functional Access application shell with several popular security features that Access VBA application developers desire, but find difficult or impossible to figure out how to code. You get the source code for managi…

613 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