Creating a New Query
To add a new query, click the New button at the top of the list. This will bring up a window that lets you enter the name and type of your report. The name will simply be a way for you to identify this particular report and the type will help you categorize the various reports in the system.
Choosing a Data Table
In order to create a report, you must have at least one table from which the information for the report is being drawn. Use the Main Table dropdown to select the table that contains the information you want (ie: if you want the report to show information about work orders, select the Work Orders table).
NOTE: Be sure to choose the correct main table. The main table can not be changed once it is selected.
When you’re finished, click the Save button.
The report you’ve just made will be opened in edit mode and you’ll be taken to the Report tab. Here’s you’ll see, and be able to change, some basic information about your report:
- Description – The name of the report. It’s displayed in large bold letter at the top of the screen.
- Comments – Enter any description here which will aid users in identifying what this report queries. If your description is clear enough, you may not need to enter anything here.
- SQL code – This will display the SQL code that the system uses to run the query. At the moment, it says “Configuration Error…” because you have not set up the report yet.
- Owner – This will default to the user logged in creating the report.
- Type – You can categorize your reports any way you wish. The most typical categorization would be by the section of the system you are querying. For example: work order reports would be type WORK, but reporting on work order charges would be CHARGES.
- Public – If you would like all users to have access to this report, make this field 'True.'
- Read Only – To prohibit anyone but the owner from editing this report, set this field to 'True.' A 'read only' report will allow users to run the report but prohibit any changes to the report properties, tables, or fields.
- Display grid lines? – To separate your report information with grid lines for easier reading, set this field to 'True.'
- Display runtime criterias? – Setting this option to 'True' will show you all the report filters in the header of the report after it is run.
- Alternate row color? – Set this option to 'True' if you would like to have alternate colors on the rows. Then use the Alternate color fields to select the colors.
- Alternate color 1 – You may type in a basic color in these fields. You may also use the color palette to choose a color or type in an HTML color code.
- Alternate color 2 – This is where you can set the second color. The rows will alternate between the two.
- Display record count? – If you would like to see a total indicating the number of records the report returned, make this 'True.'
- Display links? – Setting this to 'True' will allow you to link directly to an item from its key field.
- Specify sort at runtime? – If you need to change the sorting of your report frequently, you can set this option to 'True.' You will then choose the sorting criteria each time you run the report.
- Top X Report? – If this is turned to ‘True,’ the report will only show a set number of records that are ranked the highest, by the sorting you specify.
- Top X Count – This is used along with the Top X Report? field and allows you to set how many records will show: Top 10, Top 20, etc
- Top X Type? – This field determines whether the report will be based on a certain number of records or all records within a certain percentage.
You only need enter the information which you feel will give your report the look you want. The most important field is the Description. Without it, your users will have no idea what the report is. The other fields are optional and allow either ease of reading or use. This information can be modified at any time either from the main query page or from with the detail of the report itself.
When you are finished entering the information, click the Save button.
Clicking the Advanced Options button will bring up a screen where you can change these fields and others, including:
- Auto generate? – This field turns the Auto-Generation functionality on or off. An auto-generated report will run itself on a regular basis, based on the frequency information entered, and be emailed to the email address(es) listed. This is a great way to share information, without having to locate and manually run a specific report.
- Auto-Generation Output – When someone receives an email for an automatically generated report, the email will contain the report, in the form on an attached file. The type of file output selected for the report shows here. Reports can be run in HTML, PDF, excel, CSV, etc.
- Frequency – This field is used, along with the “Frequency Type” field, to determine how often the report should run. Enter the number of days, weeks, or months.
- Frequency Type – This is where you can decide whether the number entered in the ‘Frequency” field refers to days, weeks, or months.
- Email Addresses – Enter the email address to which the report should be sent. If you’d like to enter multiple email addresses, just separate them with a comma and no space.
- Generate at – Here, you can select a range of time, when the report will generated.
- Next Run Date – Shows the next date the report will be auto-generated, based on the frequency defined. When setting up the report to auto-generate, for the first time, you’ll need to select a date, so it will know when to start.
- Last Executed– This shows the last date this report was auto-generated.
- Auto Generate Destination – When configuring a report to automatically generate, this field determines if the report will be sent via email or using FTP.
- Server Address – This is where you can enter the address to the server where the file will go.
- User Name – This stores the user name for accessing the server.
- Password – This stores the password for accessing the server.
- Port – Specifies an address and port to which the server should connect
- FTP Passive Mode – Specifies if the FTP connection should be an Active or Passive connection.
- Server Path – Enter the file path to the location on the server where this report will be saved.
NOTE: To configure a report to generate using FTP, you’ll also need to populate the aforementioned fields: Auto generate, Auto-Generation Output, Frequency, Frequency Type, Generate at, Next Run Date, and Auto Generate Destination.