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 Analytics – Date Prompts and Datasets

Oil and Water JavaScript and Cognos 2Date Prompts and datasets

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.

Custom JavaScript Date Prompts and datasets 1

 

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.

Custom JavaScript Date Prompts and datasets 2

 

But let’s get more complicated.  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?

JavaScript Date Prompts and datasets 3

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([{‘use’:moment(eval(o[“minDate”])).format(‘YYYY-MM-DD’)}]);

      ctrl.setValidator(fromVal);         

      return true;

    };

 

 

Let’s see it in action.

Custom JavaScript Date Prompts and datasets 2

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([{‘use’:dynStartDate}]);

  if(!secondaryRun&&dynEndDate)   oControlHost.page.getControlByName(this.ctrlTo).setValues([{‘use’:dynEndDate}]);

 

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([{‘use’:minDate}]);

                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

 

Custom JavaScript Date Prompts and datasets 4

 

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.

Custom JavaScript Date Prompts and datasets 5

 

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

Custom JavaScript Date Prompts and datasets 6

 

And preventing movement to before/after the specified dates.

Custom JavaScript Date Prompts and datasets 7

 

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 (112 downloads)

 

Conclusion

I hope you find this closer look at custom JavaScript with date prompts and datasets helpful.  If you haven’t already, be sure to check out my previous article on custom JavaScript to get more tips.  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