Export a Report to MS Excel®

This topic outlines the different methods on Exporting a Report to MS Excel®.

ClosedGetting Started
Who Can Use It? Where Is It?
  • User
  • Assistant Project Manager
  • Project manager
  • Director
  • Administrator
  • System Administrator
    		•

    Reports > Edit > Style and Save

    Reports > Run > Export to Excel

    Tip: If you want to manipulate the financial data in MS Excel® use the Default Report to MS Excel® option. This will export all the financial columns in Currency cell format.

    Default the report to export using MS Excel®

    You can setup a report to automatically export to MS Excel® format. The MS Excel® report format allows you to have a large number of columns that would not fit on one printable page width. The report column headers will be exported with the results. The advantage of using the Report Format feature is that the financial data within MS Excel® is shown in Number or Currency Cell Format.

    ClosedSetting you Report export default to MS Excel® format

    To setup your report to export directly to a new MS Excel® file:

    1. Open Reports List and highlight the report you want to export.

    2. Select Edit Report.

    3. Go to the Style and Save tab.

    4. Choose the Report Format of MS Excel®.

    5. Save the report.

    6. Run the report to view the MS Excel® Export results.

    Locate Report Formats topic to learn more.

    ClosedGrouping Report options will create separate MS Excel® Worksheets

    If you need to separate the report results by each Office or Project Manager, then the report grouping options can help. The Grouping options allow you to have one MS Excel® report created with a separate tab for each group (i.e. Project Manager).

    To setup your report to use a grouping option:

    1. Open Reports List and highlight the report you want to export.

    2. Select Edit Report.

    3. Go to the Sorting and Grouping tab.

    4. Select the Group By option (i.e. Project Manager).

    5. Save the report.

    6. Run the report.

    7. Go to the MS Excel® file created and you will see a new tab for each Grouping (i.e. Project Manager).

    Locate Report Grouping topic to learn more. 

    MS Excel® - Using Template NEW 4.8

    Export data to MS Excel® using a template, to ensure that the same formatting is applied to each report that you export. Define the worksheet, and starting position to populate the data to within the MS Excel® template. Setup in the MS Excel® worksheet the column headings, and add pivot tables, graphs or macros into the MS Excel® template file. Setup these items into the template file and they will be populated ready for review when the Synergy report data is added to the template.

    ClosedComplete the following steps to create an MS Excel® template
    1. Select the Style and Save tab.
    2. Select the Report Format of 'Excel (using template)'.
    3. Create a new MS Excel® template by:
      1. Select the 'create a new excel template' from the drop down list.
      2. Type the template name into the pop-up box. Note: This will be the name of the MS Excel® template file, and the template name shown in this drop down list.
      3. The MS Excel® file will open to allow you to apply formatting to the worksheet.
      4. Enter a name for the worksheet. The default name for a worksheet in MS Excel®2010 is 'Sheet1'. Rename the sheet if required.
      5. Type the Report Column Headers across a row, which is after any company information needed in the MS Excel®worksheet.
      6. Write down the Row number and Cell number from where the cell data can be overwritten by the report.
        Note: The cell formatting like font or colour will remain. The text of the cell is overwritten when the template is used to export a report to Excel.
      7. Setup any Pivot tables or macros you want to run from the data sheet created by the Synergy report.
      8. Save and close the MS Excel®file once formatting has been applied.
        Note: The MS Excel®template created is saved to the 'Excel Templates' folder in the Synergy directory on the server. Make sure any changes that you make to the template are also saved to the same location.
    4. In the 'Excel Template' drop down list select which MS Excel® template to use from the list.
    5. Return to Synergy > Reports > Style and Save Tab to continue setting up the report to use the template.
    6. Type the name of the worksheet to populate the data within in the 'Sheet' field. i.e. 'Sheet1' is the default worksheet name.
    7. Type the Starting Column and Row from where the report data will be populated within the worksheet. I.e. Column 2, Row 6.
    8. Check the box to 'Save this report as one of my reports' and type a report title to be displayed in reports list for this report.
    9. Click Save.
    10. Locate the report on your 'My Reports' tab and run the report.

    Tips:

    • New templates are saved on the Synergy server in the 'Excel Templates' folder.
    • If using macros remember to save the template as a macro enabled spreadsheet.
    • Start the report data to populate from Row 2 onwards, to ensure the column headers are not overridden.
    • When you run the report, a copy of the template file is saved to the computer desktop with the report data populated on the defined worksheet.
    • When running the report again, the file is renamed to be 'Copy 1 of <template name>' when the a file with the same name already exists on your desktop.
    • Want to know more about the settings shown in Reports Manager? Locate the Style and Save topic to learn more.

    MS Excel® - Using Existing Workbook

    Add the report results to an existing MS Excel® File saved on your computer (or network). When using this option the report data will automatically add the report results to a defined worksheet existing file. If the selection location already is populated with data, this report format will overwrite the contents of the chosen location within the MS Excel® file. Locate Existing Excel File topic to learn more.

    ClosedTo export the report data to an existing MS Excel® workbook
    1. Select the Style and Save tab.
    2. In the report format drop down select MS Excel® (existing workbook).
    3. In the MS Excel® file field, browse to the location of the saved MS Excel®workbook.
    4. Highlight the file and select Open.
    5. Type the name of the sheet within the MS Excel® file, where the data from Synergy should be exported to.
    6. Specify which Column and Row the data should be added to the worksheet from. i.e. Column C / Row 5.
    7. Select Save this report as one of my reports and enter a report title.
    8. Click Save.
    9. Locate the report on the 'My Reports' tab and run the report.

    Save the report preview to MS Excel®

    When running the Report you can preview the results before you Export the file. To use this setup the report with a Report Export type of MS Excel®. The report formatting such as header, footer and column titles will be exported to MS Excel® with the results.

    ClosedTo save the previewed report to MS Excel®

    1. Open Reports List and highlight the required report.

    2. Run the report.

    3. The report will be displayed in Preview.

    4. Select the Export icon and choose the MS Excel® option.

    5. Choose the location to save the file.

    6. Open the saved file to view the results in the same format as shown in the Report Preview view.

    Tip: The financial data columns will be exported in text format, and you can convert the text to number format in MS Excel®. Locate MS Office® topic to learn more.

    Want to learn more?

    Looking for more help? Try reviewing the following topics: Concept Link IconView Topics

     

    © 2019 Total Synergy Pty Ltd

     

     

    Open topic with navigation