use commands to populate lists for the user to select from for filtering

Posted on 2011-04-28
Medium Priority
Last Modified: 2012-05-11
I have the following join statement using a parameter in my SQL command object:
 LEFT OUTER JOIN patient_fyi_flags fyi ON p.pat_id = fyi.patient_id AND fyi.Active_C = '1' AND fyi.pat_flag_type_c IN {?FYI Flag}
I have checked the checkbox "Allows multiple values" in the Command Parameter form.

How do I then somehow make it so that the command displays a list of FYI flags for the user to select from?
Question by:wdelaney05
  • 4
  • 2
LVL 101

Expert Comment

ID: 35484097
I don't use commands very often.  I just tried to build one and at least in CR XI, I don't see a way to provide a value list for the parameter.

I also don't see a way to allow it to accept multiple values.

What version of Crystal?

Where did you add the parameter?


Author Comment

ID: 35484197
Ah, it's you, mlmcc. I'm using CR 2008, version I added the parameter in the Parameter List in the Modify Command window. I selected the parameter name in the Parameter List in the Modify Command window, then clicked the Modify button. The Command Parameter window then displays. In that window you can check the checkbox "Allows multiple values".
LVL 101

Expert Comment

ID: 35484633
That must be a new feature in CR2008.

If I can get to a CR2008 install, I'll see if I can figure it out.

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Accepted Solution

wdelaney05 earned 0 total points
ID: 35484976
mlmcc: Ok, I just figured it out. In short, I built two command objects. I have my main one and then I have a second one with a "Select distinct fyi_id, fyi_flag_name from my table" SQL statement. I built a dynamic parameter list based upon the second command object's fyi_flag_name. I created a new parameter {FYI Flag} based upon this second command object. In my Select Record - Expert window, I entered the statement {COMMAND.fyi.pat_flag_type_c} = {FYI Flag}. {COMMAND.fyi.pat_flag_type_c} is from my first command object.  {FYI Flag} is from my second command object.
I know this may be confusing. Let me collect my thoughts and then I will write a more detailed recipe on how to do this. This will take me a while...
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35768002
There is parameter in that, 3 type to fill parameter value
1. DAtaset,
2. Manuall
3. Query

Use query window to fill the parameter options.

Author Comment

ID: 35917376
Apologies for the delay in closing this question!

Author Closing Comment

ID: 35950945
I guess I'll award my answer as the best by default. So far, this is working with {?fyiFlagID} as a command parameter. I set this up as a dyamic parameter, though, that calls a second command object named FYIFlagList. this command object has the following SQL:
pat_flag_type_c AS "FYI FlagTypeID"
, name
from zc_bpa_trigger_fyi
ORDER BY upper(name)
For the dynamic parameter, I pointed it to FYIFlagList as the value source. The user is then prompted for the FYI flag from a dynamic list.
Do not link the two command objects, together. You'll get an informational message if you ever update the SQL and then save: Your current link configuration contains multiple starting points. Please be advised that this is generally not supported.
Just click ok - the functionality doesn't seem to be degraded in any way, I tested this quite a bit.

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your organization moving toward a cloud and mobile-first environment? In this transition, your IT department will encounter many challenges, such as navigating how to: Deploy new applications and services to a growing team Accommodate employee…
Suggested Courses
Course of the Month9 days, 15 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question