SubQuery Reports

Home > Report Manager > Working with Reports > Advanced Reports > SubQuery Reports

Sub Query Reports

A Sub Query report is used to generate a list of items that is then used in a filter of an outer parent report.

An example might be where a Sub Query report is first used to generate a unique list of customer codes whose purchases to date sum to a value larger than $1000. The outer report might then want to pull out information on what cars these same customers drive from a different container but specifically for the Customer subset generated by the Sub Query report. A Sub Query report always only has one Display field so that a parent Report can reference it in a filter.

The logic for the above example in English might read:

Give me the Cars that my Customers drive, and only show me the this information for those customers that have spent more than $1000 to date.

In the Structured Query Language (SQL) this would read something like this:

SELECT Customer.Name, Customer.Car FROM Customer
WHERE Customer.Code IN
  (SELECT Sales.CustomerCode FROM Sales
  GROUP BY Sales.CustomerCode
  HAVING SUM(Sales.Amount) > 1000)

In Sage Accpac Intelligence to achieve this same effect you could:

1. First create a Sub Query Report (right click on a folder > Add Sub Query Report) called Big Spenders using the container Sales, with the Display Field CustomerCode, and an Aggregate Filter on Amount with Comparison Operator Greater Than, Comparator 1000 and Aggregate function SUM.
2. Then create an ordinary report using the container Customer with Display Fields Name and Car and specify a filter using the expression Code, Comparison Operator Is In SubQuery and SubQuery Big Spenders.