Dynamic Parameter Problem (Adding a Command)

[Garyjr123]

I created a command for my report:

select resindstaffinfo.last_name + ', ' + resindstaffinfo.first_name from phsprod.dbo.resindstaffinfo
where resindstaffinfo.is_pract=1

I did not link this command in the Database expert and I am not referencing it in my main report but when I try to run a report it comes back blank when I enter the surgeons name from this command.

My parameter set up looks like this:

Name: Practitioner
Type: String (greyed out)
List of Values: Dynamic

Prompt Group Text: (blank)

Choose a Data Source: Existing (Expr1000 - Prompt Group)

Value: Expr1000 Description: (None) Parameters: ?Practitioner

Value Options - Level 1 (Expr1000)
Prompt Text: Enter Practitioner (Last Name, First)
Sort Order: Ascending by Value
Prompt with Description Only: False
Allow multiple values: True
Allow discrete values: True
Allow range values: False

What did I do wrong or how do I fix it so that my report populates?

Thank you,

Gary

 

[lbass]

So the dynamic pick list was populated?

How did you set up the selection formula to reference the parameter?

-LB

 

[Garyjr123]

Hey LB!

Yes, the data populated perfectly (or near as I can tell when I right click on the field and browse the data).

In the Select Expert (last line):

{appt.start_datetime} in {?StartDate} to {?StopDate} and
{res2.resunit_id} = {?ResUnitID} and
{res2.restype_id} = 2 and
{resbooking.start_datetime} in {?StartDate} to {?StopDate} and
{Command.Expr1000} = {?Practitioner}

I'm thinking I shouldn't set it to 'equals to'....

 

[lbass]

No, the = is fine. Your formula should NOT reference the command field--it should reference your database field that returns "lastname, firstname." You should not link the command OR reference it in the main report.

-LB

 

[Garyjr123]

Ok, since I concatenated two (last_name & first_name)fields within the table resindstaffinfo which table.field should I be referencing?

 

[lbass]

It has to exactly match the way people are entering it, so if the user is entering:

Doe, John

...then you should use a formula like this:

{table.lastname}+", "+{table.firstname}

...and then set this formula (or its contents) = your parameter.

-LB

 

[Garyjr123]

Now...

I changed the Selection Expert:

{appt.start_datetime} in {?StartDate} to {?StopDate} and
{res2.resunit_id} = {?ResUnitID} and
{res2.restype_id} = 2 and
{resindstaffinfo.last_name} + ", " + {resindstaffinfo.first_name} = {?Practitioner}

and I tried

{appt.start_datetime} in {?StartDate} to {?StopDate} and
{res2.resunit_id} = {?ResUnitID} and
{res2.restype_id} = 2 and
({resindstaffinfo.last_name} + ", " + {resindstaffinfo.first_name}) = {?Practitioner}

I am still getting a blank report.

Could my GH be a problem since I concatenated the parameter?
GH1: probooking.prim_pract_id
GH2: appt.start_datetime
GH3a: appt.appt_id
GH3b: subreport1
Details: subreport2
GF1-3: (suppressed)

Subreport1 is linked from appt.appt_id to subreport probooking.appt_id

Subreport2 is linked from probooking.prim_pract_id and probooking.pro_id to subreport resindstaffinfo.resind_id and practproauthlist.pro_id

I was thinking that it could be of one of my subreports but none of my main report populates (not just a subreport).

 

[lbass]

If you leave out the last line in the selection formula do you get results?

You should also make sure the case matches for the name formula and the parameter entry.

-LB

 

[Garyjr123]

LB,

To be continued tomorrow...

I think I need to start from scratch with this report.

Gary

 

[lbass]

Well, I think you just need to do a little troubleshooting, but that you are on the right track.

-LB