Fixed title of SUBNM formula cell

Post Reply
LukaszM
Posts: 9
Joined: Thu Dec 08, 2011 9:43 am
OLAP Product: TM1
Version: 9.5.2 and 10.1
Excel Version: 2003 - 2010

Fixed title of SUBNM formula cell

Post by LukaszM »

Hi everyone,
it's a pleasure to finally become a member of this helpful forum, not only as a reader, but also as a writer.

I have a question about a SUBNM function. We have several websheets uploaded to an application folder, those websheets are based on active forms and active buttons to launch processes and navigate between sheets, all accessed via TM1 Web.
What I would like to achieve is to enter a subnm in a cell with a fixed text (title) in it. So, in my example it would be something like:

=subnm(server_name:dimension;subset_name;"")&"TITLE"

It works fine in Excel - TITLE is displayed in the cell and after double-clicking, user is being displayed a subnm dialog box. However, in TM1 Web the cell seems to contain only "TITLE" text, subnm function doesn't work anymore.

Is there any other aproach, or what I want to achieve is just impossible?

Regards,
Łukasz M.
Christopher Kernahan
Community Contributor
Posts: 147
Joined: Mon Nov 29, 2010 6:30 pm
OLAP Product: Cognos TM1
Version: 10.1
Excel Version: Office 2010

Re: Fixed title of SUBNM formula cell

Post by Christopher Kernahan »

Hi LukaszM,

Can I ask, what is the purpose of the TITLE addition?

If it is a help statement, why not put it in the next cell?
If it is information on the element, can't you use an alias?

Maybe if you let us know what you're trying to achieve.
LukaszM
Posts: 9
Joined: Thu Dec 08, 2011 9:43 am
OLAP Product: TM1
Version: 9.5.2 and 10.1
Excel Version: 2003 - 2010

Re: Fixed title of SUBNM formula cell

Post by LukaszM »

Hi Christopher,
Want I want to achieve might be kinda weird, but I didn't come up with better idea. Our client wants to display a list of investment tasks. This is done be appropriate active form. Each task has some attributes (active form columns) and client wants to filter those tasks by specific attribute (for example department, area, investment type etc.) - basically, he wants to filter the list by all available attributes that are defined by lists.

So I try to add a subnm cell which would serve as "FILTER" button. There, the user would define a subset (each user would have his own private subset) in an advanced view, filtering by attributes defined when a task is added (I have a rule between }ElementAttribute_tasks and the cube that the active form is referred to, so basically the data is mirrored - attributes of elements and strings displayed in the cube are the same), save this subset and refresh.
This subset is entered as parameter in TM1RPTROW function, so, when the subset is changed, the list of tasks changes accordingly.

Everything works fine, but I would like to avoid telling the user that he has to double click on empty cell in order to do a filtering.
I hope you understand what I mean

Regards,
Łukasz M.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Fixed title of SUBNM formula cell

Post by lotsaram »

If you want to filter elements by selected attributes then rather than selecting a hardcoded subset name (which needs to be prebuilt) you would be much better off to have the user select the attributes and attribute values to filter on using cell input or dropdowns and use this to parse an MDX expression with Excel formula and then pass this as the MDX argument to the TM1RptRow formula. This will be much more flexible and dynamic and will work without pre-defining any subsets and without any VBA in Excel and TM1 Web.
LukaszM
Posts: 9
Joined: Thu Dec 08, 2011 9:43 am
OLAP Product: TM1
Version: 9.5.2 and 10.1
Excel Version: 2003 - 2010

Re: Fixed title of SUBNM formula cell

Post by LukaszM »

Thanks lotsaram,
that indeed might be better approach, as long as the client would prefer limited amount of filtering attributes to refreshing the subset.
Anyway, since now I thought that everything we can do in Excel using formulas will be also work in TM1 Web and only VBA is prohibited.
Now, as far as I understand, using something like
=function(arg1;arg2...)&"text that I want to be visible in the cell"
won't work and there is no work around to this. Am I right?
Regards,
Łukasz M.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Fixed title of SUBNM formula cell

Post by lotsaram »

LukaszM wrote:=SUBNM(arg1;arg2...)&"text that I want to be visible in the cell"
won't work and there is no work around to this. Am I right?
Actually I'm surprised that this works in Excel ....
If you wrap SUBNM in anything or append anything to it then it will not launch the subset editor in web. The formula must be "pure" even =(SUBNM(arg1;arg2...)) will not work.
I think it is easy to achieve what you want, as per the suggestion earlier just separate into separate cells
=SUBNM(arg1;arg2...) say in A9
and in the B9 cell to the right ...
=IF(A9=""," <- Please select an element to filter on!","")

... or something similar.
Post Reply