excel and subsets

[stingo]

Hi All,

I have this problem.

I want to build a worksheet with a view containing a dimension in row (in my example the product dimension).
this dimension has to show me just the lev 0 members that are contained in a subset (in my case the product category) choosed by the user via a drop down menu.

the formula of the row is something like:

=TM1RPTROW($B$9,SERVER&":APS_Product_GM","CHEESE")

where cheese is the name of a subset contained in the drop down list and SERVER is the cell name containing the server name.

Now, what I tried to make the solution work is to substitute the formula with this:

=TM1RPTROW($B$9,SERVER&":APS_Product_GM",E14)

where E14 is the cell containing the drop down.

Well, this solution works on opening but if I try to change the value on the DD menu ( i.e. from 'cheese' to 'grocery' ) the system starts to calculate and at the end it still show me the cheese products.

How can I solve it?

[jim wood]

I'm not 100% certain I understand your issue (working on older version and all that) but could you get around this by creating a dynamic subset? Then refer to this subset using the subnm forumula?

Jim.

[tomok]

What Jim Wood said. The TM1RPTROW formula is specifically designed to be used in Active Forms for drilling into a dimension in a report, not for drop-downs for user selection. You should be using a SUBNM formula for that.

[stingo]

thanks guys, I'll try and I'll let you know.

RGDS
stefano


EDIT:

I'm checking the reference guide and it is saying this:

Do not use cell references as arguments with the SUBNM function. Cell references prevent the function from correctly calling and launching the Subset Editor when you double-click the cell that contains the SUBNM function.

so it is not solving my issue.

working on it.


EDIT2:

reading again the comments it seems that the issue is not clear.

I have my drop downs made with SUBNM
I have my ROW done with the TM1RPTROW function
and it is ok.

the row function is written like this:

=TM1RPTROW($B$9,SERVER&":Product","Cheese")

where cheese is a subset in my product dimension (that is in row in the report).
What I want is to keep the TM1RPTROW as it is but to put parametric the name of the subset.

The goal is to permit the user (via drop down) to choose which subset to show in row.

[Alan Kirk]

thanks guys, I'll try and I'll let you know.

RGDS
stefano

EDIT:

I'm checking the reference guide and it is saying this:

Do not use cell references as arguments with the SUBNM function. Cell references prevent the function from correctly calling and launching the Subset Editor when you double-click the cell that contains the SUBNM function.

so it is not solving my issue.

working on it.

That applied to versions prior to 9.4 FP1. The behaviour was changed in that version, though the documentation wasn't; in fact they still haven't changed it in 9.5.1, where I just confirmed that a cell reference works just fine. However you do have to ensure that you specify all arguments in the formula which you didn't have to do previously. See: http://forums.olapforums.com/viewtopic.php?f=18&t=980&

I believe that with 9.4.1 you should be able to use cell references and get the result that you want; check it to confirm.

[stingo]

thanks guys, I'll try and I'll let you know.

RGDS
stefano

EDIT:

I'm checking the reference guide and it is saying this:

Do not use cell references as arguments with the SUBNM function. Cell references prevent the function from correctly calling and launching the Subset Editor when you double-click the cell that contains the SUBNM function.

so it is not solving my issue.

working on it.

That applied to versions prior to 9.4 FP1. The behaviour was changed in that version, though the documentation wasn't; in fact they still haven't changed it in 9.5.1, where I just confirmed that a cell reference works just fine. However you do have to ensure that you specify all arguments in the formula which you didn't have to do previously. See: http://forums.olapforums.com/viewtopic.php?f=18&t=980&

I believe that with 9.4.1 you should be able to use cell references and get the result that you want; check it to confirm.

Hi,

yes It works on the SUBNM but the issue was misunderstood. I tried to rewrite it down in my previous post edited.

[tomok]

What I want is to keep the TM1RPTROW as it is but to put parametric the name of the subset.

Just replace the "Cheese" in your TM1RPTROW formula with a cell reference to where "Cheese" has been chosen through a drop-down created via data validation in Excel.

[stingo]

What I want is to keep the TM1RPTROW as it is but to put parametric the name of the subset.

Just replace the "Cheese" in your TM1RPTROW formula with a cell reference to where "Cheese" has been chosen through a drop-down created via data validation in Excel.

ok, this is what was not working in the first post....

Now, what I tried to make the solution work is to substitute the formula with this:

=TM1RPTROW($B$9,SERVER&":APS_Product_GM",E14)

where E14 is the cell containing the drop down.

[lotsaram]

Hi Stingo,

I think your problem is a simple one. Provided the text in cell "E14" is indeed a valid public subset in the APS_Product_GM dimension then all that you need to do is REBUILD rather than RECALCULATE the worksheet. (Alt+F9 rather than Shift+F9)

If that's not it then I as well as everyone else do not understand what exactly you are asking! So if that doesn't fix it for you maybe try re-expressing your problem, perhaps with an example workbook or screenshots.

[stingo]

Hi Stingo,

I think your problem is a simple one. Provided the text in cell "E14" is indeed a valid public subset in the APS_Product_GM dimension then all that you need to do is REBUILD rather than RECALCULATE the worksheet. (Alt+F9 rather than Shift+F9)

If that's not it then I as well as everyone else do not understand what exactly you are asking! So if that doesn't fix it for you maybe try re-expressing your problem, perhaps with an example workbook or screenshots.

hi,

yes, it solved my problem.

thank you.

I just put a refresh button with a rebuild function and it worked. It's a pity that I could not do it with an automatic calculation, but it's ok.

thanks all for the replies.

KR
Stefano