cancel
Showing results for 
Search instead for 
Did you mean: 
intapiuser
Community Team Member
Community Team Member

Render existing HTML code stored in your data model

Follow the steps below in order to URL clickable within a pivot table. Some steps are different depending on whether your Sisense instance is running on Windows or Linux. 

You can check whether your Sisense instance is running on Linux or Windows by following the steps in this article: Check whether running Sisense on Linux or Windows

Common Steps

1. Create a custom column within the table with the URLs you would like to make clickable. In this example I called it HTMLURL
Craft the HTML version of the link using <a href> tags. I used column "Link" as the URL and column "Name" as the display text
'<a href="' + [Link] + '" target="_blank">' + [Name] + '</a>'
This will create a column with the URL wrapped with HTML elements.
3. Rebuild the cube to include the custom column
4. Create a pivot table with the new column


Additional Steps for Sisense on Linux

These steps will only need to be performed once and can only be taken by an Admin or Sys Admin in the Sisense instance. 

5. Go to Admin tab > System Management > Configuration
6. Within the resulting window, expand the "Pivot Table" section, and toggle on "Allow rendering Pivot Table content as HTML"
7. Then scroll down to the bottom of the page and click Save
8. Check back on your pivot widget, it should be rendering the HTML as a hyperlink.

Additional Steps for Sisense on Windows

5. To render the HTML, we need to edit the the script of the widget. Within the widget editor, click the 3 dots at the top right hand corner, then click "Edit Script"
6. Paste the following script into the script editor window. Then click Save.
widget.on('ready', function(){
     var tags = $('tbody span:contains("<")');
     for(var i = 0; i<tags.length; i++){
         $(tags[i]).replaceWith(String(tags[i].textContent))
     }
});
7. Close the script window and refresh the widget page. The result should be a formatted link
8. Save the newly edited widget by hitting "Apply"

Limitations

File exported to Excel/CSV will contain not rendered HTML code.
  1. Add a custom field in your data model and compute there your link. Your link should have the following view:
    https://example.com?displayedValue=[SomeValue]&{optionalParameters}
    In terms of this link, displayedValue is a value that a user will see instead of the link on the pivot

    Build your cube.
  2. Add created field/fields to your Pivot widget and apply the following widget's script:
var linkRowNumber = [
 1, 3
]; //Columns' numbers where we need to render links. Numeration in JS starts from 0.
var displayedValue = 'displayedValue'; //Name of the query parameter that stores displayed value for link
var defaultDisplayedValue = 'Link'; //This value will be displayed on top of the link, if another value was not found

widget.transformPivot(
 {
  type: ['member']
 },
 function setCellBackground(metadata, cell) {
  if (metadata.rowIndex && linkRowNumber.includes(metadata.colIndex)) {
   cell.content = "<a href=" + cell.content + ' target="_blank">' + getParameterByName(displayedValue , cell.content) + "</a>"; //Prepare link
   cell.contentType = "html";
  }
 }
);


function getParameterByName(name, url) {
 name = name.replace(/[\[\]]/g, "\\$&");
 var regex = new RegExp("[?&]" + name + "(=([^&#]*)|&|#|$)");
 var results = regex.exec(url);
 if (!results) return defaultDisplayedValue;
 if (!results[2]) return defaultDisplayedValue;
 return decodeURIComponent(results[2].replace(/\+/g, " "));
}
Now, links will be rendered by the widget's script. Also, when you export this Pivot into Excel/CSV, these links will be shown as valid links without any additional HTML code:

Enjoy!

Rate this article:
Comments
rafaelderas
8 - Cloud Apps
8 - Cloud Apps

Does this solution only works with custom columns? I have added the url generation to the SQ part of a data node on a live model, but when displaying the dashboard, I don't get the clickable link I just get all the html code from the column I generated in the data model (URLs blurred intentionally)

Screenshot 2024-01-24 at 13.03.12.jpeg

Laflet
9 - Travel Pro
9 - Travel Pro
liamcameron
8 - Cloud Apps
8 - Cloud Apps

hey @intapiuser what if we just want to display the text in our pivot... so maybe we want to show the min(date) or first(string)... pivots do not seem to support this and we've been unable to find a way to do it... seems like this is close! but we're not using URLs

Version history
Last update:
‎03-02-2023 09:52 AM
Updated by:
Contributors