TM1 Forum

Hello everybody
I'm quite new both in Cognos and this forum.

I'm writing to you to ask you an advice about TM1 Excel add-in (perspective)
According to your experience is that possible to drive the drill down of a dimension by a combo box?
A explain myself better.
I've got an Excel sheet where on the columns are the Years (father) and the Months (son) in this way
2009
jan-09
feb-09
...
...
2010
jan-10
feb-10

Then in another cell of the sheet (not in page) I've got a combo box within just the years (2009, 2010)

What I would like to do is to change the year in the combo box and to drill down for the selected year.
For instance: if a choose the 2009 in the columns of my report I want to see from 2009 [] to 2009 jan-09 feb-09 mar-09 .................... 2010 [+] (so without son)

Is that possible (even using Excel Macro) to create a procedure like?

Please, if you have time Let me know .

Thanks in advance for you help.


Nicola

nicola531 wrote:Hello everybody
I'm quite new both in Cognos and this forum.

I'm writing to you to ask you an advice about TM1 Excel add-in (perspective)
According to your experience is that possible to drive the drill down of a dimension by a combo box?
A explain myself better.
I've got an Excel sheet where on the columns are the Years (father) and the Months (son) in this way
2009
jan-09
feb-09
...
...
2010
jan-10
feb-10

Then in another cell of the sheet (not in page) I've got a combo box within just the years (2009, 2010)

What I would like to do is to change the year in the combo box and to drill down for the selected year.
For instance: if a choose the 2009 in the columns of my report I want to see from 2009 [] to 2009 jan-09 feb-09 mar-09 .................... 2010 [+] (so without son)

Is that possible (even using Excel Macro) to create a procedure like?

Please, if you have time Let me know .

Thanks in advance for you help.

Nicola

Admin note: Moved from General (which is for subjects not related to any particular tool) to Cognos TM1.

Hi and welcome Nicola.

This is actually quite an easy one to achieve without any significant (or indeed any) coding.

All you have to do is this:
1/ Name a range to hold the year selected. Assign that range name to your combo box's LinkedCell property. Let's suppose that you name the range Year.
2/ In the report sheet cell that will hold the selected year, you'll have the formula =Year to pull through the ComboBox selection. Let's say, hypothetically, you have that in cell B10 of your report sheet.
3/ To get the months that fall under that year, you use the ElComp function like so:
=ELCOMP("planning sample:Periods",Year,COLUMN()-COLUMN($B$10))
(Where planning sample:Periods is the dimension (replace that with your own obviously), Year is the range name that your original year is stored in, and COLUMN()-COLUMN($B$10) is just an expression that will keep incrementing as you copy the formula to the right; replace $B$10 with the address of the column that contains the =Year formula.)

Essentially the Elcomp formula will simply derive the children that fall under the selected element, regardless of what that element is. This is very easy to do with years divided into months, since you'll know that there are always 12 children of the year consolidation and therefore you have to copy the formula across 12 columns.

There are several ways that you could derive the 2010 element name using plain and simple Excel formulas, but post again if you have problems making that work.