BusinessObjects Board

Case statement variable is not working in Snowflake universe

Hi Team,
I have created variable with case statement on snowflake universe, I am encountering error as below mentioned error when I am using this variable with measure. It’s generic error.
The universe does not allow using a complex expression in a GROUP BY statement. You cannot run this query. (IES 00010)
Please advise me if I miss anything here

This is a Snowflake syntax issue with the SQL that’s being generated from the universe/query in the report. Because your query is aggregating data, it needs a group by. One of the dimensions you’re using is a case statement, which Snowflake doesn’t allow in a group by. So, you’ll have to find another way of getting the piece of data that uses the case statement - possibly by merging two queries: one will have the measures and the other will have just the case statement and whatever other dimension(s) you need for the merge.

-Dell

Hi Dell
Thanks a lot for your quick response, Yes, I will figure out the other options. It is simple functionality how come snowflake is not allowing case statement in group by. But I could able to run same query in snowflake. is it BO driver limitation or snowflake ?

Thanks,
Ram.

It may be a limitation of the driver you’re using to connect to Snowflake.

-Dell

It may be worth checking the prm file of the data connection you use if you have admin access to the server. It has a parameter group_by_supports_complex… Change it to yes, restart connection servers and try. Snowflake supports case statement in group by.

Hi Dell/bobano,
Thanks for you guys advises. It was driver issue.
It is working fine now when I am using Snowflake driver.

Team
do we have any best practices documentation on Snowflake universe?

It will be no different to any other universe based on a database.

Add value (which it sounds like you are by using case expressions), Make sure that your measures have aggregate functions (usually sum), make sure that numeric objects are formatted correctly (e.g. just 0 for years so they appear as 2022 not 2,022.00). Correct use of contexts and aliases, all the good stuff found in most universe guides - the one with the product is a good starting point.