Creating Power Point Presentations from within Brio Intelligence

Creating Power. Point Presentations from within Brio Intelligence™ Exploiting the Automation Interface David Eastwood Maddox Ford Ltd. www. maddoxford. co. uk

Overview • Work arose out of a need to create and update complex Power. Point® presentations from Brio Intelligence™ • This presentation makes use of Brio Intelligence’s Java. Script capabilities and the Automation interface • Key samples of code are shown and described • Although centred on controlling Power. Point presentations, the ideas are applicable to other Office products 2

Software Versions • The code featured has been developed under Brio 6. 2. 3 (and earlier) • It works with both Designer and Insight • The software has been tested on Office 97, Office 2000 3

Origins of the work • Prompted by the needs of a Category Management application • End users worked with complex Power. Point presentations which used Brio queries as the source of data • There was a need to: – update existing slides for different time periods and/or customers – provide a simple control panel to create and format the slides as well as compose the data queries – some graph types needed were not available in Brio – Radar, Bubble 4

What do the applications look like? 5

The Power. Point object model Application o. PPT † Presentation(s) o. PPTPres † Slide(s) o. PPTSlide † Shape(s) OLEFormat * o. PPTChart or o. PPTTable † * The full object model is available from MSDN – see refs. at the end † Object names used by us within Brio 6 Object

Getting started • Create the link to Power. Point o. PPT = new JOOLEObject("Power. Point. Application"); • Create a new presentation o. PPT. Visible = true; o. PPTPres = o. PPT. Presentations. Add(); Alert("New presentation created: rn"+ o. PPTPres. Name) • Open an existing presentation s. File = o. PPTPres tb. File. Location. Text; // name from text box = o. PPT. Presentations. Open(s. File); Alert("Presentation Loaded: rn"+ o. PPTPres. Name) 7

Slide manipulation (1) • Add a slide to the end of the slide show n. Slide. Nums = o. PPTPres. Slides. Count; o. PPTSlide = o. PPTPres. Slides. Add(pp. Layout. Title. Only, (n. Slide. Nums+1)) //*** // const pp. Layout. Title. Only == 11 • Add a title (if the slide has one defined) o. PPTSlide. Shapes. Title. Text. Frame. Text. Range. Text = "Sample title"; *** Note this syntax – reverse order to Office documentation 8

Slide manipulation (2) • Add a footer o. PPTFooter = o. PPTSlide. Shapes. Add. Textbox(20, 700, 500, 1); //*** o. PPTFooter. Text. Frame. Text. Range = "Slide created on " + (new Date()). to. UTCString(); • Format some text o. PPTFooter. Text. Frame. Text. Range. Font. Size = 8; o. PPTFooter. Text. Frame. Text. Range. Font. Italic = true; *** Note this syntax – reverse order to Office documentation 9

Objects in slides • Charts and tables are embedded objects on a slide • They form part of the Shapes collection • The OLEFormat object contains the methods and properties of embedded OLE objects 10

Graph creation (1) • We chose to use Microsoft Graph – you could use embedded Excel graphs instead • Equivalent to the Insert/Chart menu in Power. Point 11

Graph creation (2) • Graph has an attached datasheet whose layout determines the look of the graph 12

Create the graph object o. New = o. PPTSlide. Shapes. Add. OLEObject(false, "", 0, "", false, "", "MSGraph. Chart", 350, 600, 150, 50); // **** //Add. OLEObject arguments are: //link, iconlabel, inconindex, iconfilename, displayasicon, filename, classname, //height, width, top, left o. PPTChart =o. New. OLEFormat. Object; // now set the chart type o. PPTChart. Type = xl. Column. Clustered ; // const xl. Column. Clustered == 54 o. PPTChart. Application. Plot. By = xl. Columns; // const xl. Columns == 2 *** Note this syntax – reverse order to Office documentation 13

Moving data • Data can be exported via the clipboard • Exports from table sections can use the Get. Cell() method but it’s slower • We copied the data via the clipboard (and rearranged it within the Data. Sheet if necessary): o. PPTChart. Application. Data. Sheet. Columns. Clear; Active. Document. Sections["Pivot"]. Copy(); o. PPTChart. Application. Data. Sheet. Range("00: 00"). Paste; 14

Formatting • All Power. Point format properties are available to be read and altered • As are the properties of embedded objects such as graphs: function Add. Data. Labels(chart){ chart. Application. Chart. Apply. Data. Labels; for (var i=1; i<=chart. Series. Collection. Count; i++){ chart. Series. Collection. Item(i). Has. Data. Labels = true; chart. Series. Collection. Item(i). Data. Labels. Font. Size = 10; } } 15

Table creation • We chose to create embedded spreadsheets. • You can also use embedded Word Tables and (in Power. Point 2000) native Power. Point tables (members of the Shapes collection) o. New = o. PPTSlide. Shapes. Add. OLEObject(false, "", 0, "", false, "", "Excel. Sheet", 350, 600, 150, 50); //*** //Add. OLEObject arguments are: // link, iconlabel, iconindex, iconfilename, displayasicon, // filename, classname, height, width, top, left o. PPTTable = o. New. OLEFormat. Object; *** Note this syntax – reverse order to Office documentation 16

Table data • Copy the data to the table Active. Document. Sections["Pivot"]. Copy(); o. PPTTable. Sheets. Item(1). Paste; 17

Updating slides • We need to identify the source of the data already on the slide: – did Brio create this slide? – which Query created this slide? – which Section was the source of the data? – what Limits were used when the query ran? • We can then reset the Brio query to match the slide and allow the user to vary these settings (e. g. time period) 18

Identifying slides and their source • Slides have a Slide. ID property – a unique ID independent of slide order (and not visible to the Power. Point user) • Slides have Tags – this lets you create your own properties for a slide Slide. Tags. Add("Value", "TAG LABEL"); //*** // the name of the Tag Label should be in caps // Power. Point will return caps anyway! *** Note this syntax – reverse order to Office documentation 19

Using Tags • We use tags to – Label the slide as being created by Brio – Show the code version used to create the slide – Name the query which created the slide – Name limits and limit values – etc. 20

Finding objects in a slide • When updating a slide, you will need to find the object from the Shapes collection function Find. OLE(o. PPTSlide){ var n. Shape. Count = o. PPTSlide. Shapes. Count for (var n=1; n<=n. Shape. Count; n++){ var n. Shape. Type= o. PPTSlide. Shapes. Item(n). Type; if (n. Shape. Type == mso. Embedded. OLEObject){ // Const mso. Embedded. OLEObject == 7 break; } } return o. PPTSlide. Shapes. Item(n). OLEFormat. Object; } 21

Live demo • I will illustrate the code we have been discussing via the demo application • This demo code available from Maddox Ford web site www. maddoxford. co. uk deastwood@maddoxford. co. uk 22

Coding considerations • (Brio 6. 2. 3) When calling Power. Point 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] 23

Development aids (1) • Use VB Script editor in Power. Point to confirm syntax of commands • You may need to load additional Help files in Power. Point • MSDN also has reference information and articles 24

‘On-Line Help’ • Find the object or method • Press F 1 25

Development aids (2) • External Java. Script editor can be helpful with large scripts 26

Development aids (3) • Create a set of constants to match those used by Office • Try to follow good Java. Script coding conventions 27

Reference Material and Acknowledgements • MSDN http: //msdn. microsoft. com/library/en-us/modcore/html/deovr. Object. Model. Guide. asp • On-line Help in Office (VB Editors) • Platypus Java. Script editor http: //www. c-point. com/pjedit. htm • ‘Java. Script – The Definitive Guide’ David Flanagan, O’Reilly • Maddox Ford Brio Development Standards deastwood@maddoxford. co. uk 28