Data-Basics User Defined reporting tool enables users to create and modify their own reports. Rather than basing User Defined Reports directly on the tables in the database, User Defined Reports are based on database views known as User Report Queries. Advantages to this approach include:
Views can selectively include only pertinent columns (fields)
Columns from multiple tables can be automatically joined in a view.
Views can contain columns which represent a calculation (e.g. you can define a column ‘Net_Cost’ defined as ‘(Unit_Cost * Quantity).
Views can contain aggregate values, most usefully the SUM() aggregate.
Column names can be renamed to be more readable.
Security checks can be incorporated in view definitions.
Users should be familiar with Data-Basics software and with the underlying concepts of relational databases, tables and joins.
The View User Report Definitions screen is used to define User Reports. The screen is divided into three sections. The upper section is used to maintain general information about the report. The center section displays a mockup of the report; it is updated dynamically as the report columns are being defined. The lower section is used to define the columns that will be printed on the report.
User Report Id:
Uniquely Identifies the Report. This field is required.
User Report Name:
This field will be used as the report title that will appear at the top of each page.
Printing Orientation:
Select Landscape (horizontal) or Portrait (vertical).
Points:
The font size, in points (default point size is 8 points).
Sign Control:
A value of '-' results in a leading sign.
A blank value will result in a trailing sign.
If ‘(‘, negative numbers will be displayed within parentheses.
First Page Text;
The contents of this field will be printed near the top of the first page of the report.
Show Report:
Click this button to generate and display the defined report. Note if you make any changes to the report definition, Show Report will not reflect these changes until after the ‘Save’ button has been pressed.
Show SQL:
Assembles and displays the SQL corresponding to the data in the bottom window. Note if you make any changes to the report definition, Show SQL will not reflect these changes until after the ‘Save’ button has been pressed.
Export Report:
Generates an Export file that can then be imported into another database. The user can choose to export the current report, all reports, or selected reports.
Import Query:
This button will import a query from an Export file.
Width:
This determines the width of the column (in characters) on the report. A column width of zero indicates that the column is not to be printed. Zero width columns can be used when you want to provide a filter on a column but not actually print the column. A blank space is automatically inserted between columns.
Formula:
This indicates the column or expression to be extracted from the database. To display the contents of a column, double-click and select a Report Query, and then select the column from the Query.
When the user requests a User Report to be run, the report generator uses the contents of the ‘Formula’ fields to generate an SQL statement. This SQL statement is then used to extract the data for the report from the database. The report is then formatted according to instructions found in both the upper and lower windows.
J (Justification):
L: Left justify the data and the heading.
R: Right justify the data and the heading.
C: Center the heading but left justify the data.
(Blank): Defaults to ‘L’ when generating the report.
Total This Column?
Check this field if column totals for this column are to appear at the end of the report.
Filter:
To display a range filter for this column when the report is run, enter the title that should appear on the range followed by a question mark (e.g., AP Terms Id?).
This field also allows you to filter the rows returned by the database. Enter the filter in the same way you enter a filter on a Filter screen. That is, you can use commas to delimit ‘OR’ values and underscores to delimit ‘BETWEEN’ values. You can also use the ‘>’, >=’, ‘<’, ‘<=’, ‘<>’, and ‘!’ symbols at the beginning of the value.
Dates are normally entered in the form ‘yyyy-mm-dd’ in the filter. A ‘variable strategy’ dating mechanism is also available. This can be used, for example, to specify a date that will always evaluate to the last day of the prior month.
To enable others in your organization to run your user report, simply create a User Defined Function for the report, and then enable this function for the desired user groups. The following instructions provide the step-by-step details for doing so.
1. Access View User-Defined Functions.
2. Enter a unique Function Id and descriptive name.
3. Double-click on User Report Id and select the user report you just created.
4. Click the Add record button. Your report will now appear on the menu for members of User Group All.
5. In View User Groups and Function, allow access to your new report to members of the desired User Group or Groups. For reports, all check boxes (Access, Add, Change, Delete) should be selected.