Page 1 of 1
The same subnm linked on multiple sheets
Posted: Wed Dec 18, 2013 9:25 pm
by PlanningDev
Im sure someone has answered this but I couldn't find a thread so...
Is there a way to have subnm's linked between sheets? IE I make a change the subnm on sheet 2 and then move to sheet 1 and the subnm on sheet 1 shows the change? The goal being to allow for different selections on any page to persist across sheets.
Re: The same subnm linked on multiple sheets
Posted: Wed Dec 18, 2013 10:24 pm
by Alan Kirk
PlanningDev wrote:Im sure someone has answered this but I couldn't find a thread so...
Is there a way to have subnm's linked between sheets? IE I make a change the subnm on sheet 2 and then move to sheet 1 and the subnm on sheet 1 shows the change? The goal being to allow for different selections on any page to persist across sheets.
Are we talking standard Excel, or Web?
If it's standard Excel I wouldn't do it that way. Instead of having SubNMs on multiple sheets I'd have it on a Control sheet with cell references on the other sheets pointing to that. Over each such cell you can put a transparent shape which links to a macro which does the following:
Code: Select all
Range("NameOfYourControlSheetCell").Select
Application.Run "TWEVENTER1" 'Which will trigger the SubNM selection
Then move the selection back to the original sheet and recalculate according to taste.
Unless I actually need different selections on different sheets, I find it safer to keep to a single entry for title elements so that nothing "drifts off".
Incidentally, I don't blame you for not finding TWEVENTER1 in the documentation because as far as I know... it isn't. Much as that will doubtless come as a shock to you.
If it's a websheet... well, that's a different story again.
Re: The same subnm linked on multiple sheets
Posted: Wed Dec 18, 2013 10:42 pm
by PlanningDev
That's a good solution for Excel!
, unfortunately I would like this on the web
My thought at this point is to create some kind of picklist cube that contains the client dimension and then to have the selection stored there. I realize the picklist idea drops some functionality with the subset editor but I haven' t found a way to do with SUBNM.
Re: The same subnm linked on multiple sheets
Posted: Wed Dec 18, 2013 11:29 pm
by rmackenzie
PlanningDev wrote:My thought at this point is to create some kind of picklist cube that contains the client dimension and then to have the selection stored there.
That's a very good option - although you need to give some careful thought about how you organise the measures dimension. Another benefit of this method is that you can control report settings server-side.
PlanningDev wrote: I realize the picklist idea drops some functionality with the subset editor but I haven' t found a way to do with SUBNM.
Slightly clunky - but it is possible to have a stand-alone SUBNM (i.e. with nice UI as opposed to picklist) and then have the result of the user selection DBS'd back into the selection cube (potentially to an intersection without a picklist definition). Then, you could re-use that selection on other sheets.
Re: The same subnm linked on multiple sheets
Posted: Wed Dec 18, 2013 11:40 pm
by PlanningDev
rmackenzie wrote:
Slightly clunky - but it is possible to have a stand-alone SUBNM (i.e. with nice UI as opposed to picklist) and then have the result of the user selection DBS'd back into the selection cube (potentially to an intersection without a picklist definition). Then, you could re-use that selection on other sheets.
Would this allow the submnms to be linked on each page? The issues getting the subnm to show the latest selection which I don't think is possible.
Re: The same subnm linked on multiple sheets
Posted: Wed Dec 18, 2013 11:42 pm
by rmackenzie
Alan Kirk wrote:PlanningDev wrote:If it's standard Excel I wouldn't do it that way. Instead of having SubNMs on multiple sheets I'd have it on a Control sheet with cell references on the other sheets pointing to that. Over each such cell you can put a transparent shape which links to a macro which does the following:
Code: Select all
Range("NameOfYourControlSheetCell").Select
Application.Run "TWEVENTER1" 'Which will trigger the SubNM selection
Is that pretty much the same as the E_PICK macro? Actually, it isn't working for me on 10.2... But E_PICK is still OK, e.g.:
Code: Select all
Sub GetElement()
Dim strSelectedElement As String
strSelectedElement = Application.Run("E_PICK", "server:dimension", "", "", "")
Worksheets("Sheet1").Range("A1").Value = strSelectedElement
End Sub
Re: The same subnm linked on multiple sheets
Posted: Wed Dec 18, 2013 11:45 pm
by rmackenzie
PlanningDev wrote:Would this allow the submnms to be linked on each page? The issues getting the subnm to show the latest selection which I don't think is possible.
Yes, I believe so, if you are only using the SUBNM to act as a selector that writes back to the client-selection cube. You then link the SUBNMs for the cube-views directly from this client-selection cube - these SUBNMs don't have to be visible to the user. In fact, the DBRW references for the view slicers (titles) don't have to be SUBNMs at all.
Re: The same subnm linked on multiple sheets
Posted: Wed Dec 18, 2013 11:49 pm
by PlanningDev
While I believe that will work, I believe that each subnm on each page won't be able to always show the latest selection? Am I following what you are thinking? The subnm just writesback to a cube? Wouldn't you have multiple sheets that would writeback to the same data?
Re: The same subnm linked on multiple sheets
Posted: Thu Dec 19, 2013 12:16 am
by Alan Kirk
rmackenzie wrote:Alan Kirk wrote:PlanningDev wrote:If it's standard Excel I wouldn't do it that way. Instead of having SubNMs on multiple sheets I'd have it on a Control sheet with cell references on the other sheets pointing to that. Over each such cell you can put a transparent shape which links to a macro which does the following:
Code: Select all
Range("NameOfYourControlSheetCell").Select
Application.Run "TWEVENTER1" 'Which will trigger the SubNM selection
Is that pretty much the same as the E_PICK macro? Actually, it isn't working for me on 10.2... But E_PICK is still OK, e.g.:
It's a different route to the same destination in a sense. E_Pick requires that you return the selected value to a variable in the code, and that you populate the relevant cell(s) yourself. All you'd have in the workbook is one or more hard coded values. TWEVENTER1 leaves the actual SubNm in one place so that the actual SubNm can still be selected normally, but any other cell just bounces you off to the cell containing the formula and launches it as if you'd selected the original yourself. The other advantage of TWEVENTER1 is that it wasn't affected by that sodding Allow Editing Directly In Cells issue that screws up SubNms.
However you're correct; it seems to have been removed in one of the Version 10's. It still works in 9.5.2 / Excel 2010, but not in 10.2 Excel 2010.
Re: The same subnm linked on multiple sheets
Posted: Thu Dec 19, 2013 12:26 am
by rmackenzie
PlanningDev wrote:While I believe that will work, I believe that each subnm on each page won't be able to always show the latest selection? Am I following what you are thinking? The subnm just writesback to a cube? Wouldn't you have multiple sheets that would writeback to the same data?
It's hard to say just theorising! What I am suggesting is that if each SUBNM is:
Code: Select all
=SUBNM("server:dimension","",DBR("server:settings_cube",TM1USER(server),"measure_for_dimension"))
and that you use a DBS to write back the selection to the 'settings cube' after the selection is made. This doesn't work very well in Excel beccause Perspectives overwrrites the SUBNM, but you are wanting this to work in Web? You should try it in your web environment.
Re: The same subnm linked on multiple sheets
Posted: Mon Aug 31, 2015 2:19 am
by hari_cundi
Hello,
I am curious to know if anyone has had any issues using "TWEVENTER1" with TM1 10.2.2 FP3 and Excel 2010 (Version 14.0.4760.1000 32-bit). I am currently testing our "single-click" SUBNM macro and everything appears to be working correctly in our environments.
Is there any information on if IBM will continue to support this function or would you recommend excluding it from future developement?
Regards,
Hari