TM1 Forum

Hi All,
I was wondering if anyone had any suggestions on the following:

I need to move my ending year balances to a beginning year balance for the following year.

We currently have 9 dimensions in our cube. Two of these dimensions deal with time (One is Year and the other is Month). I would have built these dimensions differently but I have to work with what the previous developer put together

I would like to take all the values across all the other dimensions found in the cube where the Year element is '2010' and the Month element is 'Dec-YTD' and copy these values to where the Year element '2011' and the Month element is 'Beg Bal' (Essentially replicating the values for all the other nodes).

It seems to me this could be done with a CellGetN and CellPutN somehow but I am not quite sure how. The issue I see is that all the other dimnesions (besides the month and year) listed in the CellGet and CellPut functions have to be at the element level (or at least that is my understanding). Since there are different values stored at each of the various elements within each of these other dimensions, I am not sure how I would copy each of these different values without some sort of enormous loop to go through each dimension/element variation. Perhaps I am incorrect in my understanding of how the CellPut and CellGet functions work?


Any suggestions appreciated. Thanks in advance for any help.
-Dan

This is a simple and common requirement. Suggest you get yourself acquainted with the TurboIntegrator guide and developer guide and get a good working knowledge of TI.

CognosDeveloper wrote:Hi All,
I was wondering if anyone had any suggestions on the following:

I need to move my ending year balances to a beginning year balance for the following year.

We currently have 9 dimensions in our cube. Two of these dimensions deal with time (One is Year and the other is Month). I would have built these dimensions differently but I have to work with what the previous developer put together

I would like to take all the values across all the other dimensions found in the cube where the Year element is '2010' and the Month element is 'Dec-YTD' and copy these values to where the Year element '2011' and the Month element is 'Beg Bal' (Essentially replicating the values for all the other nodes).

It seems to me this could be done with a CellGetN and CellPutN somehow but I am not quite sure how. The issue I see is that all the other dimnesions (besides the month and year) listed in the CellGet and CellPut functions have to be at the element level (or at least that is my understanding). Since there are different values stored at each of the various elements within each of these other dimensions, I am not sure how I would copy each of these different values without some sort of enormous loop to go through each dimension/element variation. Perhaps I am incorrect in my understanding of how the CellPut and CellGet functions work?

Any suggestions appreciated. Thanks in advance for any help.
-Dan

As Lotsaram said this is pretty basic TI stuff and you do need to go through the manuals and guides, but to point you in the right direction:
- I'm assuming that 'Dec-YTD' is a consolidation, which complicates things a little, but not too much. Create subsets for each dimension. (SubsetCreate function.) For Year, insert the 2010 element only. (SubsetElementInsert.) For the month, do the same with Dec-YTD. For the other ones, loop through the dimension and add all of the N level elements. (Use DimSiz to get the size of the dimensions, then DimNm to get the elements, and ElLev to get the level. Add the element to the subset if the level is 0.) If you're feeling adventurous, you could also try the SubsetCreatebyMDX function to create these N level subsets.
- Create a View with the subsets that you've created (ViewCreate/ViewSubsetAssign). Suppress zeroes (ViewExtractSkipZeroesSet) but do NOT suppress consolidations because your YTD element is one.
- Create a process which uses that view as its data source.
- Use CellPutN in the Data tab of that process to write the values into the new month and year elements, using the Variables that will be supplied from the view for the other elements.