Feature Tutorial 1:
1A. User Selected Dates - Hyperion with Nortel/Sybase
(Notes provided for T@W Date and Unix Time fields)
Our first tutorial will focus on building
user-selected date ranges for Hyperion Dashboards for Symposium/Sybase
reports. We will create drop-down date selectors and add document
scripts that will allow the user to select their own date range, as well
as automatically setting the date range to yesterday. Setting the date
range to yesterday allows the document to be used for both broadcast, as
well as on-demand. Even if the document is being used only demand, it
simply looks more professional if the date range is configured when the
user opens the document.
To economize on time, and because you are almost certainly querying other database platforms as well, we will include the corresponding syntax for Oracle Dates and Unix time as well. We will also include an appendix that shows how pre-set the date range for any particular interval.
We'll begin by building our query as usual. For our example query, we will use the Dskillsetstat table from Nortel/Symposium. Leave the query name as "Query". Once you have added the required items to the request line, drag the Timestamp field to the limit line. Select Custom SQL so that the it reads only Dskillsetstat.Timestamp and then click OK. Do not add anything at this point.
Next, insert a new dashboard into the document. Leave it's name as 'Dashboard' as that is the name that will be used in our JavaScript samples as we proceed. On the dashboard, insert three drop down boxes and two command buttons. Name one command button "Pre-Process Script" and the second "On Start Up Script". We are adding the command buttons as we will be adding both "On Start Up" as well as "On Pre-Process" event trigger scripts to the document. In order to test our scripts we will first put them into the command buttons before actually adding them to the document scripts.
The three drop-down boxes should be named "drp_startmonth", "drp_startdate", and "drp_startyear". These, of course, will be the drop downs the user will use to select the start date. While their are plenty of methods for populating the drop downs, we are going to keep this simple and simply populate them ourselves. The months will begin with January and end with December. The dates will go form 1 to 31 and the year will be 2001 until 2011 (or whatever year you like).
Firstly, it is important to populate them in sequence (January, February, March, etc.... as our java script will actually be using the relative position of the item (known as the item "index") to determine which item to the set the drop downs to. In our case, we will be using the "On Start Up" script to set the drop down dates to yesterday. Secondly, as you might have guessed, once you have populated the drop downs simply copy and paste them onto the dashboard. Re-name the pasted drop downs as "drp_endmonth", "drp_enddate", and "drp_endyear". You should now have six populated drop-down boxes the for user to select both the start an end date.
Now it is time to to test our "On Start Up" script. Our script will be used to automatically set the drop down values to yesterday. As mentioned above, this document will work for broadcast as well as on-demand. Just be sure to enable "All Clients" for for the event triggers when you add the scripts into the document.
We will now paste the following script into the "On Start Up" command button:
//on start up
var yesterday = new Date()
yesterday.setDate(yesterday.getDate()-1)
ActiveDocument.Sections["Dashboard"].Shapes["drp_startmonth"].Select(yesterday.getMonth()+1)
ActiveDocument.Sections["Dashboard"].Shapes["drp_startdate"].Select(yesterday.getDate())
ActiveDocument.Sections["Dashboard"].Shapes["drp_startyear"].Select(yesterday.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);
Now, click on the command button and the dates should set to yesterdays date.
While we will discuss the JavaScript behind the next section ("On Pre-Process") in detail, it is assumed that you have some working knowledge of JavaScript and the above should be self-explanatory. While you don't need to understand JavaScript in order to use it, if you are uncertain as to what the above is doing, please consult the BI JavaScript forum for details.
Assuming your test was successful, it is time to add out pre-process script.
Begin by pasting the script below into your "On Pre-Process" command button. This section consists basically of four parts. The first simply sets the the bqLimitOperator type. The second and third, which set the start date and end date, simply creates a variable to hold the selected index number (month, date, year) the user has chosen from the drop down boxes and then uses then uses these variables to create a second set of variables that convert the selected index into the selected item.
In other words, what the user selected from our drop downs was simply a set of index numbers (as mentioned above). If they selected September 1, 2007 to September 9, 2007, what they really selected was 8,1,7 and 8,9,7 (months begin with 0 for January). This is of no use, of course, in setting the Sybase date field as it requires the actual name of the month, along with the actual date and year.
So the "itemselected_ " variables we create below takes the index number the user selected and uses this to pass the actual item value of the index (I.e. "September"), the selected item, which is then passed to our Timestamp limit in the fourth section.
Again, you don't need to understand JavaScript in order to use it effectively. But the more you do understand, the more you will be able to adapt existing JavaScript to your needs, as well as create your own.
//set the operator
ActiveDocument.Sections["Query"].Limits["Timestamp"].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);
//set the timestamp to the selected date range
var SQLString = "Dskillsetstat.Timestamp between '" +
itemselected_startmonth + " " +itemselected_startdate + ", " +
itemselected_startyear + "' and '" + itemselected_endmonth + " " +
itemselected_enddate + ", " + itemselected_eyear + "'"
;;
ActiveDocument.Sections["Query"].Limits["Timestamp"].CustomSQL =
SQLString;
To test our pre-process script, select a date range using the drop downs you created and then click the "On Pre-Process" button. Now, go the Query section and double click the Timestamp limit. If your test was successful, the dates should be the dates you selected. If you selected September 1, 2007 as your start and September 9, 2007 as your end date it should look thus:
Dskillsetstat.Timestamp between 'September 1, 2007' and 'September 9, 2007
Oracle Date and Unix Time
The only difference required for an Oracle Database date (such as used in T@W) is the '//set timestamp to selected date range' section.
For a date field (using an imaginary field of Sale.SoldDate):
//set the date to the selected date range
var SQLString = "Sale.SoldDate >= to_date('" + itemselected_startmonth + "/" + itemselected_startdate + "/" + itemselected_startyear + " 00:00:00 ','mm/dd/yyyy hh24:mi:ss') and Sale.SoldDate <= to_date('" + itemselected_endmonth + "/" + itemselected_enddate + "/" + itemselected_eyear + " 23:59:59','mm/dd/yyyy hh24:mi:ss')" ;
ActiveDocument.Sections["Query"].Limits["SoldDate"].CustomSQL =
SQLString;
So that the final outcome on your date limit looks like thus:
Sale.SoldDate >= to_date('September/1/2007 00:00:00 ','mm/dd/yyyy hh24:mi:ss') and Sale.SoldDate <= to_date('September/9/2007 23:59:59','mm/dd/yyyy hh24:mi:ss')
For a UNIX date field (using an imaginary field of Call.TalkDate):
//set the date to the selected UNIX date range
var SQLString = "trunc(To_date('01/01/1970','MM/dd/yyyy')+((Call.TalkDate+3600)/86400))>= to_date('" + itemselected_startmonth + "/" + itemselected_startdate + "/" + itemselected_startyear + " 00:00:00 ','mm/dd/yyyy hh24:mi:ss') and trunc(To_date('01/01/1970','MM/dd/yyyy')+((Call.TalkDate+3600)/86400)) <= to_date('" + itemselected_endmonth + "/" + itemselected_enddate + "/" + itemselected_eyear + " 23:59:59','mm/dd/yyyy hh24:mi:ss')" ;
ActiveDocument.Sections["Query"].Limits["TalkDate"].CustomSQL =
SQLString;
Putting it All Together
Finally, should you require all three in a single document, only the first last sections of the On Pre Process Event Trigger require iteration. Thus, the event would simply be:
//set the operators
ActiveDocument.Sections["Query"].Limits["Timestamp"].Operator=
bqLimitOperatorCustomSQL;
ActiveDocument.Sections["Query2"].Limits["SaleDate"].Operator=
bqLimitOperatorCustomSQL;
ActiveDocument.Sections["Query3"].Limits["TalkDate"].Operator=
bqLimitOperatorCustomSQL;
//set the start dates
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 dates
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);
//set the timestamp date and UNIX date to the selected date range
var SQLString = "Dskillsetstat.Timestamp between '" +
itemselected_startmonth + " " +itemselected_startdate + ", " +
itemselected_startyear + "' and '" + itemselected_endmonth + " " +
itemselected_enddate + ", " + itemselected_eyear + "'"
;;
ActiveDocument.Sections["Query"].Limits["Timestamp"].CustomSQL =
SQLString;
var SQLString = "Sale.SoldDate >= to_date('" + itemselected_startmonth + "/" + itemselected_startdate + "/" + itemselected_startyear + " 00:00:00 ','mm/dd/yyyy hh24:mi:ss') and Sale.SoldDate <= to_date('" + itemselected_endmonth + "/" + itemselected_enddate + "/" + itemselected_eyear + " 23:59:59','mm/dd/yyyy hh24:mi:ss')" ;
ActiveDocument.Sections["Query"].Limits["SoldDate"].CustomSQL =
SQLString;
var SQLString = "trunc(To_date('01/01/1970','MM/dd/yyyy')+((Call.TalkDate+3600)/86400))>= to_date('" + itemselected_startmonth + "/" + itemselected_startdate + "/" + itemselected_startyear + " 00:00:00 ','mm/dd/yyyy hh24:mi:ss') and trunc(To_date('01/01/1970','MM/dd/yyyy')+((Call.TalkDate+3600)/86400)) <= to_date('" + itemselected_endmonth + "/" + itemselected_enddate + "/" + itemselected_eyear + " 23:59:59','mm/dd/yyyy hh24:mi:ss')" ;
ActiveDocument.Sections["Query"].Limits["TalkDate"].CustomSQL =
SQLString;
Please note that it is perfectly fine to have all three variables with the same name, "SQLString", although it is both better practice and more easily managed to give them separate names.
Finally, having built and tested our scripts, we go to file>Document Scripts and paste our scripts into the appropriate Event Trigger category, remembering to enable the scripts for 'All Clients' as shown below.
We now have our document!
Appendix: Setting Start Up Date for Different Ranges
You can easily configure your start up script to accommodate other date ranges:
//Set start up date for range of last
seven days
//Simply add first section below to
the start up script using whatever date range you want to configure for
var lastseven = new Date()
lastseven.setDate(lastweek.getDate()-7)
var yesterday = new Date()
yesterday.setDate(yesterday.getDate()-1)
ActiveDocument.Sections["Dashboard"].Shapes["drp_startmonth"].Select(lastseven.getMonth()+1)
ActiveDocument.Sections["Dashboard"].Shapes["drp_startdate"].Select(lastseven.getDate())
ActiveDocument.Sections["Dashboard"].Shapes["drp_startyear"].Select(lastseven.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);
Questions, Feedback, or Comments?
If
you have any feedback, please contact us or
post your questions to the
BI
or
Telephony forum.
Submit
you feature article, tutorial, or case study for publication.Please see our