Difference between revisions of "Exporting Reports"

From SmartWiki
Jump to: navigation, search
(Export to SmartFolder or SFTP)
(File Extension)
(8 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
__TOC__
 +
 +
 +
=Overview=
 
{{SeqReportPageHeader}}
 
{{SeqReportPageHeader}}
  
Line 12: Line 16:
  
  
==Custom Export Configuration==
+
=File Export=
* Click the '''Custom Export''' tab (formerly Advanced Export Settings).
 
 
 
The '''Custom Export''' window is displayed.
 
 
 
[[Image:Reps200.png]]
 
 
 
This window consists of the following settings:
 
 
 
* '''Header''' - Static or variables that you need to display at the top of the export file.
 
* '''Fields''' - A list of fields taken from the report that is being used to create the report. In addition, you can select two special settings – {tab} – used to add a tab character to the specification and {break} – used to add a line feed to the specification.
 
* '''Detail''' - You construct the actual record layout for the specification in this area. This section can consist of multiple lines.
 
* '''Footer''' - Static or variables that you need to display at the bottom of the export file.
 
* '''File Extension''' - The extension that you wish to associate with the export specifications. By setting this value to any valid Windows file extension, the appropriate [[Application|application]] will be launched when the data is exported.
 
 
 
 
 
Other settings:
 
 
 
* '''Delimiter''' - tab character ('''''{TAB}'''''), comma, semi-colon, colon
 
* '''Line breaks''' - use either a UNIX line break ('''''{LINEFEED}''''')  or a Windows line break ('''''{BREAK}''''')
 
<!--56598 - Create custom export with UNIX breaks-->
 
* '''Aggregate Functions in Header or Footer'''
 
** '''COUNT''' - count the total rows per report. '''''@COUNT('''''column_name''''')@'''''
 
** '''SUM''' - sum of a column in the report. '''''@SUM('''''column_name''''')@ '''''
 
<!--56482 - Ability to Sum and Count in report custom export header-->
 
 
 
 
==File Extension==
 
==File Extension==
By setting the file extension, you define which program on your computer should be used to process the exported report data. Some common extensions that are useful for exporting data are:
+
A report can be exported into a flat file with the following file extension:
  
 
* '''txt''' – Windows Notepad.
 
* '''txt''' – Windows Notepad.
Line 46: Line 25:
 
* '''csv''' - Comma Separated Values.
 
* '''csv''' - Comma Separated Values.
 
* '''xml''' - Extensible Markup Language (can be opened in Notepad).
 
* '''xml''' - Extensible Markup Language (can be opened in Notepad).
 +
  
 
1. Set the '''Extension''' to '''txt'''.
 
1. Set the '''Extension''' to '''txt'''.
Line 78: Line 58:
  
 
7. Close the '''Notepad''' [[Application|application]].
 
7. Close the '''Notepad''' [[Application|application]].
 
==Export to Excel==
 
If you change the extension to '''xls''', then the file will be opened with MS-Excel.
 
 
1. Click the '''Reports''' tab.
 
 
2. Click the '''Edit''' button for the '''Employee List''' report.
 
 
3. Click the''' Custom Export''' tab.<br />
 
[[image:Advanced export settings.png]]
 
 
You can specify the layout of the report to be exported in the next screen
 
 
[[image:Advanced export settings2.png]]
 
 
'''Header''' section : you specify the column headings for your report to be exported.
 
 
'''Fields''' drop down : gives a list of variables that correspond to the fields selected in the report through report builder.
 
 
'''Details''' section : you specify the fields for the report to be exported. The fields that you select will be displayed right to left in exported report so select the field for the last column first.
 
 
4. Change the '''Extension''' to '''XLS''' or '''CSV'''.
 
 
5. Save the '''Layout'''.
 
 
6. Return to the '''Report''' list and click the '''Export''' button.
 
 
[[Image:Advanced export settings3.png|thumb|none|900px]]
 
 
 
The '''File''' is opened in MS-Excel.
 
 
[[Image:Excel report.png]]
 
 
  
 
'''IMPORTANT:''' In order to support '''special characters''' such as French accented letters, euro symbols etc [[SmartSimple]] uses UTF-8 encoding. When opening exported files Microsoft Excel always imports using ASCII, so special characters do not appear correctly. This is a flaw with Excel. In order to export data containing special characters to Excel use one of the procedures outlined on the [[Exporting Reports with Special Characters to Excel]] article.
 
'''IMPORTANT:''' In order to support '''special characters''' such as French accented letters, euro symbols etc [[SmartSimple]] uses UTF-8 encoding. When opening exported files Microsoft Excel always imports using ASCII, so special characters do not appear correctly. This is a flaw with Excel. In order to export data containing special characters to Excel use one of the procedures outlined on the [[Exporting Reports with Special Characters to Excel]] article.
Line 123: Line 69:
 
1. In the '''Header''' section enter the XML declaration and open the root element, ''details'':
 
1. In the '''Header''' section enter the XML declaration and open the root element, ''details'':
  
<pre><!--?xml version="1.0" encoding="ISO-8859-1"?-->{BREAK}
+
<pre>
 +
<!--?xml version="1.0" encoding="ISO-8859-1"?-->{BREAK}
 +
<details>{BREAK}
 
</pre>
 
</pre>
 
 
<details>{BREAK} </details>
 
 
 
  
  
Line 139: Line 82:
 
The {TAB}s and {BREAK}s will give the XML document proper indentation:
 
The {TAB}s and {BREAK}s will give the XML document proper indentation:
  
<pre>{TAB}{BREAK}
+
<pre>
{TAB}{TAB}@Users - First name@{BREAK}
+
{TAB}<employee>{BREAK}
{TAB}{TAB}@Users - Last name@{BREAK}
+
{TAB}{TAB}<firstname>@Users - First name@</firstname>{BREAK}
{TAB}{TAB}@Users - Salary@{BREAK}
+
{TAB}{TAB}<lastname>@Users - Last name@</lastname>{BREAK}
{TAB}{TAB}@Users - Start Date@{BREAK}
+
{TAB}{TAB}<salary>@Users - Salary@</salary>{BREAK}
{TAB}{BREAK}
+
{TAB}{TAB}<startdate>@Users - Start Date@</startdate>{BREAK}
 +
{TAB}</employee>{BREAK}
 
</pre>
 
</pre>
  
 
3. In the '''Footer''' section, close the root element:
 
3. In the '''Footer''' section, close the root element:
  
<pre> </pre>
+
<pre> </details> </pre>
  
 
4. The Export XML document opened in Notepad:
 
4. The Export XML document opened in Notepad:
 +
<pre>
 +
<?xml version="1.0" encoding="ISO-8859-1"?>
  
 +
<details>
 +
<employee>
 +
<firstname>Peter</firstname>
 +
<lastname>Jahl</lastname>
 +
<salary>25000</salary>
 +
<startdate>11/05/2003</startdate>
 +
</employee>
 +
<employee>
 +
<firstname>Herry</firstname>
 +
<lastname>Lee</lastname>
 +
<salary>55000</salary>
 +
<startdate>27/02/2005</startdate>
 +
</employee>
 +
<employee>
 +
<firstname>Peter</firstname>
 +
<lastname>Manlee</lastname>
 +
<salary>75000</salary>
 +
<startdate>12/05/2001</startdate>
 +
</employee>
 +
</details>
 +
</pre>
  
 +
==Exporting with Subreports==
 +
In order to export a report that contains subreports or merged reports you must define the Advanced Export Settings for the subreports, as well as the main report.
 +
<!--54913 - Ability to store report export to specified SmartFolder-->
 +
<!--67965 - Extend schedule report export to custom template-->
 +
'''Example:'''
  
 +
* The following would export a report and subreport in Comma Separated Values format (CSV):
 +
<pre>
 +
@Companies/Accounts - Company name@,
 +
@Companies/Accounts - Address 1@,
 +
@Companies/Accounts - City@,
 +
@Companies/Accounts - State@,
 +
@Companies/Accounts - Company ID@,
 +
@subreport_0@
 +
{BREAK}
 +
</pre>
  
 +
=Custom Export=
 +
* Click the '''Custom Export''' tab (formerly Advanced Export Settings).
  
 +
The '''Custom Export''' window is displayed.
  
<details> Peter Jahl 25000 11/05/2003 Herry Lee 55000 27/02/2005 Peter Manlee 75000 12/05/2001 </details>
+
[[Image:Reps200.png]]
 
 
 
 
 
 
 
 
  
 +
This window consists of the following settings:
  
 +
* '''Header''' - Static or variables that you need to display at the top of the export file.
 +
* '''Fields''' - A list of fields taken from the report that is being used to create the report. In addition, you can select two special settings – {tab} – used to add a tab character to the specification and {break} – used to add a line feed to the specification.
 +
* '''Detail''' - You construct the actual record layout for the specification in this area. This section can consist of multiple lines.
 +
* '''Footer''' - Static or variables that you need to display at the bottom of the export file.
 +
* '''File Extension''' - The extension that you wish to associate with the export specifications. By setting this value to any valid Windows file extension, the appropriate [[Application|application]] will be launched when the data is exported.
  
  
 +
Other settings:
  
 +
* '''Delimiter''' - tab character ('''''{TAB}'''''), comma, semi-colon, colon
 +
* '''Line breaks''' - use either a UNIX line break ('''''{LINEFEED}''''')  or a Windows line break ('''''{BREAK}''''')
 +
<!--56598 - Create custom export with UNIX breaks-->
 +
* '''Aggregate Functions in Header or Footer'''
 +
** '''COUNT''' - count the total rows per report. '''''@COUNT('''''column_name''''')@'''''
 +
** '''SUM''' - sum of a column in the report. '''''@SUM('''''column_name''''')@ '''''
 +
<!--56482 - Ability to Sum and Count in report custom export header-->
  
==Exporting with Subreports==
+
=Export to SmartFolder or SFTP=
In order to export a report that contains subreports or merged reports you must define the Advanced Export Settings for the subreports, as well as the main report.
 
<!--54913 - Ability to store report export to specified SmartFolder-->
 
<!--67965 - Extend schedule report export to custom template-->
 
'''Example:'''
 
 
 
* The following would export a report and subreport in Comma Separated Values format (CSV):
 
 
 
@Companies/Accounts - Company name@,
 
@Companies/Accounts - Address 1@,
 
@Companies/Accounts - City@,
 
@Companies/Accounts - State@,
 
@Companies/Accounts - Company ID@,
 
@subreport_0@
 
{BREAK}
 
 
 
 
 
==Export to SmartFolder or SFTP==
 
 
A report can now be automated to export to a SmartFolder or an external SFTP site. You can now schedule a recurring task on a daily, weekly, or monthly basis to automatically run a report and export the contents.
 
A report can now be automated to export to a SmartFolder or an external SFTP site. You can now schedule a recurring task on a daily, weekly, or monthly basis to automatically run a report and export the contents.
 
The file will be exported based the normal ''File Export''' and no option for '''Custom Export'''  is currently available.
 
The file will be exported based the normal ''File Export''' and no option for '''Custom Export'''  is currently available.
Line 201: Line 179:
 
:* File Export - export file based on settings from [wiki/Exporting_Reports '''File Export'''] tab
 
:* File Export - export file based on settings from [wiki/Exporting_Reports '''File Export'''] tab
 
:* Custom Export - export file based on settings from [wiki/Custom_Export '''Custom Export''']
 
:* Custom Export - export file based on settings from [wiki/Custom_Export '''Custom Export''']
* '''Frequency''' - choose the frequency of the export
+
:* '''Frequency''' - choose the frequency of the export
:* Disabled
+
:** Disabled
:* Daily 
+
:** Daily 
:* Weekly
+
:** Weekly
:* Monthly
+
:** Monthly
:* Quarterly
+
:** Quarterly
:* Yearly
+
:** Yearly
:* On Demand - used when report is linked to an autoloader.
+
:** On Demand - used when report is linked to an autoloader.
* '''At (Start Date)''' - specify time for the scheduled export
+
'''At (Start Date)''' - specify time for the scheduled export
* '''Starting''' - specify the start date for the scheduled export
+
'''Starting''' - specify the start date for the scheduled export
* '''Export To''' - select where to export the file
+
:* '''Export To''' - select where to export the file
:* '''SFTP'''
+
:** '''SFTP'''
:** '''URL''' - enter the url of the sftp site
+
:*** '''URL''' - enter the url of the sftp site
:** '''Username''' - username to allow access to the sftp
+
:*** '''Username''' - username to allow access to the sftp
:** '''Password''' - password to allow access to the sftp
+
:*** '''Password''' - password to allow access to the sftp
:** '''Path''' - sftp folder location where the files will be pushed i.e. ''/in'' or ''/out''
+
:*** '''Path''' - sftp folder location where the files will be pushed i.e. ''/in'' or ''/out''
:* '''Configuration Folder'''
+
'''Configuration Folder'''
:* '''SmartFolder'''
+
'''SmartFolder'''
:** '''SmartFolder''' - select the SmartFolder where the files will be pushed
+
:*** '''SmartFolder''' - select the SmartFolder where the files will be pushed
* '''Update Status After Export''' - status of the records will changed to the selected status after export.
+
'''Update Status After Export''' - status of the records will changed to the selected status after export.
* '''Trigger Workflow After Export''' - workflow selected will be triggered to all records after export.
+
'''Trigger Workflow After Export''' - workflow selected will be triggered to all records after export.
* '''Export File Name''' - file name of the file can be specified including stamp date. It is recommended to append a timestamp. To stamp the date, use [YYYY],[YY],[MM],[DD],[HH],[mm]. i.e. MyExportedReport_[YYYY]-[MM]-[DD]
+
:* '''Export File Name''' - file name of the file can be specified including stamp date. It is recommended to append a timestamp. To stamp the date, use [YYYY],[YY],[MM],[DD],[HH],[mm]. i.e. MyExportedReport_[YYYY]-[MM]-[DD]
* '''Last Exported''' - date and time of the last export.
+
:* '''Last Exported''' - date and time of the last export.
 +
:*
 +
 
 
===Examples===
 
===Examples===
:* Export Every Monday of the week @ 0900
+
:*
::Choose a future date that falls on a Monday for the '''Start Date''', '''Weekly''' for the '''Frequency''' and select the '''Time of Export''' dropdown list.
+
:*:* Export Every Monday of the week @ 0900
:* Export Every 15th of the month
+
:*:: Choose a future date that falls on a Monday for the '''Start Date''', '''Weekly''' for the '''Frequency''' and select the '''Time of Export''' dropdown list.
::Choose '''15th''' (future date) for the '''Start Date''' and '''Monthly''' for the '''Frequency''' and select the '''Time of Export''' dropdown list.
+
:*:
+
:*:* Export Every 15th of the month
 +
:*:: Choose '''15th''' (future date) for the '''Start Date''' and '''Monthly''' for the '''Frequency''' and select the '''Time of Export''' dropdown list.
 +
 
 
===Troubleshooting===
 
===Troubleshooting===
 +
:*
 +
:*:* This won't work for reports that have any criteria, or reports with any other variables that require a user session or user input - for instance, the setting that allows you to select a template from a SmartFolder before exporting. Reports violating these will either not be exported, or have no data when exported, or maybe some other unpredictable result.
 +
:*:* To reset/change the schedule, '''Disabled''' the '''Frequency''' then save it. This will delete the current setting configurations from the database. Then, enter new schedule settings and save.
 +
:*:* No option for '''Custom Export'''  is currently available
  
:* This won't work for reports that have any criteria, or reports with any other variables that require a user session or user input - for instance, the setting that allows you to select a template from a SmartFolder before exporting. Reports violating these will either not be exported, or have no data when exported, or maybe some other unpredictable result.
+
*  
:* To reset/change the schedule, '''Disabled''' the '''Frequency''' then save it. This will delete the current setting configurations from the database. Then, enter new schedule settings and save.
 
:* No option for '''Custom Export'''  is currently available
 
  
 
==See Also==
 
==See Also==
* [[Exporting List View Results to Microsoft Excel]]
+
:** [[Exporting List View Results to Microsoft Excel]]
* [[Report Variables]]
+
:** [[Report Variables]]
* [[Exporting Reports with Special Characters to Excel]]
+
:** [[Exporting Reports with Special Characters to Excel]]
* [[Exporting Multiple Lines Text Fields to HTML]]
+
:** [[Exporting Multiple Lines Text Fields to HTML]]
* [[Using ReportProperty with a Word Merge Document]]
+
:** [[Using ReportProperty with a Word Merge Document]]
 
 
 
{{PrevNextStart}} [[Creating a Dashboard to Display a Set of Charts]]
 
{{PrevNextStart}} [[Creating a Dashboard to Display a Set of Charts]]
 
{{PrevNextMid}} Back to [[Reports]] Category {{PrevNextEnd}}
 
{{PrevNextMid}} Back to [[Reports]] Category {{PrevNextEnd}}
  
 
[[Category:Reports]][[Category:XML]]
 
[[Category:Reports]][[Category:XML]]

Revision as of 15:54, 13 February 2020


Overview

caption Click here to watch a video on SmartSimple's integrated reporting subsystem.

There are two techniques to access report data from other applications.

See Plug-Ins for Microsoft Office for details

This article explains how to use the 2nd method, which is exporting.

  • If you want to export the report data to any other data format (for example comma separated values), then you can use the Export settings for the report to create the file format required.
  • You can also use this technique to create a MS-Excel or MS-Word file, without the need to use the Plug-In application.


File Export

File Extension

A report can be exported into a flat file with the following file extension:

  • txt – Windows Notepad.
  • xls – Microsoft Excel.
  • iif – QuickBooks.
  • csv - Comma Separated Values.
  • xml - Extensible Markup Language (can be opened in Notepad).


1. Set the Extension to txt.

Reps199.png

The extension txt is generally associated with Notepad.

2. Click the Save button.

3. Click the Edit Report button.

4. Click the Reports tab.

The report list is displayed and the Employee list report is now displayed with an Export button.

Advanced export settings3.png

5. Click the Export button.

Depending on your Browser and Security setting, you may need to answer some prompts before the file can be opened.

Reps197.png

6. If required, click the Open button.

The file is opened in the associated application – Notepad.

The file name is set to the report name plus the extension specified.

Reps196.png

7. Close the Notepad application.

IMPORTANT: In order to support special characters such as French accented letters, euro symbols etc SmartSimple uses UTF-8 encoding. When opening exported files Microsoft Excel always imports using ASCII, so special characters do not appear correctly. This is a flaw with Excel. In order to export data containing special characters to Excel use one of the procedures outlined on the Exporting Reports with Special Characters to Excel article.


Note: If the report has sub reports attached then you must set up the Custom Export settings for each of the sub report attached.

Export to XML

If you change the File extension to xml, then the report will construct an XML document from the report results.

1. In the Header section enter the XML declaration and open the root element, details:

<!--?xml version="1.0" encoding="ISO-8859-1"?-->{BREAK}
<details>{BREAK}



2. In the Detail section add the child elements, in the sample below the child is employee with subchildren firstname, lastname, salary and startdate.

The {TAB}s and {BREAK}s will give the XML document proper indentation:

{TAB}<employee>{BREAK}
{TAB}{TAB}<firstname>@Users - First name@</firstname>{BREAK}
{TAB}{TAB}<lastname>@Users - Last name@</lastname>{BREAK}
{TAB}{TAB}<salary>@Users - Salary@</salary>{BREAK}
{TAB}{TAB}<startdate>@Users - Start Date@</startdate>{BREAK}
{TAB}</employee>{BREAK}

3. In the Footer section, close the root element:

 </details> 

4. The Export XML document opened in Notepad:

<?xml version="1.0" encoding="ISO-8859-1"?>

<details>
	<employee>
		<firstname>Peter</firstname>
		<lastname>Jahl</lastname>
		<salary>25000</salary>
		<startdate>11/05/2003</startdate>
	</employee>
	<employee>
		<firstname>Herry</firstname>
		<lastname>Lee</lastname>
		<salary>55000</salary>
		<startdate>27/02/2005</startdate>
	</employee>
	<employee>
		<firstname>Peter</firstname>
		<lastname>Manlee</lastname>
		<salary>75000</salary>
		<startdate>12/05/2001</startdate>
	</employee>
</details>

Exporting with Subreports

In order to export a report that contains subreports or merged reports you must define the Advanced Export Settings for the subreports, as well as the main report. Example:

  • The following would export a report and subreport in Comma Separated Values format (CSV):
@Companies/Accounts - Company name@,
@Companies/Accounts - Address 1@,
@Companies/Accounts - City@,
@Companies/Accounts - State@,
@Companies/Accounts - Company ID@,
@subreport_0@
{BREAK}

Custom Export

  • Click the Custom Export tab (formerly Advanced Export Settings).

The Custom Export window is displayed.

Reps200.png

This window consists of the following settings:

  • Header - Static or variables that you need to display at the top of the export file.
  • Fields - A list of fields taken from the report that is being used to create the report. In addition, you can select two special settings – {tab} – used to add a tab character to the specification and {break} – used to add a line feed to the specification.
  • Detail - You construct the actual record layout for the specification in this area. This section can consist of multiple lines.
  • Footer - Static or variables that you need to display at the bottom of the export file.
  • File Extension - The extension that you wish to associate with the export specifications. By setting this value to any valid Windows file extension, the appropriate application will be launched when the data is exported.


Other settings:

  • Delimiter - tab character ({TAB}), comma, semi-colon, colon
  • Line breaks - use either a UNIX line break ({LINEFEED})  or a Windows line break ({BREAK})
  • Aggregate Functions in Header or Footer
    • COUNT - count the total rows per report. @COUNT(column_name)@
    • SUM - sum of a column in the report. @SUM(column_name)@ 

Export to SmartFolder or SFTP

A report can now be automated to export to a SmartFolder or an external SFTP site. You can now schedule a recurring task on a daily, weekly, or monthly basis to automatically run a report and export the contents. The file will be exported based the normal File Export' and no option for Custom Export  is currently available.

From the report configuration, go to FILE EXPORT tab and under Scheduled Export, configure desired settings:

ScheduledExport.PNG

  • Export Type - specify the type of export for the scheduled export
Options:
  • File Export - export file based on settings from [wiki/Exporting_Reports File Export] tab
  • Custom Export - export file based on settings from [wiki/Custom_Export Custom Export]
  • Frequency - choose the frequency of the export
    • Disabled
    • Daily 
    • Weekly
    • Monthly
    • Quarterly
    • Yearly
    • On Demand - used when report is linked to an autoloader.

At (Start Date) - specify time for the scheduled export Starting - specify the start date for the scheduled export

  • Export To - select where to export the file
    • SFTP
      • URL - enter the url of the sftp site
      • Username - username to allow access to the sftp
      • Password - password to allow access to the sftp
      • Path - sftp folder location where the files will be pushed i.e. /in or /out

Configuration Folder SmartFolder

      • SmartFolder - select the SmartFolder where the files will be pushed

Update Status After Export - status of the records will changed to the selected status after export. Trigger Workflow After Export - workflow selected will be triggered to all records after export.

  • Export File Name - file name of the file can be specified including stamp date. It is recommended to append a timestamp. To stamp the date, use [YYYY],[YY],[MM],[DD],[HH],[mm]. i.e. MyExportedReport_[YYYY]-[MM]-[DD]
  • Last Exported - date and time of the last export.

Examples

    • Export Every Monday of the week @ 0900
    Choose a future date that falls on a Monday for the Start Date, Weekly for the Frequency and select the Time of Export dropdown list.
    • Export Every 15th of the month
    Choose 15th (future date) for the Start Date and Monthly for the Frequency and select the Time of Export dropdown list.

Troubleshooting

    • This won't work for reports that have any criteria, or reports with any other variables that require a user session or user input - for instance, the setting that allows you to select a template from a SmartFolder before exporting. Reports violating these will either not be exported, or have no data when exported, or maybe some other unpredictable result.
    • To reset/change the schedule, Disabled the Frequency then save it. This will delete the current setting configurations from the database. Then, enter new schedule settings and save.
    • No option for Custom Export  is currently available

See Also




Previous.png Creating a Dashboard to Display a Set of Charts Back to Reports Category Next.png