Command Line SQL to use as a parameter

[Former Member]

Hello all,

I am a beginner/intermediate cyrstal XI sp2 user who is self-tought (with some help from this forum). Here is my question/request. I am attempting to utilize a command to prompt a parameter. The problem is my SQL skills are minimal. So, here is what I want to do.

I want to concatenate three fields to display in the parameter selection area as a dropdown menu.

Here is my SQL:

Select STRING(eh.EPISODE_NUMBER,' - ',eh.Program_Value,' - ',aa.assess_date) from system.episode_history eh, cwsavpmliveadassess.adult_assessment_butte aa where eh.EPISODE_NUMBER=aa.EPISODE_NUMBER AND eh.PATID = aa.PATID

Do I have to define the "aa.assess_date" as a date value?

How then do I incorporate the command into the parameters?

I hope this makes sense, if not, I will do my best to clarify.

Thank you,

KS

[Former Member · Accepted solution]

Q1: Do I have to define the "aa.assess_date" as a date value?

A1: No, you are concatenating it into a string.

Q2: How then do I incorporate the command into the parameters?

A2: Well the 1st thing you'll need to do is give you concatenated field a name... Then you'd simply create a new parameter and change the type to dynamic. Then, under "Value", where it says "Click here to add item", click it and find your command.

Just a warning: Based on the SQL you provided, you are bringing in a single concatenated field which means that you are unlikely to have a single field in your database that matches it.

You may want to bring in some sort of an ID field to use as the parameter value and use the concat field as the description.

HTH,

Jason

[Former Member · Accepted solution]

Thank you for your response.

I will give it a try and let you know how it goes.

Kevin

[Former Member · Accepted solution]

Hi Jason,

Well it kindof worked. The date field ended up as a numerical representation of the date and the report only showed one available item to select from. does this make sense? I am sure I am not explaining properly what I need so maybe I can explain a little better what I am looking for.

I have a report that represents a mental health assessment. The mental health assessment table links to 5 other tables to extract the pertinent data. The current report asks for a Client number and the date of the assessment. Most therapists can't remember the date of 40 or so clients. What I am trying to do is make the Client number explicitely link to the Episode that the assessment took place in and the program or office where it took place as well as the date. I want the data elements of Episode, Program, and Date to be a single string. So the look up would look like this:

Select Client-Episode-Program and Date: The lookup box would list a series of strings formatted as below.

- - -

What I have currently in the selection criteria is this:

"{ADULT_ASSESSMENT_BUTTE.PATID} = {?Client} and

{ADULT_ASSESSMENT_BUTTE.assess_date} = {?Assessment Date} and

isnull({episode_history.date_of_discharge}) and

{episode_history.FACILITY} = 1 and

{ADULT_ASSESSMENT_BUTTE.EPISODE_NUMBER}={episode_history.EPISODE_NUMBER}"

The data is grouped by a unique identifyer located in the Assessment Table and I have the "Select Distinct Records" Selected in the Database menu.

The Query and table linking are accurate and display the data exactly as expected.

So here a few questions to help me understand better what to do:

When I make the command do I replace any of the criteria above?

Is there a more effective way to group the data?

I hope this is helpful even if it is verbose.

I trully appreciate your help.

Thank you,

Kevin

[Former Member · Accepted solution]

Kevin,

As far as the the date showing up as a numeric string, you'll need to check the documentation for your database for a function that will convert a date field to a string field without displaying the underlying numeric values.

Google database name string functions and you're bound to find something that works.

As far as the rest of the parameter itself I'd be willing to bet that there's an AssessmentID that you aren't currently using.

So here's what I'd do.

I'd create a Command with 5 fields (no concatenation)... AssessmentID, Client_Name, Episode, Program, Date

Id then determine the most logical hierarchy that a user is likely to follow. Think of it this way. As a user trying to find a specific record, where would I generally want to start looking?

So for the sake of moving forward, lets say: Program > Episode > Client_Name > Date

I'd then create a parameter with a cascading list of values, using the command as the data source and I'd use the AssessmentID as the actual value that gets passed. See "Creating a parameter with a cascading list of values" in the online help for details.

By taking this approach, the user would first select the Program they want, which will filter the list of available episodes... The user would then choose an episode from those available, which will filter the Client_Names of available... The user would then Select the client they want and then they would finally select the appropriate date.

HTH,

Jason