Parameter from a Formula 2

[LLowrance]

I'm using CR 8.5 reporting from two different databases. A software called Perseus (a nightmare), joining to a sql server table.
The parent report is from Perseus where I have created a formula called Location.

@Location:
if tbl.location =1 then "Phoenix"
else
if tbl.location =2 then "Dallas"

You get the idea... I want to be able to put this formula into the selection criteria as a parameter, but the drop down list I want the user to select from is the text i.e."Phoenix","Dallas". So far all I can get to display in the drop down is the number. The user will not know what office the number corresponds to. Does anyone know how this can be done? I've also tried using the SWITCH function but it displays the number also. By the way, there are 72 offices so this is tedious to say the least.

Any help is appreciated.

Lowell

-LLL

 

[beltmanjr]

You could (and please keep in mind that I am using Crystal XI) go to the parameter properties and select a value field (the id of the office) and a descriptive field (the office name).

Now I'm guessing that you may not have any link between the location id and the office name in the same database (that would suck), and in this situation it would be good to have a better idea of where you could possibly find the link between office id and name? With more information we could possibly help you.

Making a lookup table may also be an idea if there really isnt any reference within your database from id to naam.

 

[lbass]

I would write a separate report that shows only the number column and the formula that results in the description. Make sure that each are attached to guidelines and then run the report and export it to tab-separated text and choose "application". It should automatically open in wordpad. Then use the replace function to replace the quote marks with nothing. Save the file, and then go into your report->parameter->edit->set default values->import->select the text document. It will populate both the number and the description column. Then set it to display by description only, but use the number value in your record selection formula.

-LB

 

[beltmanjr]

Super cool idea from lbass! Will save loads of time typing this stuff.. But reading your original post it talks about a parent report.. Is the parameter passed down to a sub report or something which comes from MSSQL?? Please explain

 

[LLowrance]

Perseus is a survey software. We use it to do claim file audits. The only field I can link to the sql tables is claim number. I had to write a formula to alter it because perseus has it as a numeric field and sql has it as alpha. Claim numbers are 9 digits, if it begins with zero, it truncates the zero so I had to LPAD with "0".
The subreport gives me all the other information i need. Unless you can join on a formula field, I'm forced to do this subreport.
I'm going to try what you both have suggested this morning and I'll let you know how it turns out.
Thanks for your assistance.

L

-LLL

 

[LLowrance]

lbass, thanks so much for the suggestion. I got it to work.

-LLL