<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 1</title>
</head>
<body>
<h3>Walkthrough - Creating a Drill Down Enquiry Screen</h3>
<p>This example assumes that you are connecting to a Microsoft SQLServer and
will be connecting to the pubs database.</p>
<p>This example shows how to create a simple enquiry screen listing stores, with
a drill down option for the sales for each store.</p>
<p>Select New Report from the <b> Open Door</b> menu. Enter DrillTest1 as the document
name</p>
<p>From the View menu, select Style then Browser..</p>
<p>From the Edit menu select Data Sources -> Add Data Source.</p>
<p>Enter <b>Stores</b> as the Data Source name, and <b>Store List</b> as the
Data Source Description.</p>
<p>For the Select List enter:</p>
<p> <b>STOR_ID,STOR_NAME,CITY</b></p>
<p>For the From Clause enter:</p>
<p> <b>STORES</b></p>
<p>The Where clause may be left blank. For the Order list enter:</p>
<p> <b>STOR_NAME</b></p>
<p>Next select OK. From the Edit menu select Browse Items -> Add Item. You
will be presented with a form listing data sources on the left, with 'Stores'
being the only data source available. Select List from the options on the right
hand side, then select OK. Cross Hairs will be displayed. Move the cursor to a
position near the top left of the screen and left click to place the list item.
You may then Righ CLick on the list Item and select Resize from the menu. Move
the cursor and left click to resize the list of stores.</p>
<p>Place the cursor over the column separators in the column headings of the
store list. Drag the column widths to display the store names properly.</p>
<p>From the Edit menu, choose Layout, then choose 'Add Drill Down'.</p>
<p>For the Button Caption enter:</p>
<p> <b>Details</b></p>
<p>For the Drill Template Name enter:</p>
<p> <b>Stordet</b></p>
<p>Next, select the Add button. The Add Drill Parameter form is displayed.
Select <b>
SQL Source</b> as the Parameter Source, <b>STORES</b> as the SQL Source and <b>1</b>
as the Column Number. Select OK to return to the 'Add Drill Button' form. Select
OK and Cross hairs are displayed.</p>
<p>Position the cursor to the right of the store list and left click. A new
Details button is added to the form.</p>
<p>Click on the Details button. A new blank document is displayed. From the View
menu, select Style then Browser.</p>
<p>From the Parameters menu, select Parameters then 'Add Parameter'.</p>
<p>For the Parameter Name enter</p>
<p> <b>STORID</b></p>
<p>For the Parameter Type Select<b> Number</b>. And for the Default Value enter:</p>
<p> <b>6380</b></p>
<p>For the Parameter Description enter:</p>
<p> <b>Store ID</b></p>
<p>Next select OK.</p>
<p>From the Edit Menu Select Data Sources, then Add Data Source. For the Data
Source Name enter:</p>
<p> <b>SALES</b></p>
<p>For the Data Source description enter</p>
<p> <b>Store Sales</b></p>
<p>For the Select List enter</p>
<p> <b>t.title "Title", s.qty * t.price "Amount"</b></p>
<p>For the From Clause enter</p>
<p> <b>sales s inner join titles t on s.title_id = t.title_id</b></p>
<p>For the Where Clause enter:</p>
<p> <b>stor_id = <<STORID>></b></p>
<p>For the Order List enter:</p>
<p> <b>1</b></p>
<p>Select OK to complete adding the Data Source. Next, from the Edit menu select
'Browse Items', then 'Add Item'. The 'Select SQL Source' form is displayed, with
only one item available. Select 'List' from the options on the right, then
Select OK. Cross hairs are displayed. Position the cursor to the top left of the
form and left click. A list of sales is displayed. Right Click on the list and
select Resize to enlarge the list.</p>
<p>To change the header text for a list, Right Click on the List Item and select
Edit Properties. From the Property Edit form, select Column Info, then change
the Header Text for Each column.</p>
<p>You may wish to add a header to the form. First Create a Data Source to
Retrieve the Store Name. From the Edit Menu Select 'Data Sources' then 'Add Data
Source'. Enter the name as <b>SN</b>, the Description as <b>Store Name</b>, the
Select List as <b>STOR_NAME</b>, the From Clause as <b>STORES</b> and the Where
Clause as <b>stor_id = <<1>></b>, then Select OK.</p>
<p>To Add the Store Name to the form, Select Browse Items from the Edit menu,
then Add Browse Item. Select 'SN' as the Data Source, choose the 'Single Column'
option, then Select OK. From the 'Select Column' form select OK to choose the
only available column. The place the item on the form. You may Move and Resize
the Store Name to place it above the list of Sales.</p>
<p>You may also add a label. From the Edit Menu, Select Layout, then Label. When
Prompted enter <b>Sales for Store:</b> as the label text. Position the label
before the store name.</p>
<p>To change the caption for the Browser Form, From the Edit menu, select
Layout, then Caption. Change the caption for the Main Browser form to <b>Store
List</b>, then change the caption for the Drill Down form to <b>Sales List</b>.</p>
<p>When you completed the exercise, you should have a main browser form similar
to the following:</p>
<p><img border="0" src="StoreList.jpg" width="522" height="338"></p>
<p>When you click on the Details button, sales for the selected store should be
displayed on a form similar to the following:</p>
<p><img border="0" src="SalesList.jpg" width="466" height="302"></p>
</body>
</html>
Submit an article or tip