Parameter and Select Expert Problem 1

[BHaines]

I'd like to shoot whoever set up our database, but there is no full name field, nor any comprehensive field that would be unique enough to make a selection with a parameter which has

LastName, FirstName

of employees. I can put in a manual list of them, but how would I then get it to select proper records? Last name and First name are in Two separate fields (S_CONTACT.LAST_NAME and S_CONTACT.FST_NAME)

Here is the code so far, which does two other parameters:

({S_CUST_SURVEY.LAST_UPD} = {?WhatDates} ) and
(
if {?WhatSites} <> "ALL" then
{S_CONTACT.SOC_SECURITY_NUM} = {?WhatSites}
else if {?WhatSites} = "ALL" then
true
)

 

[MColeman]

if trim({S_CONTACT.LAST_NAME}) & ', ' &
trim({S_CONTACT.FST_NAME) = {?nameparameter}

 

[BHaines]

But what goes after it? If it's and If statement there has to be a then doesn't there?

 

[MColeman]

put it together with the rest of the record selection
depending on how you want it to fit in

if ({S_CUST_SURVEY.LAST_UPD} = {?WhatDates})
and
({?WhatSites} = "ALL" or ({?WhatSites} <> "ALL" and {S_CONTACT.SOC_SECURITY_NUM}= {?WhatSites}))
and
(trim({S_CONTACT.LAST_NAME}) & ', ' &
trim({S_CONTACT.FST_NAME) = {?nameparameter})
then true
else false

 

[BHaines]

I don't think that will work, because I don't have a single parameter like {?nameparameter}. I can only choose one table and one field for a parameter. I'd have to make 2 separate parameters, one for last name and one for first name, and have the parameter inputs merged together somehow and compared to the @employeename function, but I can't seem to do that. I'm pretty much resigned to not doing it at this point. Of the 30+ older reports no one in the company has managed to do it either.

 

[lbass]

You can create a parameter {?employeename} and then populate the default list with names from a separate report. You would create a separate report using the concatenated name fields in the detail section, with no headers. You would then export this to a text file (ttx). Save this to a location you will remember, changing the extension to "txt". Then open the text file in WordPad and remove all the quotes using a search and replace(a space for "). Save and close the file, and then in the parameter screen click on the Import button to populate the parameter list and choose the text file.

Now you should have a list with both names that you can use in conjunction with your record selection formula.

-LB

 

[BHaines]

That seems to work ok, but is there a way to make it display all records if no one is chosen?

 

[woodyinoz]

Put a * in as a default in the parameter.

Woody.

 

[BHaines]

I feel like an idiot, but how would I do that? (I've only had a 1 week crash course in CR 10 and am working on 8.5 and feeling utterly useless at this )

 

[lbass]

Manually add "All" (without the quotes) as your topmost parameter option in the list, and build your selection formula as you did for {?WhatSites}.

-LB

 

[BHaines]

That won't work though. I want them to be able to not fill out that parameter. I want to do that with the dates too, but nothing I try seems to work. They should be able to skip all the parameters if they just want to run an all dates/all sites/all users report.

 

[lbass]

Please try these options before deciding they won't work. If they don't select anything, "All" will be at the top of the list, and will be the default.

-LB

 

[BHaines]

But ALL is not at the top of the list. The list is sorted Alphabetically and there are names which preceed it alphabetically.

 

[lbass]

Go to the field explorer->parameter->set default values->select or enter value to add->add "All" and then in the default values list, locate "All" and use the arrows to move "All" to the topmost position.

-LB

 

[BHaines]

I tried that. It still resorts it back to a lower position in the parameter window and if left blank the first user is all that is returned. This list is unordered when it's in there. It has to be alpha sorted. Is there no way to have it check for a null or blank value for that parameter.

 

[lbass]

It doesn't have to be "All". Try entering a blank space by pressing the space bar once and then adding that to the list--that should sort first. Then in your formula use " " instead of "All".

-LB

 

[BHaines]

Ok, I have the all default fixed. I had to sort it, close it, come back in, and THEN move all to the top. But although I have my forumla working to default and show all records if ALl isn't selected, now it's not working when someone IS selected.

This is the code:

if ({S_CUST_SURVEY.LAST_UPD} = {?WhatDates})
and
({?WhatSites} = "ALL" or ({?WhatSites} <> "ALL" and {S_CONTACT.SOC_SECURITY_NUM}= {?WhatSites}))
and
({?NameParameter} = "ALL" or ({?NameParameter} <> "ALL" and (trim({S_CONTACT.LAST_NAME}) & ', ' &
trim({S_CONTACT.FST_NAME}) = {?nameparameter})))
then true
else false

Somewhere I have mucked it up and although it works when no one is selected, I've tried selecting a user I have confirmed should be showing up, and I get a blank report.

 

[lbass]

Try something like:

{S_CUST_SURVEY.LAST_UPD} = {?WhatDates} and
(
{if {?WhatSites} <> "ALL" then
{S_CONTACT.SOC_SECURITY_NUM}= {?WhatSites} else
if {?WhatSites} = "ALL" then
true
)
and
(
if {?NameParameter} <> "ALL" then
(trim({S_CONTACT.LAST_NAME}) & ', ' &
trim({S_CONTACT.FST_NAME}) = {?nameparameter} else
if {?NameParameter} = "ALL" then
true
)

It looks odd to me that you are setting what looks like a social security number equal to a site. In another post, I think you used another field--is this a mistake?

-LB

 

[BHaines]

I wish it was a mistake but it's not. Some SCHMUCKNUT decided instead of putting SSN's in the SSN field, they'd put the site worked at if it was an employee. There's even an employees table, but not all the employees are in it. Yay for utter lack of data integrity which takes an act of congress to get corrected(Welcome to my nightmare)

 

[BHaines]

Yay! That worked once I took out the extraneous curly brace! Thank you so much for putting up with my confusion!