Toggle Toolbar Visibility

The script can be added to a button or wherever appropriate. I used this for on-demand dashboards both to save me the steps of having to make each component viewable, as well as for advanced end-users.

if (Application.Toolbars["Standard"].Visible==false)
{
Application.Toolbars["Standard"].Visible = true;
Application.Toolbars["Formatting"].Visible = true;
Application.ShowStatusBar = true;

Application.ActiveDocument.ShowSectionTitleBar = true;

}
else
{
Application.Toolbars["Formatting"].Visible = false;
Application.Toolbars["Standard"].Visible = false;
Application.ShowStatusBar = false;

This is used is most often used in conjunction with an “On Startup” document script such as:

Application.ShowStatusBar = false;
ActiveDocument.ShowCatalog = false;
ActiveDocument.ShowSectionTitleBar = false;
Toolbars["Standard"].Visible = false;
Toolbars["Formatting"].Visible = false;
Toolbars["Sections"].Visible = false;

Set Local Results Limits via Listbox Selection

The script below is to create a limit and then add the desired limit value(s) on a local results set via a user selection of list box value(s).

The list box values can be populated statically or dynamically. For dynamic population, see related post.

This script can be added to the list box itself or to a button. Be sure to select ‘Allow Multiple Selections’ in the list box property if required.

Reqs: Listbox (lstbx_1), Results section (“Results”), the new local limit you are going to create (“My Local Limit Column” below).

//remove existing limits

ActiveDocument.Sections["Results"].Limits.RemoveAll()

//count and add the selections to the new local limit

var count_selects=lstbx_1.SelectedList.Count;

if (count_selects>0)
{
ActiveDocument.Sections["Results"].Limits.RemoveAll()

var my_limit;
var add_limit;
my_limit=ActiveDocument.Sections["Results"].Columns["My Local Limit Column"].Name;
add_limit=ActiveDocument.Sections["Results"].Limits.CreateLimit(my_limit);

for (i=1; i<=count_selects; i++)
{

add_limit.SelectedValues.Add(lstbx_1.SelectedList.Item(i));

}

add_limit.Operator=bqLimitOperatorEqual;

ActiveDocument.Sections["Results"].Limits.Add(add_limit)

ActiveDocument.Sections["Results"].Recalculate()

}

Results Section: Convert Decimal to Time Format

This will cover converting decimal format to time format in the result section of a Hyperion document. These results can then be used in both the report and pivot sections. There are several options which we will cover.

While decimal format is preferred for end-users who want to export a section to Excel and ‘play’ with the data, the ability to convert decimal to time format is an essential ingredient in producing quality reports.

Time format (I.e. HH:MM:SS) is often requested by end users for ease of interpretation (quickly – how long is 4.61 minutes?). Additionally, it is often viewed as a more professional method of presentation in terms of appearance. This is particularly true in ‘read-only’ reports such as those exported to PDF or HTML, as well as enterprise level reports such as KPIs.

For our tutorial we will use a Symposium based example. Please note that the tutorial can be applied to any database you happen to be querying with Hyperion. The only caveat is that the formulas used below are based on results that are in seconds (as are Symposium Sybase tables). So, if your results appear in some other increment, you can either convert your results to seconds and use the formulas below or simply adjust the formulae to whatever increment you happen to be using.

We will simply be creating a Calculated Item in the result section. For our first example, we will name this calculated column “Agent ACW”.

The simplest method to implement this is when the value you wish to convert a single value. In this case we are looking at total Post Call Processing Time (postcallprocessingtime) from Symposium per Agent.

Our sample uses the dagentbyskillsetstat table from Symposium. Again, this will work equally well with any database you are querying, provided the increments are in seconds or you convert them to seconds prior to executing the calculated item we will be creating with our formulae. For minutes, etc.. adjust accordingly.

Lets begin with the formula itself.

As you can see below, we are simply using the value of Postcallprocessingtime for every break in the Agents name (break value). Please note that we have concatenated the agent’s first and last name into a field “Agent”.

Simply right click on the result section, chose ‘Calculated Item’, give it the name “Agent ACW” (or whatever you want to call it) and paste in the formula below.

Decode ( Length (Floor ( Floor ( (Sum( Postcallprocessingtime, Agent )) / 60) / 60) ) ,1, “0″ + Floor ( Floor ((Sum( Postcallprocessingtime, Agent ))/ 60) / 60), Floor ( Floor ( (Sum( Postcallprocessingtime, Agent )) / 60) / 60)) + “:” + Decode ( Length (Mod ( Floor ((Sum( Postcallprocessingtime, Agent ))/ 60), 60)), 1, “0″ + Mod ( Floor ((Sum( Postcallprocessingtime, Agent ))/ 60), 60), Mod ( Floor ((Sum( Postcallprocessingtime, Agent )) / 60), 60)) + “:” + Decode ( Length ( Mod ( (Sum( Postcallprocessingtime, Agent )), 60 )), 1, “0″ + Mod ( (Sum( Postcallprocessingtime, Agent )), 60 ), Mod ( (Sum( Postcallprocessingtime, Agent )), 60 ))

Despite it’s length and the number of nesting levels, all that is really required to employ this formula is to substitute whatever break value you like in place of (Sum( Postcallprocessingtime, Agent ).

We need to use break values for this we want the result column to return a single value for each agent.

This also highlights a limitation of this method. While the conversion makes for easier interpretation by the report recipient, the calculated column can not be used for subsequent calculations, for those, you will need to refer back to the original decimal fields (I.e. Postcallprocessingtime).


Let’s say we need a complex metric, such as Average Handle Time (AHT). Defining AHT as (Talktime + Postcallprocessingtime)/ ( Calllsanswered ) .

Our first option would be to begin with a calculated item of the break totals such as: ( Sum ( Talktime, Agent ) + Sum ( Postcallprocessingtime, Agent ) ) / Sum ( Calllsanswered, Agent ). We could name this calculated item “AHTCALC” and then simply replace Sum( Postcallprocessingtime, Agent ) in the top formulation with Sum( AHTCALC, Agent ). This would then give us

Decode ( Length (Floor ( Floor ( (Sum( AHTCALC, Agent )) / 60) / 60) ) ,1, “0″ + Floor ( Floor ((Sum( AHTCALC, Agent ))/ 60) / 60), Floor ( Floor ( (Sum( AHTCALC, Agent )) / 60) / 60)) + “:” + Decode ( Length (Mod ( Floor ((Sum( AHTCALC, Agent ))/ 60), 60)), 1, “0″ + Mod ( Floor ((Sum( AHTCALC, Agent ))/ 60), 60), Mod ( Floor ((Sum( AHTCALC, Agent )) / 60), 60)) + “:” + Decode ( Length ( Mod ( (Sum( AHTCALC, Agent )), 60 )), 1, “0″ + Mod ( (Sum( AHTCALC, Agent )), 60 ), Mod ( (Sum( AHTCALC, Agent )), 60 ))

You may find this method easier to track and manage. On larger result sets, it will also process more quickly. However, we could maintain a single calculated item as well by replacing (Sum( AHTCALC, Agent ) with the our formula:( Sum ( Talktime, Agent ) + Sum ( Postcallprocessingtime, Agent ) ) / Sum ( Calllsanswered, Agent ), giving us:

Decode ( Length (Floor ( Floor ( (Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) / 60) / 60) ) ,1, “0″ + Floor ( Floor ( (Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) / 60) / 60), Floor ( Floor ( (Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) / 60) / 60)) + “:” + Decode ( Length (Mod ( Floor ((Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) / 60), 60)), 1, “0″ + Mod ( Floor ((Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) / 60), 60), Mod ( Floor ((Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) / 60), 60)) + “:” + Decode ( Length ( Mod ( (Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) , 60 )), 1, “0″ + Mod ( (Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) , 60 ), Mod ( (Sum ( Talktime, Agent ) + Sum(Postcallprocessingtime ,Agent) ) / Sum(Callsanswered, Agent ) , 60 ))

This obviously gives us a cleaner, single computed item.

Finally, we are ready to add the item top our report or pivot section. In doing so, we should select Data Function>Maximum or Data Function>Minimum. We do as all values for each column break will be identical. Using Average or Sum will cause an error as the report or pivot can not sum or average this computed item.

It is important to map out how you want your results to be presented, as this computation is contingent upon break totals, so you must pull your results in such a way, or organize them in such a way, that will allow you to have the required data in such a format that column break totals can be effectively and accurately added.

If the above section were using an interval table for example, such as Symposium’s iagentbyskillsetstat, you would need to arrive at a method of concatenating the agent and interval break totals. This is most simply done by creating a field of the type (Agent+Time) that would provide the needed column in order to implement the above.

Dropdown to View and Export Reports

This is a simple dashboard drop down control to view and export sections.

This allows end users to preview the selected section as well as exporting the report or section in the required format and with the desired name.

Reqs: Dashboard (“Dashboard”), drop down control (“drop_ctl1″).

This document has three report sections, Sales Report, Finance Report, and Marketing Reporting.

In the values tab of the drop down property editor I’ve added the following values:

Select Report Option
——————————
Sales Report
Finance Report
Marketing Report
——————————
Export Sales Report
Export Finance Report
Export Marketing Report

The drop down script:

//first section previews
var drop_pick;
drop_pick=drop_ctl1.Item(drop_ctl1.SelectedIndex);

if (drop_pick==”Sales Report”)
{ActiveDocument.Sections["Sales Report"].Activate();
}

if (drop_pick==”Finance Report”)

{ActiveDocument.Sections["Finance Report"].Activate();

}

if (drop_pick==”Marketing Report”)

{ActiveDocument.Sections["Marketing Report"].Activate();

}

//export section using bqExportFormatPDF and CSV
if (drop_pick==”Export Sales Report”)

{ActiveDocument.Sections["Sales Report"].ExportToStream( “Sales Report”, bqExportFormatPDF, true, true, true );

}

if (drop_pick==”Export Finance Report”)

{ActiveDocument.Sections["Finance Report"].ExportToStream( “Finance Report”, bqExportFormatPDF, true, true, true );

}

if (drop_pick==”Export Marketing Reportt”)

{ActiveDocument.Sections["Marketing Report"].ExportToStream( “Marketing Report”, bqExportFormatCSV, true, true, true );

}

//reset the drop down to default value
var MyIndex =ActiveDocument.Sections["Dashboard"].Shapes["drop_ctl1"].SelectedIndex=1
ActiveDocument.Sections["Dashboard"].Shapes["drop_ctl1"].Select(MyIndex)

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’)

BRIO 8 Stuff

I have had a BRIO/Hyperion blog at Wordpress.com for some time.

I have not added anything to it in some time, but I do still get emails.

This blog will feature BRIO scripts as well as general how-to articles.