Results 1 to 5 of 5

Thread: Formula using an unknown?

  1. #1

    Default Formula using an unknown?

    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.
    Code:
    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

  2. #2
    Join Date
    Feb 2009
    Posts
    1,496

    Default Re: Formula using an unknown?

    The SQL translation of that Cyrstal Syntax would be to use an SQL Case statement like this:
    Code:
    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

  3. #3

    Default Re: Formula using an unknown?

    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.

  4. #4
    Join Date
    Feb 2009
    Posts
    1,496

    Default Re: Formula using an unknown?

    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

  5. #5

    Default Re: Formula using an unknown?

    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
    Last edited by Kyle; 28-Aug-2018 at 11:00 AM. Reason: Added a question.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •