Stupid Planning queries #7 -- User Variables

Introduction

You use User Variables, right?  That’s the functionality typically (only?) used in Planning forms to allow the user to select the member he wants to drive the form.  I use these when dimensional security doesn’t work or when the form gets too large.

Set and forget

It’s easy to set them by going to File->Preferences->Planning->User Variable Options as shown below.

How do you use them in forms?

They look just like Essbase Substitution Variables, right down to the “&” in front of the User Variable name.  

NB – I like to stick a “uv” in front of the name to differentiate them from Substitution Variables, so instead of &CompareScenario it would be &uvCompareScenario.  This isn’t a big deal when you’re building the form, but aids in identifying what’s what later on.  Or when you bounce from client to client, and app to app, and barely know what state you’re in.  The glamor of consulting.  :)

In any case, as this is the Planning Sample Application, I’m not changing it.  But you did get a Cameron Good Practice suggestion there for free.  

So how do I know who has what?

There’s really no good way to know in Planning what’s been defined by user except by asking said user.  As that could be a trifle exhausting (and inaccurate) in anything other than a teensy-weensy application, SQL comes to the rescue again.

Standard disclaimer

As always with these queries, they are 100% unsupported by Oracle and there’s a tremendous chance that I’ve gotten them wrong, so test, test, test and remember that you are hacking the tables and if anything blows up you are completely on your own.  Got it?  Good, let’s begin.

User variables query

--User variables
--    Purpose:    Get the User Variables in a Planning application
--    Modified:    30 October 2011, first write
--    Notes:        This is a simple one, isn't it?
SELECT
    O.OBJECT_NAME AS 'User',
    U.VARIABLE_NAME AS 'Variable',
    O2.OBJECT_NAME AS 'Member',
   -- Use SQL Subquery to get aliases.
   -- NB -- The overall SELECT from HSP_MEMBER ensures that members with
   -- and without an alias are selected.
   -- ISNULL puts in zero length string in place of NULL
   ISNULL((SELECT OA.OBJECT_NAME
       FROM HSP_ALIAS A
       INNER JOIN HSP_OBJECT OA
       ON A.MEMBER_ID = O2.OBJECT_ID AND
       OA.OBJECT_ID = A.ALIAS_ID), '') AS 'Alias'
FROM HSP_USER_VARIABLE_VALUE V
LEFT OUTER JOIN HSP_OBJECT O
    ON V.USER_ID = O.OBJECT_ID
LEFT OUTER JOIN HSP_OBJECT O2
    ON V.MEMBER_ID = O2.OBJECT_ID
LEFT OUTER JOIN HSP_USER_VARIABLE U
    ON V.VARIABLE_ID = U.VARIABLE_ID
ORDER BY 1, 2

And the output

User	Variable	Member	Alias
Biff	Allocation Expense	330000	Minority Interest Income
Biff	Allocation Quarter	Q1
Biff	CompareScenario	Forecast
Biff	CompareVersion	Working
Biff	Current Version	Working
Biff	CurrentScenario	Forecast
Biff	My Region	E01_0	North America Corporate
Biff	My Segment	BAS	Bookshelf Audio System
Biff	Revenue Measure	411100	Operating Revenue
hypadmin	Allocation Expense	312100	Interest Income
hypadmin	Allocation Quarter	Q4
hypadmin	CompareScenario	Plan
hypadmin	CompareVersion	Final
hypadmin	Current Version	Working
hypadmin	CurrentScenario	Forecast
hypadmin	My Region	E01_101_1110	MA
hypadmin	My Segment	Seg01	Electronics
hypadmin	Revenue Measure	400000	Gross Profit

 

Conclusion

Yet Another Planning Query (YAPQ), pronounced Yap Que, hits the dust.  I know there are other people there doing these.  I wonder why I never hear from them.  Am I doing something akin to an arch-villain exposing the inner works of the Guild of Calamitous Intent?  This stuff is so easy, even a Planning consultant can do it?  No matter, I forge onwards.