2100 Clearwater Dr. Suite 330, Oak Brook IL 60523

1-(708) 575-2090 contact@pmsquare.com Visit our other locations: australia singapore philippines

Custom JavaScript in Cognos – Searchable Checkbox List

This is the sixth article in a series I am doing about custom JavaScript in Cognos Analytics (I really like custom JavaScript in Cognos) so be sure to check out my other blogs.  In this article, I will explore how to get one of my most popular widgets for Cognos 10 – the searchable checkbox list – to work in Cognos Analytics interactive mode.  In theory it should still work for non-interactive mode, but what do we have for interactive mode? Now that we can use jQuery in the reports, an entire universe of widgets are available. Why adapt my old solution when far more clever people wrote far better scripts? 

 

In this case, let’s focus on <a href=”https://pbauerochse.github.io/searchable-option-list/”>Searchable Option List</a> by Patrick Bauerochse. Much nicer than the one I wrote, it also has a nice grouping option.

 

The widget itself expects data in a certain format. We can transform data coming from a query to the expected format fairly easily. To begin, let’s pull all parameters into an array:

if( oParameter && ( oParameter.values.length > 0 ) ){
for(var i=0;i<oParameter.values.length;++i){
preselectedVals.push(oParameter.values[i].use)
}
};

 

The data container expects two or three fields. Display, Use, and Group. If Group exists, we need to do some fancy array functionality to group the display and use values.

groupBy = function(xs, key) {
return xs.reduce(function(rv, x) {
(rv[x[key]] = rv[x[key]] || []).push(x);
return rv;
}, {});
}

 

Then it’s a matter of creating the JSON.
for(var i=0;i<groupCount;++i){
solJSON[i]={};
solJSON[i][‘type’]=’optiongroup’;
solJSON[i][‘label’]=oDataStore.columnValues[group][i];
solJSON[i][‘children’] = [];
for(var j=0;j<groupedJSON[i].length;++j){
solJSON[i][‘children’][j]={};
solJSON[i][‘children’][j][‘type’]=’option’;
solJSON[i][‘children’][j][‘value’]=oDataStore.columnValues[use][groupedJSON[i][j][use]];
solJSON[i][‘children’][j][‘label’]=oDataStore.columnValues[display][groupedJSON[i][j][display]];
if(preselectedVals && $.inArray(oDataStore.columnValues[display][groupedJSON[i][j][display]].toString(),preselectedVals)>=0) solJSON[i][‘children’][j][‘selected’]=’true’;
}
}

 

Without the group it’s much simpler.

 

for(var i=0;i<groupedJSON.length;++i){
solJSON[i]={};
solJSON[i][‘type’]=’option’;
solJSON[i][‘value’]=oDataStore.columnValues[use][groupedJSON[i][use]];
solJSON[i][‘label’]=oDataStore.columnValues[display][groupedJSON[i][display]];
if(preselectedVals && $.inArray(oDataStore.columnValues[display][groupedJSON[i][display]].toString(),preselectedVals)>=0) solJSON[i][‘selected’]=’true’;
}

 

In both cases, if the value exists in the parameters list, it adds a selected=true attribute to the JSON.

Once the data has been collected and transformed into the JSON it’s time to start drawing the control. The config of the control expects a few options.

 

, pmaxHeight = (o&&o[“maxHeight”])?o[“maxHeight”]:150
, pMultiple = (o&&o[“multiple”])?o[“multiple”]:false
, param= (o&&o[“parameter”])?o[“parameter”]:’Parameter1′
, hideOptions = (o&&o[“hideOptions”])?o[“hideOptions”]:false

 

maxHeight controls the height of the drop down. multiple controls whether it shows checkboxes or radios. parameter is the parameter name, the same value you would use in a prompt alias. hideOptions lets hide the labels that appear over the prompt. If hideOptions is selected, the placeholder text of the input will be updated to show the display value if one is selected, or “(multiple)” if more than one is selected.

 

To use it, first drag in a custom control. The properties should look something like this:

Custom JavaScript in Cognos - Searchable Checkbox

 

In this case, I’m setting the parameter value of the prompt to “product”, I’m hiding the options, and I’m instructing the script to create checkboxes instead of radios.

 

Next, a dataset has to be associated with the prompt. To do this I created a new query with three data items.
Group: [Sales (query)].[Products].[Product line]
Display: [Sales (query)].[Products].[Product]
Use: [Sales (query)].[Products].[Base product number]

 

Those were added to the dataset:

 

Custom JavaScript in Cognos - Searchable Checkbox

 

Now, let’s see it in action.

 

Custom JavaScript in Cognos - Searchable Checkbox

 

By setting the config to multiple=false, it automatically turns into a radio prompt.

 

Custom JavaScript in Cognos - Searchable Checkbox

 

Personally, I’m not a big fan of the options appearing over the prompt, but there’s no accounting for taste. Setting hideOptions to false will allow the selected options to appear.

 

Custom JavaScript in Cognos - Searchable Checkbox

 

Ultimately this is a very effective (and if disabling the hideOptions), and very compact way of creating a multiselect prompt. I’ve made a few modifications to the original sol.js – adding support for selecting/deselcting only the visible options after searching, and making “select none” work with radio prompts.

 

You can view the report and js files here: Custom JavaScript in Cognos - Searchable Checkbox (6 downloads)

Conclusion

I hope you find this article on custom JavaScript in Cognos Analytics helpful.  If your organization has not yet migrated to Cognos Analytics, check out our Cognos Analytics Upgrade Hub strategies and resources.  And if you haven’t already, be sure to check out our blog for additional tips and tricks.  Subscribe to our e-newsletter for more technical articles and updates delivered directly to your inbox.

 

SUBSCRIBE

 

If you would like to learn more how PMsquare can help your organization please reach out to us at:

United States Australia Singapore, Philipines, Thailand
PMsquare LLC Cornerstone PMsquare | A Cornerstone Company
P: 708-575-2092
E: Chris Loechel
P:+61 1300 840 048
E: Piers Wilson
P:+65 6635 1700
E: Carsten Brandt

Blog post shared courtesy of PMsquare LLC