Custom JavaScript in Cognos – Searchable Checkbox List

Paul Mendelson, November 1, 2017

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);

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][‘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][‘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][‘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:

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:

Now, let’s see it in action.

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

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.

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

Conclusion

I hope you find this article on custom JavaScript in Cognos Analytics helpful.  If you haven’t already, be sure to check out our blog for additional tips and tricks. Be sure to subscribe to our newsletter for data and analytics news, updates, and insights delivered directly to your inbox.

Next Steps

If you have any questions or would like PMsquare to provide guidance and support for your analytics solution, contact us today.