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.
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
-- Purpose: Get the User Variables in a Planning application
-- Modified: 30 October 2011, first write
-- Notes: This is a simple one, isn't it?
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
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
|Biff||Allocation Expense||330000||Minority Interest Income|
|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||Revenue Measure||400000||Gross Profit|
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.