IRP Admin
Common Tasks (1)
Open
Interventions (1)
Open
Languages (1)
Open
Questions & Answers (1)
Open
Reviews (1)
Open
Site Scripts (1)
Open
Web Services (1)
Open
Training Videos
Peer Support

SQL Editor

You can find this section under Programming Utilities > SQL Editor in the IRP Admin left navigation menu.

This section shares some functionality with the Reports section. You can use the SQL Editor to:

  • Add a Report
  • Edit a Report
  • Run a Report
  • Download Report Data
  • Run SQL

Reports allow you to pull information from your IRP database on any aspect of your IRP instance with a few exceptions for sensitive information such as passwords, card details, and so forth. You can create and run Reports within the IRP or export them as a delimited text file, which can then be opened with your spreadsheet software for formatting and further analysis. To create reports, you will require knowledge of the SQL programming language and an understanding of the structure of your IRP’s database.

If your Admin User account has DBA access, you will be able to create and run SQL scripts to pull data from the database quickly.

We strongly advise you speak to your Support Team with regards to setting up and editing Reports or running SQL scripts.

How To Guides (6)

Close
Open
How To Add a Report
Close
How To Edit a Report

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 dropdown and click the 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
    Report 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 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 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 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 button to save it.
Open
How To Download Report Data
Open
How To Run a Report
Open
How To Run a SQL Script
Open
How To Add an Email Report

Copyright © 2020 IRP Commerce. Use of this website constitutes acceptance of the IRP World Terms of Use, IRP Privacy Policy and IRP Cookie Policy

IRP Commerce Limited, Concourse 3, Catalyst, BT3 9DT, UK. Company Number: NI 041856. VAT Number: GB 888249658
A Deloitte Fast 50 Company seven times: 2010, 2011, 2012, 2013, 2014, 2018 & 2019