LISTAGG / LISTAGG Order In Answers

Temoryian

Hi everyone,

We are using 12.2.1.4.0 of OBIEE.  I noticed in answers that I am able to use a LISTAGG function on what appears to be only non-fact columns/values.  Previously in this and other forums I have read that LISTAGG was not an option unless wrapped in EVALUATE functions.  LISTAGG is also not mentioned in the Logical SQL Reference Guide.  This has been nice to be able to list by ID or other unique identifiers to quickly build a new column in answers.  Maybe this has always been an option and I just didn't realize but I don't believe it was.

I haven't been able to find a way to order the values that LISTAGG produces in answers.  My result sets seem to LISTAGG them in the order encountered and therefore will list values in a pivot as "John,Smith" and "Smith,John".  Has anyone had success in ordering the listagg?  Again I know I can do this at the RPD level but just curious if there is a way to do this in answers.  Also are there any other functions out there that the OBI server recognizes that are helpful or a documnent that has all of the functions that the OBI server will recognize? 

Thanks for your time.

Joel

Do you have an Order By Clause in your call to the LISTAGG function?

Have you looked at this previous thread? - How To get Distinct Records using ListAgg function in OBIEE Report ?

Temoryian

Hi Joel,

Thank you for sharing.  I had seen that thread and noticed that they wrapped the LISTAGG in an EVALUATE function.  My organization has the EVALUATE_SUPPORT_LEVEL parameter in the NQSCONFIG.INI file turned off as mentioned here and unfortunately they won't turn it on:

https://gerardnico.com/dat/obiee/obis/logical_sql/evaluate

Now I could go to the RPD and execute this but not everyone in my organization has access to the RPD and that is why I'm looking for a way to order in Answers.  Currently in the Criteria tab we have columns similar to the one created below:

LISTAGG("Table Name"."Column Name" by "Employee"."Emplid")

In this same Edit Column Formula in the Critera tab in Answers can I order this LISTAGG in any way by "Column Name"?

With this being a custom column in Answers this is the first selection step executed if that is helpful.

Thanks again for your time.  Much appreciated.