T I P S & T R I C K S

1. T I P S & T R I C K S

2. A Brio Intelligence™ Developer’s JavaScript toolkit David Eastwood, Director Maddox Ford Ltdwww.maddoxford.co.uk

3. Overview • We were consistently needing to supply the same functionality for our Brio EIS development - often the same code was used in several places within the same application • This led to the emergence of a function toolkit which has now been used in several major projects over the last 3 years • Key samples of code are shown and described • Other techniques such as error trapping are also discussed T I P S & T R I C K S

4. Software Versions • The code featured has been developed using versions of Brio Intelligence from 6.0 to 6.6.1 • It works with both Designer and Insight (Client/Server and Plugins) • The software has been used for live applications accessing Oracle, DB2 and SQLServer T I P S & T R I C K S

5. Origins of the work • We were creating a series of Brio-based reporting applications used by relatively unskilled users to create operational reports and mid-level management reports • These included • Shift activity rates in supermarket central depots • Printing delivery sheets for delivery runs • Summarising the status of automated test results in a manufacturing plant • Workflow reports for an insurance broker • Some utilities also emerged from conventional dashboard applications T I P S & T R I C K S

6. Requirements • Access to multiple databases for one report • Multiple queries to create one report • Inactivity timeouts from the RDBMS • Creating a standardised look and feel for sets of up to 60 reports • Automated export to Excel/PowerPoint • Keep it simple! • Not too many controls and pictures • Quick to build • Start from a template and add the query/reports • Add the EIS to a report - usually about 30 minutes T I P S & T R I C K S

7. Coding and naming conventions • In order to speed up development we adhere to a fairly rigid naming convention for EIS objects - this means that scripts can be copied in and used with little or no modification • JavaScript coding follows our coding standards to aid maintainability T I P S & T R I C K S

8. Naming conventions used • Prefix name with variable type • ‘g_’ prefix for globals • Further ‘a’ prefix for function arguments T I P S & T R I C K S

9. Global functions • Utilities held as a series of global functions • g_fname = fname • Defined via a (hidden) button ‘Globals’ on the main EIS page • Easy to redefine after code changes • OnStartup event will define globals //Declare globals ActiveDocument.Sections["Selector"].Shapes["cbGlobals"].OnClick() T I P S & T R I C K S

10. Error trapping • Some utilities are error-trapped • Other checks done in code • Mostly omitted from these examples to simplify readability • Need to use .toString() method • not .name or .message T I P S & T R I C K S

11. Utilities described: • Purpose and features • Arguments used • Code sample T I P S & T R I C K S

12. Hiding the sections and toolbars • g_showDeveloperView • standard function to show/hide toolbars etc. • will also hide query sections from end-users • usually placed behind a logo to switch modes • arguments • abShow : true/false switch T I P S & T R I C K S

13. g_showDeveloperView function showDeveloperView(abShow){ var nSecNum; //Number of Sections var nSecCount; //Section count ShowCatalog = abShow; ShowMenuBar = abShow; // note sometimes omit this command ShowStatusBar = abShow; Toolbars["Sections"].Visible = abShow; Toolbars["Formatting"].Visible = abShow; Toolbars["Standard"].Visible = abShow; //Loop turning section properties on/off //This version only lets user see EIS & Report sections - adjust as required nSecNum = ActiveDocument.Sections.Count; for (nSecCount = 1; nSecCount <= nSecNum; nSecCount++){ if ( (ActiveDocument.Sections[nSecCount].Type != bqEIS) && (ActiveDocument.Sections[nSecCount].Type != bqReport) ) { ActiveDocument.Sections[nSecCount].Visible = abShow; } } //end for } //end of showDeveloperView definition T I P S & T R I C K S

14. Logon functions • The g_reconnect function is used • We also often reconnect before processing • some database connections have inactivity timeouts • also catches other database errors //Disconnect from Queries and then Reconnect - in case of timeout if( g_reconnect( "Query", g_sPkMSId, g_sPkMSPassword ) ){ ActiveDocument.Sections["Query"].Process() Console.Writeln("Query Processed") } T I P S & T R I C K S

15. g_reconnect /*------------------------------------------------------------------------------------- -- Function: reconnect -- Result: boolean true if connected, else false -- Parameters: asQuery - Section Name -- asId - Userid -- asPw - Password -- -- Disconnect and reconnect query to database ----------------------------------------------------------------------------------------*/ T I P S & T R I C K S

16. g_reconnect function reconnect( asQuery, asId, asPw) { var sOCEName = ActiveDocument. Sections[asQuery].DataModel.Connection.Filename // open OCE ActiveDocument. Sections[ asQuery ].DataModel.Connection.Open(sOCEName) //Disconnect from database ActiveDocument.Sections[ asQuery ].DataModel.Connection.Disconnect(); // Set Query connection parameters ActiveDocument.Sections[ asQuery ].DataModel.Connection.Username = asId; ActiveDocument.Sections[ asQuery ].DataModel.Connection.SetPassword( asPw ); // Log Query on to database try{ ActiveDocument.Sections[ asQuery ].DataModel.Connection.Connect(); } catch(errConnect) { Alert('Database connection error: '+errConnect.toString()) return false; } // Connected OK return true; } // End of reconnect definition T I P S & T R I C K S

17. Controlling limits • When driving a query from an EIS page, you can either enter limit values on the EIS page or use the standard Limit dialogs • We often customise Limit dialogs and allow the user to see variable limits - involves less code than setting limits on the EIS page • Limits often need to be preset with sensible defaults • On multi-query reports, we need to copy limits T I P S & T R I C K S

18. Lim_1(v1) Limit Lim_1 Lim_1 Lim_1 Lim_1 Lim_1 Limit Limit Limit Limit Limit Setting limits 1. Set values on EIS Page 1. Process Query 1 Limit values 2. Copy limit values 2. Copy variable limit values 3. Process remaining queries 3. Process T I P S & T R I C K S

19. Setting date limits • Set a limit to cover yesterday • 00:00:00 to 23:59:59 • Set a limit to cover the previous week • One sample shown, many variants for other date/time intervals T I P S & T R I C K S

20. g_setLimitByDay - example g_setLimitByDay("Query","Full Date",5) Example written on 7 October 2002 7/10/02 in UK format!! T I P S & T R I C K S

21. g_setLimitByDay (1) Usage: g_setLimitByDay("Query","Full Date",1) Syntax: /*------------------------------------------------------------------------------------- -- Function: setLimitByDay -- Parameters: asQueryName, asLimitName, adOffset -- -- Set custom limit on <asLimitName> in section <asQueryName> -- to between 00:00:00 <adOffset> days ago -- and 23:59:59 yesterday --------------------------------------------------------------------------------------*/ T I P S & T R I C K S

22. g_setLimitByDay (2) function setLimitByDay(asQueryName, asLimitName, adOffset) { var dStart = new Date() - adOffset ; var dEnd = new Date() - 1; // i.e. yesterday var oLimit = ActiveDocument.Sections[asQueryName].Limits[asLimitName]; dStart.setHours(0); dStart.setMinutes(0); dStart.setSeconds(0); dEnd.setHours(23); //End datetime is 1 second to midnight dEnd.setMinutes(59); dEnd.setSeconds(59); oLimit.Operator =bqLimitOperatorBetween oLimit.SelectedValues.RemoveAll(); oLimit.CustomValues.RemoveAll(); oLimit.CustomValues.Add(dStart); oLimit.CustomValues.Add(dEnd); oLimit.SelectedValues.Add(dStart); oLimit.SelectedValues.Add(dEnd); } T I P S & T R I C K S

23. Copying limits • Main purpose is to stop asking the user to enter the same limit twice • Apply the same limits to queries against different databases • Multiple queries against the same database • Union queries T I P S & T R I C K S

24. g_copyLimit Usage: g_copyLimit( "Query",'Create Date Time',"VRDT Query",'Create Date Time') Syntax: /*----------------------------------------------------------------------------------------- -- Function: copyLimit -- Parameters: asSourceSection, asSourceLimitName, -- asDestSection,asDestLimitName -- -- Copies the selected CUSTOM values from one limit to another -- duplicates other settings (Ignore, Operator, Nulls etc) -- NOTE we can't seem to set limit values where the limit is set on a computed column - we get an internal error ------------------------------------------------------------------------------------------*/ T I P S & T R I C K S

25. g_copylimit (1) function copyLimit(asSourceSection, asSourceLimitName, asDestSection,asDestLimitName) { var oSourceObj = ActiveDocument.Sections[asSourceSection].Limits[asSourceLimitName] var oDestObj = ActiveDocument.Sections[asDestSection].Limits[asDestLimitName] // read source limit settings var bIgnore = oSourceObj.Ignore var bIncludeNulls= oSourceObj.IncludeNulls var nNegate= oSourceObj.Negate var nOperator= oSourceObj.Operator var nSelectedValuesCount= oSourceObj.SelectedValues.Count var nSelVal //… continue next slide T I P S & T R I C K S

26. g_copylimit (2) //… continue from previous slide // set the limits on the second query if (bIgnore) { oDestObj.Ignore= true // not much point copying the rest! } else { oDestObj.Ignore=bIgnore // set Ignore state to match source oDestObj.IncludeNulls = bIncludeNulls oDestObj.Negate=nNegate oDestObj.Operator = nOperator oDestObj.SelectedValues.RemoveAll() oDestObj.CustomValues.RemoveAll() for (var i=1; i<=nSelectedValuesCount; i++) { nSelVal=(oSourceObj.SelectedValues[i]) oDestObj.CustomValues.Add(nSelVal) oDestObj.SelectedValues.Add(nSelVal) } // end for all limit values } // end else } // end function copyLimit T I P S & T R I C K S

27. Manipulation of EIS controls • Functions to fill controls on an EIS page • Limit values in a Down, List Box etc • Show available sections in a Drop Down • Functions to read values • Selection controls, i.e Radio Buttons, Drop Downs etc, should be passive - they should not run processes. • Control buttons should be used to actually run a process. T I P S & T R I C K S

28. Copying values to controls • Only a few lines of code - we don’t generally write cover functions // Populate List Box var oProdLimit = ActiveDocument.Sections["Query"].Limits[”Prods"] var nProds = oProdLimit .AvailableValues.Count; lbProductList.RemoveAll(); for (i = 1 ; i <= nProds; i++) { lbProductList.Add(oProdLimit .AvailableValues[i]); } T I P S & T R I C K S

29. Dashboard functions • Traffic lights • Formatting numbers in JavaScript • When writing data values on an EIS page, the underlying number is shown - not the formatted version seen in the source section • No formatting functions in JavaScript • Writing EIS settings to reports • Indicate values of user selection T I P S & T R I C K S

30. Sample dashboards (1) T I P S & T R I C K S

31. Sample dashboards (2) T I P S & T R I C K S

32. Sample dashboards (3) T I P S & T R I C K S

33. Traffic lights • Simple manipulation of coloured objects • Vary visible property T I P S & T R I C K S

34. g_format Usage: txtSales.Text = g_format({anumber},2) Syntax: /*---------------------------------------------------------------------------------------------------- -- Function: format -- Parameters: anum,anDP -- -- Returns text string with number formatted to <anDP> places ----------------------------------------------------------------------------------------------------*/ T I P S & T R I C K S

35. g_format function format(anum,anDP){ // round to right number of decimal places // eliminate fractional part for now anum = Math.round(anum*Math.pow(10,anDP)); anum = anum.toString() if (anDP != 0) { // need to stick a decimal place into the string anum = anum.slice(0,(anum.length - anDP)) + ' .' + anum.slice(anum.length - anDP) } return anum } // end format T I P S & T R I C K S

36. g_formatCurrency(1) Usage: txtSales.Text = g_formatCurrency({anumber},2) Syntax: /*---------------------------------------------------------------------------------------------------- -- Function: format -- Parameters: anum -- -- Returns text string with number formatted to <anDP> places -- (usually 2 or 0), with separators every third place -- with a currency symbol prefixed ---------------------------------------------------------------------------------------------------*/ T I P S & T R I C K S

37. g_formatCurrency(2) function formatCurrency(anum,anDP){ var sCurrencySymbol = '£'; // edit to suit var sSeparator = ',' ; var nPoint; var sOutput = ''; //blank // round to right number of decimal places // eliminate fractional part for now anum = Math.round(anum*Math.pow(10,anDP)); anum = anum.toString() if (anDP != 0) { // need to stick a decimal place into the string anum = anum.slice(0,(anum.length - anDP)) + '.' + anum.slice(anum.length - anDP) } // standard format function to here, now add the Separators & Currency // continue next slide T I P S & T R I C K S

38. g_formatCurrency(3) // continue from previous slide nPoint = anum.indexOf('.') if (nPoint == -1) { nPoint = anum.length } for (var i=0 ; i < nPoint; i++) { if (((nPoint - i)%3 == 0)&&( i!=0)) { sOutput += sSeparator }// end if add separator sOutput += anum.substr(i,1) }// end for sOutput += anum.substr(nPoint); // fails gracefully for 0 DP return sCurrencySymbol + sOutput } // end format T I P S & T R I C K S

39. Writing EIS settings to reports • Set a global variable • Use this in the report expression Global Variable T I P S & T R I C K S

40. Exporting to Excel(1) • Covered in depth in my paper at Brio 2001 • Simple export via Clipboard or Export • Fully automated via JOOLEObject • uses OLE Automation • some tricks needed • All Excel methods can be called T I P S & T R I C K S

41. Exporting to Excel(2) oExcel = new JOOLEObject("Excel.Application"); oExcel.Visible = true; oExcel.Workbooks.Add; ActiveDocument.Sections[”mysectionname"].Copy() oExcel.Sheets.Item(1).Paste T I P S & T R I C K S

42. JOOLEObject pitfalls • When calling methods with multiple arguments you need to supply the arguments in the reverse order to that shown in Microsoft documentation Brio: Slide.Tags.Add("Value", "TAG LABEL"); Microsoft: Slide.Tags.Add("TAG LABEL", "Value") • When selecting Items in a collection, use the .Item(n)method not [n] T I P S & T R I C K S

43. Development aids (1) • Use the Brio Console window • Don’t forget the Execution Window • Copious use of Alerts and writeln T I P S & T R I C K S

44. Development aids (2) • External JavaScript editor can be helpful with large scripts T I P S & T R I C K S

45. Development aids (3) • Get a good JavaScript reference book • Try to follow good JavaScript coding conventions • MUCH easier to read code (even your own) • Be disciplined about where you put the code T I P S & T R I C K S

46. Reference Material and Acknowledgements • ‘JavaScript – The Definitive Guide’ David Flanagan, O’Reilly • Maddox Ford Brio Development Standards Available from deastwood@maddoxford.co.uk • Platypus JavaScript editor http://www.c-point.com/pjedit.htm • Thanks to my colleagues for their input • especially Aelred Tobin T I P S & T R I C K S

47. Sample code • Sample code available at the conference • Download from Maddox Ford website • www.maddoxford.co.uk • email to David Eastwood at Maddox Ford • deastwood@maddoxford.co.uk T I P S & T R I C K S

48. The end