PDA

View Full Version : Formula using an unknown?



Kyle
23-Aug-2018, 08:46 AM
Can you write logic into AI for a report? An example formula in crystal would be below. With ?PARAMETER_NAME being a selected parameter the user selects.


If ({TABLENAME.FIELDNAME1}) = ({?PARAMETER_NAME}) Then {TABLENAME.FIELDNAME1} Else
If ({TABLENAME.FIELDNAME2}) = ({?PARAMETER_NAME}) Then {TABLENAME.FIELDNAME2} Else
If ({TABLENAME.FIELDNAME3}) = ({?PARAMETER_NAME}) Then {TABLENAME.FIELDNAME3}



Basically, I have a value that has the potential to be in 1 of 7 fields. I have only shown 3 above, but one could keep going to 7. I want to show one column that represents that value only if and when it is found. If a record does not have the desired value then don't show that record. If it does have the desired value show that record and show that value in this column.

Thanks all,

Kyle

Bob Cergol
24-Aug-2018, 10:17 AM
The SQL translation of that Cyrstal Syntax would be to use an SQL Case statement like this:


Case
when TABLENAME.FIELDNAME1 = @PAR1@ Then TABLENAME.FIELDNAME1
when TABLENAME.FIELDNAME2 = @PAR1@ Then TABLENAME.FIELDNAME2
when TABLENAME.FIELDNAME3 = @PAR1@ Then TABLENAME.FIELDNAME3
ELSE ''
End
as Some_Output_Column_Name


The "Else" is optional, but if no condition is satisfied, usually a good idea in order to avoid a null value as the result.

Dynamic AI supports up to 10 @PAR#@ replaceable parameters. When data type is character then the parameter must be enclosed in single quotes in the SQL.

At runtime Dynamic AI will generate a UI object to prompt the user for a value -- which can have a default specified in the view.

You would typically use parameters inside sub-queries in a dynamic view.

Bob

Kyle
24-Aug-2018, 01:47 PM
Hey Bob,

Thanks that gets me a little closer. If I use that case statement in a dynamic SQL view. It will execute the SQL and give me results. This then causes me to run into 2 issues.

Issue 1. I believe it returns all records and just leave's a blank or 0 if I use Else '0'. I only want it to return the parameter I selected.


Issue 2. It will not allow me to save the view. It errors and tells me to check the log. This may be because I am very new to all of this, however, the log doesn't seem to give me an indication of why I cannot save.

Bob Cergol
24-Aug-2018, 03:45 PM
Hi Kyle,

I think it will be best to review the specific details with you in a screen-share session so I can understand the full context of what you are trying to do. Perhaps you need to be using this case statement in a where clause that applies to a sub-query in the view, otherwise you'd just use a report filter. There's also another advanced feature called "redirected filters" that directs the run-time report filter value back into the view as if it were a parameter.

Regarding your save error -- try clicking the "columns" or "execute" button before saving and you will likely get a more useful error message.

Bob

Kyle
28-Aug-2018, 08:11 AM
Thanks Bob,

I got it. Thank you for the help after your suggestions coupled with some of your videos I was able to get it to react the way I wanted. Additionally, is there a way to create a custom pick list for @par1@? I saw how to create a date picker but in this case I want an ID and a description. Thanks again.

Kyle