The following article describes how, in general terms,
the reporting works in All Orders, how you can create your own source
queries and ultimately create your own custom reports. This article
requires knowledge of T-SQ and using and using SQL Enterprise Manager to create
All Orders 4 uses a reporting system that is comparable to
Access Reports whereby a design is combined with data in a query to generate the
report. Through the GUI you can modify the design and include on the
report fields that pre-exist in the query. If the fields do not
exists in the query there is not way to add them to the report through the GUI.
In addition, when you copy a template say the sales order, it uses the
query associated with the sales order (qryrptSalesOrder) and through the GUI
there is no way to change the query to say qryrptShipDoc. With the
information contained in this article you will be able to accomplish this.
There are four 5 tables that control the reporting function
in all orders. They all start with refReports
refReports: Contains the actual report name and design.
refReports_Data: Contains all the fields listed in the
refReports_Filters: Conatins saved filters (Where' clause) for each
refReports_Sorts: Contains saved sort order ('Order by'
clause) for each report
refReports_Groups: Contains the Group the each report
Adding fields to an existing query (Note - An All
Orders update may override queries you have added a field too)
Go to refReports table and find the (ReportSource) for the
Report (e.g. qryrptSalesOrder)
Open the VIEW called qryrptSalesOrder and add the field (e.g. CustomerAltContact)
Insert into refReports_Data a record for the new field . (Hint, look at similar fields in the
source to see how to populate the other fields)
Creating a new report and source
Copy an existing template (e.g. Sales Order)
Create the View
Add the fields in the VIEW to refReports_Data
Update the data in refReports_Filters and refReports_Sorts
Replace qryrptSalesOrder in refReports - ReportSource with
the name of the new view.
Call to speak with a NumberCruncher Solutions Consultant at: