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.
Fixed title of SUBNM formula cell
-
- 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
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.
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.
-
- 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
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.
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.
-
- 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
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.
-
- 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
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.
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.
-
- 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
Actually I'm surprised that this works in Excel ....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?
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.