TM1 Forum

I am a TM1 newbie and I have been assigned to develop TM1 application. But there are an problem I need to solve, please help~~

I created a cube that contain 3 dimensions and 1 measure, for example for cube data:

UserName Year StaffId Salary
Wilson 2011 S001 10000
Wilson 2012 S001 12000
Eric 2011 S003 15000
Eric 2000 S003 14000
Eric 2012 S003 15000

If user "Eric" login and open a Excel application, all data for "UserName" Eric should be display on Excel, what can I do for this?
Or user must choose the dimension value so the data can be shown on the Excel?
One more question, is it possible to store two record that have the same dimension value?

Thank you very much~~~~~

Assuming that your "UserName" elements are the same as the actual log-ons... be it either by using the }Client dim or a manually created one with the same elements.

You can use the TM1User(Server) function in excel. This can be found in the user guides if you need further information on it.

Dan.C wrote: I created a cube that contain 3 dimensions and 1 measure, for example for cube data:
UserName Year StaffId Salary

If I am interpreting your cube design right then you have two separate dimensions for UserName and StaffID. I would advise you to merge these two into same dimension and treat one of them as Attribute rather then a separate dimension.

I would advise you to use the }Client dimension in your cube design - you can put StaffID as an Attribute as well.

Thanks declanr and rkaif!

Actually I know the function TM1User, but I don't know how to display all data by user name. As I just know one dimension "UserName",
how can I know the data in the cube and display them row by row on the Excel, for example, if Eric logon and open the Excel application,
the Excel should display the content automatically:

Eric 2011 S003 15000
Eric 2000 S003 14000
Eric 2012 S003 15000

If Wilson logon, the Excel should display the content:
Wilson 2011 S001 10000
Wilson 2012 S001 12000

UserName and StaffId are just an example, actually there are more dimension in cube. Thanks~~~~~~!!

If you look through the user guides at the "Active Forms" section, that will do the trick.

I think for what you want you would just have your measures dim on the Y axis, "Client/User" as a title element then all others on the x axis (possible exception of forecasts etc being Title Elements also.)
If you have zero suppression turned on then it will only show relevant elements.

Hi

I suspect that your requirement is that the list should only allow a user to see their own salary data. You could just use the TM1User Excel Function, however, there would be nothing to stop someone overtyping the TM1User function with someone else's user name and seeing their salary data. I would also suggest that you would only want them to have READ access to their Salary data, otherwise it is only a matter of time before all your staff have salaries that make even CEOs of Investment Banks envious.

I would suggest the following:

Create a Staff dimension (The reason that I wouldn't use the }Client dimension itself is that you can't put consolidations into it since it is a system maintained dimension, and you might want to know things like the total salary bill or to group users by department etc).

In the Staff dimension, we can have UserName as the element name

Right Click on it and Edit Element Attributes

Add an Alias Attribute of Staff Id

Populate that either by typing or from a TI process.

You can then use the attached process.

This will
Create the Staff dimension if it doesn't already exist,
Populate the Staff dimension from the }Clients dimension to ensure that all }Clients exist in the Staff dimension
Create a Group for each Client eg JSmith has Grp_JSmith
Assign Client JSmith to his group Grp_JSmith
Give his group Grp_JSmith READ access to the element JSmith in the Staff dimension
(Behind the scenes it creates an }ElementSecurity_Staff cube which has dimensions of }Groups and the elements of the Staff dimension which are the same as the User Names).

Once you have this in place you should find that if you logon as an ordinary user and open a subset on the Staff dimension then you will be told that you don#t have read access to all elements and the only element that you will be able to see is yours eg JSmith will only see the element JSmith in the Staff dimension.

You can now just use a View on your Salary Data cube, which has Staff on Rows, and I think you had Years on Rows too, then Salary on columns. Set the View to Zero Suppress. In the Staff and Year dimensions, select all elements (might be simplest to do this as a Dynamic Subset so it will automatically reflect new Staff). Save the View as a Public Default View or some other name. Then when the user opens that View, the security will automatically cut the list of elements that they can see down to just their own user name, and the suppress zeroes will cut the list of years down to just those where they have salary data.

If you prefer to have it in Excel rather than a View then just make the View an ActiveForm, by clicking the button. Another possibility is to use the In-Spreadsheet Browser (now confirmed as here to stay in TM1 10).

Regards


Paul Simon