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
LVL 1
TBSupportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
(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
James0628Commented:
There's a basic issue, in that the stored procedure has no parameters, and your parameters are just used in the report, so the SP has to gather all of the data and pass it to CR, which then uses the parameters to filter the data.  If you have a lot of data, it would be much more efficient if you could pass the parameters to the SP.  Since you're allowing multiple values and ranges in the parameters, that may not be possible (without changing your parameters), but I wanted to mention it.

 As mlmcc said, if you enter both "Open" and "Closed" for @JobStatus, CR should include both, although, unless I'm mistaken, that test won't be passed to the server, because you're using a SP.  CR will use that test to filter the data from the SP.


 For @L_JobStatus, it depends on what you want it to do.  If you're asking what happens if you select both "Open" and "Closed" for ?JobStatus, in that case, both {?JobStatus} = "Open" and {?JobStatus} = "Closed" will be true.  Since you check for "Closed" first, you would get 'Closed jobs only'.  If you want to display something special when both are selected, you should start the formula with something like:

if {?JobStatus} = "Open" and {?JobStatus} = "Closed" then
  "Both Open and Closed"
else
  <the other tests>


 As mlmcc said, you could change the datetime parameters to dates.

 If your db server supports a date data type (more recent versions of MS SQL do), then you could change the field from a datetime to a date there, although I don't think CR 10 would understand a date data type coming from MS SQL (but I could be wrong).  Another option would be to have the SP replace the time in the datetime columns with midnight.

 However, since you're using a SP, _and_ those parameters allow multiple discrete and/or range values, those tests aren't going to be passed to the server anyway, so I don't think there's any point in trying to change the values in the SP.

 If you switch to date parameters, the times in the datetime fields will be an issue (assuming that they're not always midnight).  It looks like you're already using the Date function (in @JobClosedDate) to remove the time from ME_Job_Close_Date.  If you're going to change the ?Dates parameter from datetime to date, then you probably also want to use the Date function on DOCDATE in the record selection formula.


 FWIW, your use of @JobStatus in the record selection formula seems a bit obtuse to me.  Instead of

{@JobStatus} = {?JobStatus} and

 I would probably use something like

(
({?JobCloseDate} <> Date(1900,01,01) and {?JobStatus} = 'Closed') or
({?JobCloseDate} = Date(1900,01,01) and {?JobStatus} = 'Open')
) and

 I find that easier to follow than having to switch between the record selection formula and @JobStatus, to see exactly what's going on.  OTOH, if you created the report, it may be perfectly clear to you as is.  Also, to be accurate, since both ?JobCloseDate and ?JobStatus allow multiple values, replacing @JobStatus with the tests above could change the logic a bit.

 Also, looking at the revised tests above points out that you're actually just comparing parameters there, and not looking at the data at all.  I guess you're trying to make sure that the parameters are "compatible"?  If nothing else, I would move that test to the beginning of the record selection formula.  There's no point in checking the fields, if the parameters aren't "compatible".  It probably won't have any real effect on the performance, but it seems more efficient.

 Also, you're testing for ME_User_defined <> "", and ACTNUMBR_1 <> "5170" or "5175".  If you have many records with those values, it would be more efficient if you could include those tests in the SP.

 James

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TBSupportAuthor Commented:
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
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

TBSupportAuthor Commented:
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
James0628Commented:
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
James0628Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.