Copyright: Visual Help Copyright ® 1993-1994 Firas Bushnaq


Contents

Introduction

Welcome to Shazam Report Wizard (SRW), the positively fastest way to create sophisticated ad-hoc reports from your database applications.  With its simplified Query-By-Example (QBE) interface and prompted dialogs, you'll be creating fabulous reports in record time -- without the learning curve of full report writers.  You can accomplish this without any knowledge of Structured Query Language (SQL), the industry-standard method to search a database.

Shazam Report Wizard minimizes the need for in-depth QBE or SQL syntax knowledge commonly required by other reporting solutions.  Shazam will quickly become your resident guru at writing powerful SQL code for you.

Shazam Report Wizard's integrated QBE interface makes defining search criteria easy.  Power users are also supported with custom field expressions, aggregate calculations, Having and Group By clauses, column totals, table aliases and self-joins.  With Shazam and a few mouse clicks, just about anyone with a little database understanding can quickly become power users!

Shazam Report Wizard is truly "instant reporting at its best."

 Ordering Information

 Disclaimer of Warranty

 Distribution License and Technical Support

Quick Start: 7 Steps to Fabulous Reports

 Step 1: Choose Tables

 Step 2: Select Fields

 Step 3: Edit Field Properties

 Step 4: Link Tables

 Step 5: Format Report

 Step 6: Preview, Print and Export Report

 Step 7: Save Report

Advanced Topics

 Custom field expressions

 Multi-line search filters

 Multiple instances of the same table (self-joins)

Troubleshooting Tips & Tricks

 Borland Paradox and dBase databases

 Microsoft Access Databases

 Microsoft or Sybase SQL Server


Ordering Information

> For CompuServe orders, GO SWREG and select the following IDs:
EXE version, $39 per concurrent user, CompuServe SWREG ID #6039
Delphi DCU version,  $79 per developer, CompuServe SWREG ID #6040
Source Code, $59 plus DCU registration, CompuServe SWREG ID #6041

> For credit card orders, call the Public Service Library (PsL) at 713-524-6394, mention product #14076 and ask for the EXE or DCU versions.  Source code requires DCU registration.

> Prices are the same at CompuServe and PsL.

> Shazam Report Wizard requires the Borland Database Engine (BDE, or IDAPI as it is also called).  If you have Borland Delphi, Paradox or dBase already installed on your computer, then you have the BDE.  If not, let us know and we can provide you a copy with your purchase of Shazam Report Wizard.

> Direct ordering questions via e-mail to 70562,412@compuserve.com.

> See Also:

Distribution License and Technical Support

Disclaimer of Warranty


Distribution License
Distribution License and Technical Support

Registered users of the stand-alone version of Shazam Report Wizard are licensed to use this product by one concurrent person per copy.  Copying this program to unregistered users is illegal and unethical.  For Technical Support, see the "Help | About SRW" menu from the program.

Users of Shazam Report Wizard that has been integrated into a custom application are licensed to use this product in accordance with their software provider's Distribution License, provided that it does not violate Shazam Report Wizard's Distribution License.  For Technical Support, contact your software provider.

Unregistered users of the evaluation version of Shazam Report Wizard are licensed to evaluate the product, and to distribute an unmodified copy of SRWDEMO.ZIP to others, without cost, for evaluation.  For more information, see the "Help | About SRW" menu from the program.


Disclaimer
Disclaimer of Warranty

BECAUSE OF THE DIVERSITY OF CONDITIONS AND HARDWARE UNDER WHICH THIS SOFTWARE MAY BE USED, SHAZAM REPORT WIZARD AND RELATED MATERIALS ARE PROVIDED "AS IS" AND WITHOUT WARRANTY, WHETHER EXPRESS OR IMPLIED, INCLUDING WITHOUT LIMITATION ANY IMPLIED WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE, OR FUNCTIONALITY.  ANY LIABILITY WILL BE LIMITED EXCLUSIVELY TO THE PURCHASE PRICE OF SHAZAM REPORT WIZARD.


Choose Tables
Step 1:  Choose Tables

All reports begin with tables from which data will be retrieved.  Fundamentally, tables are separate files containing rows and columns of data.  They differ from word processor files, for example, because the data is structured or organized in a fixed pattern.  This is what makes databases very good at managing large amounts of information.

Most databases consist of many related tables, resulting in the term "relational database."  We'll describe how these tables are linked together later.  For now, let's choose all the tables that will appear in our report.

Displaying the Add Tables dialog

Press the Add Table button, or select the "Edit | Add Table" menu.  This will display the following dialog:

The Add Tables dialog consists of the following elements:

Database: Type in the database name or path, or select a pre-defined alias from the drop-down list box.  If you typed an entry, press [Enter] when done.

Tables:  Database tables are displayed in this list box.  With your mouse or arrow keys, highlight a table name and either double-click or press the [Add] button.  This will add your selected tables to the main Shazam Report Wizard window.

Add:  Adds tables to the report (see Tables above).

Close:  Closes the "Add Tables" dialog box.

What about this icon?  It provides an alternative way to select a DOS path.  Pressing this icons extends the "Add Tables" dialog to include a directory selector:

The extended "Add Tables" dialog allows you to point-and-click your way to the DOS directory that contains your database tables.  Once you have found your database directory, press the [Ok] button.

NOTE:  Trying to select tables from the Directory finder does nothing.  This list of tables is provided so that you can see if you have found the correct directory.

To remove a table from your report, press this button or select the "Edit | Drop Table" menu.

See Also:

 Step 2: Select Fields


Select Fields
Step 2: Select Fields

In Step 1, we described how tables contain rows and columns of information.  A table's columns are more commonly called "fields."

To display the fields of a table, double-click on any table name.

To include a field in your report:
     > Point to the field in the outline,
     > Hold your left mouse button,
     > "Drag" the field to the right, and
     > "Drop" the field on the grid by releasing the left mouse button.

In the image below, the Company field is being added to the report by dragging and dropping the field.

To rearrange fields:
     > Point to the field header,
     > Hold your left mouse button,
     > "Drag" the field left or right, and
     > "Drop" the field by releasing the left mouse button.

You may have up to 50 fields in a single report.

To Insert a new, blank field in between existing fields, press this button or select the "Edit | Insert Field" menu.

To Delete an existing field from the "Define Search" grid, press this button or select the "Edit | Delete Field" menu.

To Delete an entire table from your report, press this button or select the "Edit | Drop Table" menu.

See Also:

 Step 1: Choose Tables

 Step 3: Edit Fields Properties


Edit Field Properties
Step 3: Edit Field Properties

Once you have selected the fields that will appear in your report, the next step is to change each field's properties or characteristics in the report.  This includes the field heading and width, how (or if) it will be shown on the report, and any search criteria that will be used on this field.

To modify a field's properties, press this button, or double-click directly on the field in the grid, or select the "Edit | Edit Field" menu.  This displays the Edit Field dialog:

The Edit Field dialog offers an easy way to create search criteria with minimum knowledge of Query-By-Example (QBE) or Structured Query Language (SQL) syntax.  Power is not sacrified on the alter of ease-of-use, however.  Many users will create exceptionally powerful reports without knowing a single SQL keyword.

The Edit Field dialog consists of the following elements:

Header

The Header property is the description of the field that will appear on the report and cannot be blank.  It defaults to the name of the field in the database. Some field names will be cryptic or unacceptable on a business report.  The Header property allows you to customize the field description as it will appear on the report.  Headers that contain spaces or reserved words are managed by Shazam automatically.  Key Point: headers cannot be blank.

Type

The Type property is the data type of the field and cannot be modified. For example, the SaleDate field in our report can only accept valid Date values.  If you type anything else, an error message will be displayed

Show

The Show property determines how (or if) a field will be displayed on your report. Valid Show property values include:

•	Show: Displays the field on the report.  This is the default setting.

•	Group: Used when one or more fields has a Show setting of Sum, Count, Average, Minimum or Maximum.  Any fields that do not have one of these settings MUST be set to Group.  This information only applies when one or more fields in your report has a Show setting of Sum, Count, Average, Minimum or Maximum.

•	Hide:  Prevents the field from displaying on the report.  Use Hide when you need to search a field but do not want that field displayed.  Example: Display all orders where Company equals "Unisco."  Since there is only one company, there is little need to repeat the Company name.

•	Sum:  Total up all the values in a set of records (field must be numeric).

•	Count:  Count the number of occurances in a set of records.

•	Average:  Calculate the average value of a set of records (field must be numeric).

•	Minimum:  Display the minimum value in a set of records.

•	Maximum:  Display the maximum value in a set of records.

The Sum, Count, Average, Minimum and Maximum values are called aggregate functions.  Filter values that are used on columns with an aggregate function are treated differently.  For example:

•	"Display customers where AmountPaid was greater than 500" displays the RECORDS that exceed an AmountPaid greater than 500.

•	"Display customers where the SUM of AmountPaid was greater than 500" displays the records where their collective TOTAL of AmountPaid exceeds 500.

The first example applies the filter values against the actual values of the records. The second example applies the values after a calculation has been performed on a set of records.

You may have other fields and filter values as needed in a report with aggregate functions.  However, the Show setting of the other fields is paramount. Key Points:

•	Use a Show value of "Hide" to hide fields that are only being searched but not displayed.

•	Use a Show value of "Group" to calculate summary values by other columns (i.e. "Display all customers BY CITY where AmountPaid is greater than 500").  The City field would have a Show setting of "Group" and the AmountPaid field would have a Show setting of "Sum."

•	Never use a Show setting of "Group" if the report does not include aggregate functions.

Width

The Width property determines how long the field will appear on the report.  If you want to hide the field, change the Show property to Hide.

Align

The Align property determines if the values in a field will display Left, Right or Center within the width of the displayed field.  Most date and numeric data types automatically align themselves to the right.

Sort

The Sort property determines if the field will be used to rearrange the order in which the records are displayed on the report.  When more than one column has been selected to sort on, the collective sort order is based on the left-to-right sequence of these columns in the "Define Search" grid.  Sort property values are:

•	"None" for no sort.

•	"A-Z" or "1-9" (data type dependent) for Ascending sort order.

•	"Z-A" or "9-1" (data type dependent) for Descending sort order.

Total

The Total property determines if a numeric field will have a final summary value at the end of the report.  If the data type of the field is not numeric, this property setting is ignored.

Filter

Filtering records is at the heart of meaningful reports and Shazam's filtering process is about as easy as it gets.  A search filter consists of a Filter Command and one or more search values.  Valid Filter Commands are:

•	Is Equal To: Include records that equal any one of up to 5 values.

•	Is Between: Include records that are within a range of 2 values.

•	Is Greater Than: Include records that are greater than a single value.

•	Is Greater or Equal To: Include records that are greater than or equal to a single value.

•	Is Less Than: Include records that are less than a single value.

•	Is Less or Equal To: Include records that are less than or equal to a single value.

•	Contains: Include records that contain up to 5 matching values within the field.  Use "value%" to search for all records that start with value.  Use "%value" to search for all records that end with value.  Use "%value%" to search for all records include value anywhere in the field..

•	Is Not Equal To: Exclude records that equal up to 5 matching values.

•	Is Not Between: Exclude records that are within the range of 2 values.

•	Does Not Contain: Exclude records that contain a value (see Contains above).

On fields with a Show setting of Sum, Count, Average, Minimum or Maximum, your filter values must be numeric.

Navigation Commands

Once you have displayed the Edit Field dialog, you can navigate between columns or Search Filters within your report without leaving this dialog.  The four buttons are defined as follows:

•	Left: Move to the column that is left of the current column.

•	Right: Move to the column that is right of the current column.

•	Next: Move to the next Search Filter.

•	Previous: Move to the previous Search Filter.

All four of the navigation commands automatically save any changes to the current column before moving.  Remember this when you press the [Cancel] button and find that some changes have already been saved.

While the the Left and Right navigation commands change columns, the Next and Previous buttons change Search Filters within the same column.  Each column can have up to 5 separate Search Filters.  Each line that starts with Filter # in the "Define Search" grid is treated as a completely separate search on the database.  Within each line, a record must match all Search Filters between columns to be included in the report.

Clear

The clear button clears the currently displayed Search Filters.

Ok, Cancel & Help

Press [Ok] to accept changes, [Cancel] to reject unsaved changes and [Help] to display this Help topic.

See Also:

 Step 2: Select Fields

 Step 4: Link Tables

 Advanced Topics: Custom field expressions

 Advanced Topics: Multi-line search filters


Link Tables
Step 4: Link Tables

As we described in Step 1, most databases consist of many related tables, resulting in the term "relational database."  In the example below, the Customer table is related to the Orders table, and the Orders table is related to the Items table:

Within Shazam Report Wizard, linking tables is very easy. Using the same example from above, the user is joining the ITEMS.OrderNo field with the ORDER.OrderNo field in Shazam below.

The process of linking tables is similar to selecting fields:

     > Point to the field in the outline and select the left linking field,
     > Hold your left mouse button,
     > "Drag" the field to the right, and
     > "Drop" the field on the grid by releasing the left mouse button.
     > Repeat these steps for the right link.

Linking tables always consists of a left and right field name.

Currently, the only link type supported is "Equals."  Left and Right joins are not supported.

Both fields on the left and right must be a compatible data type or an error will be displayed.

All tables included in the tables outline must be linked or dropped from the report.  If a table is not linked, an error message will be displayed.

See Also:

 Step 3: Edit Field Properties

 Step 5: Format Report

 Multiple instances of the same table (self-joins)


Format Report
Step 5: Format Report

To format your report, press this button or select the "Edit | Format Report" menu.  This will display the Format Report dialog:

The "Format" tab of this dialog consists of the following elements:

•	Suppress Duplicates:  Any records which are 100% identical will be suppressed from appearing in your report.  Only one record from the set of duplicates will appear.

•	Page Orientation: Determines if the printed page will appear in Portrait (Tall) or Landscape (Wide) mode.

•	Margins: Determines the distance from the edge of the paper that report content will begin.

The "Titles" tab of this dialog consists of the following elements:

•	Title: Determines the Title of the report.  Press the related font button to changes the Title's font.

•	Sub Title:  Determines the Sub Title of the report that appears immediately under the title.  Press the related font button to changes the Sub Title's font.

•	Footer:  Determines the footer that appears at the bottom, left edge of the report.  Press the related font button to changes the Footers's font.

•	Font Settings for Report Records: Press this button to change the font that will be used for displaying records.

Press [Ok] when your changes are completed or [Cancel] to close this dialog.  NOTE:  Font changes are saved when you press [Ok] from the Font Dialog.  [Cancel] has no effect on font changes.

See Also:

 Step 4: Link Tables

 Step 6: Preview, Print and Export Report


Preview Report
Step 6: Preview Report

Press the "Preview Report" tab or select the "View | Preview Report" menu to see the report.  The following is displayed:

Press the First, Previous, Next or Last buttons to display the corresponding page.

Press the Print button to print the report.

Select "File | Export" to export the report to Paradox, dBase or ASCII text files.

See Also:

 Step 5: Format Report

 Step 7: Save Report


Save Report
Step 7: Save Report

Once you are satisified with the contents of your report, make sure you save it for use later.

You have now completed the "7 Steps to Fabulous Reports."

See Also:

 Step 6: Preview, Print and Export Report

 Contents


Self Joins
Multiple instances of the same table

Shazam Report Wizard supports multiple instances of the same table by automatically aliasing each table name with a number.  This allows the same table to be treated separately, allowing self-joins and other powerful reporting features.


Custom Expressions
Custom Field Expressions

Custom Field Expressions can be defined from the Edit Field Dialog by selecting the "Custom" tab.  This displays the following:

Double-click on field names and Operators/Functions to add them to the custom expression field.

Always use a table name in a custom expression.  Otherwise, the SQL code that is generated may fail.

See Also:

 Step 3: Edit Fields Properties


Multi Row Filters
Multi-Line Search Filters

Shazam Report Wizard supports up to 5 rows of search criteria at the same time.  Each line is treated as a separate search.

In the example above, the first line contains 3 columns of search criteria and the second line contains a single search value.  A vendor will be dislayed in this report if:

1 	The vendor's product was sold between 6/1/93 and 11/1/93, AND

2 	The List Price is more than $1,000, AND

3 	The vendor's city of business is in Vancouver or Southfield or Indianapolis or Cornish.

OR

1 	The vendor is a preferred vendor.

Multi-Line search filters allow you to execute completly different search criteria within the same report.


Paradox and dBase

If you include Filter Values on calculated columns, Shazam Report Wizard will display the following message:

To work around this limitation, simply export the report to a file and create a second report that includes the Filter Values on the previously calculated fields.


Access
Microsoft Access

Microsoft Access does not support the ANSI-89 SQL standard like most database management systems.  As a result, there are two issues that you must handle by editing the SQL code that Shazam creates:

•	Field Alises: If your custom field header differs from the actual field name, Microsoft Access requires the keyword AS between the field name and the header.  For example, Shazam generates the following code:

SELECT Customers."Company Name" Company,
         Customers."Contact Name" Contact,
         Customers."Contact Title" Title,
         Customers.Address,
         Customers.City
    FROM Customers
   WHERE (((Customers.City IN('London', 'Madrid'))))

However, you need to insert AS in front of the first three field names, as follows:

SELECT Customers."Company Name" AS Company,
         Customers."Contact Name" AS Contact,
         Customers."Contact Title" AS Title,
         Customers.Address,
         Customers.City
    FROM Customers
   WHERE (((Customers.City IN('London', 'Madrid'))))

•	The second example involves dates. Shazam generates the following code:

  SELECT Customers."Company Name",
         Customers.City,
         Orders."Order Date"
    FROM Customers,
         Orders,
         "Order Details"
   WHERE ((Customers."Customer ID" = Orders."Customer ID")
     AND (Orders."Order ID" = "Order Details"."Order ID"))
     AND (((Orders."Order Date" > '1/1/93'))
      OR ((Customers.City = 'Freeport')
     AND (Orders."Order Date" > '1/1/95')))

However, Microsoft Access needs pound symbols (#) as ticks marks around dates, not single quotes.  Thus, change this code to the following:

  SELECT Customers."Company Name",
         Customers.City,
         Orders."Order Date"
    FROM Customers,
         Orders,
         "Order Details"
   WHERE ((Customers."Customer ID" = Orders."Customer ID")
     AND (Orders."Order ID" = "Order Details"."Order ID"))
     AND (((Orders."Order Date" > #1/1/93#))
      OR ((Customers.City = 'Freeport')
     AND (Orders."Order Date" > #1/1/95#)))

Unfortunately, these changes will not be saved.  Future versions of Shazam Report Wizard will automatically manage these issues with Microsoft Access.


SQL Server

Only one database may be accessed at a time with SQL Server.