Hello Cognos masters!
Is it possible to have a date prompt (not a value prompt) dynamically set the default value to follow the last date used in our cube model?
Basically we have a report that users want to run but don't want to be prompted when they first run the page. They want to report to use the most recent date (which in our case will be the last ETL date so it can't just be today's date) and render without them selecting any prompts at first.
I've found that this can be accomplished using a value prompt but can't seem to do the same thing with a date prompt.
Any help would be greatly appreciated.
I wrote something that does this a while back. http://www.pmsquare.com/main/blog/custom-javascript-in-cognos-analytics-notifications/
It handles dynamic start/end dates, as well as adding some additional features. This is designed to work with two prompts, so you'd to adapt it a little.
It's worth mentioning that even with setting the prompt control date, you still need a way to set the default values in the filter. What I like to do is to create a parameter map in the FM that returns the correct date, and reference that in the macro:
[Namespace].[Date Table].[Date] = #prompt('ReportDate','date',sq($latestdateLookup{1}))#
Create a new query subject in FM that pulls maximum([date]) measure is not null, add a static 1 column, and reference those when building the parameter map.
I had the same requirement and using a couple of blog posts by Paul I solved this problem by doing the following with just 1 date prompt (i'm not sure if I did everything right but it works):
1.- Created a Prompt Page
1.1.- Added a Date Prompt to the prompt page and make sure that the parameter filters your query and that it's optional
1.2.- Added a Custom Control right next to the Date Prompt
1.2.1.- Created a query, added an SQL object to the query and added the SQL code to bring MAX(date) from DB as MaxDate
1.2.2.- Created a Data Set for the Custom Control
1.2.3.- Added a Data Item to the data set query pointing to MaxDate from the previously created query (maybe step 1.2.1 can be made directly on this query but i haven't tested it)
1.3.- Added Finish prompt button from the buttons that appear at the bottom and deleted the other buttons
1.4.- setted module path on custom control to a .js file with the following content (PromptDate is the name of my prompt):
define(["/bi/CustomJavaScript/moment.js", "jquery"], function() {
"use strict";
function SeteaFechaInicial(){};
SeteaFechaInicial.prototype.initialize = function( oControlHost, fnDoneInitializing )
{
var o = oControlHost.configuration
, moment=require('/bi/CustomJavaScript/moment.js')
, secondaryRun
, dynEndDate
, datastores
, ctrlDateStock = o ? o["PromptDate"] : "PromptDate"
, ctrl = oControlHost.page.getControlByName(ctrlDateStock);
//optional dynDate from configuration
if(!secondaryRun&&o&&o["dynEndDate"]&&moment(eval(o["dynEndDate"])).isValid()) {
dynEndDate=moment(eval(o["dynEndDate"])).format('YYYY-MM-DD');
};
//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]=='MaxDate') dynEndDate = datastores[i].columnValues[j][0].substring(0,10);
}
}
if(!secondaryRun&&dynEndDate)
{
//this sets the MaxDate to the Date Prompt
oControlHost.page.getControlByName(ctrlDateStock).setValues([{'use':dynEndDate}]);
}
sessionStorage.setItem(this.ctrlDateStock+'secondaryRun',1);
//this "clicks" on the Finish prompt button
oControlHost.finish();
};
fnDoneInitializing();
};
return SeteaFechaInicial;
});
1.4.1.- The moment.js file can be found on Paul's blog and most of the javascript code it's taken directly from his examples
Hope it helps!