Command - Parameter - Multiple Values
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=22857
Printed Date: 28 Apr 2024 at 2:29am
Topic: Command - Parameter - Multiple Values
Posted By: BoltzGirl
Subject: Command - Parameter - Multiple Values
Date Posted: 21 Jan 2020 at 10:09am
|
I was given a report that was written quite some time ago and by someone else. I was challenged with figuring out why the IPA parameter used in this report, won't accept multiple values. I thought it would be a matter of editing the command, selecting the parameter and marking the box that says "Allow Multiple Values"....well that didn't work! Here is the part of the command I think that is having the problem. Is there something I need to change in this statement also, to allow multiple values to be chosen using this parameter?
WHERE amv.meh_ipa_id = ipam.ipa_id and ipam.ipa_id=ipax.ipa_id and amv.auth_number = am.auth_number and amv.auth_status in ('OP','NW','HD','CL') and amv.admit_date <= ---decode({?date},to_date('1800,01,01', 'YYYY,MM,DD'),sysdate,{?date}) decode({?Date},to_date('1800,01,01', 'YYYY,MM,DD'),sysdate, decode({?Date},to_date('1900,01,01', 'YYYY,MM,DD'),sysdate-1,{?Date})) and (amv.disch_thru_date >= ---decode({?date},to_date('1800,01,01', 'YYYY,MM,DD'),sysdate,{?date}) decode({?Date},to_date('1800,01,01', 'YYYY,MM,DD'),sysdate, decode({?Date},to_date('1900,01,01', 'YYYY,MM,DD'),sysdate-1,{?Date})) or amv.disch_thru_date is null) and amv.auth_type = '{?AuthType}' and ipax.ipa_parent_id = '{?IPA}') Auths ------------- Always appreciate the help! |
Replies:
Posted By: DBlank
Date Posted: 23 Jan 2020 at 6:11am
|
Is your parameter a dynamic parameter?
Is your issue literally not being able to select more than one item from the parameter or that it is not filtering based on that selection? |
Posted By: BoltzGirl
Date Posted: 23 Jan 2020 at 6:34am
|
It is not a dynamic parameter, it's static which I just verified. Should it be dynamic
Yes the issue literally is not being able to select more than one item from the parameter. ------------- Always appreciate the help! |
Posted By: DBlank
Date Posted: 23 Jan 2020 at 7:49am
| In the parameter set up screen under Value Options there is a option called 'Allow Multiple Values'. Set it to True |
Posted By: BoltzGirl
Date Posted: 23 Jan 2020 at 8:50am
|
I tried that right off the bat and it will not let me change that value to True. ------------- Always appreciate the help! |
Posted By: DBlank
Date Posted: 23 Jan 2020 at 8:53am
|
Not sure why but that is what controls it.
Is the parameter a data source parameter or one created in Crystal? |
Posted By: BoltzGirl
Date Posted: 23 Jan 2020 at 9:03am
|
The parameter is created in Crystal, under Parameter Fields yet it also lies within the command.
When I go to edit the command, there are 3 parameters also there and one of them is this IPA one that I wanted to change. That's why I though it was originally something with the command. One co-worker offered this opinion below: ***** I don’t know for sure, but it might be a problem with this statement: and (amv.disch_thru_date >= ---decode({?date},to_date('1800,01,01', 'YYYY,MM,DD'),sysdate,{?date}) decode({?Date},to_date('1800,01,01', 'YYYY,MM,DD'),sysdate, decode({?Date},to_date('1900,01,01', 'YYYY,MM,DD'),sysdate-1,{?Date})) or amv.disch_thru_date is null) and amv.auth_type = '{?AuthType}' and ipax.ipa_parent_id = '{?IPA}') Auths The line would normally be something like ipax.ipa_parent_id = ‘Value’. It looks like the way to reference the parameter is {?IPA}…. So if you make it ‘{?IPA}’ then I think it might partially fix your issue—notice there is an extra ending parenthesis right now that I think needs to be removed. ****** However, removing, adding the parens didn't work either from his opinion. ------------- Always appreciate the help! |
Posted By: DBlank
Date Posted: 23 Jan 2020 at 10:22am
|
If the parameter is being created in the command and passed to crystal it might not allow for multiple values.
I'd make a copy of the report then. Create another parameter in crystal call it IPA2, and set it to allow for multiple values. IF that allows you to do it then use it (?IPA2) in the crystal select. I'd then edit the command to remove any reference to the other @IPA. |
Posted By: hilfy
Date Posted: 23 Jan 2020 at 10:32am
|
This blog post has a lot of good information about how to use parameters with Commands: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/.
There are a couple of problems with how the Command is configured: 1. If you have a multiple-select parameter, you don't need the quotes around it. 2. Change "=" to "in" 3. Edit the parameter in the Command Editor - that's where you'll find the option to make it multi-select. -Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: DBlank
Date Posted: 24 Jan 2020 at 2:19am
| Thanks Hilfy, I never use Commands so this is great reference material. |
Posted By: hilfy
Date Posted: 24 Jan 2020 at 10:04am
|
You're welcome! I wrote the blog in 2015 because I got tired of having to type the same info over and over.
I'm glad it's of help! -Dell ------------- Proviti, Data & Analytics Practice http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics |
Posted By: BoltzGirl
Date Posted: 24 Jan 2020 at 10:30am
|
Thank you so much for that link and the information!
Really awesome information and I shared that with my staff here at work! GREAT INFO!!  ------------- Always appreciate the help! |