Creating a Sample

Using GUIDE in DQL Mode › Creating a Sample

Creating a Sample

This section presents a sample objective, the report needed to accomplish the objective, and presents each Guide panel as it would appear if all options were selected from the GUIDED QUERY CREATION: OPTION SELECTION (DQHV0) panel.

You can follow the tutorial and create your own query. Simply choose a table on your database that has both character and numeric data and follow the directions on the following pages (using the correct names for your data).

Background

Assume you are a sales manager who is planning a new sales campaign. Your company produces personal computer accessories. Several items are available, each ranging in price from the economy model to the deluxe model.

You want to know which customers are most likely to order this year's deluxe diskette holder, which you hope will make last year's model obsolete. You will send color brochures advertising the latest deluxe diskette holder to all customers who seem to order your most expensive items.

You also want to know which customers order less expensive items. You will send them the new color brochure that advertises the entire product line. In addition, you want to know which items a particular customer likes, and how large the orders are, so you can make decisions about a letter to go along with your brochures.

Objective

You want a report that shows total dollar amount of orders for each customer, along with the average price per order. You will use the report in planning your sales campaign and in supplying you with data to personalize a letter to each customer. You know the data you need is in the CAI-DETAIL-TBL file. And you know CA Dataquery is the solution to your reporting needs.

Following is a sample of how you want your report to look:

04/14/11 PAGE 1 14:56:22 FILLED CUSTOMER ORDERS DETAIL CUST-ID ITM-ID SHIP-QTY UNIT-PRICE SHIP-PRICE --------- ---------- ----------- ------------- ------------- 01008 C10000 2 29.50 59.00 C10002 6 14.00 84.00 C10001 4 21.00 84.00 C10005 2 66.75 133.50 TOTAL CUST-ID 01008 360.50 AVERAGE ITEM PRICE 00032.81 01009 A60005 1 219.99 219.99 A70000 4 99.99 399.96 A60008 2 179.99 359.98 TOTAL CUST-ID 1009 979.93 AVERAGE ITEM PRICE 166.65

Compare the numbered items on the sample report for a description of each item you, as the sales manager, want to know about.

Report Item	Your Query Should
Subject	Search a table for all filled orders.
Title	Define a logical title.
CUST-ID	Sort rows by customer and show totals for each customer.
ITM-ID	Report the items ordered by each customer.
SHIP-QTY	Tell you how many of each item was shipped.
UNIT-PRICE	Tell you the price per item.
SHIP-PRICE	Calculate the total cost of each item shipped to a customer.
TOTAL CUST-ID	Tell you the total amount ordered by each customer.
AVERAGE ITEM PRICE	Tell you the average item price for a customer.