Flow in record selection Expression

[ramkrish11]

Hello All,

I am new to Crystal Reports and am having trouble understanding the flow that one can build into record selection Formula Editor.

I am using CR XI on Oracle 9.2

I have a DateRange parameter with possible values Daily, Weekly, Monthly. I need to evaluate it in the report and come up with the start date and end date values used in the SQL.

I tried the following in the record selection Formula editor.

---------------------------------------------------------
global datetimevar StartDate;
global datetimevar EndDate;

select {?DateRange}
case "Daily":
StartDate = (currentdate - 1)
case "Weekly":
StartDate = minimum(WeektodateFromSun)
case "Monthly":
if (day(Currentdate)) = 1 then
StartDate = minimum(LastFullMonth)
else
StartDate = minimum(MonthtoDate);

StartDate = Datetime(Datepart(StartDate),timevalue('00:00:00'));
EndDate = Datetime(Datepart(CurrentDate),timevalue('00:00:00'));

//
// use the date fields in the sql below
//
{PAYMENT_REPOSIT.CREDIT_CARD_NUM} in ["nnnnnnnnnnnnn", "nnnnnnnnnnnnnn"] and
{PAYMENT_REPOSIT.CANCEL_IND} = "N" and
({PAYMENT_REPOSIT.EXTRACT_DATE} >= StartDate
and {PAYMENT_REPOSIT.EXTRACT_DATE} < EndDate)
---------------------------------------------------------

I dont quite see how the evaluation of parameter will work with the SQL expression part in the same area. Any explanations or pointers to relevant articles explaning what we can and cannot do (and how to do this ) in the Record Selection formula editor will be of great help.

Greatly appreciate your help

Ram

 

[rosemaryl]

Dear Ram,

You made this way more complicated then it needed to be.

Try this:
{PAYMENT_REPOSIT.CREDIT_CARD_NUM} in ["nnnnnnnnnnnnn", "nnnnnnnnnnnnnn"]
and
{PAYMENT_REPOSIT.CANCEL_IND} = "N"
and
({PAYMENT_REPOSIT.EXTRACT_DATE} in
Switch (
{?DateRange} = "Daily", (currentdate - 1) to Currentdate,
{?DateRange} = "Weekly", minimum(WeektodateFromSun) to currentdate,
{?DateRange} = "Monthly",(if day(Currentdate) = 1
then minimum(LastFullMonth) to currentdate
else minimum(MonthtoDate) to currentdate
)
)

I think the above is what you want from trying to understand what you think you were doing.

You can search these forums, search the help fiels within Crystal or purchase a book for more information. I recommend George Peck's books.

Hope that helps.

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[ramkrish11]

Thanks Rosemary,

I was planning to make the evaluation of the keyword into a custom function. So that the SQLs will remain fairly uncomplicated.

Is George Peck's book for XI already out ?

thanks again
Ram

 

[rosemaryl]

Dear Ram,

Glad to help, I think ... as I do not understand your statement:

make the evaluation of the keyword into a custom function. So that the SQLs will remain fairly uncomplicated.

I don't know if the book is out yet, an Amazon search should answer that question.

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[ramkrish11]

By Evaluation of keyword, I mean evaluating keywords such as Daily, Monthly, Weekly, etc to decide on the data ranges.
I would rather have that evaluation (since there is a very good chance that this list of keywords will grow) in a function and then call that function to have 2 formula fields set up with start date and end date and then use those formula fields in the sql.

This way, I can easily add more functionality to the function without actually having to change each report.

Thanks much

 

[rosemaryl]

Dear Ram,

Ah, you mean the possible parameter values that will be passed.

Generally if formulas (which would include variable declarations) are used in the selection criteria Crystal will not pass the criteria to the where clause. This makes for inefficient record selection.

You can check this by doing a Database/show sql query on
Since I don't have Crystal 11, perhaps there is some functionality of which I am not aware.

Best regards,

ro

Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[ramkrish11]

That was one of my concerns too. Bcoz if it doesnt get pushed to the database, then it will return the whole data set and then do filtering in the client, which will be a huge hit on performance.

But this seems to be working Ok because I can see the actual dates in the SQL statement.

thank you very much.