Sixth, and final, in a series of blog posts about free extensions to OpenText Interactive Viewer.
Do your users ever need to filter across multiple columns in an iHub table? It can be painful for a user to filter a table column by column when the term they’re filtering on appears in several columns. In this situation, users want a way to filter the entire table at one time.
This post shows how to create a table-wide search box in a table header, like the one shown above. Our table-wide search is flexible: The user can make the search case sensitive with a simple click of a check box, and clear the filters by searching for nothing. Just to the right of the search box, we’ve added a <div> that helps the user know what searches have already been applied to the table (as seen below), making it easier to apply multiple filters on top of each other.
We perform table-wide searches by iterating through the resultset and, if we find a match in the columns that we are searching, adding the row’s unique identifier (we call this a RowID) to an array, and finally returning all of the columns identified in the array.
The steps for creating a table-wide search box are:
- Add one text item for the search box and another text item for the list of search terms in the header of the table. Depending on the widths of the columns in the tables, you may want to merge several cells for these text items.
- Change the type of each text item to “HTML”.
- Copy the <input> HTML elements below into the leftmost text item. The screenshot below the block of code shows how it should look.
Search all columns: <input type="text" id="searchString"><br><input type="checkbox" id="ignoreCase" checked="true"> Ignore case<input type="submit" value="Search" onclick='javascript:searchTable()'>
- Copy the <div> HTML element below into the rightmost test item. The screenshot below the block of code shows how it should look.
<div id="searchTerms">yep</div>
- In clientScriptsonContextUpdate, paste the code found at the end of this document into the window. You can find clientScriptsonContentUpdate by clicking on an empty portion of the Layout Manager, clicking on the Script tab, and selecting clientScripts in the first pulldown and onContentUpdate in the second. It will look like this:
- Create a column in your dataset called “ROWID” and make it the first column in your table. Make this column Hidden via the Property Editor. In the dataset, only the column “ROWID” must be unique; you don’t necessarily need to use the SQL ROWID (which your table may not even have). In our example, ROWID is the following:
“SELECT ROW_NUMBER() OVER () AS ROWID, COUNTRY, …”.
- Test your report in OpenText Analytics Designer.
Troubleshooting
If your embed code is not working, try debugging in Chrome.
If you add “debugger” in your JavaScript, Chrome will break at that point when Chrome tools debugger is open.
Conclusion
We can make it easy for a user to find the information that he or she wants by simply adding a couple of HTML input items and a <div> to the table header and including a small amount of JavaScript.
We hope you’ve found this series of extension tips for iHub Interactive Viewer helpful. Please Subscribe (at upper right) to be notified when they are posted, and let us know in the comments what other extensions and functionality you’d like to see.
Previous blog posts in this series:
Full Table Search JavaScript Code
var columns = new Array();
window.myViewerId = this.id;
this.createFastFilters = function () {
debugger;
// First create the list of filter terms
var searchTerms = sessionStorage["searchTerms"];
var termDiv = document.getElementById("searchTerms");
termDiv.innerHTML = "createFastFilters";
if (searchTerms != "" && searchTerms != 'undefined' && searchTerms != null) {
termDiv.innerHTML = "Search Terms:
" + searchTerms;
} else {
termDiv.innerHTML = "No Search Terms";
}
var table = this.getViewer().getTable();
var request = new actuate.data.Request(table.getBookmark(), 0, 100);
request.setMaxRows(0);
}
window.searchTable = function() {
debugger;
var table = actuate.getViewer(myViewerId).getTable();
var elem = document.getElementById("searchString");
var searchTerm = elem.value;
if (searchTerm == "") {
sessionStorage["searchTerms"] = "";
table.clearFilters("ROWID");
table.submit();
return;
}
if (sessionStorage["searchTerms"] == "" || sessionStorage["searchTerms"] == 'undefined' || sessionStorage["searchTerms"] == null) {
sessionStorage["searchTerms"] = searchTerm;
} else {
sessionStorage["searchTerms"] = sessionStorage["searchTerms"] + " && " + searchTerm;
}
var request = new actuate.data.Request(table.getBookmark(), 0, 100);
request.setMaxRows(0);
request.setColumns(columns);
actuate.getViewer(myViewerId).downloadResultSet(request,window.searchColumns);
//alert('Hello!! ' + searchTerm);
}
window.searchColumns = function(resultSet) {
var elem = document.getElementById("searchString");
if (document.getElementById("ignoreCase").checked == true)
var searchTerm = new RegExp(elem.value, "i");
else
var searchTerm = new RegExp(elem.value);
var columnIndex = 1;
var i = 0, rowidIndex = 0;
var rowIds = new Array();
var resultColumns = resultSet.getColumnNames();
for (columnIndex = 0; columnIndex < resultColumns.length; columnIndex++) {
if (resultColumns[columnIndex] == "ROWID") {
rowidIndex = columnIndex;
break;
}
}
if (searchTerm.length < 1) {
return;
}
while (resultSet.next()) {
for (columnIndex = 0; columnIndex < resultColumns.length; columnIndex++) { if (resultSet.getValue(columnIndex+1).search(searchTerm) != -1) { //alert("Found in: " + resultSet.getValue(columnIndex+1)); rowIds[i] = resultSet.getValue(rowidIndex+1); i++; continue; } } } debugger; // Found at least one, so create filter if (i > 0) {
var table = actuate.getViewer(myViewerId).getTable();
var filter = new actuate.data.Filter("ROWID", actuate.data.Filter.IN, rowIds);
table.setFilters(filter);
table.submit();
}
}
this.createFastFilters();
