Dave's Adventures in Business Intelligence

The @Select() function adds a lot of flexibility as long as it is used properly. When to use it (and when not to use it) is probably a future blog post (or perhaps even more than one 😆 ). But one of the things that I don’t care for is the fact that when I do use it, the referenced code is embedded within extra parenthesis. It turns out there is a way to change that behavior.

The Way It Was

I have built universes with an “ALL” constant added to list of values queries (also a future blog post). Once the universe was delivered the user requested that the word “ALL” be changed to something different. That meant I had to search throughout the entire universe to find the original “ALL” and update it. That meant searching through prompt objects as well as a large number of LOV queries.

Instead of updating it to the newly requested string, I used the @Select() function.

My new object named “ALL String” looked like this:

' ALL '

The spaces are to try to encourage the ALL string to appear at the top. Here is what a prompt looked like:

( @Select(Item\Item) IN @Prompt('Please select Item','A','LOV\Item',multi,constrained)
OR @Select(LOV\ALL String) IN @Prompt('Please select Item','A','LOV\Item',multi,constrained) )

The SQL code behind that prompt looked like this:

( ( table.item ) IN @Prompt('Please select Item','A','LOV\Item',multi,constrained)
OR ( ' All ' ) IN @Prompt('Please select Item','A','LOV\Item',multi,constrained) )

The parenthesis around the ‘ ALL ‘ are completely unnecessary. They also exist around the table.item reference as I have used the @Select() function there as well. They don’t break anything, but it does – in my opinion – make the SQL harder to read and understand. I have been asked more than once by the DBA team why I have them there. 🙂

Losing My Parenthesis

In looking for a solution to something completely unrelated I noticed a new parameter that has appeared in my universe. (Note: This blog post pertains to XI R2. I have not checked to see if this feature is available in earlier versions.) The parameter name is EVAL_WITHOUT_PARENTHESIS and the default value is No. If you open the parameter screen for your universe and update this from “No” to “Yes” then the SQL code for the same condition posted above becomes the following:

( table.item IN @Prompt('Please select Item','A','LOV\Item',multi,constrained)
OR ' All ' IN @Prompt('Please select Item','A','LOV\Item',multi,constrained) )

I like this much better. I believe the default is set to “No” in order to preserve any complex expression logic from the referenced object. But for a simple constant reference they are certainly not needed. I would rather put the parenthesis in myself when I know they’re needed rather than having them there all the time. This parameter lets me do that.