Hi all,
Using 9.5.2 and excel 2007.
I wondered if there was a way of returning the Element of a Dimension that a costs sits against in a TM1 worksheet. So if a cost sits at a cross point in a cube and I know every parameter except 1 and there will only ever a value against an element in that dimension once (at that combination of every other parameter.
For example we have a daily cost cube with a Daily activities dimension and a time dimension (and three other dimensions: Country, Project, Version). The user will only ever input the costs for that day against one activity and he does this via an active form. A separate cost report has been created (based on the same cube) which displays the total daily cost information by project and time (day) rather than by daily activity which is at an "All Daily Activities" level. The days are across the columns. I would have liked to put something like a DBRW formula above each day which displayed the "Daily Activity" that all the costs for that day are associated with.
However I don't know how to achieve this. The costs are sitting against a Daily Activity (which is an element of the Daily Activity Dimension). It is that element that I want to bring back into the cell for reference.
So far I have tried:
1)
Creating another cube with the same Dimensions + one more called "aString_aNumber" which has 2 elements, one string and one simple. The rule attached brings in the Costs from the original cube into the simple element. The rule then says:
[!Country,!Time',!Project,'Budget','aString'] = S:
IF(
DB('DDAR String',!Country,!Time,!Project,'Budget','Project Set Up','aNumber')@='0',
STET,
'Printing'
);
this doesn't work - which may not surprise you! Whether there is a value in the cell or not, the aString element says "Project Set up". The plan for this was if I could get the daily cost activity element as a string value in cell, I would then be able to DBRW into this cube in the TM1 worksheet that I mentioned above.
2)
From the Daily Activity Cost input sheet I tried to DBSA the Activity element name as an attribute to the day in the Time dimension (after\creating a new attribute called Daily Activity and the project name). However, to get a value from the active from to send I needed to overcome some hurdles in excel - I was trying to find the first cell that was not blank in the day column by using a mixture of OFFSET,AND,MATCH functions. However, I failed to succeed with this mainly because I can't use VBA as this input form is on TM1 Web.
So I am back to square one. If anyone has any suggestions I would be most grateful.
Cheers
Paid Advertisement:
