TM1 Forum

Hi All,

I am using 9.5.2, excel 2007 (solution below is for TM1 web).

I have an active form which is a Commitment Register - a report showing all committed costs for a period. The cube that the active form is based on has 12 dimensions. On the active form 5 dims are pages (inc the time dim), 6 are on the columns and 1 (the measures dim) is on the rows.

At the top of the active form there are 5 drop down menu's so that the user can filter the dims in the pages - as the SUBNM references the cell with the result of the drop down menu and if nothing is selected form the dropdown it there is an IF statement to show the top consolidation point in the dim. This works well.

On the 6 dims on the columns there is a tick box in the column header. If left blank it returned a "False" statement in a cell, if ticked it returned a "True" statement. An IF statement on the cell returned the name of a subset. If "False" the subset name was "Drill down", this allows the user to drill down from the top member on this dim. If "True" the subset name was "Default", this shows all n levels on this dim. The subset parameter of the TM1RPTROW formula references the subset shown in the IF statement. This works quite well, the user deciding which dims to show n levels etc.

I have also created a mechanism where the user can select one element from the dim. I have inserted a SUBNM formula above the column header for the user to select the element. The subsetelements parameter of the TM1RPTROW formula references this cell. If left blank it returns all elements, if the user selects an element it only returns that element.

The Problem:

On these 6 dims on the columns the users STILL want more filtering ability. I think the ideal would be to have a filter that works like the excel version (I can't use the excel version as the report is viewed on TM1 web and the excel filter will not work here). Where the user can select an element or many elements or no elements on each of the 6 dims on the columns.

What I have tried:

1. I have looked into using the TM1RPTFilter (after reading this thread and Lotsarams suggested approach http://www.tm1forum.com/viewtopic.php?p=25448). However am I correct in thinking that you can only apply the TM1RPTFilter to the dim on the rows? As I cannot seem to edit it to filter the dims on the columns.

2. I have tried to utilise the MDX parameter of the TM1RPTFilter formula. I added 5 rows of SUBNM's above the header so that a user can select multiple elements that he wants to return from the dim (trying to replicate excel's multiple selection in it's filter function). I thought that this should have worked but I am not having much success.

Any Design Advice:

I wondered if you had successfully designed similar (or much better) reports with filtering on etc. I am open to any advice on how to better design the report and I'm sure there are more tricks that would help me achieve a better report.

Cheers

You description makes it sound rather complicated. I think unless you include some screenshots to give an idea what the report actually looks like and what you are seeking to achieve then it's going to be rather difficult for anyone to offer any constructive advice.

lotsaram wrote:You description makes it sound rather complicated. I think unless you include some screenshots to give an idea what the report actually looks like and what you are seeking to achieve then it's going to be rather difficult for anyone to offer any constructive advice.

Sorry Lotsaram I should have included screenshots in my original post. I have attached a screenshot of the active form which shows 4 interrogation methods:

1. Drop down lists - user can either not select anything - in which case DBRW's return all data" or make a selection. The source of drop down lists are ELCOMP formulas.
2. Drill down from consolidated element is normal active form functionality
3. The tick box - this selected a different subset. The TM1PRTROW formula when unticked references a subset called Drill Down. When ticked it references a subset called "Default" which is all n level's
4. Type in unique element name - this box allows user to type in element name. The Element parameter of the TM1RPTROW formula references this. If filled in the DBRW's will display only the values for that element.

NOTE: number 4 could have been a SUBNM formula and user could have selected element they wanted to see to save on typo's etc. However, end user does not like this function!

I hope that helps.

Cheers