Crystal Formula

Yahoo Archive
1
Try today() + 7 instead of CurrentDate + 7



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Chris Thompson
Sent: Wednesday, March 23, 2011 12:07 PM
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Crystal Formula





Ive tried that but now it errors on CurrentDate + 7 and says ...

A string is required here

Any thoughts?

________________________________
From: Tony Wheeldon <tony.wheeldon@... <mailto:tony.wheeldon%40ritchey.co.uk> >
To: "vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> " <vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> >
Sent: Wed, 23 March, 2011 15:46:51
Subject: RE: [Vantage] Crystal Formula


Try removing the date function altogether, i.e.

if {ELEC01_SOWaitingParts3.OrderHed_RequestDate} <= CurrentDate + 7

____

Tony

Tony Wheeldon

Group IT Manager

D/L: +44 (0)1765 680804 Ritchey Ltd
M: +44 (0)7825246046 Fearby Road
F: +44 (0)1765 689851 Masham, Ripon
E: tony.wheeldon@... <mailto:tony.wheeldon%40ritchey.co.uk> <mailto:tony.wheeldon@... <mailto:tony.wheeldon%40ritchey.co.uk> > North
Yorkshire
W: www.ritchey.co.uk<http://www.ritchey.co.uk> HG4 4ES

Ritchey Limited is a limited company registered in England and Wales.

Registered number:07330611. Registered office: Fearby Road, Masham, Ripon, North
Yorkshire. HG4 4ES

Please note that Ritchey Limited may monitor email traffic data and also the
content of email for the purposes of security and staff training.

This message contains confidential information and is intended only for the
intended recipients. If you are not an intended recipient you should not
disseminate, distribute or copy this e-mail. Please notify
tony.wheeldon@... <mailto:tony.wheeldon%40ritchey.co.uk> immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system. E-mail transmission
cannot be guaranteed to be secure or error-free as information could be
intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain
viruses. Tony Wheeldon therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a result of e-mail
transmission. If verification is required please request a hard-copy version.

P Please consider the environment - do you really need to print this email?




From: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com> ] On Behalf Of
Chris Thompson
Sent: 23 March 2011 14:01
To: vantage@yahoogroups.com <mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Crystal Formula

Hi All

I have a crystal report and I want to create the following formular but I am
going wrong somewhere:-

if IsNull({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) then 'hello'
else
if date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) <= CurrentDate + 7
then
'Due for despatch in the next 7 days'
else
if date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) <= CurrentDate + 14
AND
date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) > CurrentDate + 7
then
'Due for despatch in the next 14 days'
else
if date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) <= CurrentDate + 21
AND
date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) > CurrentDate + 14
then
'Due for despatch in the next 21 days'

When I try to group by the formula, I get an error which highlights
date({.......}) and says...

Bad date format string

Any suggestions would be appreciated.

Thanks,

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
2
This is what I have
stringVar ONUM :=CStr ({OrderRel.OrderNum},0000000);
If my value is 22.00 it is showing 22 not 0000022.
Am I doing something wrong? I thought this should work.
3
the format string should be in quotes


--- In vantage@yahoogroups.com, "Michael McWilliams"
<mmcwilliams22@...> wrote:
>
> This is what I have
> stringVar ONUM :=CStr ({OrderRel.OrderNum},0000000);
> If my value is 22.00 it is showing 22 not 0000022.
> Am I doing something wrong? I thought this should work.
>
4
Has anyone developed a Crystal formula that computes the number of days/hours between two dates NOT including Saturday and Sunday (i.e. the number of workdays from start to finish).
Â
Thanks,
-Karl




[Non-text portions of this message have been removed]
5
Here's the formula I used - that even took holidays into account! FWIW,
I searched and searched and searched for a built in function (like
Excel's NETWORKDAYS) to no avail.

I believe I got this formula from someone on this list.

numbervar intCount;
numbervar StartMonth;
datevar StartDate;
datevar EndDate;
datevar array holiday;
global numbervar WDM;

holiday := [CDate(2008, 1, 01),CDate(2008, 3, 21),CDate(2008, 5,
26),CDate(2008, 7, 04),CDate(2008, 9, 01),CDate(2008, 11,
27),CDate(2008, 11, 28),CDate(2008, 12, 24),CDate(2008, 12, 25)];
StartMonth:= Month(date({BAQReportParameter.Option01}));
StartDate:= date(2008, StartMonth, 01);
EndDate:=date({BAQReportParameter.Option01});

intCount:= 0;

While (StartDate <= EndDate) Do
(
//Make the above < and not <= to not count the EndDate
if not(StartDate in holiday) then (
Select WeekDay(StartDate)
Case 1, 7:
intCount:= intCount
Case 2, 3, 4, 5, 6:
intCount:= intCount + 1
);
StartDate:= StartDate + 1;
);

WDM:= intCount;




________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Karl Dash
Sent: Thursday, August 28, 2008 3:53 PM
To: Vantage Group
Subject: [Vantage] Crystal Formula



Has anyone developed a Crystal formula that computes the number of
days/hours between two dates NOT including Saturday and Sunday (i.e. the
number of workdays from start to finish).

Thanks,
-Karl

[Non-text portions of this message have been removed]






[Non-text portions of this message have been removed]
6
Thanks Ken, works great!
Â
-Karl

--- On Thu, 8/28/08, Ken Williams <ken@...> wrote:

From: Ken Williams <ken@...>
Subject: RE: [Vantage] Crystal Formula
To: vantage@yahoogroups.com
Date: Thursday, August 28, 2008, 3:09 PM






Here's the formula I used - that even took holidays into account! FWIW,
I searched and searched and searched for a built in function (like
Excel's NETWORKDAYS) to no avail.

I believe I got this formula from someone on this list.

numbervar intCount;
numbervar StartMonth;
datevar StartDate;
datevar EndDate;
datevar array holiday;
global numbervar WDM;

holiday := [CDate(2008, 1, 01),CDate(2008, 3, 21),CDate(2008, 5,
26),CDate(2008, 7, 04),CDate(2008, 9, 01),CDate(2008, 11,
27),CDate(2008, 11, 28),CDate(2008, 12, 24),CDate(2008, 12, 25)];
StartMonth:= Month(date({ BAQReportParamet er.Option01} ));
StartDate:= date(2008, StartMonth, 01);
EndDate:=date( {BAQReportParame ter.Option01} );

intCount:= 0;

While (StartDate <= EndDate) Do
(
//Make the above < and not <= to not count the EndDate
if not(StartDate in holiday) then (
Select WeekDay(StartDate)
Case 1, 7:
intCount:= intCount
Case 2, 3, 4, 5, 6:
intCount:= intCount + 1
);
StartDate:= StartDate + 1;
);

WDM:= intCount;



____________ _________ _________ __

From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf
Of Karl Dash
Sent: Thursday, August 28, 2008 3:53 PM
To: Vantage Group
Subject: [Vantage] Crystal Formula

Has anyone developed a Crystal formula that computes the number of
days/hours between two dates NOT including Saturday and Sunday (i.e. the
number of workdays from start to finish).

Thanks,
-Karl

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]


















[Non-text portions of this message have been removed]
7
Hi All

I have a crystal report and I want to create the following formular but I am
going wrong somewhere:-

if IsNull({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) then 'hello'
else
if date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) <= CurrentDate + 7
then
'Due for despatch in the next 7 days'
else
if date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) <= CurrentDate + 14
AND
date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) > CurrentDate + 7
then
'Due for despatch in the next 14 days'
else
if date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) <= CurrentDate + 21
AND
date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) > CurrentDate + 14
then
'Due for despatch in the next 21 days'

When I try to group by the formula, I get an error which highlights
date({.......}) and says...

Bad date format string



Any suggestions would be appreciated.

Thanks,




[Non-text portions of this message have been removed]
8
Try removing the date function altogether, i.e.

if {ELEC01_SOWaitingParts3.OrderHed_RequestDate} <= CurrentDate + 7


____

Tony




Tony Wheeldon

Group IT Manager





D/L: +44 (0)1765 680804 Ritchey Ltd
M: +44 (0)7825246046 Fearby Road
F: +44 (0)1765 689851 Masham, Ripon
E: tony.wheeldon@...<mailto:tony.wheeldon@...> North Yorkshire
W: www.ritchey.co.uk<http://www.ritchey.co.uk> HG4 4ES





Ritchey Limited is a limited company registered in England and Wales.

Registered number:07330611. Registered office: Fearby Road, Masham, Ripon, North Yorkshire. HG4 4ES

Please note that Ritchey Limited may monitor email traffic data and also the content of email for the purposes of security and staff training.

This message contains confidential information and is intended only for the intended recipients. If you are not an intended recipient you should not disseminate, distribute or copy this e-mail. Please notify tony.wheeldon@... immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. Tony Wheeldon therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version.



P Please consider the environment - do you really need to print this email?






From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Chris Thompson
Sent: 23 March 2011 14:01
To: vantage@yahoogroups.com
Subject: [Vantage] Crystal Formula



Hi All

I have a crystal report and I want to create the following formular but I am
going wrong somewhere:-

if IsNull({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) then 'hello'
else
if date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) <= CurrentDate + 7
then
'Due for despatch in the next 7 days'
else
if date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) <= CurrentDate + 14
AND
date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) > CurrentDate + 7
then
'Due for despatch in the next 14 days'
else
if date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) <= CurrentDate + 21
AND
date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) > CurrentDate + 14
then
'Due for despatch in the next 21 days'

When I try to group by the formula, I get an error which highlights
date({.......}) and says...

Bad date format string

Any suggestions would be appreciated.

Thanks,

[Non-text portions of this message have been removed]



[Non-text portions of this message have been removed]
9
Ive tried that but now it errors on CurrentDate + 7Â and says ...

A string is required here

Any thoughts?




________________________________
From: Tony Wheeldon <tony.wheeldon@...>
To: "vantage@yahoogroups.com" <vantage@yahoogroups.com>
Sent: Wed, 23 March, 2011 15:46:51
Subject: RE: [Vantage] Crystal Formula

Â
Try removing the date function altogether, i.e.

if {ELEC01_SOWaitingParts3.OrderHed_RequestDate} <= CurrentDate + 7


____

Tony




Tony Wheeldon

Group IT Manager





D/L: +44 (0)1765 680804 Ritchey Ltd
M: +44 (0)7825246046 Fearby Road
F: +44 (0)1765 689851 Masham, Ripon
E: tony.wheeldon@...<mailto:tony.wheeldon@...> North
Yorkshire
W: www.ritchey.co.uk<http://www.ritchey.co.uk> HG4 4ES





Ritchey Limited is a limited company registered in England and Wales.

Registered number:07330611. Registered office: Fearby Road, Masham, Ripon, North
Yorkshire. HG4 4ES

Please note that Ritchey Limited may monitor email traffic data and also the
content of email for the purposes of security and staff training.

This message contains confidential information and is intended only for the
intended recipients. If you are not an intended recipient you should not
disseminate, distribute or copy this e-mail. Please notify
tony.wheeldon@... immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system. E-mail transmission
cannot be guaranteed to be secure or error-free as information could be
intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain
viruses. Tony Wheeldon therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a result of e-mail
transmission. If verification is required please request a hard-copy version.



P Please consider the environment - do you really need to print this email?



--


From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
Chris Thompson
Sent: 23 March 2011 14:01
To: vantage@yahoogroups.com
Subject: [Vantage] Crystal Formula



Hi All

I have a crystal report and I want to create the following formular but I am
going wrong somewhere:-

if IsNull({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) then 'hello'
else
if date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) <= CurrentDate + 7
then
'Due for despatch in the next 7 days'
else
if date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) <= CurrentDate + 14
AND
date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) > CurrentDate + 7
then
'Due for despatch in the next 14 days'
else
if date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) <= CurrentDate + 21
AND
date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) > CurrentDate + 14
then
'Due for despatch in the next 21 days'

When I try to group by the formula, I get an error which highlights
date({.......}) and says...

Bad date format string

Any suggestions would be appreciated.

Thanks,

[Non-text portions of this message have been removed]


[Non-text portions of this message have been removed]







[Non-text portions of this message have been removed]
10
What are you reporting off?

SQL?
Progress
BAQ?


____

Tony




Tony Wheeldon

Group IT Manager





D/L: +44 (0)1765 680804 Ritchey Ltd
M: +44 (0)7825246046 Fearby Road
F: +44 (0)1765 689851 Masham, Ripon
E: tony.wheeldon@...<mailto:tony.wheeldon@...> North Yorkshire
W: www.ritchey.co.uk<http://www.ritchey.co.uk> HG4 4ES





Ritchey Limited is a limited company registered in England and Wales.

Registered number:07330611. Registered office: Fearby Road, Masham, Ripon, North Yorkshire. HG4 4ES

Please note that Ritchey Limited may monitor email traffic data and also the content of email for the purposes of security and staff training.

This message contains confidential information and is intended only for the intended recipients. If you are not an intended recipient you should not disseminate, distribute or copy this e-mail. Please notify tony.wheeldon@... immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. Tony Wheeldon therefore does not accept liability for any errors or omissions in the contents of this message, which arise as a result of e-mail transmission. If verification is required please request a hard-copy version.



P Please consider the environment - do you really need to print this email?






From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Chris Thompson
Sent: 23 March 2011 16:07
To: vantage@yahoogroups.com
Subject: Re: [Vantage] Crystal Formula



Ive tried that but now it errors on CurrentDate + 7 and says ...

A string is required here

Any thoughts?

________________________________
From: Tony Wheeldon <tony.wheeldon@...<mailto:tony.wheeldon%40ritchey.co.uk>>
To: "vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>" <vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>>
Sent: Wed, 23 March, 2011 15:46:51
Subject: RE: [Vantage] Crystal Formula


Try removing the date function altogether, i.e.

if {ELEC01_SOWaitingParts3.OrderHed_RequestDate} <= CurrentDate + 7

____

Tony

Tony Wheeldon

Group IT Manager

D/L: +44 (0)1765 680804 Ritchey Ltd
M: +44 (0)7825246046 Fearby Road
F: +44 (0)1765 689851 Masham, Ripon
E: tony.wheeldon@...<mailto:tony.wheeldon%40ritchey.co.uk><mailto:tony.wheeldon@...<mailto:tony.wheeldon%40ritchey.co.uk>> North
Yorkshire
W: www.ritchey.co.uk<http://www.ritchey.co.uk> HG4 4ES

Ritchey Limited is a limited company registered in England and Wales.

Registered number:07330611. Registered office: Fearby Road, Masham, Ripon, North
Yorkshire. HG4 4ES

Please note that Ritchey Limited may monitor email traffic data and also the
content of email for the purposes of security and staff training.

This message contains confidential information and is intended only for the
intended recipients. If you are not an intended recipient you should not
disseminate, distribute or copy this e-mail. Please notify
tony.wheeldon@...<mailto:tony.wheeldon%40ritchey.co.uk> immediately by e-mail if you have received this
e-mail by mistake and delete this e-mail from your system. E-mail transmission
cannot be guaranteed to be secure or error-free as information could be
intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain
viruses. Tony Wheeldon therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a result of e-mail
transmission. If verification is required please request a hard-copy version.

P Please consider the environment - do you really need to print this email?




From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of
Chris Thompson
Sent: 23 March 2011 14:01
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] Crystal Formula

Hi All

I have a crystal report and I want to create the following formular but I am
going wrong somewhere:-

if IsNull({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) then 'hello'
else
if date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) <= CurrentDate + 7
then
'Due for despatch in the next 7 days'
else
if date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) <= CurrentDate + 14
AND
date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) > CurrentDate + 7
then
'Due for despatch in the next 14 days'
else
if date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) <= CurrentDate + 21
AND
date({ELEC01_SOWaitingParts3.OrderHed_RequestDate}) > CurrentDate + 14
then
'Due for despatch in the next 21 days'

When I try to group by the formula, I get an error which highlights
date({.......}) and says...

Bad date format string

Any suggestions would be appreciated.

Thanks,

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]



[Non-text portions of this message have been removed]