dynamic parameter

[cdulong]

I am trying to put the finishing touches on a seniority report. This is what I have so far:

-> 2 Tables - Full Time & Part Time
-> Each table contains hours of seniority based on class (full time & part time)

I have two different reports a full time report and a part time report, my full time report works great (as it takes all the records from the full time tables and also the records from the part time table that the names match the records in the full time table). Hope I haven't lost anyone yet.

The Part Time report is were I am having difficulty. I currently have it set up so that when the report is ran it looks at all the records in the 'part time' table and finds only the records that do not have a matching record in the 'full time' table (thus employee must be Part Time). Now for my issue, when the report is ran I have it set up so that the user can select employees not to show up on the report via a dynamic parameter (ie: employee has not passed probation). When the list is shown to select the employees it includes all employees both Full Time and Part Time, my question is how would I set this up so that I only have Part Time employees show up on this list?

If you need more info please ask.

Thanks,
cdulong

 

[ainkca]

Can you post the text in your selection criteria?

 

[cdulong]

The first line automatically eliminates emplyees with less than 520 hours (standard probabtion period), I have also set up a 'Left Outer Join' for "PartTime.Name --> FullTime.Name"

{PARTTIME.ACCBALHOUR} >= 520.00 and
isnull({FULLTIME.ACCBALHOUR}) and
{PARTTIME.NAME} = {?Include}

 

[lbass]

Your parameter {?Include} is confusing, since you earlier indicated you wanted to exclude employees by parameter. Anyway, you could create a command that is set up like this:

select parttime.name
from parttime left outer join fulltime on
parttime.name = fulltime.name
where fulltime.name is null and
parttime.accbalhour >= 520

Add the punctuation appropriate to your database. Do not link the command to any tables and do not reference this table in your main report. Use it only for the dynamic parameter where you will choose {command.name} to populate the parameter option list.

-LB

 

[cdulong]

Just to clairify when you say 'create a command' do you mean create a SQL Expression?

Also, when I create this command should I use:

parttime.name

or

{parttime.name}

Thanks,
cdulong

 

[cdulong]

lbass,

I did figure out what you meant by 'create a command'
after I did what you suggeted, I get an error after trying to create the parameter:

"A parameter already exists with the name. Please specify a different name."

I tried changing the parameter name but still no luck. any suggestions?

 

[lbass]

I think you have to remove the existing parameter and create a new one, so remove all references to it before creating the new parameter.

-LB

 

[cdulong]

Thanks works great now.


Except for if I do not want to exclude any employees it will not let me run the report (Value is not valid). Any way I can set this up to work correctly?

Thanks,

 

[lbass]

What is your current selection formula? Please copy it here.

-LB

 

[cdulong]

{PARTTIME.ACCBALHOUR} >= 520 and
isnull({FULLTIME.ACCBALHOUR}) and
{PARTTIME.NAME} <> {?Exclude}

 

[cdulong]

just to keep you up to date I think I managed to find a work around. I adde the following to the custom command you had me create the other day:

union select '*INCLUDE ALL*'
from parttime

For one the '*' puts this at the top of the list so the user will be able to use and find this option quickley every time.

Thanks for you help.

 

[lbass]

Yes, that is the approach I would have suggested.

-LB