Why I created a blog

Its been many years since I first created this blog. It has remained true to Essbase and related information over those years. Hopefully it has answered questions and given you insight over those years. I will continue to provide my observations and comments on the ever changing world of EPM. Don't be surprised if the scope of the blog changes and brings in other Hyperion topics.


Friday, March 8, 2019

Undocumented New Filter Feature in .115 Part 2



Apparently I reached a size limitation in Blogger so to see part 1 of this post go here

Multiple Line fun

As I talk about above, you can have multiple lines on the source. I asked for this because a client has users that have access to multiple locations and it was easy to generate a file with a row for each location. For example 
User1    District01
User1    District02
User2   District04
User2   District05

Up until this version, the @Datasourcelookup could not handle that.  Now it can as I noted above. It merges the multiple rows into a singe statement.  This is both good and bad. In the case if my client it will work great as it is a single dimension they need to restrict. If I had two dimensions that were interdependent, it would be a problem as I show above it will union the rows. 

Now what I actually wanted for my client was not really the districts (shown above) but the  descendants of the district including the district.   When I asked Oracle about it, I was told I would have to use expressions, but I' don;t give up easily. A behavior was changed in the .115 release with how things are returned. It used to be the whole line was returned in double quote. Now double quotes are only added if you don;t have your own double quotes of if the member names have reason to need them (spaces, special character, all number).  So I tried getting creative.   I tried taking the filter line and adding an @IDescendants in the beginning of the filter

@IDescendants(@Datasourcelookup("Sample.Security_test_DS","USERNAME","$LoginGroup","Rpoduct_Group"))

But that didn't work because @IDescendants looks for a single member name.

I remembered a few years ago, they added a new function @(I)LDescendants  (I for include) which brings back the descendants of a list of members. 
SO  what the heck what could go wrong, I gave it a try. Notice I'm not using the expression any more but the product_group column 


To my utter joy and amazement, it worked

Multiple Datasourlookups

In prior versions there was an issue of having multiple Datasourcelookups on a single row. and you will notice the samples in the admin guide put the look ups on separate rows. It seems to be fixed now
My filter  now has (ll in a single row, I split it here to make it more readable)
@ILDescendants(@Datasourcelookup("Sample.Security_test_DS","USERNAME","$LoginUser","Product_Group"))
,@ILDescendants(@Datasourcelookup("Sample.Security_test_DS","USERNAME","$LoginUser","Market_Group"))

and it works perfectly

Again be aware it will union the results so be careful what you ask for. 

My final tip will be, if your filter gives you a error  (I purposely changed USERNAME to USERNAMES) where can you go to look to see what it it trying to do? 

 

 Silly me, I though I would look in the application log. BUT NO. Click on the Console card, slect LOGS and look at the platform log
 

I selected view log from the Ellipses and see  (of course this is just a snipit of code, there is a lot more that is shown.)

 


Finally, if you are testing, remember to disconnect from Smart View and reconnect to test any changes. 
Hopefully, this helps to get you on the road to use the efficient filters.