PMsquare

View Original

Custom JavaScript in Cognos Analytics – Date Prompts and Datasets

Paul Mendelson

In the previous article, we talked about controls via custom javaScript in Cognos Analytics.  We created a select prompt that dynamically generates list of field names in a specified list, and allows the user to toggle the visibility of individual columns.  In this article, we’ll spend some time on date prompts and datasets and look at cases where we don’t need to generate an interface control, and go over a practical example.

Custom JavaScript – Date Prompts

Many reports have separate Start and End date prompts.  This is to provide a finer grain of control than is possible with the standard Cognos date range prompts.   An often requested requirement is to provide a way to prevent users from selecting start dates AFTER the end date, or vice versa.  Another request is to dynamically set the default date, and the start and end date.

Let’s start with the first requirement.  It’s fairly simple to do with the Prompt API.  Each time the start date is changed, it need to be compared against the end date.  If the start date is later than the end date, reset the value:

 var fromVal = function(values){
	var o = oControlHost.configuration
  	, ctrlFrom = o ? o[“fromPrompt”] : ‘fromDate’
  	, ctrlTo =   o ? o[“toPrompt”] : ‘toDate’
  	, ctrl = oControlHost.page.getControlByName(ctrlFrom)
  	, fromDt = moment(values[0].use).toDate()
  	, toDt =   moment(oControlHost.page.getControlByName(ctrlTo).getValues()[0].use).toDate();
//basic check to ensure the from date is before than the to date.
if(fromDt > toDt ) {
  	ctrl.setValidator(function(){});
  	ctrl.setValues(oControlHost.page.getControlByName(ctrlTo).getValues());
  	ctrl.setValidator(fromVal);         
  	return true;
  	};    
  }
  oControlHost.page.getControlByName(this.ctrlFrom).setValidator(fromVal);


Before we continue, you might be wondering what the setValidator lines are doing there.  It turns out there appears to be a small bug in the way setValues functions.  When setValues is applying a date, the date prompt first resets to the current date! This might cause an infinite loop in some cases.  So we turn off the validator, set the date, then reapply the validator. 

We’re getting the names of the prompts from the configuration.

 Then, with those values, we can call the prompt to set the validator.  In the validator, we get the from and to dates, converting to an actual date object.  We compare the two dates, and if fromDt is greater than toDt, we set the value of ctrlFrom to the same value as ctrlTo.

But let’s get more complicated with this custom Javascript in Cognos.  Now I want to dynamically set the date to yesterday, and I want to set the available range of dates to three months, one month back to one month forward.  To support this case, I decided to use an open source date library called Moment.js.  Moment simplifies selecting dates.  Want yesterday?  Try: moment().subtract(1,’days’).  End of next month?  Use: moment().add(1,’month’).endOf(‘month’).  But how can we use this?

First, we want to make sure the dynamic date only kicks in on the first run of the report.  It wouldn’t help anyone if the dates kept reverting.  So let’s use sessionStorage for that.  This code is set in the initialization part of the function.

  secondaryRun = sessionStorage.getItem(this.ctrlFrom+’secondaryRun’);
//optional dynDate from configuration
if(!secondaryRun&&o&&o[“dynStartDate”]&&moment(eval(o[“dynStartDate”])).isValid()) {
	dynStartDate=moment(eval(o[“dynStartDate”])).format(‘YYYY-MM-DD’);
  };

In this case the storage for startDate1secondaryRun hasn’t been set yet, so continue.  Does o exist? does o[“dynStartDate”] exist?  Is it a valid moment?  If all these are true, define dynStartDate to the date selected.

Next, let’s ensure that users don’t select a date outside the range selected.  We need to do this as part of the validator:

//optional minDate from configuration
if(o&&o[“minDate”]&&moment(eval(o[“minDate”])).isValid()&&fromDt < moment(eval(o[“minDate”])).toDate()) {
  	ctrl.setValidator(function(){});
  	ctrl.setValues([]);
  	ctrl.setValidator(fromVal);         
  	return true;
	}; 

Let’s set it in action.



Custom JavaScript – Datasets

And now one tweak.  There are many times where you would want to retrieve a date from a table, and use that to set the min/max range or the default dates.  In order to do this we need to add support for data sets into the custom JavaScript. 

In the initializer, for the first run we have:

//check for datasets
   if(oControlHost.control.dataStores) datastores = oControlHost.control.dataStores;
 
	if(datastores&&datastores.length>0){
 
//if datastores have been found, let’s start looking for minDate.  First we loop through the datastores
for(var i=0;i<datastores.length;++i){
    	//then we loop through the columns
    	for(var j=0;j<datastores[i].columnNames.length;++j){
      	//test for dynDate
      	if(datastores[i].columnNames[j]==’dynStartDate’) dynStartDate = datastores[i].columnValues[j][0].substring(0,10);
      	if(datastores[i].columnNames[j]==’dynEndDate’) dynEndDate = datastores[i].columnValues[j][0].substring(0,10);
    	}
  	}
 
	};
 
  if(!secondaryRun&&dynStartDate) oControlHost.page.getControlByName(this.ctrlFrom).setValues([]);
  if(!secondaryRun&&dynEndDate)   oControlHost.page.getControlByName(this.ctrlTo).setValues([]);

And the validator now contains:

    var o = oControlHost.configuration
      , ctrlFrom = o ? o[“fromPrompt”] : ‘fromDate’
      , ctrlTo =   o ? o[“toPrompt”] : ‘toDate’
      , ctrl = oControlHost.page.getControlByName(ctrlFrom)
      , fromDt = moment(values[0].use).toDate()
      , toDt =   moment(oControlHost.page.getControlByName(ctrlTo).getValues()[0].use).toDate()
      , datastores =[]
      , minDate;
//check for datasets
if(oControlHost.control.dataStores) {
	datastores = oControlHost.control.dataStores;
 
 
  	if(datastores&&datastores.length>0){
 
//if datastores have been found, let’s start looking for minDate.  First we loop through the datastores
	for(var i=0;i<datastores.length;++i){
      	//then we loop through the columns
      	for(var j=0;j<datastores[i].columnNames.length;++j){
        	//test each column for minDate, and if we find it populate minDate
        	if(datastores[i].columnNames[j]==’minDate’) {
          	minDate = datastores[i].columnValues[j][0].substring(0,10);
              if(fromDt < moment(minDate).toDate()) {
            	ctrl.setValidator(function(){});
            	ctrl.setValues([]);
            	ctrl.setValidator(fromVal);         
          	}
        	}
      	}
    	}
  	}
	};

When the control loads, it receives the information from the query in the form of a JavaScript object.  We have an array called dataStores held inside the control.  The dataStore contains an array of datasets returned from the query.  Each dataset can be interrogated for number of columns, field names, data types.  We can call individual cells and get the value, or even the formatted value.  The datasets can also be sorted in various ways.  In this case, we’re looking for a specific value called “minDate”

First, the queries.  To make this as flexible as possible, I added support for multiple queries The script loops through the datasets looking for four values.

  1. dynStartDate

  2. dynEndDate

  3. minDate

  4. maxDate

They can be coming from different queries, or a single.  They’re all completely optional.  In this case, we have two queries, returning a dynamic start date, min date, and a max date.  dynEndDate doesn’t appear, so the prompt will default to today.

The queries are associated to the prompt by selecting the control, and clicking the “Add data set” button.  You can have as many as you want.

The drop zones should be familiar to anyone with experience with active reports.  The dates themselves need to be defined with no aggregation, which makes things a little difficult.  In this example, the expression used is: 

minimum([Sales (query)].[Time].[Date]) for the minDate, and minimum([Sales (query)].[Time].[Date])+1 month for the dynStartDate. 

As you can see, it works beautifully.

The datastore object has quite a few functions available, with more to come with later versions.  For now, the custom JavaScript loops through each datastore, then loops through the columns looking for the specified name.  Once it finds it, it then applies the value as needed. 

Setting the default values

And preventing movement to before/after the specified dates.

Developers using this will need some instruction on using this – the names used are hardcoded in the JavaScript.  In the future, they may provide a way of having the required and optional parameters automatically appear.

To use this technique, change the file type of FromToDateValidation.txt and moment.txt to .js, and save both under webcontents/javascript.  The control should reference /javascript/FromToDateValidation.js

This technique simply prevents users from selecting invalid date ranges, but it doesn’t give any other notifications to the user.  In the next article, we’ll expand to include different ways of letting users know exactly why the date range prompts are not doing what they want.

You can view the report and js files here: Blog: JavaScript - files (342 downloads)

Conclusion

I hope you find this closer look at custom JavaScript in Cognos helpful.  If you haven’t already, be sure to check out my other articles on custom JavaScript to get more tips.  Subscribe to our e-newsletter for more technical articles and updates 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.