SQL Editor

To add a report, follow these steps:

1. Go to Programming Utilities > SQL Editor in the IRP  left navigation menu.
2. Scroll to the SQL Editor section and enter the following details:

   Setting	Description
   SQL	The Microsoft SQL code for this report. You can enter the SQL query in the SQL Editor window. Depending on user preference, you may find it easier to create the query in an external editor and paste the finished query into the SQL Editor window once finished. Example query: The following SQL query will return the Model IDs for all Active Models in your IRP's database: SELECT ModelID FROM   Models WHERE  Active = 1
   Report Type	Type Description Public This is the default Report Type. We advise that all reports, except email reports be set to this. Private As implied, this report type will be private. DeleteInsertUpdate Can be used to differentiate reports which use DELETE, INSERT or UPDATE commands. These reports can be created as Public if desired. Correction Can be used to differentiate reports which correct data. These reports can be created as Public if desired. Email Email Reports allow you to define a select set of email addresses to send a particular email to. More information on creating an Email Report can be found in the How To Add an Email Report article. System Can be used to differentiate reports which perform system related tasks. These reports can be created as Public if desired. TradeTalk Can be used to differentiate reports which perform TradeTalk tasks. These reports can be created as Public if desired.
   Report Title	The title/name used to identify this Report within your IRP. We advise that titles include the area and the operation. For example, a report which will return all of the Model IDs for Active Models in your IRP's database could be called 'Models - All Active Models'. A report which will return all customers who have placed at least three orders with a minimum spend of at least £200 could be called 'Customers - who have placed at least 3 orders with min spend of £200'.
   Save As Report	This must be checked to save the SQL query entered in the SQL section as a report. Otherwise the query will just run.

3. Click the Run SQL button to run the SQL query.
   Any errors with the query will be highlighted at the top of the page:
   
   If the query is valid, it will run and the results will be displayed in the Report Results section once finished.
   If you have checked the Save As Report checkbox, this will also save the report.
4. This report can now be selected from the drop down and run as described in the How To Run a Report article in this section.

To edit a report, follow these steps:

1. Go to Programming Utilities > SQL Editor in the IRP  left navigation menu.
2. Select the report that you want to edit from the drop-down list and click the Edit Report button.
   
3. You can edit the following details for a report:

   Setting	Description
   Report Title	The title/name used to identify this report within your IRP. We advise that titles include the area and the operation. For example, a report which will return all of the Model IDs for Active Models in your IRP's database could be called 'Models - All Active Models'. A report which will return all customers who have placed at least three orders with a minimum spend of at least £200 could be called 'Customers - who have placed at least 3 orders with min spend of £200'.
   Report SQL	The Microsoft SQL code for this Report. You can enter the SQL query in the SQL Editor window. Depending on user preference, you may find it easier to create the query in an external editor and paste the finished query into the SQL Editor window once finished. Example Query: The following SQL query will return the Model IDs for all Active Models in your IRP's database: SELECT ModelID FROM   Models WHERE  Active = 1
   Report Type	Type Description Public This is the default Report Type. We advise that all reports, except email reports be set top this. Private As implied, this report type will be private. DeleteInsertUpdate Can be used to differentiate reports which use DELETE, INSERT or UPDATE commands. These reports can be created as Public if desired. Correction Can be used to differentiate reports which correct data. These reports can be created as Public if desired. Email Email Reports allow you to define a select set of email addresses to send a particular email to. More information on creating an Email Report can be found in the 'How To: Create an Email Report' article. System Can be used to differentiate reports which perform system related tasks. These reports can be created as Public if desired. TradeTalk Can be used to differentiate reports which perform TradeTalk tasks. These reports can be created as Public if desired.
   Description	Description of this report to be displayed to Admin Users.
   Public Download Options
   Note that these settings should only be used if the report is to be used as a data feed, available to download by third parties with no IRP access. This will allow them to access authorised and specific information relating to Customers, Products, Orders and so forth. For further details, see the How To Add or Edit a Data Feed Report article in the Reports help topic.
   Allow Download	Should this report be available for download through a parameterised Feed.aspx URL.
   Username	If this report has been made downloadable, a username is required. This will be required in the URL.
   Password	An optional password for downloading this report. If included, this will be required in the URL. The password provides an opportunity for additional security.
   Format	This sets the File Format/Delimitation for the download.
   Feed URL	The URL at which the download can be accessed.
   Quick Access Users
   Note that this section is only available when editing a report. It allows you to modify which users have access to a report in their Quick Reports panel. To grant a user access to this report in their Quick Reports panel, highlight their username in the Non-Added Admin Users column and click the Add >>> button. To remove a user's access to this Report in their Quick Reports panel, highlight their username in the Added Admin Users column and click the Remove <<< button. You can access Quick Reports by clicking the button in the bottom navigation of the IRP Admin.

4. Any changes to the SQL query should be tested before being saved. To do this, click the Run Report button.
   Any errors with the query will be highlighted at the top of the page:
   
   If the query is valid, it will run and the results will be displayed in the Report Results section once finished. You can then decide if any changes need to be made to the SQL query.
5. When you are happy with the updated SQL query, click the Update Report button to save it.

You can download report data and open the data in third-party spreadsheet software for viewing/administration.

To download a report, follow these steps:

1. Go to Programming Utilities > SQL Editor in the IRP left navigation menu.
2. Click the Download button at the right side of the report that you want to download.
3. Select the report data you want to download from the dropdown.
   
4. Select the File Format/Delimitation for the download in the second dropdown.

   Delimitation	Description	Example
   Comma Delimited	Output will be delimited by commas.	5772,17178,28.00
   Tab Delimited	Output will be delimited by tabs.	5772    17178    28.00
   Pipe Delimited	Output will be delimited by pipes.	5772|17178|28.00
   Text Export	Output will be delimited by tildes. New lines will be delimited by percentage symbols.	5772~~~~~~17178~~~~~~28.00%%%%%%

5. Click the Download Report button.
6. The report data file will be downloaded to your local Download folder. Your browser should show you the file download progress.
7. Once downloaded, you can open the report in your third-party software for viewing/administration.

To run a report in your IRP Admin, follow these steps:

1. Go to Programming Utilities > SQL Editor in the IRP  left navigation menu.
2. Select the report you want to run from the dropdown and click the Run Report button.
   
3. The results will be displayed in the Report Results section at the bottom of the page.

If you want to query your database quickly, you can easily create and run a SQL script.

To run a SQL script in your IRP Admin, follow these steps:

1. Go to Programming Utilities > SQL Editor in the IRP left navigation menu.
2. Enter the SQL script in the SQL text box of the SQL Editor section. Depending on user preference, you may find it easier to create the script in an external editor and paste the finished script into the SQL text box once finished.
   Example script:
   The following SQL script will return the Model IDs for all Active Models in your IRP's database:
   
   SELECT ModelID
   FROM   Models
   WHERE  Active = 1
3. Click the Run SQL button to run the SQL script.
   Any errors with the script will be highlighted at the top of the page:
   
   If the script is valid, it will run and the results will be displayed in the Report Results section once finished.
   If you have checked the Save As Report checkbox, this will also save the report.

You use Email Reports to send emails to a specific set of customers.

Note that email reports are also one of the Segmentation features in the IRP Marketing Cloud (M-Cloud) section.

To create an Email Report, follow these steps:

1. Go to Programming Utilities > SQL Editor in the IRP left navigation menu.
2. Scroll to the SQL Editor section and enter the following details:

   Setting	Description
   SQL	The report will consist of multiple lines such as those shown below: SELECT -1 AS MailingListID,'emailaddress2@domain2.com' AS EmailAddress UNION SELECT -1 AS MailingListID,'emailaddress3@domain3.ie' AS EmailAddress UNION SELECT -1 AS MailingListID,'emailaddress5@domain5.com' AS EmailAddress Each line will contain an email address for a specific customer. The last line should not have a UNION command as there are no further statements to combine. Depending on user preference, you may find it easier to create the script in an external editor and paste the finished script into the SQL text box once finished.
   Report Type	This should be set to Email.
   Report Title	The title/name used to identify this report within your IRP. We advise that titles include the area and the operation. For example, a report which will be used to send emails to all Australian Customers could be called 'Emails - All Australian Customers'.
   Save As Report	This must be checked to save the SQL query entered in the SQL section as an Email Report. If you do not save the report, you will not be able to use it to send emails.

3. Click the Run SQL button to run and save the SQL query.
   Any errors with the query will be highlighted at the top of the page:
   
   If the query is valid, it will run and the results will be displayed in the Report Results section once finished.
   If you have checked the Save As Report checkbox, this will also save the report.