When you are shipping lots of products to numerous different customers, you may find the option to filter out certain orders very useful. Using QueryTree you can do exactly this. The filtering functionality is flexible, allowing you to produce a range of tables with the information that you need.
For this example we want to look at the number of orders sent to a particular shipping address in a specified time period. To get our report started, here is what we need:
- The Orders database table from our example database
- The Orders Details Extended table from our example database
- One Join tool
- Two Filter tools
- One Select tool
And here is what your report builder will look like:
1. First of all we need to join the two database tables, as the Order Details Extended database holds information about the specific products ordered and their quantities for each Order ID.
The Join options we will use are as follows:
- Join Type: Only matches from both
- Table 1 Column: Order ID
- Table 2 Column: Order ID
2. Once we have combined our tables, we want to look at orders sent to a specific shipping address. For this example we'll use Kirchgasse.
Here is where we use our first Filter tool, and we populate the fields in the tool options with the following:
- Column: Ship Address
- Compare Type: Contains
- Compare Value: Enter a value...
- Text box underneath: Kirchgasse
This gives us a table with Order ID's where the shipping address contains Kirchgasse, you should be able to see that these are all for the same customer too.
3. Take a look through the table and you'll see that all of the orders have been made between 1996-1998. We want a table that contains orders that have been made in 1996 only. So again we use a Filter tool for this purpose.
The Filter options are as follows:
- Column: Order Date
- Compare Type: Less than
- Compare Value: Enter a value...
- Text box underneath: 1997-01-01 00:00
4. Now we have a table with orders made in 1996 that have been sent to the shipping address Kirchgasse. You can see that there are quite a few columns in this table. Selecting the columns that we are interested in will make the table easier to read.
So now we add a Select tool on to the report builder and choose the following options:
- Order ID
- Customer ID
- Order Date
- Product ID
- Product Name
- Quantity
This gives us a much neater table:
For this example you can alter the options in the Filter and Select tools to give you any combination of information that you want. So why not try changing the shipping address? Or maybe filter out orders to a certain ship city instead. You could also filter out a particular Order ID or maybe look at a different set of columns.
The possibilities are almost endless!