Report Builder

General Information

Introduction
Technical Support
How To ...
Database Knowledge
Aliases
Using APS Example Data

Report builder TM is a trademark of Yasser Almohanna, Kuwait

This manual was created with the free trial version of HelpScribble.
This notice will not appear in manuals created with the full version.

Introduction

APS products is full and reach with reports more that you could imagine , but sometimes a client might need some reports that the APS applications does not have thats where APS Report Builder comes.
Report Builder provides end users with a quick and easy way of creating attractive reports and ad-hoc queries for databases. The results of the database query can be exported to MS Excel for example, or integrated into APS Applications .
it is a powerful tool to build reports and to query your data , it also helps you to manipulate your data (update,modify,delete and insert) .
APS Report Builder uses the ANSI-standard Local SQL to manipulate the APS data , by using this query language you can create any reports you want.
once you create a report or a query you can save it as a part of your application reports menu , so you can call it back again any time by simply pull down menu and click .

For a complete introduction to ANSI-standard SQL, see one of the many available third-party books.

Highlights

Easy to use , reliable, and very flexible

You can use Expressions beside the SQL to have a very powerful solution for your reports

Easy to manipulate your custom reports by adding and deleting reports from the menu

Preview the query result before printing the report .

Export your custom reports to Excel so you can change it's colors and modify it to fit your needs.

Adding the report name,date and grid option.

General Information

This manual was created with the free trial version of HelpScribble.
This notice will not appear in manuals created with the full version.

Technical Support

We will be more than happy to assist you with any problems you may have with APS products. We will get back to you as soon as possible. Before submitting a query or bug report, be sure to check that the information is not already provided in the Frequently Asked Questions page at:

http://Advanced.servehttp.com/APS_006.htm

otherwise write down the error details and the number you have on the screen and send it through Services Page | Feed Back section at:

http://Advanced.servehttp.com/APS_006.htm

General Information

This manual was created with the free trial version of HelpScribble.
This notice will not appear in manuals created with the full version.

How To ...

Use Report Builder
Use Expressions
Write SQL scripts
General Information

This manual was created with the free trial version of HelpScribble.
This notice will not appear in manuals created with the full version.

Use Report Builder

The following declarations helps you to know each option in the report builder and how to use it .

1. Select Alias (Specific Accounts)

This option is to Select Specific Accounts and it's transactions for a company and the financial year belongs to this company.
simply Select from the Main Screen the Alias name which represents the Company Number and it's financial year from the alias combo box .

See Related Topics below for further alias definitions .

Figure 1.0 shows the Alias Names .

2. Preview Table Names and Table Columns

Once you select the alias, the table names will appear in the table combo box .
by double clicking on the table name it will be inserted in the SQL syntax automatically , also once you click on one of the table names the column names for this table will be previewed in the column list .
by double clicking on the column name it will be automatically inserted in the SQL syntax .

Figure 1.1 shows the Table Names .

Figure 1.2 shows the Column Names .

3. Writing SQL Syntax

In the SQL Syntax Editor you can write your own SQL scripts , the APS report builder include some common SQL commands in a list to help you use it to write your own SQL scripts , also the Table Names and Columns appears for more assistance .
once you write valid SQL syntax press F9 or Click on Execute Query to run the SQL script and the result will appear in a grid .

Figure 1.3 shows the Report Options dialog .

6. Printing and saving the Report

After you adjust the report options, you are ready to print and save the report , by pressing the Print Report button the report will be previewed and printed on screens.

Use the Menu / Save Report to save the report to your Application Report Menu as shown below .

Figure 1.4 shows a custom report appears in the applications Report Menu .

7. Exporting Report to Excel

After you Execute your Query and see the result appears in the grid , you can easily export the report to the Excel .
simply edit the Excel File name as show below and press Export to Excel Button .

This manual was created with the free trial version of HelpScribble.
This notice will not appear in manuals created with the full version.

Use Expressions

Expression is an expression that includes one or more of the summary operators in the following table:

Operator    Use
------------------------------------------------------------------------------------------------------------------------
Sum    Totals the values for a numeric field or expression
Avg    Computes the average value for a numeric or date-time field or expression
Count     Specifies the number of non-blank values for a field or expression
Min     Indicates the minimum value for a string, numeric, or date-time field or expression
Max              Indicates the maximum value for a string, numeric, or date-time field or expression

The summary operators act on field values or on expressions built from field values .You can create expressions by using operators on summarized values with other summarized values, or on summarized values and constants. However, you can't combine summarized values with field values, because such expressions are ambiguous (there is no indication of which record should supply the field value.) These rules are illustrated in the following expressions:

Sum(Qty * Price)                           legal summary of an expression on fields
Max(Field1) - Max(Field2)            legal expression on summaries
Avg(Discount rate) * 100              legal expression of summary and constant
Min(Sum(Field1))                         illegal nested summaries
Count(Field1) - Field2                  illegal expression of summary and field

General Information

This manual was created with the free trial version of HelpScribble.
This notice will not appear in manuals created with the full version.

Write SQL scripts

What is local SQL?

Local SQL is the subset of the SQL-92 specification used to access Database tables.

The local SQL language set

The SQL statements fall into two categories: Data Manipulation Language (DML) and Data Definition Language (DDL).

DML consists of SQL statements used for retrieving, inserting, updating, and deleting table data. SELECT is a DML statement.

DDL consists of SQL statements used for creating, altering, and deleting tables, and for creating and deleting indexes. CREATE TABLE and DROP INDEX are DDL statements.

in APS Report Builder you don't need to use the DDL otherwise you could corrupt your data , the following lines will give you some few samples on using DML SQL statements on APS Tables .

DML Statement List

Local SQL supports the following data manipulation language (DML) statements:

DML Statements          Description

SELECT                      Retrieves existing data from a table.
DELETE                      Deletes existing data from a table.
INSERT                       Adds new data to a table.
UPDATE                     Modifies existing data in a table.

Using SQL Examples with APS Tables

for APS Tables definitions refer to
Database Knowledge

SELECT statement

Use the SELECT statement to retrieve zero, one, or multiple rows into a dynamic result set. The rows retrieved may come from one or multiple tables (the latter referred to as a "join"). The statement may retrieve data from all or only some of the columns in the source table(s).
SELECT statement to retrieve data from multiple tables (joining tables).

The FROM clause identifies the table(s) from which data is retrieved.

The following statement retrieves data from two columns (Account No and Account Name)

SELECT acc_no,Acc_Aname
FROM Accdata

Use DISTINCT to limit the retrieved data to only distinct rows. The distinctness of rows is based on the combination of all of the columns in the SELECT clause columns list. DISTINCT can only be used with simple column types like CHAR and INTEGER; it cannot be used with complex column types like BLOB and memo.

SELECT DISTINCT PartNo, EXTRACT(YEAR FROM TrDate) AS Yr
FROM Items

DELETE statement

Use DELETE to delete one or more rows from one existing table per statement.

DELETE FROM "Employee.db"

The optional WHERE clause restricts row deletions to a subset of rows in the table that meet a logical criteria. If no WHERE clause is specified, all rows in the table are deleted. The statement below deletes all of the rows from the EMPLOYEE table if the value in its EmpNo column is found in the result set produced by a SELECT subquery against the OLD_EMPLOYEE table.

DELETE FROM "Employee.db"

WHERE (Empno IN (SELECT Empno FROM "Old_Employee.db"))

The DELETE statement only supports SELECT subqueries in the WHERE clause. References to tables other than the one from which rows are deleted or columns in such tables are only possible in SELECT subqueries.

INSERT statement

Use the INSERT statement to add new rows of data to a single table.

Use a table reference in the INTO clause to specify the table to receive the incoming data.

The columns list is a comma-separated list, enclosed in parentheses, of columns in the table and is optional. The VALUES clause is a comma-separated list of update atoms, enclosed in parentheses. Unless the source of new rows is a SELECT subquery, the VALUES clause is required.

If no columns list is specified, incoming update values (update atoms) are stored in fields as they are defined sequentially in the table structure. Update atoms are applied to columns in the order the update atoms are listed in the VALUES clause. There can be fewer update atoms than there are columns in the table, but there cannot be more.

If an explicit columns list is stated, incoming update atoms (in the order they appear in the VALUES clause) are stored in the listed columns (in the order they appear in the columns list). NULL values are stored in any columns that are not in a columns list. When a columns list is explicitly described, there must be exactly the same number of update atoms in the VALUES clause as there are columns in the list.

INSERT INTO "Customer.db"

(CustNo, Company)

VALUES (9842, "Inprise Corporation")

To add rows to one table that are retrieved from another table, omit the VALUES keyword and use a subquery as the source for the new rows.

INSERT INTO "Customer.db"

(CustNo, Company)
SELECT CustNo, Company
FROM "OldCustomer.db"

UPDATE statement

Use the UPDATE statement to modify one or more column values in one or more existing rows in a single table per statement.

Use a table reference in the UPDATE clause to specify the table to receive the data changes.

The SET clause is a comma-separated list of update expressions. Each expression comprises the name of a column, the assignment operator (=), and the update value (update atom) for that column. The update atoms in any one update expression may be literal values, singleton return values from a SELECT subquery, or calculated values. Subqueries supplying an update atom for an update expression must return a singleton result set (one row) and return only a single column.

UPDATE Employee

SET Salary = 1000
Where EmpNo=5

When update atoms come from a SELECT subquery, one SELECT statement must be provided for each column being updated in the target table. Each SELECT subquery providing an update atom must be enclosed in parentheses. The following statement updates the two columns OnHand and InventoryDate in the table INVENTORY, each with a separate SELECT subquery.

The optional WHERE clause restricts updates to a subset of rows in the table. If no WHERE clause is specified, all rows in the table are updated using the SET clause update expressions.

SQL Reserved Words

Below is an alphabetical list of words reserved by local SQL. Avoid using these reserved words for the names of meta data objects (tables, columns, and indexes). An "Invalid use of keyword error" occurs when reserved words are used as names for objects. If a meta data object must have a reserved word as it name, prevent the error by enclosing the name in quotation marks and prefixing the reference with the table name.

The following are operators used in local SQL. Avoid using these characters in the names of meta data objects.

||, -, *, /, <>, <, >, ,(comma), =, <=, >=, ~=, !=, ^=, (, )

UnSupported Language

All previous Examples are abstracted from Borland Local SQL Help File with some modifications to work with APS Tables.

For a complete introduction to ANSI-standard SQL, see one of the many available third-party books.

General Information

This manual was created with the free trial version of HelpScribble.
This notice will not appear in manuals created with the full version.

Data Base Knowledge

the APS data is stored in a Database (Paradox) you have to know some knowledge about it i.e. using DataBase Navigator Bar , Database structure and column fields and SQL Language, the following topics introduce some Jargons and common considerations when designing a Custom reports :

A . using the Navigator Bar :

     1    2    3    4
Declarations of Navigator Bar :
1 . First Record .
2 . Prior Record .
3 . Next Record.
4 . Last Record.

B . Database Structure :
When designing a report , you must understand how the data is structured. Based on that structure, you can then design a custom report .

Database
is an organized collection of information; in Database Desktop, a collection of related tables and queries in a given directory, An Alias Represents a path for stored database for a given directory .

See Related Topics below for Alias further declaration.

Table
is a structure made up of rows (records) and columns (fields) that contains data.

Record

A horizontal row in a data table that contains a group of related fields of data.

Field
is a column of information in a table. A collection of related fields makes up one record.

C . SQL

Is a Structured Query Language (abbreviated SQL and commonly pronounced "sequel"). The standard language for storing and manipulating data in relational databases.

D . APS Tables

To work with Report Builder you have to know the APS Tables :

APS Accounting Tables
AccData    : Account Table.
AccState   : Statement of Account Table.
CostData   : Cost Center Table.
BSGroup   : Balance Sheet Groups Table.
GlData      : General Voucher Data (Master) .
GLTrData : General Voucher Data (Detail)
RecData   : Accounts Payable&Recievable Tables (Master) .
RecTrData : Accounts Payable&Recievable Tables (Detail) .
WorkP      : Work and Progress Table.

APS Stock Control Tables
StoreData : Stores Table.
Vendors    : Vendor Table.
Employee : Employee Table.
Customer : Client Table.
MItems     : Master Items Table.
Parts        : Items Table .
Orders      : Order Table (Master).
Items        : Orders Table (Detail).

Related Topics:
Aliases
Use Report Builder
Database Knowledge
Use Expressions
Write SQL scripts
General Information

This manual was created with the free trial version of HelpScribble.
This notice will not appear in manuals created with the full version.

Aliases

An alias is a name and a set of parameters that describe a network resource. APS applications uses aliases to connect with shared databases.
you can also say that the Alias is a shortcut for along path .
i.e.
Alias Name Path
-----------------------------------------------------------------------------------
A11947 c:\ourprogs\fmyeaccprg2\stock\data\1\1947

Figure 1.0 Showing the A11947 Alias Definition, this is the path where your 1947 Data Stored on your (c) Drive .

As you can see the (A11947) Alias name is a combination of letters and numbers to be described as follows :
A: pronounce for APS Application .
1 : is the Company Number .
1947:is the financial year for this company .

APS Aliases Examples :

Figure 1.1 Showing the Databases Aliases .

A11947 : An Alias for the AAPS example data
A12001: An Alias points to your Stock Control and Accounting 2001 data
A12002: An Alias points to your Stock Control and Accounting 2002 data
EaMaster : An Alias points to the Years definintions Created for each company
EmptyData : An Alias points to the Empty data for APS applications

Each Financial year created by APS as an Alias define it's paths on your local or network drive .

Note :
* All the APS system Aliases has the same figures described above the other aliases appears on the screen doesn't belong to APS system, please leave it the way it is .

This manual was created with the free trial version of HelpScribble.
This notice will not appear in manuals created with the full version.

Using APS Example Data

APS includes a sample accounting & Stock data to use it simply select from Aliases combobox the Alias (A11947) .

General Information

This manual was created with the free trial version of HelpScribble.
This notice will not appear in manuals created with the full version.