Help with formula

[eastwestk]

Hello Everyone,

In my crystal report XI R2, I want as follows:

For June 14th thru June 20th (Mon thru Sun) - I would like to display "Release Number 100" in the page header of crystal report

For June 21st thru June 27th (Mon thru Sun) - I would like to display "Release Number 101" in the page header of crystal report

For June 28th thru July 04th (Mon thru Sun) - I would like to display "Release Number 102" in the page header of crystal report

For July 05th thru July 11th (Mon thru Sun) - I would like to display "Release Number 103" in the page header of crystal report

and so on (the release number should keep incrementing)

I don't want to hardcode this, please help me in accomplishing this.

Any help will be greatly appreciated

Thanks in advance

 

[lbass]

Where are the dates coming from--are they based on a daterange parameter? If so, use a formula like this:

numbervar start := 100;
numbervar i;
numbervar j := 52; //maximum number of weeks you will be incrementing across
datevar startdt := date(2010,6,14);
stringvar relno := "";
for i := 1 to j do(
start := start + 1;
if minimum({?daterange}) = dateadd("ww",i,startdt) then
relno := totext(start,0,"");
);
"Release Number "+relno

-LB

 

[eastwestk]

The crystal report is calling a sql server stored procedure and it has 2 datetime parameters called
@StartDate and @EndDate.

 

[lbass]

Then substitute {@StartDate} for minimum({?Daterange}).

-LB

 

[eastwestk]

I have tried the below line of statement and the formula is only displaying the string "Release Number", I think comparison is not taking place. I will try, do you have any suggestion please.

if ({?@StartDate}) = dateadd("ww",i,startdt)

 

[lbass]

Please show the entire revised formula.

-LB

 

[eastwestk]

Below is the entire formula:

numbervar start := 100;
numbervar i;
numbervar j := 52; //maximum number of weeks you will be incrementing across
datevar startdt := date(2010,6,14);
stringvar relno := "";
for i := 1 to j do(
start := start + 1;
if {?@StartDate} = dateadd("ww",i,startdt) then
relno := totext(start,0,"");
);
"Release Number "+relno

Thanks for all your help!

 

[eastwestk]

Do you have any suggestions for me. I am still working on it.

 

[lbass]

You are placing this in the report header, correct? If you place {?@StartDate} in the report header, what does it return? This assumes that {?@StartDate} is always a Monday.

-LB

 

[eastwestk]

I have a below formula in page header which is displaying the {?@StartDate} & {?@EndDate} and I do not have anything in report header:

if (isnull({?@StartDate}) and isnull({?@EndDate})) then
"Transactions Between" & ' ' & ToText(Date(DateAdd("d",-1,CurrentDate))) & ' ' & "thru" &  ' ' & ToText(Date(DateAdd("d",-1,CurrentDate)))

else if isnull({?@EndDate}) then
"Transactions Between" & ' ' & ToText(Date({?@StartDate})) & ' ' & "thru" &  ' ' & ToText(CurrentDate) 

else
"Transactions Between" & ' ' &  ToText(Date({?@StartDate})) & ' ' & "thru" &  ' ' & ToText(Date({?@EndDate}))

 

[lbass]

You didn't show how it displayed. Also you didn't clarify whether {?@startdate} was always a Monday.

However, please try:

numbervar start := 100;
numbervar i;numbervar j := 52; //maximum number of weeks you will be incrementing across
datevar startdt := date(2010,6,14);
stringvar relno := "";
for i := 1 to j do(
start := start + 1;
if [red]date({?@StartDate})[/red] = dateadd("ww",i,startdt) then
relno := totext(start,0,"")
);
"Release Number "+relno

-LB

 

[eastwestk]

If I pass the {?@StartDate} as 06/14/2010 and {?@EndDate} as 06/16/2010. In the page header I have a formula which will display as follows and it's not always monday:

Transactions Between 06/14/2010 thru 06/16/2010

If I pass the {?@StartDate} as 06/15/2010 and {?@EndDate} as 06/16/2010. In the page header I have formula which will display as follows and it's not always monday:

Transactions Between 06/15/2010 thru 06/16/2010

and so on

I tried your code but always it's displaying only the string "Release Number ". Please help me.

 

[lbass]

So you tried the revised code? Did you use a Monday date? It tests out here.

I don't understand how the release numbers are supposed to relate to the start date if you are saying the start date can be other than monday.

-LB

 

[eastwestk]

Yes I tried the revised code and used a Monday date. But the below worked.

datevar startdt := date(2010,6,14);

numbervar relno := 100;

numbervar noofdays := 0;

noofdays := datediff("d",startdt,{?@StartDate});

numbervar divd := 0;

if (noofdays <> 0) then
(
divd := truncate((noofdays/7));
relno := (relno + divd)
)
else
0;

"Release Number:" & ' ' & ToText(relno,0,"")