__TOC__
=Overview=
{{SeqReportPageHeader}}
==Custom File Export Configuration==* Click the '''Custom Export''' tab (formerly Advanced Export Settings)[[Image:ReportExport.PNG]]
The '''Custom Export''' window is displayed.
* '''Export Format''' - a dropdown list of different file format you can export the report to.* '''Folder Type''' - can be exported to either a [[SmartFolders_Overview|SmartFolder]] or a [Image:Reps200[SmartFolders_Overview#Configuration_-_Advanced|Configuration Folder]]* '''Folder''' - choose the subfolder where you want to export the file to.* '''Data Position''' - determines the starting position of your list view results (the "data").* '''Meta Data Position''' - determines the starting position of the report criteria (the "metadata").png]If your report does not utilize [wiki/Criteria criteria], the Meta Data will be blank.* '''Export File Name''' - set a filename that is applied when the report is exported. If left empty then the default name for exported file is "Book".
This window consists of the following settings<pre>Note: * '''Header''' - Static or variables that you need to display at The syntax for both the top of the export file.* '''Fields''' - A list of "Data Position" and "Meta Data Position" 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.* ''worksheet'Detail';'' - You construct the actual record layout for the specification in this area. This section can consist of multiple lines.* column'';'Footer'row'' - Static or variables that you need to display at the bottom of the export file.* '''File Extension''' - The extension default setting is 0;0;0 for both fields. This means that you wish to associate your list view results will be exported, with the export specifications. By setting this topmost, leftmost value to any valid Windows file extensionappearing in the first worksheet, the appropriate [[Application|application]] will be launched when the data is exportedin cell A1. Other settings: * '''Delimiter''' - tab character ('''''{TAB}''''')The numbering begins at zero, commaso the first worksheet, semi-colon, colon* '''Line breaks''' - use either a UNIX line break ('''''{LINEFEED}''''') first column or first row would be referenced with a Windows line break ('''''{BREAK}''''')0, as opposed to 1.<!--56598 - Create custom export with UNIX breaks--br />* '''Aggregate Functions Therefore, if you wanted either the list view results or the metadata to be placed in Header or Footer'''** '''COUNT''' - count the total rows per report. '''''@COUNT('''''column_name''''')@'''''** '''SUM''' - sum <br /> third worksheet of a column your Excel file, starting in the report. cell B4, you would enter ''2;1;3'''@SUM('''''column_name''''')@ '<br /> The syntax for the first worksheet, cell A1 would be ''0;0;0''<!--56482 - Ability to Sum and Count in report custom export header--/pre>
==File Extension==
By setting A report can be exported into a flat file with the following 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:
* '''txt''' – Windows Notepad.
* '''csv''' - Comma Separated Values.
* '''xml''' - Extensible Markup Language (can be opened in Notepad).
1. Set the '''Extension''' to '''txt'''.
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.
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}<details>{BREAK}
</pre>
<details>{BREAK} </details>
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:
<pre>{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}
</pre>
3. In the '''Footer''' section, close the root element:
<pre> </details> </pre>
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=
The Custom Export settings allows users to configure the information they wish to export from SmartSimple to external applications such as MS Word, MS Excel or Quickbooks.
[[Image:Reps200.png|800px]]
This window consists of the following settings:
<details> Peter Jahl 25000 11/05/2003 Herry Lee 55000 27/02/2005 Peter Manlee 75000 12/05/2001 </details>* '''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-->
* Please refer to '''[[Exporting Reports with Total Group By]]''' when exporting reports when [[Using Total Group Options in Report Builder]]
==Using Custom Export to export in 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.
==Exporting with Subreports==In order to export '''Fields''' drop down : gives a report list of variables that contains subreports or merged reports you must define correspond to the Advanced Export Settings for fields selected in the subreports, as well as the main reportthrough report builder.<!--54913 - Ability to store report export to specified SmartFolder--><!--67965 - Extend schedule report export to custom template-->'''Example:'''
* '''Details''' section : you specify the fields for the report to be exported. The following would export a fields that you select will be displayed right to left in exported report and subreport in Comma Separated Values format (CSV):so select the field for the last column first.
@Companies/Accounts - Company name@, @Companies/Accounts - Address 1@, @Companies/Accounts - City@, @Companies/Accounts - State@, @Companies/Accounts - Company ID@, @subreport_0@ {BREAK}4. Change the '''Extension''' to '''XLS''' or '''CSV'''.
5. Save the '''Layout'''.
==Export to SmartFolder or SFTP==A report can now be automated to export to a SmartFolder or an external SFTP site6. You can now schedule a recurring task on a daily, weekly, or monthly basis Return to automatically run a report and export the contents.The file will be exported based the normal ''File Export'Report''' list and no option for click the '''Custom Export''' is currently availablebutton.<!--54913 - Ability to store report export to specified SmartFolder--><!--67965 - Extend schedule report export to custom template-->
From the report configuration, go to '''FILE EXPORT''' tab and under '''Scheduled Export''', configure desired settings[[Image:Advanced export settings3.png|thumb|none|900px]]
The '''File''' is opened in MS-Excel.
[[Image:ExportReport-1Excel report.PNG|500pxpng]]
* '''=Export Type '''- specify the type of export for the scheduled exportto SmartFolder or SFTP=: ''Options:'':* File Export - A report can now be automated to export file based as report as per schedule 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 a daily, weekly, or monthly basis to a SmartFolder or 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: '''external 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 Go to [YYYY],[YYScheduled Report Export],[MM],[DD],[HH],[mm]. i.e. MyExportedReport_[YYYY]-[MM]-[DD]* '''Last Exported''' - date of the last export on how to configure scheduled report exports.
===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 <!--54913 - for instance, the setting that allows you Ability to store report export to select a template from a specified 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 <!--67965 - Extend 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 availablereport export to custom template-->
==See Also==
* [[Exporting List View Results to Microsoft Excel]]
* [[Report Variables]]
* [[Exporting Reports with Special Characters to Excel]]
* [[Exporting Multiple Lines Text Fields to HTML]]
* [[Using ReportProperty with a Word Merge Document]]
=See Also=
:* [[Exporting List View Results to Microsoft Excel]]
:* [[Report Variables]]
:* [[Exporting Reports with Special Characters to Excel]]
:* [[Exporting Multiple Lines Text Fields to HTML]]
:* [[Using ReportProperty with a Word Merge Document]]
{{PrevNextStart}} [[Creating a Dashboard to Display a Set of Charts]]
{{PrevNextMid}} Back to [[Reports]] Category {{PrevNextEnd}}
[[Category:Reports]] [[Category:XML]]
