Report Design: Using Boolean parameter

Print Page | Close Window

Using Boolean parameter

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=941
Printed Date: 03 May 2024 at 9:44am

Topic: Using Boolean parameter

Posted By: gazala
Subject: Using Boolean parameter
Date Posted: 28 Jun 2007 at 9:58am

I have three parameters on report , one is boolean and two are dates.Condition is if user selects True from boolean parameter,it should display all data from table regardless of date ranges,whereas if user select False it should display data within date range.Problem is its not giving any result if user selects true.when i change the parameter type from boolean to String its giviing result.I have a limitation that i cannot change the parameter type from boolean to String.Below is the query for doing that:

select event_id,event_dt from event
where event_dt like decode('{?alldates}','True','%') or event_dt between
{?startdate} and {?endedate}

any suggestions ?

Replies:

Posted By: jkwrpc
Date Posted: 28 Jun 2007 at 10:28am

I havent researched it but what happens if you remove the string quotes ('True') from around True (True). It may see it as a value and not a string.

Regards,

John W.

http://www.CustomReportWriters.net - www.CustomReportWriters.net

Posted By: BrianBischof
Date Posted: 28 Jun 2007 at 11:03am

Yeah, I agree that you should remove the quotes from 'True'. However, I'm a bit confused. I know that Boolean in SQL is a 0 or 1, Not the word true or false like it is in CR Syntax. Thus, I would swap out 'True' with 1 (or is it -1 in SQL? I forget). Secondly, the 'like' operator only works with strings. Thus, that is why it makes sense that you have to convert True to a string. But I wouldn't use a string anyway. I would do something like

where {?alldates} =1  or event_dt between  {?startdate} and {?endedate}

To fully research this, I would have to see how CR passes a boolean parameter to SQL. What exact value does SQL see on it's end? Actually, this is a great topic for the book. Please report back if you have a breakthrough. I'll have to put it on my to-do list as well.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: gazala
Date Posted: 28 Jun 2007 at 11:26am

Thanks for the reply.I got it worked by simply using Upper function around Paramater name and its value.So it means we have to use ' ' around Boolean parameter.The code looks like as below:

select event_id,event_dt from event
where event_dt like decode(upper('{?alldates}'),upper('True'),'%') or event_dt between
{?sdate} and {?edate}

Posted By: jkwrpc
Date Posted: 28 Jun 2007 at 11:37am

Thanks for passing the solution on to me. Never can tell when I will need to put it to use.

Regards,

John W.

http://www.CustomReportWriters.net - www.CustomReportWriters.net

Posted By: BrianBischof
Date Posted: 28 Jun 2007 at 12:11pm

Wow. I still don't understand how that made it work. Gotta print this one out and research it.

-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Print Page | Close Window