Hyperion User Selected Date Range

Below is a quick run-down for employing user-selected date ranges, which can still be used for broadcast purposes as well.

Reqs: Dashboard named “Dashboard”, 6 dropdown controls on dashboard, and two query sections:

Dropdowns (dropdowns manually populated or use script):
drp_startmonth
drp_startdate
drp_startyear
drp_endmonth
drp_enddate
drp_endyear

Query Sections:
q_calls
q_sales
1. On Start Up document script:
//on start up date script for past seven days
//this can be used both for broadcasts as well as on demand

var lastweek = new Date()
lastweek.setDate(lastweek.getDate()-7)
var yesterday = new Date()
yesterday.setDate(yesterday.getDate()-1)

ActiveDocument.Sections["Dashboard"].Shapes["drp_startmonth"].Select(lastweek.getMonth()+1)
ActiveDocument.Sections["Dashboard"].Shapes["drp_startdate"].Select(lastweek.getDate())
ActiveDocument.Sections["Dashboard"].Shapes["drp_startyear"].Select(lastweek.getFullYear()-2000)

ActiveDocument.Sections["Dashboard"].Shapes["drp_endmonth"].Select(yesterday.getMonth()+1)
ActiveDocument.Sections["Dashboard"].Shapes["drp_enddate"].Select(yesterday.getDate());
ActiveDocument.Sections["Dashboard"].Shapes["drp_endyear"].Select(yesterday.getFullYear()-2000);

2. On Pre-Process document script:

//On Pre-Process script to set CustomSQL limit to drop down values
//q_calls is querying Symposium sysbase timestamp
//q_sales is querying an Oracle sales db where “Created Date” is the date of sale

ActiveDocument.Sections["q_calls"].Limits["Timestamp"].Operator= bqLimitOperatorCustomSQL;
ActiveDocument.Sections["q_sales"].Limits["Created Date"].Operator= bqLimitOperatorCustomSQL;

//set the start date

var startmonth = ActiveDocument.Sections["Dashboard"].Shapes["drp_startmonth"].SelectedIndex;
var itemselected_startmonth = ActiveDocument.Sections["Dashboard"].Shapes["drp_startmonth"].Item(startmonth);

var startdate = ActiveDocument.Sections["Dashboard"].Shapes["drp_startdate"].SelectedIndex;
var itemselected_startdate = ActiveDocument.Sections["Dashboard"].Shapes["drp_startdate"].Item(startdate);

var startyear = ActiveDocument.Sections["Dashboard"].Shapes["drp_startyear"].SelectedIndex;
var itemselected_startyear = ActiveDocument.Sections["Dashboard"].Shapes["drp_startyear"].Item(startyear);

//set the end date

var endmonth = ActiveDocument.Sections["Dashboard"].Shapes["drp_endmonth"].SelectedIndex;
var itemselected_endmonth = ActiveDocument.Sections["Dashboard"].Shapes["drp_endmonth"].Item(endmonth);

var enddate = ActiveDocument.Sections["Dashboard"].Shapes["drp_enddate"].SelectedIndex;
var itemselected_enddate = ActiveDocument.Sections["Dashboard"].Shapes["drp_enddate"].Item(enddate);

var endyear = ActiveDocument.Sections["Dashboard"].Shapes["drp_endyear"].SelectedIndex;
var itemselected_eyear = ActiveDocument.Sections["Dashboard"].Shapes["drp_endyear"].Item(endyear);

var SQLString = “Dagentbyskillsetstat.Timestamp between ‘” + itemselected_startmonth + ” ” +itemselected_startdate + “, ” + itemselected_startyear + “‘ and ‘” +

itemselected_endmonth + ” ” + itemselected_enddate + “, ” + itemselected_eyear + “‘”
;;

ActiveDocument.Sections["q_calls"].Limits["Timestamp"].CustomSQL = SQLString;

var Created_Date_SQLString = “Call.Created_Date >= to_date(‘” + itemselected_startmonth + “/” + itemselected_startdate + “/” + itemselected_startyear + ” 00:00:00

‘,’mm/dd/yyyy hh24:mi:ss’) and Call.Created_Date <= to_date(‘” + itemselected_endmonth + “/” + itemselected_enddate + “/” + itemselected_eyear + “ 23:59:59′,’mm/dd/yyyy hh24:mi:ss’)” ; ActiveDocument.Sections["q_sales"].Limits["Created Date"].CustomSQL = Created_Date_SQLString; So – clicking on CustomSQL Limits should display the following for the default date (last seven days). Also test with using drop downs for custom date range: //q_calls Dagentbyskillsetstat.Timestamp between ‘December 30, 2008′ and ‘January 5, 2009′ //q_sales Call.Created_Date >= to_date(‘December/30/2008 00:00:00 ‘,’mm/dd/yyyy hh24:mi:ss’) and Call.Created_Date <= to_date(‘January/5/2008 23:59:59′,’mm/dd/yyyy

hh24:mi:ss’)