Sliced data to reveal lock status in Excel
Posted: Fri Jan 31, 2014 7:53 pm
After I sliced data into Excel, is there anyway that I can display the locked (gray) cells different from the unlocked (white) cells?
There is no "built-in" way.
So i would start by asking the question: "Can the users learn? Or do they need to be spoonfed everything?"
If the answer is spoonfeeding i'd still prefer to not do this however:
You can use a TI to build a replica cube (all the same dimensions PLUS the }clients dimension.)
In the same TI have the datasource set to show ALL leaf level elements only (for the source cube).
In the datatab, loop through the client dimension and do a check to see if each of the cubes dimensions have element security set; if they do you need to check if the user in question has WRITE access - if they have WRITE access to every relevant dimension (and only every single one) you put a 1 in that cell of the replica cube.
(the same basic concept applies if you are using other types of security but you just need to think through what security control object it is you check against.
Then in your worksheet (in hidden columns etc) do a DBRW (or possibly a DBR might be needed due to the order in which DBRWs are executed) at the replica cube (using the TM1User function also). Use conditional formatting in the actual cells based on whether the "replica" cells show a 1 or 0.
You would need to re-run the TI every time security or a relevant dimension changed etc.
Note that this sounds like far too much effort for the benefit pay off to me.