How to Use Export to Excel

PointForce iTopia includes the ability to export the results of a search operation from a view to 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 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.

The following list identifies the parameters in which Export to Excel works in PointForce Enterprise:

• Dates and numbers in Microsoft Excel are displayed and entered using the running PC's configured regional settings. Export to Excel supports this functionality.

• Although iTopia supports a total numerical precision of 17 digits, Excel has a limitation of 15 digits. Any attempt to import or export numbers which are greater than 15 digits will result in rounding.

• The value and the description for each multiple value column that uses a renderer code of 6 - Enumeration 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.

  Document	Sequence	Transaction Type	Transaction Type - Description	Transaction Amount
  1000	1	DI	Debit Invoice	500.00
  1000	2	CM	Credit Memo	-50.00
  1000	3	CP	Cash Payment	-450.00

Prerequisites

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

• Microsoft Excel must be installed on your 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.

Configuring Interactivity with Microsoft Excel

To configure MS Excel to work with the Edit with Excel feature in PointForce iTopia, see the Configuring Interactivity with MS Excel topic.

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

Using TECSYS' Export to Excel Macros

Now that you have installed the necessary templates and add-in, you need to create appropriate application views and corresponding Excel templates. TECSYS provides three resources that format search results exported from the application database to the Excel format. These should be used as a starting point to generate a new template for a view.

The templates provided by TECSYS reside in the Documents\Tecsys\iTopia\Template folder on your PC. All are ready to use out of the box, as corresponding application views already exist for each of them.

The following table lists the views and templates provided by TECSYS to showcase this feature. (These views are only available to users with permissions to view metadata.)

Note: The Export to Excel statistic view name is identical to the Excel template filename, excluding the .xlt suffix (meta_md_user_role.stats). This is a requirement. Also note the .stats suffix on the Export to Excel statistic view name. This is purely coincidental and does not represent a requirement.

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

1. From within the application, launch an existing generic view (an example of a generic view is ibis_table name , or ibis_sucu).

2. Click the Personalize action from the view's Search page.

3. Go to the Search Results tab, 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 action and specify a unique description for the view. This is the description that will appear on the page when you launch the view.

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

1. Launch Windows Explorer.

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

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

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

5. Launch Microsoft Excel.

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

   Note: The worksheets (i.e. Data, and Summary, PivotTable, and/or BarGraph and BarGraphData) the spreadsheet contains by default. You can format the worksheets to show the data you need. The first sheet must always be called Data, 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, select Tools-Macro-Visual Basic) to view the code the template contains and to customize it to your requirements, if needed. Note that this step should only be performed by a resource who is experienced with using Visual Basic.

When Export to Excel is executed on a view, it automatically assigns the view name as the name of the file and worksheet. However, Excel allows at most 31 characters for worksheet names. Consequently, if you assign a view a name that is longer than 31 characters, Excel will rename the generated worksheet to "Recovered_Sheet1". This will cause a discrepancy between the view name and the template name. As a result, if you then attempt to apply your template against the view, it will not execute.

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

1. From within the application, launch a personalized view, named viewname.suffix.

2. Perform a search. 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 titled viewname.suffix, and you are presented with a pop-up menu.

4. Click the Format button from the TECSYS iTopia tab in the Excel ribbon.

   

   The exported results are formatted and then saved in an Excel workbook titled viewname.suffix_report.xls. The new spreadsheet is stored in the Documents\Tecsys\iTopia\Output folder. The spreadsheet is opened and displays either the Bar Graph, the Pivot Table and/or the Summary report, whichever are applicable.

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.