The same subnm linked on multiple sheets

[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.

[Alan Kirk]

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.

[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.

[rmackenzie]

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.

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.

[PlanningDev]

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.

[rmackenzie]

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

[rmackenzie]

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.

[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?

[Alan Kirk]

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.

[rmackenzie]

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.

[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