Contents
Ad Hoc Reports
This topic covers the basics of deploying Ad Hoc reports, created with the OpenRPT report writer embedded in the xTuple client, using a Custom Command.
The Report
Below we see a preview of the report definition we are going to deploy called MYQOHByItem.
MetaSQL
Next we will look at the report's detail query and determine the MetaSQL parameter it uses to pass the user's input from the OpenRPT RTPrender application to the query. Below we see that the user will select an Item from RPTrender and its item_id value will be passed to the report's query to generate the report using the MetaSQL <? value("item_id") ?>.
Parameter Query
To make RPTrender easy for the end user to use, we can define a Parameter query in the report definition itself that RPTrender will execute when the user clicks the LIST button to display a list of selection options. In this case the list will contain Item Numbers concatenated with their Descriptions and sorted by Item Number.
It is important to note that the query can only contain two columns and the first column must always be the "_id" for the key field that will be passed into the report's queries through MetaSQL. The second column is what the user sees when the LIST button is pressed. So again, on RPTrender, the user will see Item Numbers concatenated with their Descriptions and sorted by Item Number. When one is selected from the list, the item_id for that Item will be passed through the MetaSQL parameter called "item_id" into the report's queries.
The Custom Command
Now we create the Customer Command in xTuple that calls the RPTrender application and loads our report definition. Custom Commands are defined under System -> Design -> Custom Commands. We make the following entries:
- Module - the xTuple module under which the menu option will appear
- Menu Label - the menu label for our ad hoc report
- Priv. Name - the name of the privilege that is create to protect access to the new menu option
- Action name: blank for an ad hoc report
- Executeable: the path and executable for the RPTrender binary
On the Arguments tab we create three entries
- -databaseURL=psql://server/db:port - this entry defines the connection information for your server, database and port.
- -username=mike - this entry defines the user that will logon and if omitted will require the user to enter a user
- -loadfromdb=MYQOHByItem - this is the name of your report as it is stored in the xTuple DB
Grant Privilege
Creating the custom command creates the privilege associated with it. To execute the Ad Hoc report, the user or the user's group must be granted the custom command's privilege. Below we see the association of the privilege for the ad hoc report with the group that is linked to multiple users.
Run Report from xTuple
The Custom Command we created associated our Ad Hoc report with the Inventory module. Below we see that it is executed under Inventory -> Custom.
RPTrender
Our Custom Command passed to the RPTrender application the server, port, DB, username and the report definition stored in the DB. The user simply enters the password.
RPTrender List
Earlier in our report definition we defined a dynamic parameter query to lookup Items and their Descriptions and populate the MetaSQL Parameter "item_id" based on the entry the user selects after pressing the LIST button.
Preview Report
The Print Preview button enables us to view the results of our report.
