Reporting - Interactive SQL Queries

Reporting - Interactive SQL Queries

INTRODUCTION

In agility the user can interact direct with the database. Obviously this needs knowledge of the database structure but this can provide a user with information very quickly.

STARTING SQL

To start SQL go to Reporting\Interactive SQL Queries and this window opens:


Where it says SQL statement this is where the user can type and paste content.
If, the user wants to save this query then go to File\Save\Enter a name for the query and select OK
To open select the yellow folder and choose from the list.

In SQL the where clause lets the user reduce what information is being returned.
The standard format for SQL:
SELECT- This is where you are listing the fields in the database required or use a wild card * for ALL columns
FROM - The table that holds the data
WHERE - The filter that allows the user to return only certain bits of information rather than everything.

SAVE and OPEN QUERY

Many times a query may be sent to help a company these can be chargeable.
Once a query has been sent these can be copied and then  pasted into the above screen.
The user can then SAVE the query by selecting File - SAVE and a window opens to enter a name for the script.


Once name is entered press OK

To open queries that have been saved go to Reporting - Interactive Queries
Then File - OPEN
From the list select the query required


Once highlighted press SELECT

EXAMPLE

The feature also allows a Date and Stock Parameter if needed using the following format.
trandate between  <"Enter Start Date DD-MON-YY"'01-JAN-04'> and <"Enter End Date DD-MON-YY"'31-DEC-04'> and s.stkcode = <"Enter Stock Code"'Invalid Code'>
Trandate is the field
<"Enter Start Date DD-MON-YY"'01-JAN-04'> This is used so the user can enter a start date
<"Enter End Date DD-MON-YY"'31-DEC-04'> This is used so the user can enter an end date
Stkcode -s the field
= <"Enter Stock Code"'Invalid Code'> This is used to create the parameter

    • Related Articles

    • Purchases - Invoice Query Reasons

      INTRODUCTION When an invoice from a supplier is entered it is possible to flag it for authorisation by setting a query flag. This could be the case for example if prices were different to those on the Purchase Order or different items supplied. QUERY ...

    • Images - Why are Images not changing?

      Introduction Images in Agility Professional can be overwritten. A new image will replace the old image, if the name and file type is correct. If you find images not being overwritten, follow the steps below to check everything is correct. For a full ...

    • Update Stock Records - Supplier

      Change a supplier from one currency to another and transfer stock Create a new supplier account with the currency recording what the new account is To update the stock records with the new supplier code follow the instructions: Go to ...

    • Stock - Images in Agility Professional

      INTRODUCTION The Agility system supports images for stock codes. These images are held in a cloud location (Amazon S3 bucket). Before images can be added please contact Agility as changes need to be made to your server. Agility will then let you know ...

    • Nominal - Cost Centres

      INTTRODUCTION Cost centres give the business another layer of analysing how certain areas of business are costing. By looking at these cost centres can help a business make decisions on the how to reduce these ongoing costs. SET UP To create a Cost ...