# Exporting a Report Model Astera makes it easy to export data from a report model directly to a number of different destination types such as Excel and CSV. Moreover, it supports the export of data to a wide range of on-premise and cloud databases including SQL Server, Oracle, DB2, Sybase, MySQL, etc. In this document, we will learn how to export data in a report model to three different destination types. ## **Sample Use Case** In this example, we have incoming data extracted by the report model below. Download the sample data file and report model here: {% file src="" %} ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2FYMLiIHIAbLcCxJd8db3f%2F0.png?alt=media) The model contains a hierarchical structure with three data regions and multiple fields. Click on the *Preview Data* icon placed in the toolbar above Astera's designer. A *Data Preview* window will open, displaying the extracted data. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2FEm3nMuBkV0BlU91rkIds%2F1.png?alt=media) Let’s export the data extracted by this report model to an Excel destination, a CSV file, and a SQL database table. ## **Exporting to an Excel Destination** 1. Select the *Create New Export Settings and Run (to Excel)* icon in the toolbar under the *Model Layout* panel. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2FffxredyV5yv6Ro4BzdRt%2F2.png?alt=media) A configuration window will open. This is where the export properties are defined. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2FZxapxUO8JQWy1bUKWXCF%2F3.png?alt=media) 2. Provide a *File Path* where Astera will save the exported data with a *.xlsx* extension. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2FAjbjsYRjJYfq3GI0f6d8%2F4.png?alt=media) Once you have provided the destination file path, you can choose multiple options from the export setting window to configure the data export file. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2FLhq1xzRZull0lYwZrN9X%2F5.png?alt=media) * *First Row Contains Header* – directs Astera to read headers from the source file. * *Worksheet* – specifies the title for the worksheet to export the data to within the Excel file. * *Append to File(If Exists)* – adds the exported data to an existing Excel sheet without overwriting the data it contains. * *Write to Multiple Files* – saves the exported data to multiple files instead of one single file. Specify the same file in multiple Excel destinations and Astera will create a single file with multiple worksheets and write all data to that file. *Rules for Filtering* – specifies a criterion to export only filtered records to an Excel sheet. On expanding, users can see an expression box with built-in functions to facilitate rule-based filtering. This feature is explained in detail in the next section. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2FcUQmMzqPaXeuF5HSsb51%2F6.png?alt=media) 3. Click *Next*. This is the *Layout Builder* screen where the extracted data fields can be modified as per the requirements. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2FYygTcp0ISh9tpVX4AwPU%2F7.png?alt=media) 4. Click *OK to* start the export of data. View its job trace in the *Job Progress* window. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2FUeZ19QcYFQpctcfEUKlw%2F8.png?alt=media) Astera will generate the Excel file and provide its clickable link in the job trace. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2FHJ59FD2otwdJhgRiHdqT%2F9.png?alt=media) The export to an Excel sheet is complete. ## **Exporting to a CSV File** 1. Select the *Create New Export Settings and Run (to CSV)* icon placed in the Toolbar under the *Model Layout* panel. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2FDIcouElCvutEu185nyPZ%2F10.png?alt=media) A configuration window will open. This is where the export properties for CSV files are defined. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2FABQngSYGgGcA0THahKt9%2F11.png?alt=media) 2. Provide a *File Path* where Astera will save the exported data with a *.csv* extension. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2FeysWxu40oR0IElxgDN31%2F12.png?alt=media) Once the file path has been provided, move on to other options to configure the exported file. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2F70MGli0WSg2UBxw6MGUk%2F13.png?alt=media) * *First Row Contains Header* – directs Astera to read headers from the source file. * *Field Delimiter* – allows users to select a delimiter from the drop-down list for the fields. * *Record Delimiter* – allows users to specify any delimiter for the records within fields. * *Encoding box* – allows users to choose the encoding scheme for the CSV file from a list of choices. The default value is *Unicode (UTF-8)*. * *Text Qualifier* – is a symbol that identifies where text begins and ends. It is used specifically when importing data rather than exporting. * *Apply Text Qualifier to All Fields* – adds the specified qualifier to all the incoming fields. * *Null Text* – directs Astera to replace a certain value in the report model with a null value. * *Append to File(If Exists)* – adds the exported data to an existing CSV file without overwriting the data it contains. * *Hierarchical Destination* – sorts the incoming data into hierarchies where necessary. * *Write to Multiple Files* – saves the exported data to multiple files instead of one single file. * *Rules for filtering data* – specifies a criterion to export only filtered records to a CSV file. On expanding, users can see an expression box with built-in functions to facilitate rule-based filtering. This feature is explained in detail in the next section. 3. Click *Next*. This is the *Layout Builder* screen where the extracted data fields can be modified as per the requirements. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2F1jt0yzG4188YnMeR4i87%2F14.png?alt=media) 4. Click *OK*, this starts the export of data. View its job trace in the *Job Progress* window. At the end of a successful run, Astera will generate the CSV file and provide its clickable link in the job trace. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2Fp1QObbatBMrGZINGouQW%2F15.png?alt=media) The export to a CSV file is complete. ## **Exporting to Database Table** 1. Select the *Create New Export Settings and Run (to Database)* icon in the Toolbar under the *Model Layout* panel. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2FL3zajh894eS95DRj02uL%2F16.png?alt=media) A configuration window will open. The first screen is the *Database Connection* screen. If a connection has been recently set up, access its configuration settings through the *Recently Used* option. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2Fi2XGbRXRdMn83Y61zsSG%2F17.png?alt=media) 2. Select the required *Data Provider* from the drop-down list. In this example, we are exporting data to a SQL Server database table. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2F1rwGgsm7c4dd6emidhp3%2F18.png?alt=media) 3. Establish a connection with the database by filling in all the necessary details. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2FvlUEFwBEeGAo2fIx270t%2F19.png?alt=media) Click on the *Test Connection* button and Astera will notify the user upon successful connection. ![](https://627607815-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F6xzBT0roYJkfVS5klkLl%2Fuploads%2FfrzNojjRFPV5gsJNhWxU%2F20.png?alt=media) 4. Click *Next*. On this screen, either pick an existing table or create a new table for export data. In this example, we have created a new table called *Invoice\_FurnitureMart*.
Once a database table has been specified move on to other options to configure the database table as per your requirements.
* *Define Input Ports for Mapping* – defines a writing strategy for the database table for each record. For example, if the data is being exported to an existing database table then users can also enable update and upsert options for each record. * *Database Options* – deals with system-generated keys and null values. * *Data Load Options* – specifies the type of insert of your records into a destination database. Records can be inserted in bulk or each record at a time. 5. Click *Next*. This is the *Layout Builder* screen where the extracted data fields can be modified as per the requirements.
6. Click *OK*, this starts the export of data. View its job trace in the *Job Progress* window.
The export to a SQL Server database table is complete. ## **Rule-Based Filtered Export** Astera enables users to specify a criterion to export only filtered records to the destination file. This option is available when data is exported to an Excel sheet or a CSV file. 1. Select the export destination of your choice. In this example, data in a report model is being exported to an Excel sheet. On its configuration window, after specifying the properties, expand *Rule for Filtering Data*.
On expanding, users can see an expression box with built-in functions to facilitate rule-based filtering. Expand the *Order\_8* node to access the fields of the extracted data.
Now, we only want to export data that contains *SOFA* in the Items field. For that, we have to specify an expression in the expression builder. 2. In the expression builder, write *Contains(”SOFA”, Items)*. Click on the *Compile* button to verify whether the expression is successful or not.
Click *OK* to close the export settings window. 3. Now click on the *Run Selected Export* icon placed in the toolbar under the *Data Export Settings* panel.
Export will begin. Observe its trace in the *Job Progress* window.
At the end of a successful export, Astera will generate the Excel file with filtered records and provide its clickable link in the job trace.
This completes rule-based filtered export.