1 / 49

T I P S & T R I C K S

T I P S & T R I C K S. A Brio Intelligence ™ Developer’s JavaScript toolkit. David Eastwood, Director Maddox Ford Ltd www.maddoxford.co.uk. Overview.

akiva
Download Presentation

T I P S & T R I C K S

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related