Smartstaff
1,385
edits
Changes
→Custom Export
__TOC__
=Overview=
{{SeqReportPageHeader}}
There are two techniques to access report data from other [[Applications|applications]].
* If you want to access reports from '''Microsoft Excel''' or '''Microsoft Word''', then you can use the [[SmartSimple]] [[:Category:Plug-Ins for Microsoft Office|Plug-Ins for Microsoft Office]] for these [[Applications |applications]] to access report data.
: See [[:Category:Plug-Ins for Microsoft Office|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 [[:Category:Plug-Ins for Microsoft Office|Plug-In]] [[Application|application]].
=File Export=
[[Image:ReportExport.PNG]]
* '''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'''.
2. Click the '''Save''' button.
The report list is displayed and the '''Employee''' list report is now displayed with an '''Export''' button.
[[Image:Reps198Advanced export settings3.png|thumb|none|900px]]
5. Click the '''Export''' button.
[[Image: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.
7. Close the '''Notepad''' [[Application|application]].
'''Note:''' If the report has sub reports attached then you must set up the Custom Export settings for each of the sub report attached.
===CSV Data Security===
When exporting data into a CSV format, files can become vulnerable to [https://owasp.org/www-community/attacks/CSV_Injection malicious CSV injection attacks]. As a precautionary measure, an additional space will be added at the beginning of any values starting with at signs ( @ ), plus signs ( + ), minus signs ( - ), piping symbols ( | ), or equals signs ( = ). Therefore, when you export a file from Platform3 into a CSV format and later need to import that file into another system, please ensure you trim the additional space in front of any values.
==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'':
<pre>
<!--?xml version="1.0" encoding="ISO-8859-1"?-->{BREAK}
<details>{BREAK}
</pre>
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:
* '''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''''')@ '''''
** '''Current Date''' - add current date using standard format YYYY, YY, MM, DD, HH, mm
** [[Sscalculation|sscalculation]] can also be used in the header or footer
<!--Ticket# 56482 - Ability to Sum and Count in report custom export header-->
<!--Ticket# 122241 - To add current date in the header for custom export report-->
* 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''' Advanced Report SettingsCustom 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.
6. Return to the '''Report''' list and click the '''Export''' button.
[[imageImage:Advanced export settings3.png|thumb|none|900px]]
The '''File''' is opened in MS-Excel.
[[Image:Excel report.png]]
Go to [[Scheduled Report Export]] on how to configure scheduled report exports.
=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:IntegrationXML]]