Parameters using formulas

[morles]

I want to create a parameter which has a dropdown list of Customers. However this list is coming from a formula and crystal doesn’t seem to allow this. Is there any way that this can be done?

 

[briangriffin]

Can you recreate the formula in SQL? If so, write a command and base the parameter on that - don't link the command to any other tables.

select
customername + someformulamanipulation
from customertable

 

[morles]

Thanks for you response. I can recreate in SQL but as Im using a FULL OUTER JOIN to join two tables a NULL value is returned for every column of the table that lacks a matching row. I would like if there was no match that it returned text

Heres an example of my statement:

SELECT table1.Opp_Id, table1.Name, table1.Objective, table1.Revenue, table1.Name_Type, table4.QuoteNo, table2.Name as CustomerName
FROM ((table1 LEFT OUTER JOIN table3 ON table1.Opp_Id=table3.Opp_Id) LEFT OUTER JOIN table4 ON table1.Opp_Id= table4.Client_Id) FULL OUTER JOIN table2 ON table3.Client_Id=table2.Client_Id
_____________
I would like:
If {Command.CustomerName} = "" then {Command.Name} else {Command.CustomerName}

I know I can do this using a formula but then I wont be able to create a dynamic parameter from this formula. Im not overly familar with SQL so I dont know how to include this exception within the SQL statement.
Many thanks

 

[IanWaterman]

Try

SELECT table1.Opp_Id, table1.Objective, table1.Revenue, table1.Name_Type, table4.QuoteNo,
case when table2.Name is null then table1.Name else table2.Name end as CustomerName
FROM ((table1 LEFT OUTER JOIN table3 ON table1.Opp_Id=table3.Opp_Id)
LEFT OUTER JOIN table4 ON table1.Opp_Id= table4.Client_Id)
FULL OUTER JOIN table2 ON table3.Client_Id=table2.Client_Id

Not sure what your database is but there are functions that could be used instead of the Case statement

Oracle
NVL(table2.Name, table1.Name)

SQL server
isnull(table2.Name, table1.Name)
Ian

 

[morles]

Thank you. That worked great.Im using SQL Server. The only issue I seem to have now is that not all the customer names are being displayed when the user is prompted to pick a customer. I set up a dynamic parameter to enable the user to select the relevant customer.

The customers are on the report before it is filtered but there is no option to pick particular customers from the drop down list which users are prompted with. There doesnt seem to be any logical reason for this.