Tutorial 4 - Building the Customer Report Procedure
The Customer Report Procedure takes one input parameter from the user - namely, the customer ID number. So, when we open the new Report Procedure Workspace window, we will select the Parameters component and click on the New parameter entry in the Palette located at the bottom of the Name column:
This will add new variable to the Name column (par1) and also load it into the Parameters component:
Before going any further in assembling the report, let us assign a meaningful (mnemonic) name to the parameter variable par1. Double-click on "par1" in the Palette; this makes the cell editable. Overtype "par1" with "CustomerID" and press Enter (or just click elsewhere with the mouse): the name in the Parameters component changes to the new name, as shown in the figure below:
The given example is limited to one component only (Parameters) because there is not much in our Workspace yet, but when a variable (or a parameter) is renamed in the Palette, all occurrences of this name in all executable components of the procedure are changed.
Our report calls for some customer data (CompanyName and Phone) to be printed on the page, so we will bring in a Retrieval component and set it to Customers table with CompanyName and Phone fields to be fetched from it:
The retrieval from the Customers table should be limited to only one row for the customer of our interest - i.e. with the customer ID number passed through the Parameters component. This is done using the Search tab of the Database Retrieval Inspector. Click on Search tab and drag up drag up the entry named CustomerID until it reaches the top of the Inspector's scrollable view. Once the mouse is released, the dragged entry "lands" in the scrollable view, and changes its appearance; it now has three controls in it:
The run-time button tells the Retrieval component how to handle the content of the comparison value field: as a hard-coded value, or as the name of the variable that carries the value. Initially, the run-time button is white in color, and the value field is editable. Anything entered in the field will be treated on the "as is" basis and passed to the database retrieval machinery at the time of execution. Pressing the run-time button changes its color to gray, and makes the value field non-editable. In this state, the value field (which now reads "Drag variable/field here") can be a target for dragging variables or parameters from the Palette, as the figure below shows:
We will drag the CustomerID parameter into the Inspector's value field:
The Expert tab shows the resulting SQL SELECT statement. The actual syntax of the generated SQL is database-dependent; the shown example is based on MS SQL Server 2000. In the case of McKoi SQL, the same statement would be formatted as "select CompanyName,Phone from Customers where CustomerID = CAST([CustomerID] AS CHAR(10))" had the CustomerID field been of CHAR type (in actual McKoi table this field is of VARCHAR type, which requires no casting). Scribe is aware of idiosyncrasies of individual databases, and adapts the generated SQL code accordingly.
Here, [CustomerID] is a placeholder that tells Scribe to substitute the value of CustomerID parameter in its place when the statement is executed (i.e. at the run-time).
The next component to bring into the Workspace is the "Next row?" from the Inspector window, and connect the Retrieval component to it. This process is described in Tutorial2 and won't be repeated here.
We can now proceed to the retrieval of order data from Orders table for the given Customer. Again, we bring in a Retrieval component and set it to Orders table with OrderID, OrderDate and ShippedDate fields fetched from it:
Like Search criteria for Customers table retrieval, Orders' Search criteria too is set to select only those rows that have their CustomerID field match the customer ID passed through the Parameters component. The process is identical to the one described earlier. We also add the "Next row?" component to the Workspace and connect the Orders retrieval box to it.
Finally, we add the Print component, and set it to the Customers print template. The process of setting the Print component fields is described in Tutorial 2. Note that one of the fields in the template is Customers.CustomerID, and we do not retrieve this field explicitly from the Customers table. Instead, this field will have to be manually linked with the Customer ID parameter. We do this by dragging the Customer ID from the Parameters section in the Palette's Local Resources, and dropping it into the Print component's Inspector, in the right-side column next to the Customers.CustomerID field. The complete flowchart should look like this:
Check the procedure syntax, and if it is correct, run the test. The test traceback window now has one input field for the run-time parameter (CustomerID). Enter VINET and step through the flowchart until the Print component is reached. You should see something like this:
Before wrapping up this Tutorial, let us document what we have done in this procedure. There are two ways of doing so in Scribe, and we will use them both.
1. Procedure description (legend).
The description is entered into the Procedure Inspector, and can be viewed by the user running the report (to get the Legend Inspector displayed, click anywhere in the Workspace's background).
2. In-procedure notes.
The Title component in the procedure Palette is a multi-line text field that can be used for documenting various aspects of the procedure operation.
Save the report as Query/Customer.
After saving the report, we can execute it using the Production section of the Application Launcher window. Double-click on Run Report entry; this brings up the following window:
Select Report > Select menu item, then highlight the Query/Customer entry in the selection panel:
Press Select button; the report submission panel will be displayed:
The panel shows one input parameter: Customer ID (as set in the Parameters component). In Scribe Personal, the "Run off line" option is disabled; in Scribe Enterprise you could check this box to run the report as a separate background process.
The completed report is displayed in the window:
