Link to home
Start Free TrialLog in
Avatar of TBSupport
TBSupport

asked on

Crystal Reports 10.0.0.533: "Combined" Choices for Parameter

Hello:

Attached is my Crystal Report for version 10.0.0.533, and also attached is the programming for the report that is based on a stored procedure in a SQL database.  The name of this stored procedure is spCommissionSummary;1.

In the report, there is a parameter called "JobStatus".  The choices are either "Open" or "Closed".  I'm hoping that the parameter will, actually, allow the end user to choose both choices if need be.  That's my ultimate goal, here.

Also in the report is a formula called "JobStatus".  This formula says that if the parameter called "JobCloseDate" does not contain the date 01/01/1900, then the job is closed, else the job is open.

To that effect, this report parameter called "JobCloseDate" that pulls from a field in the spCommissionSummary;1 stored procedure (attached) called ME_Job_Close_Date.  This parameter offers "Discrete and a Range of Values".  

The Select Expert of the report states that the formula "JobStatus" equals the Parameter "JobStatus".  Here is the Select Expert's formula:

(
{@JobClosedDate} = {?JobCloseDate}
or {@IncludeOpenJobs} = 'True'
) and
{spCommissionSummary;1.ME_User_defined} <> "" and
not ({spCommissionSummary;1.ACTNUMBR_1} in ["5170", "5175"]) and
{@JobStatus} = {?JobStatus} and
{spCommissionSummary;1.DOCDATE} = {?Dates}

The following are my questions:

(1) Does the parameter "JobStatus" work, where the end user can choose both "Open" and "Closed" rather than one or the other if necessary?
(2)  Does the formula @L_JobStatus that is embedded on the report work?
(3) Is there a way to eliminate the "time" portion of the dates in this report where the end user does not have to specify midnight when prompted by the parameters?  If so, is eliminating the time portion required to be done in the programming of the stored procedure or can this be done simply in the Select Expert?

Thanks!  Much appreciated!

TBSupport
US-COMMISSION-REPORT-SUMMARY---PRODUCER-
US-Commission-Report-All-Editors.docx
Avatar of Mike McCracken
Mike McCracken

(1) Does the parameter "JobStatus" work, where the end user can choose both "Open" and "Closed" rather than one or the other if necessary?
It should work.  Crystal is smart enough to change the SQL to use IN or separate the multiple values with OR in the SQL passed to the database.

(2)  Does the formula @L_JobStatus that is embedded on the report work?
What is this formula supposed to do?

 (3) Is there a way to eliminate the "time" portion of the dates in this report where the end user does not have to specify midnight when prompted by the parameters?  If so, is eliminating the time portion required to be done in the programming of the stored procedure or can this be done simply in the Select Expert?
Yes you can eliminate the time by making the parameter a date rather than a datetime type.  However if the database has times then you will probably want to use date ranges rather than equal to a selected date.
You would want to delete the times from the dates in the database rather than trying to do it in the SQL.  Doing it in the SQL will slow the execution and probably make the date selection occur on the client rather than on the database server.  You could have the user enter a date start and end or if you always want to use 1 day or 1 week you could do that calculation in the selection.

mlmcc
ASKER CERTIFIED SOLUTION
Avatar of James0628
James0628

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TBSupport

ASKER

Hello:

If I choose "Date" instead of "DateTime" in the Edit Parameters field, the field is stripped out from the stored procedure.  So, I won't be able to go that route.

Any other suggestions?

TBSupport
Hello:

The formula @L_JobStatus is supposed to show one of the following:

if {?JobStatus}= 'Closed'
then 'Closed jobs only'
else if {?JobStatus} = 'Open'
then 'Open jobs only'
else 'Open jobs included'

TBSupport
If I choose "Date" instead of "DateTime" in the Edit Parameters field, the field is stripped out from the stored procedure.
I take it that you're trying to get the default values from the field?  How important is that, really?  Unless you plan to keep going back to the parameter edit screen and resetting the default values, over and over, can't you just set the default values some other way?

 If your db supports the date data type, you could try converting the datetimes to dates in the SP.  I suspect that CR 10 won't understand it, but I could be wrong.  You could give it a try.  For example,

CONVERT (date, JOBGL.ME_Job_Close_Date) AS ME_Job_Close_Date,


 As for @L_JobStatus, you didn't say what you want when both "Open" and "Closed" are selected, but based on your use of the word "only", I think you may want something like this:

if {?JobStatus} = 'Closed' and {?JobStatus} <> 'Open'
then 'Closed jobs only'
else if {?JobStatus} = 'Open' and {?JobStatus} <> 'Closed'
then 'Open jobs only'
else 'Open jobs included'

 James
I don't know that it matters, but, FWIW, I didn't realize that you'd accepted a solution when I wrote that last post.

 James