Creating a Report View

This Wiki is read-only for reference purposes to avoid broken links.

After having created tables and windows and after having filled the tables with data, it is now time to create a report that displays the data in an intelligent fashion. Here is a simple way to go about it.

1. First, ensure you have setup your tables and have populated them with some data.
2. Using your database tools (such as phpAdmin for Postgres), setup the queries that will generate the report data. It is fine to include extra fields in the query.
   
   Important: Avoid aliases. If you are going to use aliases, use only lower case and ensure there are no spaces in the alias names. Also ensure that you include the ad_org_id and ad_client_id fields from the base table. The query will be imported as a table into the application dictionary. The software can handle upper case characters but not spaces in sql names. The software also converts upper case dictionary entries to lower case when it queries the database resulting in errors.
   
   
3. Once the query is working, save it in the database. In postgres, save it as a view with owner adempiere. Follow the naming conventions for the view. Use "rv_[my_query]" for dictionary items and "yyy_rv_[my_query]" for user maintained items replacing the yyy with the code used for the client. (Using a client code makes the tables, views and functions easy to find).
4. Log in as System in the System Admin role and, in the application dictionary, create a table entry that points to the view created above. Use the same name for the table as you used for the view. Click on the buttom to "Create Columns from DB". This will copy all the colums from the query into the Application Dictionary. (If you forgot to include the ad_org_id and ad_client_id fields, you will have to go back and do so now.)
5. Open the Application Dictionary Report View window and add an entry refering to the table just created. Use the same name again. Leave the WHERE and ORDER BY clauses blank for now. Save the record.
6. Open the Application Dictionary Report & Process window and add an entry refering to the Report entry just created. Use the same name again. Check the Report box and, in the Report View field, select the Report View just created. Save the record.
7. Open the Application Dictionary Menu and add the report to the menu.
8. Log as the client and access the report. Your data should appear but so will all the extra columns and the lower-case spaceless names. Click on the properties icon (the crossed tools) and edit the report properties to remove the unwanted columns, correct the column names and generally improve the report appearance.