User Profile

rapidbisupport

Data Pipeline

Joined 02-19-2023

105 Posts84 LikesLikes received30 Solutions

View All Badges

User Widgets

Contributions

Custom sort for Bar and Column Chart
Custom sort for Bar and Column Chart Question: I have seen several posts of people asking if there is a simple way to apply custom sorts for car and column charts, and until recently, I have been using the community customBarColumnChart Plugin, which is not supported anymore, and also not working correctly in the latest version of Sisense cloud. Not sure why this cannot become a standard feature within the system, as there seem to be a lot of requests for this functionality. Answer: The following widget scripts will allow you to sort categories in a bar/column chart. You can change the order of items in sortOrder to change the order. They cater to when you want to sort by a Break By, or when you want to sort by Categories with no Break By. BREAK BY: widget.on('processresult', (w, args) => { var sortOrder = ['Yellow', 'Black', 'Red', 'Blue ', 'Gold', 'Silver'] args.result.series.sort(function (a, b) { return sortOrder.indexOf(a.name) - sortOrder.indexOf(b.name) }) }) The configuration is also attached in a screenshot below: ALT Text: A bar chart displaying total order quantities by region. Two regions are represented: Europe and USA. Each region has bars segmented by color: yellow, blue, black, gold, red, and silver. The chart highlights comparisons in order quantities between the two regions, with specific quantities not indicated in the image. CATEGORIES: If you're aiming to sort by the Categories directly, then you can use the following instead: widget.on('processresult', (w, args) => { var sortOrder = ['USA', 'Europe'] args.result.series[0].data.sort(function (a, b) { return sortOrder.indexOf(a.selectionData[0]) - sortOrder.indexOf(b.selectionData[0]) }) args.result.xAxis.categories.sort(function (a, b) { return sortOrder.indexOf(a) - sortOrder.indexOf(b) }) }) This configuration looks like this: ALT text: Bar chart comparing total order quantity between the USA and Europe. The USA has a higher total order quantity than Europe, with both categories represented in dark blue bars on a white background. This was tested using Sample Retail. Let me know how it goes for you in the comments! Check out this related content: Academy Documentation
01-21-2025 Place Widget & Dashboard Scripts
bar
Build Failure
Code-first Analytics
Plugins
2.4KViews
3likes
4Comments
Re: Import live model with Formula: Not working
Hi ramansingh89 , Is there a version discrepancy between environments? Have you tried exporting and importing using rest endpoints? Any difference? you could take a look a the model you've exported in a text editor and check to see if there is any reference to formulaManagement ? Let me know how you go? Thanks, Daniel RAPID BI [email protected] RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons
09-16-2024 Place Help and How-To
1KViews
1like
1Comment
Re: Tricky Combo Chart in Sisense How?
Yep - you can remove them by using the args.result.plotOptions in the process result: args.result.plotOptions.line.dataLabels.enabled = false Or all together: let selectedColor = "#F7921E" let unselectedColor = "#00ACCD" widget.on('processresult', (w, args) => { args.result.plotOptions.line.dataLabels.enabled = false let categoryItem = widget.metadata.panels[0].items[0].jaql.dim let myTeams = widget.dashboard.filters.item(categoryItem).jaql.filter.members args.result.series[0].data.forEach((d) => { d.color = unselectedColor d.selected = false }) for (let i = 0; i < myTeams.length; i++) { let myTeam = myTeams[i] args.result.series[0].data.find((d) => { return d.selectionData[0] === myTeam }).color = selectedColor } for (let j = 0; j < args.result.xAxis.categories.length; j++) { if (!myTeams.includes(args.result.xAxis.categories[j])) { args.result.xAxis.categories[j] = '' } } }) Which looks like this: Let me know how you go? Thanks, Daniel RAPID BI [email protected] RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons
09-11-2024 Place Help and How-To
2KViews
0likes
0Comments
Re: One-Click to Convert Widget Filter to Dashboard Filter
That's an awesome idea. Would be great to see it OOTB. As an FYI, after a bit of experimentation, you should be able to use this dashboard script (or convert to a plugin): prism.on('beforemenu', (ev, args) => { if (args.settings.name !== "widget-metadataitem" || args.settings.item.panel !== "scope") { return } let item = args.settings.item let menuItem = { id: "moveFilterToDashboard", caption: "Copy filter to Dashboard", desc: "Copy filter to Dashboard", execute: function () { args.settings.scope.dashboard.filters.update(args.settings.item) } } args.settings.items.push(menuItem) }) I did make sure the script copied rather than moved, as it's easier to delete the widget filter than to recreate if you use the button in error. This one will add a menu item, exactly as you specified. Let me know how you go? Thanks, Daniel RAPID BI [email protected] RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons
09-10-2024 Place Product Feedback Forum
628Views
0likes
0Comments
Re: Tricky Combo Chart in Sisense How?
Hey mate, Sure thing - i've provided a few variables at the top of the script to change selected and unseelcted colors: let selectedColor = "#F7921E" let unselectedColor = "#00ACCD" widget.on('processresult', (w, args) => { let categoryItem = widget.metadata.panels[0].items[0].jaql.dim let myTeams = widget.dashboard.filters.item(categoryItem).jaql.filter.members args.result.series[0].data.forEach((d) => { d.color = unselectedColor d.selected = false }) for (let i = 0; i < myTeams.length; i++) { let myTeam = myTeams[i] args.result.series[0].data.find((d) => { return d.selectionData[0] === myTeam }).color = selectedColor } for (let j = 0; j < args.result.xAxis.categories.length; j++) { if (!myTeams.includes(args.result.xAxis.categories[j])) { args.result.xAxis.categories[j] = '' } } }) With the value labels, you can do this in the interface (the "Value Label" toggle): Let me know how you go? Thanks, Daniel RAPID BI [email protected] RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons
09-10-2024 Place Help and How-To
2.1KViews
0likes
2Comments
Re: Tricky Combo Chart in Sisense How?
Missed these - a few extra steps required: Let's make a few changes to the widget script, where we will detect the selected dashboard filters related to the first category item and then change the color and replace the xAxis text appropriately: let myColor = "red" widget.on('processresult', (w, args) => { let categoryItem = widget.metadata.panels[0].items[0].jaql.dim let myTeams = widget.dashboard.filters.item(categoryItem).jaql.filter.members for (let i = 0; i < myTeams.length; i++) { let myTeam = myTeams[i] args.result.series[0].data.find((d) => { return d.selectionData[0] === myTeam }).color = myColor } for (let j = 0; j < args.result.xAxis.categories.length; j++) { if (!myTeams.includes(args.result.xAxis.categories[j])) { args.result.xAxis.categories[j] = '' } } }) Secondly, lets make sure the widget filters behaviour is set to 'Highlight': This should result in this here: Let me know how you go? Thanks, Daniel RAPID BI [email protected] RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons
09-09-2024 Place Help and How-To
2.1KViews
0likes
2Comments
Re: Tricky Combo Chart in Sisense How?
Hi lalasisi , A few steps here. In the columns shelf you want your teams. In the values shelf, you want: your metric for comparison & your metric for comparison ALL (teams) then click on three dots > Series Type > Line. Lastly, this widget script will highlight the relevant team: let myColor = "red" let myTeam = 'Lily Brown' widget.on('processresult', (w, args) => { args.result.series[0].data.find((d) => { return d.selectionData[0] === myTeam}).color = myColor }) With the result looking like: Let me know how you go? Thanks, Daniel RAPID BI [email protected] RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons
09-08-2024 Place Help and How-To
2.1KViews
0likes
4Comments
Re: How to change week date filter to display differently?
Hey Astroraf , I think you'd have less head/heartache by just creating a dimWeeks and attaching a string in the format you want from a datamodel perspective - but this wouldn't be as tightly integrated into the filters panel (multiple filters, etc.) Here is a dashboard script that waits for the weeks in the dropdown to exist, and then changes their text relative to their original text. It's a bit of a mess - but you could change the result text by adjusting the getSundayFromWeekNum function at top. Not sure what effect it'll have on performance as it always checks for there to be an element created consistent with the weeks filter, and then when it exists it changes the text values - would be worthwhile testing and iterating before deploying. If I had more time, i'd experiement with destroying the observer when not needed anymore - but not sure how that'd work and how the list is generated after the fact. // https://thewebdev.info/2021/08/29/how-to-calculate-date-from-week-number-in-javascript/ function getSundayFromWeekNum(weekNum, year) { const sunday = new Date(year, 0, (1 + (weekNum - 1) * 7)); while (sunday.getDay() !== 0) { sunday.setDate(sunday.getDate() - 1); } return sunday; } dashboard.on('initialized', (d, args) => { // Function to update the text format function updateWeekFormat() { $('.K1ShsxUa7DDQ260o6OLv').find('.custom-checkbox__text').each((i, element) => { if (element.mod) { return } element.mod = true const text = element.textContent; const [week, year] = text.split(' '); element.textContent = getSundayFromWeekNum(week, year).toLocaleDateString('en-us') }); } // Create a MutationObserver to watch for changes in the dropdown const observer = new MutationObserver((mutationsList, observer) => { if ($('.K1ShsxUa7DDQ260o6OLv').find('.custom-checkbox__text').length === 0) { return } for (const mutation of mutationsList) { if (mutation.type === 'childList') { updateWeekFormat(); } } }); // Start observing the dropdown for changes observer.observe(document, { childList: true, subtree: true }); // Initial call to update the format if elements are already present updateWeekFormat(); }) Let me know how you go? Thanks, Daniel RAPID BI [email protected] RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons
09-08-2024 Place Help and How-To
1.4KViews
1like
0Comments
Re: Script to dynamically generate URLs for hyperlinks in Pivot Table rows
Hey Jake_Raz , Looks good! There was some weirdness around the count of rows. It seemed different from pivot to table controls, so glad you were able to get to the bottom of it. let rows = tableBody.find('tr'); I found for some weird reason that pivots were doubling this count and tables were not, so I had some logic around this to catch it. I don't think this will cause issues because you will always be searching for the 'rows' in the 'tableBody' which relate to the specific widget - it shouldn't spill over or cause any issues across any other widgets. Nice work! Thanks, Daniel RAPID BI [email protected] RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons
08-25-2024 Place Help and How-To
3.8KViews
1like
0Comments
Re: Script to dynamically generate URLs for hyperlinks in Pivot Table rows
Hi Jake_Raz , Seems that your instance is leveraging some custom method (prism.createDrillDown) to produce urls based on certain columns and set them to target columns on pivot and table widgets. Furthermore, looks like some of this string is hardcoded into the function. Based on the code you provided, I have the following widget script that is a bit more complex, but should allow you to generate the url you need: function getValueFromCell(tableBody, rowNumber, columnNumber) { let row = tableBody.find(`tr:nth-child(${rowNumber})`) let cell = row.find(`td:nth-child(${columnNumber})`) let cellVal = (widget.type == 'pivot') ? $(cell).attr('val') : cell.html() return cellVal } function setValueToCell(tableBody, rowNumber, columnNumber, valueToSet) { let row = tableBody.find(`tr:nth-child(${rowNumber})`) let cell = row.find(`td:nth-child(${columnNumber})`) if (widget.type == 'pivot') { cell.find('span').html(valueToSet) return } cell.html(valueToSet) } widget.on('domready', () => { let widgetElem = (widget.inFullScreenMode) ? $('.widget-body') : $(`[widgetid="${widget.oid}"]`) let tableBody = widgetElem.find('tbody') let rowCount = widget.type == 'pivot' ? tableBody.find('tr').length / 2 : tableBody.find('tr').length + 1 for (let rowIndex = 1; rowIndex < rowCount + 1; rowIndex++) { // we can configure the target column and desired string here // we set target column as 2 - meaning we'll overwrite the value of the cell in the second column. let targetColumn = 2 // we can then gather some information from adjacent cells // current value gets the value from the currently processed row in column 2 let currentValue = getValueFromCell(tableBody, rowIndex, 2) // current value gets the value from the currently processed row in column 1 let valueFromFirstColumn = getValueFromCell(tableBody, rowIndex, 1) // current value gets the value from the currently processed row in column 3 let valueFromThirdColumn = getValueFromCell(tableBody, rowIndex, 3) // formulate the string to write to the cell let string = `<a href="https://${valueFromFirstColumn}_${valueFromThirdColumn}">${currentValue} 'HELLO'</a>` // set the value to the cell setValueToCell(tableBody, rowIndex, targetColumn, string) } }) Note that this should work only with the Windows Sisense, and uses a heap of jQuery to: find the table, row and cells in the rows, gather the value of a cell given a row and column index, change the html in a cell given a row, column and some string to change it to. This one uses the 'domready' event, but in the case of pivot you can also use the 'ready' event. 'domready' is only required if you're using a table. It'll require some modification from your end - getting the values from the columns you need, and using those values to generate the string you'll then eventually pass to the setValueToCell method. I've wrapped this up in a for loop that should iterate for rows in the table (so that you can modify the value of the cell in each row). Feel free to reach out again if you need further assistance with implementing this, as it's more complex than the script you shared to me - i'd be happy to jump on another early call. Lastly, some resources: https://sisense.dev/guides/customJs/jsApiRef/widgetClass/ Let me know how you go? Thanks, Daniel RAPID BI [email protected] RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons
08-22-2024 Place Help and How-To
3.8KViews
1like
2Comments