Using Export to Excel with Templates

Index
Introduction
Prerequisites
One-Time Set Up Procedure
Using TECSYS Export to Excel Macros
Known Issue with Excel 2007
Troubleshooting IE 7 Usage Issues

     
  1. Introduction

    Pointforce iTopia includes the ability to export the results of a search operation from a view into a Microsoft Excel spreadsheet. While viewing the results in Excel format, you can perform a number of actions, such as:

    • View the results in a more graphical display by using Excel's graphical features;
    • Sort or filter columns;
    • Perform mathematical calculations on the results and determine averages, percentages, and so on;
    • Analyze data with pivot tables;
    • Analyze statistical tables;
    • Save the results, and then email or print them in Excel format; and,
    • Embed the spreadsheet in a Microsoft Word document.

    Please refer to the Excel product's online help system for more details.

    Macros are available for auto-execution when exporting a Pointforce iTopia search results set to Microsoft Excel. These macros generate reports from the exported data, thus taking full advantage of Excel's macro language capabilities. Examples of the types of reports that can be generated include reports with subtotals, bar graphs, pivot tables, and so on. Sample implementations are available that showcase the technology. They include the following:

    • Rep Sales by Month (ibis_rep_sales_by_month) — a view on historical rep sales statistics that generates a summary report, a pivot table, a bar graph and a pivot table.

    • Product Unit Sales (ibis_product_unit_sales) — a view on product sales that generates a summary report and a pivot table.

    • Outstanding POs (ibis_outstanding_pos) - a view on purchase history that generates a summary report that demonstrates the addition of an extended price column that was not available in the view and a pivot table.

    The value and the description for each multiple value column will display in separate columns. The value column retains the same title (i.e., the column's label), while the description column uses the column's label followed by one blank space, a dash, another blank space, and then the word Description.

    In the following example, the 'Purchase Orders Details' view (v_pooop) displays the fields as below in the view:

    Company Whse PO No Supplier Code Order Type
    10 01 000001 ANTEXP S-Stock

    When the view is Exported to Excel, the columns will display as follows with the Order Type and Order Type-Description in separate columns:

    Company Whse PO No Supplier Code Order Type Order Type-Description
    10 01 000001 HONFUR S Stock

     

  2. Prerequisites:

    To use the Export to Excel feature, the following technical requirements must be satisfied beforehand:

    • Microsoft Excel must be installed on the user's system.

    • The user must be granted the appropriate permissions to execute the Export to Excel resource.

    • The user must be granted the Export operation for the view or resource from which data is to be exported.

    In addition, the following file locations must be taken into account:

    • The Windows XP \My Documents folder is called \Documents in Windows Vista. In this document, all references to the Documents folder assume one or the other depending on the operating system used. The paths to each folder are as follows:

      • Windows XP: C:\Documents and Settings\username\My Documents
      • Windows Vista: C:\Users\username\Documents
    Note: When selecting the 'username' for either XP or Vista you may see two user names. For example 'teivel.pf' and 'teivel'. Be sure to select the user name without the .xx. If you do not select the correct user name the templates will not work.

     

  3. One-Time Set up Procedure:

    The following procedure defines the steps needed to install and use TECSYS' Visual Basic templates for use within Microsoft Excel 2003 (for installation on Microsoft Excel 2007, see the instructions below).

    Over and beyond the prerequisites listed earlier in this topic, the user must also have the appropriate security levels assigned in Excel for macros and the ActiveX components.

    To set up a PC to use the TECSYS templates and macros for Excel 2003, perform the following steps: (these steps must be repeated for each PC you wish to use)

    1. Launch the Export to Excel (meta_export_excel) resource from iTopia by entering Export to Excel in the Search field and selecting 'Go'.

    2. Click the Download hyperlink to download the Tecsys.zip file onto your local system.

    3. Open the ZIP file and extract its contents into your Windows Documents folder. This will create a folder called Tecsys\iTopia (i.e., Documents\Tecsys\iTopia), as well as three sub-folders entitled Add-In, Output, and Template.

    4. Launch Microsoft Excel and from the Tools menu, select the Add-Ins option.

    5. Click the Browse button and go to the Documents\Tecsys\iTopia\Add-In folder.

    6. Select the tecsys_iTopia.xla file and click OK. This will add the Tecsys_iTopia option to the list of Add-Ins.

    7. Ensure that the Tecsys_iTopia check box is selected, and then click OK.

    To set up a PC to use the TECSYS templates and macros for Excel 2007, perform the following step: (these steps must be repeated for each PC you wish to use)

    1. Launch the Export to Excel (meta_export_excel) resource from iTopia by entering Export to Excel in the Search field and selecting 'Go'.

    2. Click the Download hyperlink to download the Tecsys.zip file onto your local system.

    3. Open the Zip file and extract its contents into your Windows Documents folder. This will create a folder called Tecsys\iTopia. (i.e., Users\username\Documents\Tecsys\iTopia), as well as three sub-folders entitled Add-In, Output, and Template.

    4. Launch Microsoft Excel and select the Office button and then select Excel Options and then select Add-Ins.

    5. Select the Go button at the bottom of the Add-in page to the right of the 'Manage' field.

    6. When the Add-in popup displays, click the Browse button and go to the \Users\username\Documents\Tecsys\iTopia\Add-in folder.

    7. Select the tecsys_elite.xla file and click OK.

    8. Click OK again to close the Add-in popup.

    9. Select the Office button again and then select Excel Options and then select Trust Centre.

    10. Select the Trust Centre Settings button in the Microsoft Trust Centre Settings section.

    11. Select Macro Setting and then select the Enable all macros (not recommended; potentially dangerous code can run) radio button.

    12. Select the check box for Trust access to the VBA project object modules in the Developer Macro Settings.

     

  4. Using TECSYS' Export to Excel Macros:

    Now that you have installed the necessary templates and add-in, you need to create appropriate iTopia views and corresponding Excel Templates.

    TECSYS provides three resources that format search results exported from the iTopia database to the Excel format. These should be used as a starting point to generate a new template for a view.

    Note: When the install is done using the steps in the One-Time Setup Procedure section, there will be 3 templates that are automatically installed that you can remove and replace with the templates provide by Pointforce Enterprise. The 3 templates that can be removed are 'dms_cust_hist.stats.xlt', 'dms_ord_l.stats.xlt', 'meta_md_user_role.stats.xlt'. (These templates were meant for use with the Elite series of views and will NOT be useful in the Pointforce iTopia environment.)

    The following three templates will be emailed to you:

    • ibis_outstanding_pos.xlt
    • ibis_product_unit_sales.xlt
    • ibis_rep_sales_by_month.xlt

    You must copy these templates into the Documents\Tecsys\Template folder on your PC. There are currently three templates available which exist in the views used with Excel Templates: 'Rep Sales by Month', 'Product Unit Sales' and 'Outstanding POs'. All are ready to use out of the box, as corresponding Pointforce iTopia views already exist for each of them.

    The following table lists the views and templates provided by TECSYS to showcase this feature:

    Base View Export to Excel Resource Name Export to Excel View Description Excel Template Filename
    Ibis_v_pooop Ibis_outstanding_pos Outstanding POs Ibis_outstanding_pos.xlt
    Ibis_inidp Ibis_product_unit_sales Product Unit Sales Ibis_product_unit_sales.xlt
    Ibis_saah Ibis_rep_sales_by_month Rep Sales by Month Ibis_rep_sales_by_month.xlt

    Note: The Export to Excel view names are identical to the Excel template filenames, excluding the ".xlt" suffix. This is a requirement.

    To create a view to be used with the Export to Excel macros, do this:

    Note: When creating new views to be used with an Excel Template you must be logged into iTopia as the 'system' user.

    1. From within Pointforce iTopia, launch an existing generic view.

    2. Click the Personalize button from the view's Search/Results Grid page.

    3. Go to the 'Search Results' folder, and personalize the results to contain the fields you wish to export to Excel, and the order in which you wish to present them.

    4. Once you have finished personalizing the view, click the Save As button. When the Save View as page is opened you can enter any New Title name desired. In the New View Name field you should enter a short description of the view seperating each word with an underscore. This is the resource name of the view which MUST match the name of the template. The description of the view can always be overridden later via the Resource Master resource.

    To personalize an Excel template to be used with a personalized view, do this:

    1. Launch Windows Explorer.

    2. Go to the Documents\Tecsys\Template folder on your PC.

    3. Copy one of the existing templates (e.g. ibis_product_unit_sales.xlt) and save it in the same folder (i.e. Documents\Tecsys\Template).

    4. Rename the copied template to be the resource name of the personalized view (i.e. viewname), followed by the ".xlt" suffix.

    5. Launch Microsoft Excel.

    6. Open the new template (i.e. viewname.xlt).

      Note: The worksheets (i.e., Data, and Summary, PivotTable, and/or BarGraph and BarGraphDataData, as that is where the raw data from the personalized view is dumped. The other worksheets should contain links to the data in the first sheet, the formulae you wish to include, and the formatting specifications you wish to apply. Tip : Once the personalized template is created, you may want to protect or hide the Data worksheet to ensure that its content is never modified manually.

    7. Press Alt+F11 (or, if you are using Excel 2003, select Tools-Macro-Visual Basic, if you are using Excel 2007, select the Developer tab and select Visual Basic) to view the code the template contains and to customize it to your requirements, if needed. Note: This step should only be performed by a user who is experienced with using Visual Basic.

      Note For users using Excel 2007: If the Developer tab is not displayed in Excel follow these steps:

      • Select the Microsoft Office button, and then click Excel Options.

      • Click Popular, and select the Show Developer tab in the Ribbon check box.

      Note For users using Excel 2007: If you are using Excel 2007, all templates must be saved as 'Excel 97 2003 workbooks' or they will not function.

    To use the Export to Excel macros on a personalized view, do this:

    1. From within iTopia, launch a personalized view, named viewname.

    2. Perform a search in the view. The search results are displayed.

    3. Refine your search criteria and repeat step 2, if needed, and then click the Export to Excel action. The search results are exported to the corresponding Excel template, into a worksheet entitled viewname[1], and you are presented with a pop-up menu.

    4. Click the Format button on the pop-up menu. The exported results are formatted and then saved in an Excel workbook entitled viewname_report.xls. The new spreadsheet is stored in the Documents\Tecsys\Output folder. The spreadsheet is opened and displays either the Bar Graph, the Pivot Table and/or the Summary report, whichever are applicable.

      Note: If you are using Excel 2007, the new spreadsheets will display [Compatibility Mode] after the title of the workbook.

    5. While viewing the formatted output, either click the Summary tab to view the subtotals, the PivotTable tab to perform a data analysis, or the BarGraph tab.

    6. Click the File-Save option to save your changes.

    If you wish to allow others access to new templates the apporopriate permissions must be applied for the view to either specific users or roles that have been assigned to users.

     

  5. Known Issue Using Excel 2007

    When performing Export with Excel using Excel 2007, the following additional message displays: "The file you are trying to open is in a different format then specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file. Yes/No/Help."

    Click Yes and the file opens normally.

     

  6. Troubleshooting Internet Explorer 7.0 Usage Issues

    This section provides information on known issues regarding the usage of iTopia views on Internet Explorer (IE) 7.0.

    • We also recommend that you refer to the Compatibility Requirements document for general guidelines on security settings and enabling activeX settings.

    1. Issues and Changed Behaviour:

      The following problems have been encountered when Pointforce iTopia is launched using IE 7.0:

      • Security Certificate Warning

        • A problem occurs when a Web site is running in SSL mode and the security certificate is not valid. Microsoft has changed the security notification message from a pop-up to a page that is displayed. After you click "Continue to this website (not recommended)", the address bar appears in red.

          This page will not appear in SSL when the certificate is valid. Customers must purchase a certificate to avoid this problem.

      • Hot Key

        • The Alt+D hot key combination is no longer trapped. The event that traps the hot keys is ignored for the Alt+D combination. The solution to this problem is to change the hot key definition to use another letter that does not present this problem.

      • Pointforce Web Client

        All resources running under Pointforce iTopia Web Client must open in a new window. To enable this behaviour, do this:

        1. From Internet Explorer, on the Tools menu select Internet Options.

        2. Select the General tab.

        3. Click the Settings button from within the Tabs section.

        4. Ensure that the Always switch to new tabs when they are created checkbox is selected.

        5. Ensure that the Let Internet Explorer decide how pop-ups should open radio button is enabled.

        6. Click OK to accept your changes.

        7. Click OK a second time to clear the Internet Options dialog from the screen.

      • Zoom

        The Zoom feature is not supported.

    2. Known Problems with Windows Vista:

      • Using Export with Excel

        When using Windows Vista, the Export With Excel feature launches Excel based on the Security Zone for which the Web page is configured. This has the following impact on the product:

        • When your Web page is configured for the Local Intranet security zone, the Export With Excel feature launches in a separate Microsoft Excel window. In this case, you can simply close the spreadsheet once you are finished and return to the iTopia view to resume your work.

        • When your Web page is configured for the Trusted Sites security zone, the Export With Excel feature launches in the same Internet Explorer window as the current view and Microsoft Excel is embedded within. In this scenario, you must click the browser window's Back button to return to the view from which the Export With Excel feature was launched; otherwise, if you simply close the spreadsheet, the browser window housing the view will also be closed.

    3. Known Problems with Third-Party Add-Ons:

      Plug-ins for the following applications have been identified as causing Web Client resources to malfunction and to cause Internet Explorer (versions 6.0 and 7.0) browser windows to hang or close unexpectedly:

      • Skype 3.0

      • Flash Get

      To eliminate the problem, you must uninstall or disable the suspected plug-in for Internet Explorer.

    4. New Features

      • Multiple Tabs

        Switch to a new tab when launching a resource using a full or partial resource name from iTopia. To enable this behaviour, do this:

        1. From Internet Explorer, on the Tools menu, select Internet Options.

        2. Select the General tab.

        3. Click the Settings button from within the Tabs section.

        4. Ensure that the Always switch to new tabs when they are created check box is selected.

        5. Click on the OK button to accept your changes.

        6. Click OK a second time to clear the Internet Options dialog from the screen.

      • Printing

        It is now possible to automatically fit window contents onto selected paper. Use these settings for optimal search results printing:

        1. On the view, select Page or Show All.

        2. From the Printer icon, select Print Preview.

        3. Select Landscape.

        4. Select Only the selected frame.

        5. Select Shrink to Fit.

        6. Click Print.

 
Back