Crsytal Record Selction

[rexie]

Hi all,

Crystal Report ver 8.5

I am trying to print a report based on dates. Below is my example.


I have 4 dates in my report
1.Date1
2. Date2
3. Date 3
4. Date4

THE REPORT SHOULD PRINT ONLY WHEN THE FOLLOWING
What I want The report to do is that,
(Condition 1) If Date1 is Non zero (has no values)
and
(Condition 2) the difference (b/w the current date and the Date1 is more than or equal to 30)
and
(Condition 3) Date2 and Date3 and Date4 should be Non zero (has no values)


Please help me as to how I can take care of the report

regards

 

[Ngolem]

How do you specify Date1....by a parameter? or are Date 1,2,3,4 separate fields....more info required

 

[rexie]

ALL the dates are table fields, these are not paramters. But one date is a parameter field. Redefined the quesion, see below.

Thank you very much for the mail. I really appreciate it. The report I am looking out is as below.

In the parameter entry in Crystal, lets assume we have a (only one) parameter Date i.e DATE-1.

I would like to get all the records from the Table-A,
where DATE-2(is another Date field in the table, Table -A) - DATE-1(parameter Date) >= 30( is greater than or equal to 30 days) and DATE-3(is again another Date field in the table, Table -A)

To put it short:-
Select all the records like below.
(DATE-2 - DATE2) >= 30 and DATE-3 is Null

Thanks for all the help, let me know if I could clarify you better

 

[Ngolem]

I find this confusing since you changes the names on me. Also "non-zero" to me means that it HAS a value...I think you mean that the fields are empty ie. NULL

Is it like this?

Date_1 is a parameter input. {?Date_1}
Date_2, Date_3 are SEPARATE fields in a Table_A

so your formula would be something like:

I am not in my office but I think there is a function called DateDif...I may have the syntax wrong but it is something like this

datediff("d",{Table_A.Date_2},{?Date_1})

I am sure I spelled something wrong but you get the idea...this gives you the difference between 2 dates in days

so your formula would be

(isnull(Table_A.Date_3} and
datediff("d",{Table_A.Date_2},{?Date_1}) >= 30)

If there is more to this story....give all the details...not just snipets...

Hope this helps...Jim

 

[Rozzi80]

I usually create formula: DAYSDIFF

{TableA.Date2} - {TableA.Date1}

Criteria is then

DAYSDIFF >= 30 and
{TableA.Date3} <> Date(0, 0, 0)

However, I may not have read your question correctly.

 

[rexie]

Hi,

Thanks a lot for your reply.

Here is the formula I used:-

Datediff (&quot;d&quot;, {TN_SIGNAL_FIXTURE_JOB.CN_DATE_RECEIVED},{?Received Date})

both the fields are Datetime fields and when I try to run, it is giving me an error
&quot;The result of this selection formula must be a boolean&quot;, can you throw some light please

thanks a zillion

regards
rexie

 

[dgillz]

A record selction formula must be a boolean formula, in other words a formula that evaluates to true or false. Your formula calculates a date difference.

An example of a boolean formula in your case would be:

{Datefield} in {?Date1} to Dateadd(&quot;d&quot;,-30,{>date1})

This would include all records where {datefield) is in the range of {?Date1} to 30 days prior to {?Date1}

Let me know if you have any questions.
Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[dgillz]

A record selction formula must be a boolean formula, in other words a formula that evaluates to true or false. Your formula calculates a date difference.

An example of a boolean formula in your case would be:

{Datefield} in {?Date1} to Dateadd(&quot;d&quot;,-30,{?date1})

This would include all records where {datefield) is in the range of {?Date1} to 30 days prior to {?Date1}

Let me know if you have any questions.
Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[rexie]

hi dgillz,

I appreciate your reponse. This is what i am looking to retrieve. I want to select all the records from a table based on some dates. Example below:-
(All the dates below are Datetime (datatype))
Date_1, Date_2, Date_3, , Date_4, and DAte_A(parameter date).

Select all the records, which are 30 days or greater (ie. the difference b/w Date 1 and Date_A(parameter date) and Date_2, Date_3 and Date_4 should be 0 or NULL.

This what I am trying to achieve, please help, thanks a lot

 

[Ngolem]

to make it Boolean just add the condition you want like this

Datediff (&quot;d&quot;, {TN_SIGNAL_FIXTURE_JOB.CN_DATE_RECEIVED},{?Received Date}) >= 30

now it evaluates true/false for each record

 

[rexie]

Hi Ngolem,

Thanks, your formula worked fine and now I am getting a hang of Crystal little bit.

The real side of the story of my report was little more than I explained. I wanted to go phase by phase. The first phase is complete, which you have given me a solution.

Phase 2
========

The user wants to run only 1 report for all the dates. For example. below:-

1. Give me all the records for Dates (difference) for DateA
2. Give me all the records for Dates (difference) for DateB
3. Give me all the records for Dates (difference) for DateC
4. Give me all the records for Dates (difference) for DateD

I know it is little conufusing, I want the user to select the parameter date they want to run the report for, if they select the (lets say DATEA), the the statements for DateA only should run, if the select DateB, the n the function for DateB should run and so on.

So, the question is how do we conditionally run a report, example, if DATEA is seleted, DATEB, DATEC,DATED(parameter fields) should be empty and so on.

Ngolem, I have tried my best to explain my problem, but if you wish me to explain it again, I am more than happy to do it

thanks a zillion

 

[dgillz]

Use if-then-else logic in your record selection formula:

If {?parmfield}=&quot;A&quot; then <<first record selection formula>> else

If {?parmfield}=&quot;B&quot; then <<2nd record selection formula>> else

etc., etc.
Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[rexie]

Hi,

Thanks for your reply, yes, I wanted to try something like below.

If (Paramter DateA is NOT NULL) then
(Datediff (&quot;d&quot;, {TN_SIGNAL_FIXTURE_JOB.CN_DATE_RECEIVED},{?Received Date}) >= 30)

ELSE

If (Paramter DateB is NOT NULL) then
(Datediff (&quot;d&quot;, {TN_SIGNAL_FIXTURE_JOB.CN_DATE_B},{?B_ Date}) >= 30)

How do i write the correct syntax for IF condition for NOT NULL and is there an endif needed

please suggest, thanks a lot

 

[Ngolem]

use of if-then-else statements should be held to a minimum since they are not pushed to the server for evaluation

I suggest that you create a new parameter and add it to your report.

{?Reporting Date}
param type: String
description: Enter the Date that the report is based on
Select &quot;A&quot;,&quot;B&quot;,&quot;C&quot;,&quot;D&quot;

now in your selection formula add the following

switch (
uppercase({?Reporting Date}) = &quot;A&quot;, Datediff (&quot;d&quot;, {Table.DateA},{?Received Date}) >= 30,
uppercase({?Reporting Date}) = &quot;B&quot;, Datediff (&quot;d&quot;, {Table.DateB},{?Received Date}) >= 30,
uppercase({?Reporting Date}) = &quot;C&quot;, Datediff (&quot;d&quot;, {Table.DateC},{?Received Date}) >= 30,
uppercase({?Reporting Date}) = &quot;D&quot;, Datediff (&quot;d&quot;, {Table.DateD},{?Received Date}) >= 30
)

this should do it and hopefully be pushed to the server....though I have never tried it with Datediff

Hope this helps...jim

 

[rexie]

Hi Jim,

Will the Datediff work though the ?ReceivedDate is a string(datatype)

 

[rexie]

Jim,

Superb, it worked great, I just substituted the date with current datetime and wow, I greatly thank you for the level of help renedered, great!!!!, u rock!!!