Reports

To add or edit a report, follow these steps:

1. Go to Programming Utilities > Reports in the IRP left navigation menu.
2. To add a new report, click the Add New Report button on the right side of the page.
   To edit an existing report, click the Edit button at the right side of the report you want to edit.
3. Enter or edit the following details:

   Setting	Description
   Report Details
   Report ID	The unique IRP identifier for this report. This is generated automatically when adding a report.
   Report Title	The title/name used to identify this report within your IRP.
   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 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 or Edit 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.
   Report Parameters
   You can create parameters for use in your reports. This can make it easier for other Admin Users to quickly configure and update reports. For details see How To Use Parameters In Reports.
   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 allows 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 this section.
   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	Choose a format to generate a public Feed URL for this downloadable report.
   File Extension	The file extension for the report, e.g. csv.
   File Name	The name of the report file.
   Append Date to File Name	Append Date to the File Name
   Include Column Names	Include Column Names in the file.
   Text Qualifier	The Text Qualifier, e.g. "
   Feed URL	The public download URL for this Feed / Report. Note that each of the report parameters must be specified for the feed URL.
   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. Quick Reports can be accessed by clicking the button in the bottom navigation of the IRP Admin.

4. At this point it is a good idea to test that the SQL query is valid and is returning the required information. 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 Script.
5. When you are happy with the current SQL Script, click on the Insert Report or the Update Report button to save it.

Often you would use SQL parameters to protect against SQL Injection but you can also create parameters to make it easier for Admin Users to run reports.

As an example, if you wanted to create a report on all active models that have out-of-stock items, you could enter the following in the 'Report SQL' box on the ReportEdit.aspx page without making use of any parameters:

To create parameters for other Admin Users to use in this report, you could proceed as follows:

1. On the ReportEdit.aspx page, scroll down to the Report Parameters section.
2. Enter a suitable Parameter Name. In this example we will create two parameters called InStock and Active. DO NOT include the @ character.
3. Select the appropriate Data Type for the parameter — NVarChar (string), Boolean (Bit), Int (Integer), Decimal or DateTime. We will use Boolean for 'Active' and Integer for 'InStock'.
4. If relevant, you can also enter a Default Value and a Length for the parameter.
5. Click the Add Parameter button. The parameters will now be listed in a grid within the Report Parameters section and also under the Report SQL section at the top of the page.
6. Amend the SQL in the Report SQL section so that the parameters are included. Here is our example (the parameters are indicated with a '@' symbol):
   
7. Click the Update Report button.
8. Enter a value for the parameters in the parameter boxes under the Report SQL section. For our example, we are entering 0 for 'InStock' and ticking the box for 'Active', as shown in the following screen capture:
   
   
   
9. Click the Run button. You will now see the results in the Report Results section at the bottom of the page:
   

There are additional steps involved if you are updating an existing report where the parameters are defined as part of the report. In this case, any parameters are normally defined at the start of the SQL, including the Data Type, for example:

DECLARE @StartDate DATETIME

You need to remove any such parameters that are defined as part of the SQL once you have added the parameters outside of the SQL using the Report Parameters section (as described above) — otherwise, when you run the report, errors will be thrown.

1. Go to Programming Utilities > Reports in the IRP left navigation menu.
2. Click the Run button at the right side of the report that you want to run.
   If you have saved the report in your Quick Reports panel, you can also run the report from there.
3. The results will be displayed in the Report Results section at the bottom of the page.

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 > Reports in the IRP left navigation menu.
2. Click the Download button at the right side of the report that you want to download.
   If you have saved the report in your Quick Reports panel, you can also download the data from there.
3. The report data CSV file will be downloaded to your local Download folder. Your browser should show you the file download progress.
4. Once downloaded, you can open the report in your third-party software for viewing/administration.

To add a report to your Quick Reports access panel, follow these steps:

1. Go to Programming Utilities > Reports in the IRP left navigation menu.
2. Click the Edit button at the right side of the report that you want to add to your Quick Reports access panel.
3. Scroll down to the Quick Access Users section and click the Add button to move your name from the ‘Non-Added Admin Users‘ box to the ‘Added Admin Users‘ box.
4. Click the Update Report button.
5. When you have added at least one report to the Quick Reports access panel, you can subsequently access your reports by clicking the button in the bottom navigation of the IRP Admin. This allows you to quickly access reports that you run on a regular basis. When you click the button, the lightbox appears as follows:
   
   
6. To remove a report from your Quick Reports access panel, click the Remove button to move your name from the ‘Added Admin Users‘ box to the ‘Non-Added Admin Users‘ box. You can also remove the report using the Remove button shown in the screen capture above.

You can create reports to allow third parties, who do not have access to your IRP, to access your data. This allows them to access authorised and specific information relating to Customers, Products, Orders and so forth.

These reports tend to be used for smaller bespoke third-party companies as the big companies such as Google, Affiliate Window, Ciao, Clix Galore and so forth are covered in the Feeds section.

To set up a Data Feed Report, follow these steps:

1. Go to Programming Utilities > Reports in the IRP left navigation menu.
2. To add a new Data Feed Report, click the Add New Report button at the right side of the page.
   To edit an existing Data Feed Report, click the Edit button at the right side of the report that you want to edit.
3. Enter or edit the following details:

   Setting	Description
   Report Details
   Report ID	The unique IRP identifier for this report. This is generated automatically when adding a report.
   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 that will return Order Data for the last 24 hours database could be called 'Data Feed - Orders Over Last 24 Hours'.
   Report SQL	The Microsoft SQL Code for this report. The format of the Data Feed will be defined by the third party. This will define what fields are required and in order and any other features such as column headers being output, all fields being encased in quotation marks and so forth. You must ensure that the data being retrieved by the report is valid and in the correct format. If you have any questions, please contact your Support Team.
   Report Type	This should be set to Public.
   Description	Description of this report to be displayed to Admin Users.
   Public Download Options
   Allow Download	Must be checked for Data Feed Reports.
   Username	Used to identify user/system for report. Can be any value (e.g. default) or specific (e.g. affiliateWindow). This will be required in the URL.
   Password	Used to identify user/system for report. Can be any value (e.g. defaultPassword) or specific (e.g. affiliateWindowPassword). 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	The File Format/Delimitation Method used to separate fields in the file. Note that this field is accessible only  when editing a Data Feed Report.
   Feed URL	The public download URL for this Data Feed Report. Note that this field is accessible only  when editing a Data Feed Report.
   Quick Access Users
   Note that this section is available only 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. Quick Reports can be accessed by clicking the button in the bottom navigation of the IRP Admin.

4. At this point it is a good idea to test that the SQL query is valid and is returning the required information. 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 Script.
5. When you are happy with the current SQL Script, click the Insert Report oar the Update Report button to save it.
6. You must now copy/update the URL from which the Data Feed can be accessed. Once the report has been saved, click the Edit button at the right side of the report row. Scroll to the Public Download Options section and copy the Feed URL value. Paste the value into an external editor window.
7. The URL will consist of the following components and will be generated automatically based on the information you have set in the Public Download Options section:

   URL Component	Description	Example
   Domain	Your Domain Name with the Feed.aspx path.	http://www.yourIRP.com/Feed.aspx?
   Report ID	The unique IRP identifier for this report.	ReportID=105
   Username	The Username defined in the Public Download Options section.	&Username=ExampleUsername
   Password	The Password defined in the Public Download Options section, if any.	&Password=ExamplePassword
   File Name	The Name of the File that will be created. This can be modified within the URL.	&FileName=reportID105_
   Append Date	Whether or not the File Name will have a timestamp added to it.	&AppendDateToName=true &AppendDateToName=false
   Save	Defines whether the data will be output in the browser (false), or downloaded (true). We advise that the default value of 'true' is used.	&Save=true &Save=false
   Format	The Format defined in the Public Download Options section.	&Format=comma &Format=tab &Format=pipe
   File Extension	The corresponding File Extension to match the Format defined in the Public Download Options section.	&Extension=csv &Extension=tsv &Extension=txt

   URL Feed Example:
   http://www.yourIRP.com/Feed.aspx?ReportID=105&Username=ExampleUsername&Password=ExamplePassword
   &FileName=reportID105_&AppendDateToName=true&Save=true&Format=tab&Extension=tsv
   Note that you can change these settings as necessary, as long as an accepted value shown in the table above is used, and the feed should be entered as one continuous URL.
8. Test that the feed works by copying the URL into the address bar of your browser and pressing the Enter key on your keyboard. If the report has been set up correctly and the feed URL is correct, the file should download.

Email Reports are used 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 > Reports in the IRP left navigation menu.
2. To add a new Email Report, click the Add New Report button on the right side of the page.
   To edit an existing Email Report, click the Edit button at the right side of the report that you want to edit.
3. Enter or edit the following details:

   Setting	Description
   Report Details
   Report ID	The unique IRP identifier for this report. This is generated automatically when adding a report.
   Report Title	The title/name used to identify this Email 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'.
   Report SQL	The report will consist of multiple lines like the one 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	Select the type of the report, in this case: Email.
   Description	Add a description of this report to be displayed to Admin Users.

   Note that, as this is not a Data Feed Report, you do not need to enter any information in the Public Download Options section.
4. At this point it is a good idea to test that the SQL query is valid and is returning the required information. 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 Script.
   
5. When you are happy with the current SQL Script, click the Insert Report button or the Update Report button to save it.