Editing a Parameter Append All Values

[Garyjr123]

I have a parameter on a report that I would like to do two things with:

1) When I append the table I get more then just physician abbrs, I get the whole table. I would like to limit the table to just physicians.

2) When the end user runs the report, I would like them to be able to pick ALL the physicians or the ones they want so I need an 'ALL' command to be able to be typed in.

I'm not sure the exact way to go about 1 but I'm thinking a formula for 2 would work.

Ideas?

Gary

 

[lbass]

What version of CR are you using? Will this be a dynamic or static parameter?

-LB

 

[Garyjr123]

CR XI and a dynamic parameter.

 

[lbass]

Then create a command like this:

select table.abbreviation
from table
where table.roletype = "Physician"
union
select 'All'
from table

Replace roletype = "Physician" with however you identify a person as a physician.

So not link the command to any tables in the main report, and do not reference the command in the body of the report--use it only to dynamically populate the picklist.

In the main report, set up the dynamic parameter to access the {command.abbreviation} field, and set it up to to allow multiple values. Then in your record selection formula (reprot->selection formula->record, enter:

(
(
{table.abbreviation} <> "All" and
{table.abbreviation} = {?Physician}
) or
{table.abbreviation} = "All"
)

-LB

 

[Garyjr123]

Ok so I'm trying to create this formula the way you are describing and I am running into an issue:

select {resindstaffinfo.resind_id} from resindstaffinfo
where {resindstaffinfo.is_pract} = "1"
union
select 'All'
from res_resindstaffinfo.abbr

Basically, the field I use to determine that the staff member is a physician is {resindstaffinfo.is_pract} = "1" and the parameter for physician abbr is in another table I have linked res_resindstaffinfo.abbr so that every staff member that has a resindstaffinfo.is_pract of 1 should be pulled into the list using a field from the other table res_resindstaffinfo.abbr. Is this possible? I'm not sure if I'm on the right track.

 

[lbass]

First of all, this is NOT a formula per se, but a command, which needs to be created in the "add command" area above your table list in the database expert, so the above syntax wouldn't work. Please identify your database and also go to database->show SQL query and copy that into the thread. Then I can help you with the command syntax, assuming that the table containing the phys abbr is in your current SQL.

Also, I was assuming that for some reason you want to show the physician abbreviation instead of the physician name for the prompt. Is that correct? Do you want to display the name, too?

-LB

 

[Garyjr123]

Here is the SQL Query for the report:

SELECT "res_resindstaffinfo"."name", "res_practreslist"."name", "proname"."abbr", "proname"."name", "res_practreslist"."abbr", "practproauthlist"."has_resmap", "pro"."has_resmap", "practproauthlist"."is_authorized", "res_practreslist"."restype_id", "res_resindstaffinfo"."respooltype_id", "pro"."resunit_id"
FROM ((((("phsprod"."dbo"."res" "res_resindstaffinfo" INNER JOIN "phsprod"."dbo"."resindstaffinfo" "resindstaffinfo" ON "res_resindstaffinfo"."res_id"="resindstaffinfo"."resind_id") LEFT OUTER JOIN "phsprod"."dbo"."practproauthlist" "practproauthlist" ON "resindstaffinfo"."resind_id"="practproauthlist"."resind_id") INNER JOIN "phsprod"."dbo"."pro" "pro" ON "practproauthlist"."pro_id"="pro"."pro_id") LEFT OUTER JOIN "phsprod"."dbo"."practreslist" "practreslist" ON "pro"."pro_id"="practreslist"."pro_id") LEFT OUTER JOIN "phsprod"."dbo"."proname" "proname" ON "pro"."proname_id"="proname"."proname_id") LEFT OUTER JOIN "phsprod"."dbo"."res" "res_practreslist" ON "practreslist"."res_id"="res_practreslist"."res_id"
WHERE "res_practreslist"."restype_id"=1 AND "res_resindstaffinfo"."respooltype_id"=1 AND "pro"."resunit_id"=5
ORDER BY "res_resindstaffinfo"."name", "proname"."abbr", "res_practreslist"."abbr"


I was reading up on dynamic picklists and I'm thinking that I do want to use the physician abbr but possibly add the physician name to the description by using resindstaffinfo.lastname + ' ,' + resindstaffinfo.firstname.

 

[lbass]

Is this an Oracle database? I'm not sure about the syntax for the concatenation, but for Oracle it would be:

select "res_resindstaffinfo"."abbr", "resindstaffinfo"."lastname"||' ,'||"resindstaffinfo"."firstname" "Name"
from "phsprod"."dbo"."res" "res_resindstaffinfo"
INNER JOIN "phsprod"."dbo"."resindstaffinfo" "resindstaffinfo" ON
"res_resindstaffinfo"."res_id"="resindstaffinfo"."resind_id"
where "resindstaffinfo"."is_pract" = '1'
union
select 'All', 'All'
from "phsprod"."dbo"."res" "res_resindstaffinfo"

Add the above query to database->database expert->your datasource-add command.

Do not link the command. In the main report, set up the dynamic parameter and add {command.abbr} as your linking field and {command.name} as the description field.

(
(
{res_resindstaffinfo.abbr} <> "All" and
{res_resindstaffinfo.abbr} = {?Physician}
) or
{res_resindstaffinfo.abbr} = "All"
)

-LB

 

[Garyjr123]

It's a SQL database.

 

[lbass]

Did you try the command? What happened?

-LB

 

[lbass]

Also try:

select "res_resindstaffinfo"."abbr", "resindstaffinfo"."lastname"+' ,'+"resindstaffinfo"."firstname" "Name"
from "phsprod"."dbo"."res" "res_resindstaffinfo"
INNER JOIN "phsprod"."dbo"."resindstaffinfo" "resindstaffinfo" ON
"res_resindstaffinfo"."res_id"="resindstaffinfo"."resind_id"
where "resindstaffinfo"."is_pract" = '1'
union
select 'All', 'All'
from "phsprod"."dbo"."res" "res_resindstaffinfo"

-LB

 

[Garyjr123]

LB,

As usual you are my hero. I already fixed your Oracle Query with a SQL query, added to my report, and finally created the parameter which so far has worked.

BTW, you are missing the AS between ".first_name" and "NAME"

 

[lbass]

In Oracle you don't use "as". Glad you got it working.

-LB

 

[Garyjr123]

LB,

The only thing I am seeing that is not working is being able to add all the physicians. It looks like the dynamic parameter added 'ALL' to the physician list and when you select 'ALL' no info populates the report. I'm guessingthe report is looking for a physician that is named 'ALL' instead of choosing all the physicians for the report. This is what I set up as the record selection:

{res_practreslist.restype_id} = 1 and
{res_resindstaffinfo.respooltype_id} = 1 and
{@Currently_Authorized} = "Y" and
{pro.resunit_id} = {?ResuintID} and
not ({@Pract_Has_Resmap} in [" "]) and
(
({res_resindstaffinfo.abbr} <> "All" and
{res_resindstaffinfo.abbr} = {?Pract_Abbr}) or
{res_resindstaffinfo.abbr} = "All"
)

the command looks like this:

SELECT
"res_resindstaffinfo"."abbr", ("resindstaffinfo"."last_name" + ' , ' + "resindstaffinfo"."first_name") as "Name"
FROM
"phsprod"."dbo"."res" "res_resindstaffinfo" INNER JOIN "phsprod"."dbo"."resindstaffinfo" "resindstaffinfo"
ON "res_resindstaffinfo"."res_id"="resindstaffinfo"."resind_id"
WHERE
"resindstaffinfo"."is_pract" = '1'
UNION
SELECT 'All', 'All'
FROM "phsprod"."dbo"."res" "res_resindstaffinfo

 

[lbass]

Sorry--I wasn't thinking. Should be:

(
(
{?Physician" <> "All" and
{res_resindstaffinfo.abbr} = {?Physician}
) or
{?Physician} = "All"
)

-LB

 

[Garyjr123]

Eureka! (Great show on SyFy btw, check it out). It works! Thanks again LB!