You need a working knowledge of SQL to build reports.
Create a NEW Report
To create a new report requires that you have a working knowledge of SQL.
If you have editor permission you will see a button ADD NEW.
Click on this button and complete the fields (all are required):
Report Name: Short report name
Report Group: Enter a name for grouping reports
Description: Enter a long description to explain the report
Role Permissions: Enter a list of roles separated by commas that have access to the report.
Once you click SAVE the report will be added to the list.
TIP - when reports are being developed use a permission of Draft that doesn't exist in the role setup. This will make sure no one except those with EDITOR permission can see the report on the list. Once the report is finalized, change the permissions to the roles required.
Once the report is listed, then check Select Report. The report will open in a new tab.
Report SQL Editor
Click on the OPEN EDITOR button.
This will open a window where you can enter the SQL program.
Best practice is to develop the program in an outside SQL editor then paste the code into the top text box.
Once you have pasted the SQL click REFRESH. This will run the SQL and return the results in the lower half of the screen.
Any messages such as errors are displayed in the SQL Message Log tab.
Once you have a working report click SAVE.
Modifying Column Headings
When the report runs it will return column names as defined in the database. Sometimes these column headings are difficult to understand for users or less than ideal. You can modify the column headings as follows:
- Click on COLUMNS button on the right.
- Select a column using the drop down. You can then change the label.
Creating Parameters
You can create a parameter that can be inserted into the SQL when it runs.
First you need to modify your where clause by putting {} brackets around a parameter name.
For example if you have a where clause:
Where Customer_code = '123'
Change this to:
Where Customer_code = {Customer Number}
The system will create the parameter input field on the top of the screen.
You can then click on the PARAMETERS button on the right and define various settings for the parameter:
- Define the Parameter Label
- Make the entry field required before the report will run
- Set the parameter type to one of Text, Date, Number.
- Set a default value for the parameter
Enter the parameters and click REFRESH to test your report.
Once everything is working click SAVE.
If you still have the report in DRAFT role, go back to the report list and assign the correct roles.
Your report is now active.