Hyperlink TM1 Web format

[declanr]

All,

I am using the excel =Hyperlink() formula in some of my websheets to navigate to other websheets.
So essentially every numeric cell in my P&L can be clicked on and take you to the more detailed analysis/input screen. Functionally its perfect and works a treat.

The issue is with the formatting, in Excel I am viewing it as nice comma separated values (e.g. 1,000,000) but when it gets rendered to the web it seems to have lost that ability and is just showing as the number without thousand separators.
This is purely an aesthetic thing but with some sizeable numbers we do need the separators, I can just put the link in the row names instead of in the values but I prefer the click on the value method.

Obviously not the end of the world as I have a way to continue regardless but was wondering if anyone had encountered it? Or has a solution? Or if I just need to chalk it up to "one of those TM1 things."

Cheers,
Declan

[declanr]

Scratch that, just shook off the morning brain and worked out a way.

I had been parsing in the value like:
=hyperlink(A5, DBRW(stuff))

Instead of directly using the DBRW i will do that in another cell and pass it in as a text formatted value.

[AmbPin]

Hello,

You could use the text function wrapped arounf your dbrw:-

=hyperlink(A5, TEXTDBRW(stuff), format))

[lotsaram]

All,

I am using the excel =Hyperlink() formula in some of my websheets to navigate to other websheets.
So essentially every numeric cell in my P&L can be clicked on and take you to the more detailed analysis/input screen. Functionally its perfect and works a treat.

The issue is with the formatting, in Excel I am viewing it as nice comma separated values (e.g. 1,000,000) but when it gets rendered to the web it seems to have lost that ability and is just showing as the number without thousand separators.
This is purely an aesthetic thing but with some sizeable numbers we do need the separators, I can just put the link in the row names instead of in the values but I prefer the click on the value method.

Obviously not the end of the world as I have a way to continue regardless but was wondering if anyone had encountered it? Or has a solution? Or if I just need to chalk it up to "one of those TM1 things."

Cheers,
Declan

That is a brilliant idea! Are you just passing the URL to a websheet or view or also passing in title elements?

[declanr]

That is a brilliant idea! Are you just passing the URL to a websheet or view or also passing in title elements?

I just pass the URL to the spreadsheet but I match title elements by having hidden DBSAs that send the selected subset elements to an attribute against the TM1 User's name in the }clients dim. That then gets retreived and pulled into the initial SUBNM formula in the new websheet whilst still allowing the user to change the element in the SUBNM... which in turn sends the newly selected element back to the attribute for them to see when they go back to the original websheet or another one... if you can follow that ramble?

Essentially it's just to create a similar setup to the Consolidation tools of Controller of Hyperion where you can click on a value until you drill to the entry point but its looking a damn bit slicker in my opinion and for a company that already own TM1 or CX for other purposes its a damn bit cheaper!

BTW this is the code I went with in the end:

Code: Select all

=HYPERLINK($B15,IF(DBRW($G$6,$G$8,$G$9,G$14,$G$11,$F15,$G$10)=0,TEXT(DBRW($G$6,$G$8,$G$9,G$14,$G$11,$F15,$G$10),"-"),IF(DBRW($G$6,$G$8,$G$9,G$14,$G$11,$F15,$G$10)<1000,TEXT(DBRW($G$6,$G$8,$G$9,G$14,$G$11,$F15,$G$10),"0"),TEXT(DBRW($G$6,$G$8,$G$9,G$14,$G$11,$F15,$G$10),"0,000"))))

[AmbPin]

Hi Declan,
That sounds really clever. So in my simple example below, you would have a hidden dbsa that sends back the value of the company subnm, but if the user clicked on the £100 hyperlink how do you tell the recieving sheet that the user selected Item Code a?

[lotsaram]

Actually rather than the hidden dbsa why not just replace the subnm with a dbr selected via picklist?

[AmbPin]

I agree, picklists probably are the way to go instead of the subnm, but I don't see how would you tell the target application that "item Code A" was selected if the user clicked on a £100 hyperlink.
We have done similar using an action button linking title elements but would love to know how to do this with hyperlinks as it would be much more user friendly.
Perhaps I have mis-interpreted Declan's suggestion.

[declanr]

Actually rather than the hidden dbsa why not just replace the subnm with a dbr selected via picklist?

The picklist idea is one I have used before in previous applications where there was only a small subset of elements to choose from and it worked well but in this instance there is quite a deep structure for users to choose from so I find that the subset editor makes it easier for them to navigate.

We have done similar using an action button linking title elements but would love to know how to do this with hyperlinks as it would be much more user friendly.
Perhaps I have mis-interpreted Declan's suggestion.

In this particular instance rather than passing parameters from the actual value I pass them from the hyperlink hidden in column A.
So for example the "Trade Income" row would all navigate to one websheet that details various types of Trade Income etc and in my Balance Sheet both the "Share Capital" and "Share Premium" would take you to the same input page which would be a shareholder ownership screen.

it think your specific example is looking for more flexibility but if you look through the websheet navigation option of the API guide there is a section on how to use what is essentially a hyperlink to pass title elements to another websheet (haven't looked in a while but I'm sure its there somewhere)... what you could then do is use an MDX statement in your TM1RPTROW formula that references the "Title Element" to give you a flexible list of elements.

[AmbPin]

Wow thanks Declan, I had not seen this page before:-
http://pic.dhe.ibm.com/infocenter/ctm1/ ... ect_N905A3
I foresee hours of fun/frustration playing with this..

[Andy Key]

Note that if you start passing parameters to your url such that it's length exceeds 255 chars, Excel will show a #Value! error for the HyperLink() function. TM1Web doesn't seem to mind and correctly displays the hyperlinked value. And no, I haven't test just how long the url can be before TM1Web gives up as well.

[AmbPin]

Hi Andy,
Thanks for the tip. You can get around this limitation with some Excel functions by concatenating strings but this does not work for the Hyperlink function.