Creating PowerPoint Presentations from within Brio Intelligence ™ Exploiting the Automation Interface

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

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

3. 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

4. Origins of the work • Prompted by the needs of a Category Management application • End users worked with complex PowerPoint 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

5. What do the applications look like?

6. The PowerPoint object model * The full object model is available from MSDN – see refs. at the end † Object names used by us within Brio Application oPPT † Presentation(s) oPPTPres † Slide(s) oPPTSlide † Shape(s) OLEFormat * Object oPPTChart or oPPTTable †

7. Getting started • Create the link to PowerPointoPPT = new JOOLEObject("PowerPoint.Application"); • Create a new presentationoPPT.Visible = true; oPPTPres = oPPT.Presentations.Add(); Alert("New presentation created: \r\n"+ oPPTPres.Name) • Open an existing presentation sFile = tbFileLocation.Text; // name from text box oPPTPres = oPPT.Presentations.Open(sFile); Alert("Presentation Loaded: \r\n"+ oPPTPres.Name)

8. Slide manipulation (1) • Add a slide to the end of the slide show nSlideNums = oPPTPres.Slides.Count; oPPTSlide = oPPTPres.Slides.Add(ppLayoutTitleOnly,(nSlideNums+1)) //*** // const ppLayoutTitleOnly == 11 • Add a title (if the slide has one defined) oPPTSlide.Shapes.Title.TextFrame.TextRange.Text = "Sample title"; *** Note this syntax – reverse order to Office documentation

9. Slide manipulation (2) • Add a footer oPPTFooter = oPPTSlide.Shapes.AddTextbox(20,700,500,10,1); //*** oPPTFooter.TextFrame.TextRange = "Slide created on " + (new Date()).toUTCString(); • Format some text oPPTFooter.TextFrame.TextRange.Font.Size = 8; oPPTFooter.TextFrame.TextRange.Font.Italic = true; *** Note this syntax – reverse order to Office documentation

10. 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

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

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

13. Create the graph object oNew = oPPTSlide.Shapes.AddOLEObject(false,"",0,"",false,"", "MSGraph.Chart",350,600,150,50); // **** //AddOLEObject arguments are: //link,iconlabel,inconindex,iconfilename,displayasicon,filename,classname, //height,width,top,left oPPTChart =oNew.OLEFormat.Object; // now set the chart type oPPTChart.ChartType = xlColumnClustered ; // const xlColumnClustered == 54 oPPTChart.Application.PlotBy = xlColumns; // const xlColumns == 2 *** Note this syntax – reverse order to Office documentation

14. Moving data • Data can be exported via the clipboard • Exports from table sections can use the GetCell() method but it’s slower • We copied the data via the clipboard (and re-arranged it within the DataSheet if necessary): oPPTChart.Application.DataSheet.Columns.Clear; ActiveDocument.Sections["Pivot"].Copy(); oPPTChart.Application.DataSheet.Range("00:00").Paste;

15. Formatting • All PowerPoint format properties are available to be read and altered • As are the properties of embedded objects such as graphs: function AddDataLabels(chart){ chart.Application.Chart.ApplyDataLabels; for (var i=1; i<=chart.SeriesCollection.Count; i++){ chart.SeriesCollection.Item(i).HasDataLabels = true; chart.SeriesCollection.Item(i).DataLabels.Font.Size = 10; } }

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

17. Table data • Copy the data to the table ActiveDocument.Sections["Pivot"].Copy(); oPPTTable.Sheets.Item(1).Paste;

18. 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)

19. Identifying slides and their source • Slides have a SlideID property – a unique ID independent of slide order (and not visible to the PowerPoint 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 // PowerPoint will return caps anyway! *** Note this syntax – reverse order to Office documentation

20. 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.

21. Finding objects in a slide • When updating a slide, you will need to find the object from the Shapes collection function FindOLE(oPPTSlide){ var nShapeCount = oPPTSlide.Shapes.Count for (var n=1; n<=nShapeCount; n++){ var nShapeType= oPPTSlide.Shapes.Item(n).Type; if (nShapeType == msoEmbeddedOLEObject){ // Const msoEmbeddedOLEObject == 7 break; } } return oPPTSlide.Shapes.Item(n).OLEFormat.Object; }

22. 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

23. Coding considerations • (Brio 6.2.3) When calling PowerPoint 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]

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

25. ‘On-Line Help’ • Find the object or method • Press F1

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

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

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