Phone 1-866-278-6243
Free Trial Free Trial Schedule a Demo Schedule a Demo

Creating source queries for Reports - Advanced

Creating source queries for Reports - Advanced

All Orders 4.X

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 'Views'.  

Overview

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.

Tables

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 query

  • refReports_Filters: Conatins saved filters (Where' clause) for each report.

  • refReports_Sorts: Contains saved sort order ('Order by' clause) for each report

  • refReports_Groups: Contains the Group the each report belongs to.

Adding fields to an existing query (Note - An All Orders update may override queries you have added a field too)

  1. Go to refReports table and find the (ReportSource) for the Report (e.g. qryrptSalesOrder)

  2. Open the VIEW called qryrptSalesOrder and add the field (e.g. CustomerAltContact)

  3. 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

  1. Copy an existing template (e.g. Sales Order)

  2. Create the View

  3. Add the fields in the VIEW to refReports_Data

  4. Update the data in refReports_Filters and refReports_Sorts

  5. Replace qryrptSalesOrder in refReports - ReportSource with the name of the new view.

2/22/2007



Call Cruncher
More questions?

Call to speak with a NumberCruncher Solutions Consultant at:

call us