As in Excel, the Jedox HYPERLINK() function can create a shortcut to another location in the current workbook or open a document stored on a network server or other location. In addition, the HYPERLINK() function also allows values to be set in or transferred to spreadsheet cells, named ranges, or variables when the link is clicked.
If the target of the hyperlink is another report or file in Jedox Web, the path is case-sensitive; the hyperlink must then specify the same uppercase/lowercase characters used for the folders and filenames of the target.
The syntax for the function is HYPERLINK(link_location, friendly_name, screen_tip, source1, destination1[, source2, destination2, …]). The arguments are outlined below.
Link location
|
"SELF" |
Link to the active workbook, Sheet1, A1. This link will also work after renaming or moving the workbook. |
|
"[SELF]Sheet2!A5" |
Link to the active workbook, Sheet2, A5. This link will also work after renaming or moving the workbook. |
|
"/Jedox/Financials/Accounting/Demo.wss" |
Link to the Demo workbook in the "Jedox" group, "Financials" hierarchy, "Accounting" folder. |
|
"/Jedox/Financials/Accounting/Demo.wss#Frame2" |
In framesets: opens the Demo.wss workbook in the frame called "Frame2". |
|
"[/Jedox/Financials/Accounting |
Opens the Demo workbook and selects A1 on Sheet2. Absolute references with $ are not necessary and disallowed here. |
|
"[/Jedox/Financials/Accounting |
In framesets: opens the Demo workbook and selects A1 on Sheet2, in Frame2. |
|
"[/Jedox/Financials/Accounting |
Opens the Demo workbook and selects the range A2:B3 on the sheet called 'Sheet1' (use singe quotes for sheet names with spaces. Absolute references with $ are not necessary and disallowed here). |
|
"[/Jedox/Financials/Accounting/Demo.wss]A2" |
Opens the Demo workbook and selects A2 on the first sheet. Absolute references with $ are not necessary and disallowed here. |
|
"[/Jedox/Financials/Accounting |
Opens the Demo workbook and selects range called "Range 1" on the first sheet. |
|
"[/Jedox/Financials/Accounting |
Opens the Demo workbook and selects range called "Range 1" on "Sheet2". |
|
"/Jedox/Financials/Documentation/Manual.pdf" |
Link to the PDF document Manual.pdf in the "Jedox" group, "Financials" hierarchy, "Documentation" folder. |
|
"http://www.google.com/" |
Link to a URL. |
|
"mailto:test@example.com?subject=some text!&Body=Test" |
A link that opens a mail client and creates an email to a recipient with a subject and a body. |
|
A5 or $A$5 |
Use the content from another cell on the worksheet for link_location. |
Friendly name
|
"A hyperlink caption" |
Regular string. |
|
B5 or $B$5 |
Use content from another cell on the worksheet for friendly_name. |
|
CONCATENATE(B2,C2) |
The result of a formula. |
Screen tip
|
"Some hyperlink tip" |
Regular string. You can use the HTML tags br (line break), b (bold) and i (italic) is supported. Other HTML tags are not supported. |
|
C5 or $C$5 |
Use content from another cell on the worksheet for screen_tip. |
|
CONCATENATE(B2,C2) |
The result of a formula. |
Source N
|
B7 |
Use the content of cell B7 on an active sheet. |
|
A2:B3 |
Use the content from range A2:B3 on an active sheet. |
|
Sheet2!A2:B3 |
Use the content from range A2:B3 on the sheet "Sheet2". |
|
Range1 |
Use the content from a range called "Range1" on the first sheet. |
|
Sheet2!Range1 |
Use the content from a range called "Range1" on Sheet2. |
|
@var1 |
Use the content from variable "var1". |
|
"Some text" |
A constant value. |
|
{"value1","value2","value3"} |
A list of constant values. |
Destination N
|
B7 |
Set the cell B7 on the active sheet. |
|
A2:B3 |
Set the range A2:B3 on the active sheet. |
|
"Sheet2!A2:B3" |
Set the range A2:B3 on the sheet "Sheet2". |
|
SomeRange |
Set the content from a range called "Range1" on the active sheet. |
|
Sheet2!SomeRange |
Set the content from a range called "Range1" on Sheet2. |
|
"@var1" |
Set value of variable "var1". |
Applicable only by URL link_location
|
"Some text" |
A constant value. |
|
{"value1","value2","value3"} |
A list of constant values. |
Notes:
-
When link_location is a URL, source1 is key, and destination1 is value in URL parameters list:
http://www.google.com?source1=destination1&source2=destination2
-
Transferring a value into a cell in a new sheet which is inside of a DynaRange is currently only partially supported; the transferred value will only be set in the first row / column of the DynaRange.
-
When transferring a value into a named range on a new sheet, where a DynaRange depends on this named range, the DynaRange will not use the transferred value in its initialization. In this scenario, a variable should be used for the transfer instead.
Target Types
You can specify a target type that determines where and how the hyperlink will be opened. In Hyperlink Spreadsheet functions, the target type is appended with a leading hashmark (#) to the link target.
| Target type | Target is in Jedox Web | Target is external URL |
| _new | Opens target link in new WSS tab (DEFAULT). | Opens target in a new browser window or tab with limited browser UI. |
| _blank | Opens target link in new browser window. Note: after executing the function with this target, the same report cannot be opened within Jedox in some other context for the remainder of the current session. |
Opens target in a new browser tab |
| _self | Closes current file and opens target link in the same tab. | Not supported as types for external hyperlink targets. |
| _top | Closes the current frameset (or current file) and opens target link in the same tab. | Not supported as types for external hyperlink targets. |
| FrameN | Should only be used in a frameset context. Opens the linked document in FrameN of the same WSS window | Not supported as types for external hyperlink targets. |
Jedox is generally built to run within a single browser window. Usage of multiple reports in parallel is possible through tabs inside of the Jedox application. The usage of several reports of the same Jedox environments within multiple browser windows, which is triggered by using the _blank target type, has constraints with regards to the synchronization of the application's state between the windows, and is also not supported in the Jedox Plan & Forecast mobile application. The type _blank should generally be avoided, in favor of usage of _new target type.
Note: whether links open in a new browser tab or browser window depends on the configuration of the browser itself.
Relative Hyperlinks
You can also specify a relative location in the formula HYPERLINK(). This allows you to refer to the target report relative to the location of the current report rather than a specific report.
Note: when you insert a hyperlink from the ribbon in Jedox Web and you want the DynaRange to react to a transferred value, make sure you use a variable as a destination for the hyperlink.
The syntax is similar to UNIX-like path expression. For example, to link from the current report to another one called "Data Entry" which is located in the same directory, the syntax is:
=HYPERLINK("./Data Entry.wss",…)
To open a report which is located in another folder, for example "Planning", located on the same level as the folder of the current report, the syntax is:
=HYPERLINK("../Planning/Data Entry.wss",…)
The file "Function_HYPERLINK" is stored in "Default/Public Files".
Enter the following function in A4 with Insert - Function:
=HYPERLINK("/Demo Spreadsheets/Demos/A.wss#Frame3","Hyperlinktext","Mousetext",A1:D2,"Sheet4!A10:D11")
The target area must already exist. Make sure you create the file "Demo Spreadsheets/Demos/A.wss" with "Sheet4" before executing function. The function above copies the range A1:D2 of the file "Function_HYPERLINK", then opens the file "Demo Spreadsheets/Demos/A.wss" and pastes the copied area in the cells A10:D11 of "Sheet4".
Note: the source area must be entered without quotes, the target area with double-quotes.
The function can therefore also transfer cell values. To do this, you can also enter a range of up to 13 source/target mappings.
By adding "#frame3", the file "A.wss” will be opened in “frame3” if the function is called in a frameset and “frame3” exists (see Framesets). If these conditions are not met, then "A.wss" will open in a new tab.
"Mousetext" displays the path to the target file when you hold the cursor over the link. To prevent displaying the path, use “Mousetext#simple” instead.
If the display text is a number (e.g. a PALO.DATA formula) you need to use the TEXT() function to format this number. The normal number formatting via the "Format cells" dialog will not work in this case.
Example:
=HYPERLINK("[SELF]Sheet2!A20",TEXT(PALO.DATA("localhost/Demo","Sales","All Products","Europe","Year","All Years","All Datatypes","Units"),"#,##0.00"),"Tip")
Example with position and size information for the target browser window. This information is only possible with the options #_blank and #_new:
=HYPERLINK("/Default/Public Files/target.wss#_blank,top=200,left=200,width=300,height=300","My link","My tooltip")
Example with position and size information and additional information for the target browser window (address bar, menu bar, window resizable, scrollbars, status bar, toolbar, each with =[yes|no]):
=HYPERLINK("/Default/Public Files/target.wss#_blank,top=200,left=200,width=300,height=300, location=yes,menubar=no,resizable=yes,scrollbars=yes,status=no,toolbar=no","My link","My tooltip")
This additional information is only possible with the option #_blank and is only for the browser window (e.g. a Jedox Spreadsheet has always scroll bars).
Updated September 27, 2022