Home > SI5 Documentation > User Guide > Reports > Excel

Excel

Excel Reports

It is possible to export data directly to MS Excel by using the Excel Reports feature. This allows you create a custom report using Excel and have the data populate automatically based on how the report is run in SI.

Note: Excel reports in SI5 will NOT work with Excel 2010, you must use Excel 2007.

The basic steps to using this feature are:

  1. Create an excel file that is linked to the D-Tools SI5 XML Schema 
  2. Map D-Tools data fields into one or more worksheets in this file
  3. Create pivot tables, graphs, or other reports using this data
  4. Import the report into the Reports List using the Import Reports button:

 

File:Si5Wiki/SI5/10Reports/Excel/ex_import_reports.jpg

  1. Run the report from the Reports List just like any other report. Excel will open with the specific data populated.

 

Note: Sample excel reports can be downloaded here, and imported into SI5.

Create a Custom Excel Report File

Link to D-Tools XML Schema

In Excel,

1)    Go to View > Task Pane (Developer > Source in Excel 2007)

2)    Go To XML Source (dropdown) (not needed in Excel 2007)

3)    Click on XML Maps… (button)

4)    Click on Add…

5)    Copy this URL into the File Name line: http://www.d-tools.com/schemas/SI5/reports/DToolsReport.xsd             

6)    Click Open

7)    Select DToolsReport as the XML root node

File:Reports/Excel/image002.jpg

8)    Click OK

Note: You can map this same XML source more than once if you wish to map the XML data field more than once in the next step.

Map the Data Fields into Excel Columns

The Task Pane shows a list of all data fields available. You can drag and drop data fields directly from the Task Pane to an excel spreadsheet. This will add the data field to your report source data.

File:Reports/Excel/image004.jpg

Keep adding fields until you have all of the data you wish to report on.

File:Reports/Excel/image005.jpg

Load Sample Data into Excel for Design

In order create a design layout, you will need some sample data. This data will be replaced when you run the report from the Reporting Center, but is useful to see how your report behaves prior to this.

D-Tools provides sample data in your SI5 Global Application folder (C:\Documents and Settings\All Users\Application Files\D-Tools\SI5\Other Files). Unzip the file Excel Report Sample Data.zip to get the sample data.
VISTA:  C:\ProgramData\D-Tools\SI5\Other Files

To load the sample data into your report, follow these steps:

9)    Go to XML > Data > Import… (Developer > Import in Excel 2007)

10)Browse to the XML file containing the sample data (C:\Documents and Settings\All Users\Application Files\D-Tools\SI5\Other Files\Excel Report Sample Data.xml in this example) VISTA:  C:\ProgramData\D-Tools\SI5\Other Files\Excel Report Sample Data.xml

This will load the sample data into the spreadsheet so that you can design your report.

File:Reports/Excel/image006.jpg

Create Reports Based on Imported Data Columns

Once you have imported the sample data, you can create your own report using Excel’s reporting features, such as Pivot Tables and Pivot Charts. See the Microsoft Excel users guide for details on how to do this.

Link Excel Reports to Reporting Center

Once you have created a custom Excel report, you can import it into the Reporting Center so that it will appear on your Reports List. To do this, go to the Reporting Center and to to New… Excel Report in the File Menu. The Add Report Dialog will appear.

File:Reports/Excel/image007.png

Enter the name of the report and a brief description. You can also choose which report category this will appear in. Output Format should always be “Excel” for Excel reports. Click [Browse] to find your custom Excel report file on your computer, then click OK to add the report to your list.

Running an Excel Report

At this point the Excel report will appear in your report list and can be run just like any other report. When you do, Excel will open and the data from your D-Tools project will be populated.

File:Reports/Excel/image008.jpg

 

 

Last modified
15:55, 26 Nov 2013

Tags

This page has no custom tags.

Classifications

This page has no classifications.