Using 2 Alternative Parameters ?

[dcpking]

I'm using CR 9.2.0.448 based on MS SQL Server 2000. My company is a REIT, dealing with malls. We identify a property either by number or by name - both varchar fields. I can create parameter inputs for either one, but some users want one method (by name) and some the other (by number).

Can I have two alternative parameter entry methods ? Or, how can one convert from name, say, to number (both exist as fields in the same table), maybe in a formula ?

BTW, an answer of "neither is possible" would also be good, as I could stop trying to make this work

TIA

Mike

 

[synapsevampire]

You can use just one parameter with the isnumber() to determine the record selection formula, as in:

(
if isnumeric({?PromptMe}) then
{Bob.Customer ID} = val({?PromptMe})
else if not(isnumeric({?PromptMe})) then
true
)
and
(
if not(isnumeric({?PromptMe})) then
{Bob.Ship Via} = {?PromptMe}
else if isnumeric({?PromptMe}) then
true
)

Note that the logic appears to be malformed, but it's intentionally designed this way to properly pass the SQL to the database.

-k

 

[rhinok]

[ol][li]Create a parameter called {?Parameter Type} that has default values of 'ID' and 'Name'. Only allow a single selection and don't allow modification of the value.[/li]

[li]Create a parameter called {?Parameter Value}. Leave the list of values blank.[/li]

[li]Modify your Record Selection Criteria to allow for both parameters:

{table.id} = Switch({?Parameter Type} = 'ID',{?Parameter Value})

Or

{table.name} = Switch({?Parameter Type} = 'Name',{?Parameter Value})

The Record Selection Criteria listed above will return SQL (Database|Show SQL Query) as follows when selecting a Type of 'Name' and a value of 'K, Rhino':

WHERE
    (table."ID" = '' OR
    table."Name" = 'K, Rhino')

This shouldn't be a problem unless you have low values (not Nulls) stored in the ID or Name fields.

An alternative Record Selection Criteria is:

(
  If {?Parameter Type} = 'ID'
  Then {table.ID} = {?Parameter Value}
  Else If {?Parameter Type} <> 'ID'
  Then False
)

Or

(
  If {?Parameter Type} = 'Name'
  Then {table.Name} = {?Parameter Value}
  Else If {?Parameter Type} <> 'Name'
  Then False
)

[/li][/ol]The above statement returns SQL (Database|Show SQL Query) as follows when selecting a Type of 'Name' and a value of 'K, Rhino':

WHERE
    (table."ID" = 'K, Rhinok' OR
    table."Name" = 'K, Rhinok')

Since you shouldn't have IDs and Names with the same value, this shouldn't cause any adverse affects and it accounts for low values.

If you have any other parameters that you need in addition to the ID/Name selection, then just create the new parameter and account for it in your Record Selection Criteria:

{table.field} = {?some other parameter}

AND

{table.id} = Switch({?Parameter Type} = 'ID',{?Parameter Value})

OR

{table.name} = Switch({?Parameter Type} = 'Name',{?Parameter Value})

It is important to note that in order for the ID/Name selection to work, both fields must be of the same datatype (both String in this example). You would need to convert number to a string (preferably on the db side) for your situation.

 

[Ngolem]

so basically you want to give the option of having your user select the format of the report...By Name or number of the property

Sorry...it is easy...so you will have to keep trying at it...haha

First you need a single parameter

{?ReportType}

Parameter Type: string
Description: Type "A" to report by Property Name
Type "B" to report by Property Number
Default value: A



Now you want to group either by name or number...you say the number is a string so that is good

//@Group1

if Ucase({?ReportType}) = "B" then
{Table.PropertyNumber}
else
{Table.PropertyName};

you do it this way to default to {Table.PropertyName} if the user enters bad data

now you create formula for key information display

for example: a report title

//@ReportTitle

WhilePrintingRecords;
if Ucase({?ReportType}) = "B" then
"RealEstate Report by Property Number"
else
"RealEstate Report by Property Name";

there are many other formulas that will flip-flop like this depending on the user option chosen and you can make them a lot fancier.

NOTE: don't use "WhilePrintingRecords" in the @Group1 formula...you don't use this functin in grouping, sorting or summary formualas

there is the basics for what you want


Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[dcpking]

Many thanks everyone - I got it to work fine ! One interesting thing: the first time I did it I put the

{table.id} = Switch({?Parameter Type} = 'ID',{?Parameter Value})

Or

{table.name} = Switch({?Parameter Type} = 'Name',{?Parameter Value})


into the Report | Select Expert | Show Formula field. It automagically created me TWO entries in the expert (one for id and one for name) and then, when I ran it, I got a blank screen and a page count of &quot;33 of 1+&quot;. I clicked on the &quot;Stop&quot; button to the right of the page navigation bar and CR9 just vanished - totally, between one screen refresh and the next !! Maybe I'll be a little more careful next time <g> !

Mike

 

[synapsevampire]

If it's a numeric vs. text, you shouldn't need to have the user select which it is, check my post, just use the isnumeric function - this seemed to get overly complicated...

Also, make sure that you check the Database->Show SQL Query after creating the record selection formula to make sure that you are passing the SQL. Generally a Switch does an OK job of it, but test, everything in the record selection formula should be reflected in the SQL.
-k