Live Excel

1. Live Excel PRESENTER: Brad Leupen | CTO, Entrinsik | 10-11-2010

2. What is Live Excel? • Live Excel Spreadsheets maintains a “live” connection to the report even after the browser window is closed • Data may be refreshed periodically, updating the spreadsheet cells and charts • Spreadsheet may be enhanced with charts and formulas and distributed to others without losing this connection • Changes to the report on the server will be reflected in the spreadsheet the next time it is refreshed

3. Why use Live Excel? • Great for management who are comfortable working in Excel • Excellent tool for driving mail merge documents and email campaigns • Great way to link Informer data with Microsoft components

4. Creating a Live Excel document • Export button on report details or report output • User must have the “Create a Live Excel Spreadsheet” permission

6. The I-Query File • Informer doesn’t actually create a spreadsheet, it creates an “I-Query” file that contains a URL to the Informer report • Excel interprets the .iqy file as a new Data Connection

7. Web Query Security • Informer embeds an encrypted “token” in the iqy report parameters • The token indentifies the report to run and the user to run the report as • Therefore, each live excel spreadsheet is bound to a specific user/report • Excel does not prompt for Informer username and password • Insecure • Unpredictable output • You should control access to Live Excel spreadsheets on your network just as you would regular spreadsheets with the same information

8. Opening in Excel • To open in new spreadsheet: double click the .iqy file • To add to existing spreadsheet: Data Tab -> Connections -> Add… -> Browse for More…

9. Refreshing Web Query Data • Data -> Refresh [All] • Properties • Refresh every n minutes • Refresh on opening • New report columns will appear in Live Excel output

10. Runtime Parameters • To view runtime parameter settings, click Data -> Connections -> Live Excel Connection -> Properties -> Definition -> Parameters… (whew!) • Excel will prompt for runtime parameters by default • Enter “*” to use as a wildcard • Parameter values may be re-used automatically • Prompts may be replaced by cells in the worksheet • Make sure all parameter cells are formatted as “Text”! • Runtime parameter cells can be set to refresh the spreadsheet automatically

11. Runtime Parameters Cont’d

12. Editing Connection Properties • Data -> Connections -> Properties • Edit query to change the URL (e.g. If your Informer URL changes) • Parameters to change runtime parameter defaults

13. Formulas • You can create formulas that work off live data the same way you would static data • Properties -> Fill down formulas in columns adjacent to data

14. Formulas Cont’d

15. Pivot Tables • Use Pivot Tables to summarize data and perform drill downs

16. Pivot Tables Cont’d • Tip: Use a dynamic named range to handle new columns and variable row count: • =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A), COUNTA(Sheet1!$1:$1)) • Drag and drop fields into the Row Labels / Column Labels / Values buckets to change the table. • Use “Value Field Settings” to change the aggregate function on Value fields (e.g. Sum, Count, Avg) • Use the right-click -> group feature to group date fields into month, quarter, yearly intervals

17. Pivot Charts • Similar to Pivot Tables but allow you to view the summary data in chart form

18. Mail Merge

19. Thank you! Any questions?