Copyright: Copyright ® 1995-98 ShazamWare Solutions, Inc.
Citation: Copyright ® 1995-98 ShazamWare Solutions, Inc.


Contents
  Using Shazam Report Wizard 3.5M

License Agreement     Technical Support

Welcome to Shazam Report Wizard (SRW), your instant ad-hoc reporting solution.  SRW empowers you to create reports quickly without the high learning curve that other products impose.  "In the days when you can't do anything without trawling through stacks of manuals," writes a user, "Shazam Report Wizard is refreshingly simple and easy to use."

This on-line help file provides complete documentation for using Shazam Report Wizard.  New users should review What is Query-By-Example?, Using Define Search, What is a report?,  and Using Layout Page.  All users will find Frequently Asked Questions and Database Information helpful.

Define Search    • What is QBE?    • Using Define Search     • Add Tables     • Link Tables     • Add Fields     • Edit Fields     • Filter Records Advanced Topics     •  Custom Expressions     •  Summarize Data     •  View SQL Code     •  Query Setup     •  Grid Setup	Layout Page    • What is a report?    • Using Layout Page    • Arrange Sections      • Place Objects     • Customize Objects     • Align Objects Advanced Topics    • Using @ Functions      • Label Setup     • Page Setup	Preview Report    • Preview Report     • Preview Records      Advanced Topics     • Export Reports
Copyright © 1995-1998 ShazamWare Solutions, Inc.

All Rights Reserved


End-User License Agreement

Your use of Shazam Report Wizard (SRW) is governed by the terms and conditions of the following agreement.  You should read the terms of the agreement carefully before using the software.

YOUR USE OF SHAZAM REPORT WIZARD INDICATES ACCEPTANCE OF THE END-USER LICENSE AGREEMENT.

Shazam Report WizardTM

End-User License Agreement

Ownership

Shazam Report Wizard and related materials ("Software") are the exclusive property of ShazamWare Solutions, Inc., and are protected by United States and international copyright laws.  "Shazam Report Wizard" and "ShazamWare" are trademarks of ShazamWare Solutions, Inc.

Disclaimer of Warranty

DUE TO 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.  IN NO EVENT WILL SHAZAMWARE SOLUTIONS, INC. BE LIABLE FOR DAMAGES, INCLUDING ANY LOSS OF PROFITS, LOST SAVINGS, OR OTHER INCIDENTAL OR CONSEQUENTIAL DAMAGES ARISING OUT OF THE USE OR INABILITY TO USE THE SOFTWARE, EVEN IF SHAZAMWARE SOLUTIONS, INC. HAS BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. ANY LIABILITY WILL BE LIMITED EXCLUSIVELY TO THE PURCHASE PRICE OF SHAZAM REPORT WIZARD BY THE ORIGINAL PURCHASER. YOU ASSUME TOTAL RISK AND RESPONSIBILITY FOR ALL RESULTS PRODUCED FROM SOFTWARE BY USING THE SOFTWARE. THIS AGREEMENT SHALL BE GOVERNED BY THE LAWS OF WASHINGTON STATE, USA.

Grant of License

You are granted a license to use Shazam Report Wizard as part of another software product, provided that the software product was developed in accordance with the Shazam Report Wizard Developer's License Agreement.  You may also distribute free of charge the SHZ16.EXE and SHZ32.EXE programs for viewing exported *.SHZ files without the original database.

End-User Responsibility

You are responsible to verify the accuracy of all reports that you generate from Shazam Report Wizard.

Technical Support

Your software provider is responsible to provide technical support for Shazam Report Wizard.

Limitation of License

You may not copy or distribute Shazam Report Wizard or its component parts, except for backup and archival purposes.  You may not reverse engineer, disassemble or duplicate any part of Shazam Report Wizard's design in another software product.

Changes to this License Agreement

The latest version of this License Agreement supersedes all previous versions, and governs all existing versions of the software.

Last updated: August 4, 1998


Technical Support

Contact your software provider for technical support.


What's new with SRW 3.5

Shazam Report Wizard 3.5 is a major upgrade with a host of new features:

Layout Page

--Move multiple controls

--Cut, copy, paste

--Resizing sections

Arrange Sections

Edit Section

Add Sections Wizard

Place Objects

Customize Objects

@IsData function

Improved performance and reliability

Improved documentation

Expanded control for programmers

New File Format

Reports Explorer


Define Search


What is Query-By-Example?

Query-By-Example (QBE) is a visual method to represent a database search.  QBE is generally easier to understand than Structured Query Language (SQL), which is the industry standard for defining a database search.

QBE and SQL were developed in the 1970's at roughly the same time as potentially competing standards.  Instead, they became complimentary.  SQL is now the industry standard language  for searching databases while QBE is the most common technique  for generating SQL code quickly and easily.

Shazam Report Wizard implements a powerful Query-By-Example system for users that want results instead of computer jargon.  The time you invest with Shazam Report Wizard will empower you to transform raw data into meaningful information - when you need it!

In the beginning...

All databases begin with one or more tables, which may contain vast amounts of information.  Typically, this information is only useful in small amounts.  Shazam Report Wizard provides an easy way to choose a subset of fields  and records  from your database.

Fields are selected using simple drag-and-drop techniques.

Records are selected by defining powerful search filters  from the "Define Search" tab of Shazam Report Wizard.  This is the heart of Shazam's Query-By-Example interface.  It provides an easy way to indicate the values or range of values that a record must have to appear in your report.

Consider the following query:

This query informs Shazam to display the Company, City, Contact and Phone from the Customer table where the Last Invoice Date is greater than or equal to 1/1/96, AND  the first two digits of the Customer's zip code is 98, AND  State equals California.  Since each filter value appears on the same line, all statements must be true for the record to be included in the result set.

The following query contains the same elements but produces very different results:

This query informs Shazam to display the Company, City, Contact and Phone from the Customer table where the Last Invoice Date is greater than or equal to 1/1/96, OR  the first two digits of the Customer's zip code is 98, OR  State equals California.  Since each filter value appears on a different line, at least one of the filter values must be true for the record to be included in the result set.

NOTE:  In a multi-line query, duplicate records will be returned if a record matches two or more filter lines.  See Report Options for information on turning off duplicate records).

As you can see, Shazam Report Wizard offers powerful record filtering tools (see Edit Fields and Filter Records for more information).  However, this is only the beginning of Shazam's query power.

Relating Tables

Database information is usually saved in groups of tables, each with unique information.  For example, a typical order-entry system will have information about customers, their orders, the line items that appear on each order, detailed part information that is referenced on each line item, and vendors who supply those parts.

These tables are related to each other, resulting in the term "Relational Database."  The orders table, for example, will contain the customer number that requested the order.  Since customer number appears in both the customer and orders tables, it becomes clear that we need a way to dynamically link or join the tables together.

Shazam Report Wizard provides a graphical way to link related tables.  You simply drag-and-drop the related field from one table to another.  This visual representation makes complex relationships much easier to understand.

Table linking allows you to ask questions about your data when the answer is dependent on multiple tables, or multiple instances of the same table.  See Link Tables for more information.

Calculating Information

It is quite easy to see how SRW can display fields and records as they exist in the database.  However, Shazam Report Wizard offers even more powerful features to calculate or derive  new information from the database.  SRW makes it easy to perform calculations on your data, and choose a subset of records based on the results of these calculations.  You can even compare these calculations by the values in other fields, turning previously hidden data into strategic information.  See Edit Fields for information about using the Show property with aggregate calculations.

Full Power Ahead

Shazam Report Wizard opens the full power of relational database reporting to you.  It supports Paradox, dBase, Access, SQL Server, Advantage Server, Oracle, Informix, InterBase, Sybase and other ANSI-SQL compliant database management systems.  SRW helps you turn raw data into useful information quickly.  Shazam Report Wizard is truly "instant reporting at its best."

See Also

    •	What is a report?


Using Define Search

Define Search is like a detective who searches the country side for evidence that matches his clues.  Your job, as a user, is to provide the clues needed to solve the mystery.  This is, in fact, where the term "Query-By-Example" gets its name: You provide examples of data to indicate which records should be included and excluded from the search.

The graphic below shows a picture of Define Search with key elements highlighted.  Information following the graphic describes how to use Define Search.

As the "What is Query-By-Example?" topic discusses, Shazam Report Wizard displays a relational database query in a visual manner.  This makes it easier to understand.  The tasks you can perform within Define Search are discussed in the following topics:

   •	Add Tables

   •	Link Tables

   •	Add Fields

   •	Edit Fields

   •	Filter Records

   •	Define Custom Expressions

   •	Summarize Data

   •	View SQL code

   •	Format Dates

   •	Format Numbers

See Also

   •	What is Query-By-Example?

   •	What is a report?

   •	Using Layout Page


Creating your first query


Add Tables

Press the tool bar button [image] to display the "Add Tables" dialog, or use the "Edit | Add Tables" menu.  The following dialog is displayed:

The "Add Table" dialog consists of the following elements:

Database

Type in the database name or path, or select a predefined 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 Table] button.  This will add your selected tables to the main Shazam Report Wizard window.

Add Table

The [Add Table] button adds the currently highlighted table to your report (see Tables above).

Directory

The [Directory] button allows you to select a DOS Directory/Folder that contains Paradox or dBase tables without a predefined database Alias. This displays a standard File Open dialog box. NOTE:  Selecting tables from the open dialog does not add them to the report.  It only selects the directory in which the tables exists.  Once you press [OK], you must double-click on the desired tables or select the [Add Table] button.

Close

The [Close] button closes the "Add Tables" dialog box.

Help

The [Help] button displays its associated help topic.

Removing Tables

Removing tables from a report is a two step process:

•	Select the table to delete by pressing your the left mouse button over the selected table.

•	Press the tool bar button [image] to remove the table, or use the "Edit | Drop Table" menu.

See Also

•	Link Tables

•	Add Fields


Link Tables

Database information is usually saved in groups of tables, each with unique information.  These tables are related to each other using common fields.  The orders table, for example, contains the customer number that requested the order.  Shazam Report Wizard provides a graphical way to link related tables together. You simply drag and drop the related field from one table to another, as follows:

1.	Point to and press the left mouse button over the left table's linking field.

2.	Drag your mouse over the right table's linking field.

3.	Release the left mouse button.

Above: Table linking in progress.

Above: A completed table link.

You can have up to 40 table links in a single report, and a single field can participate in up to 10 links.  Most database management systems cannot efficiently process more than seven tables at a time (SQL Server's optimal limit is four tables, depending on their size).  If you need to process many tables for a single report, you may want to break the report into multiple steps, exporting the results of each step to a temporary table and linking that table into subsequent steps.  This will often be substantially faster than processing many tables in one report.

Removing Table Links

Removing table links from a report is a two step process:

•	Select the table link to delete by pressing your the left mouse button over the selected table link.  This will highlight the selected table link.

•	Press the tool bar button [image] to remove the table link, or use the "Edit | Unlink Tables" menu.

The following question will be displayed:

If you answer [Yes], the links between CUSTOMER.CustNo and ORDERS.CustNo will be removed.  If you answer [No], the link will be unchanged.

Make certain that you replace a table link or remove the table before executing your report.  Otherwise, you may generate an error if a table has not been linked. You can also remove table links by removing their associated tables from the report.

Customizing Table Links

Normally, you can link two tables together and accept the defaults characteristics of the table link without doing anything else.  If you need change the default settings, simply double-click on a table link to display the "Link Tables" dialog:

The "Link Tables" dialog consists of the following elements:

Link Operator

The upper left hand portion of the "Link Tables" dialog displays the currently selected link operator.  The example above would be read as:

The NAMES table's NameID field Is Equal To the COLORS table's NameID field

The "Is Equal To" phrase determines how the comparison will be made between the two linking fields.  Available choices include:

Link Operator	Symbol
Is Equal To	=
Is Greater Than	>
Is Greater Or Equal To	>=
Is Less Than	<
Is Less Or Equal To	<=
Is Not Equal To	<>

Link operators other than "Is Equal To" are quite useful when your report needs to compare the values of two columns and choose records based on the comparison.  In this case, you are actually using a table link as a search filter.  Link Operators should only be changed when you are quite familiar with relational databases, and the affects these operators will have on your report.

Link Type

The bottom portion of the "Link Tables" dialog determines the Link Type.  This controls how records that do not exist in one of the tables are handled.  We will use a simple illustration to explain this concept.  Suppose you have the following two tables:

The NAMES table contains a list of people.  The Colors table contains each person's favorite color.  Notice that some colors do not have corresponding Name IDs in the Names table.  Likewise, notice that some Names to do not have corresponding colors in the Colors table.  This represents a common database situation where related tables do not contain related data.

The first Link Type includes all records from both tables "with matching records only," resulting in the following selection:

This is the most common database link, where the records displayed must have corresponding records in both tables.  This is also the default setting when you first link tables together.  The problem with this link is that it does not let you know who has not been assigned a favorite color, or which colors have not been assigned a name.

The second Link Type includes all records from the Names table and only matching records from the Colors table.  This produces the following results:

Notice that all names from the original Names table are included in this result set even though some have no corresponding entry in the Colors table.  You can add normal search filters to the Names table to suppress the names that would otherwise be included.  Any filters added to the colors table would only display the matching colors (thereby overriding the Link Type).

The third Link Type includes all records from the Colors table and only matching records from the Names table.  This produces the following results:

Notice that all colors are included in this result set even if they have no corresponding names. You can add normal search filters to the Colors table to suppress the colors that would otherwise be included.  Any filters added to the names table would only display the matching names (thereby overriding the Link Type).

As you can see, Link Type is a powerful tool.  You should experiment with small records sets before using it on large databases.  Once you understand the power available from Link Types, you'll be using them quite often.

Ok

The [OK] button accepts the changes you have made to the Link Operator and Link Type, and closes the "Link Tables" dialog.

Cancel

The [Cancel] button closes the "Link Tables" dialog without accepting any changes made.

Delete

The [Delete] button removes the current link and closes the "Link Tables" dialog.

Help

The [Help] button displays the "Link Tables" help topic.

See Also

•	Add Tables

•	Add Fields

•	Edit Fields


Add Fields

After you have added tables to your report, selecting fields is a three step process

1.	Press the left mouse button over the selected field

2.	Drag your mouse down to the query grid.

3.	Release the left mouse button.

Once you have released the left mouse button, the field will appear in the query grid.  You may have as many fields as you need in your report, limited only by your hardware and memory.

Use this [image] button to insert a new, blank field in between existing fields.

Use this [image] button to remove existing fields from a report.

Use this [image] button to change the settings and filters of a field.

To rearrange fields, perform the following steps:

1.	Point to the field header

2.	Hold your left mouse button

3.	"Drag" the field left or right

4.  "Drop" the field by releasing the left mouse button

See Also

•	Add Tables

•	Edit Fields


Edit Fields

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

The "Edit Field Properties" 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 automatically by Shazam Report Wizard.  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 a field will be displayed in your report. The two most common settings are

•	Show, to include the field in Preview Records

•	Hide, to hide the field from Preview Records and Preview Report.

Use the Hide setting to search on fields that you do not need to display.

Note:  Some database engines do not permit sorting on hidden fields.

See Summarizing Data for more information on how to use the Show property.

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.

The Define Search sort order and Layout Page section order must match.  If they do not, Shazam Report Wizard will prompt you for automatic correction.

Format: The Format property determines the manner in which the field will be displayed.  See Date Formatting Masks and Numeric Formatting Masks  for more information.

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.

Navigating from the "Edit Field Properties" Dialog

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 arrow 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 buttons automatically save any changes to the current field before moving.  Remember this when you press the [Cancel] button and find that some changes

have already been saved.

While the Left and Right navigation commands change fields, the Next and Previous buttons change Search Filters within the same field.  Each field 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.

Filter Values

See Filter Records for detailed information about using Shazam's filtering process.

See Also

•	Filter Records

•	Summarize Data

•	Date Formatting Masks

•	Numeric Formatting Masks


Filter Records

Filtering records is at the heart of meaningful reports and Shazam's filtering process is about as easy as it gets.  You edit search filters from the "Edit Field Properties" dialog (see Edit Fields) for more information).

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 that 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).

Understanding Filters with Aggregate Calculations

Filter values are treated differently on fields with a Show setting of Sum, Count, Average, Minimum or Maximum.  For example:

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

•	"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, but they must have a Show setting of Group or Hide.

See Also

•	Edit Fields

•	Summarize Data


Advanced Topics
Advanced Topics: Search


Custom Expressions

In addition to displaying fields as they exist in tables, you can also create field expressions to calculate values from two or more fields, such as (ITEMS.QTY * PARTS.Price).

To display the "Custom Expressions" tab of the "Edit Field Properties" dialog, follow these two steps:

•	Place your cursor on a blank field in the query grid.

•	Double-Click on the blank field or press this [image] button.

See Edit Fields for detailed information about the buttons that appear on this dialog.

Using Custom Expressions

Double-click on Tables/Fields and Operators/Functions to add them to the custom expression field.  Always observe the following rules when using custom field expressions:

•	When adding field names to a custom expression, always double-click on fields from the tables outline rather than entering them by hand.  Shazam Report Wizard automatically checks for reserved words and punctuation when fields are selected from the tables outline.

•	Use a table name (as aliased by SRW) in a custom expression.

•	Surround table and fields names that have punctuation or spaces, or are reserved words with double quotes.  Example: CUSTOMER."Active" and "CUST-95"."Active" both require quotes.  If selected from the tables outline, they will be automatically quoted.

•	Some database engines do not support filters on custom expressions, such as Paradox and Dbase for Windows 3.1.  See Paradox and dBase for a work around.

If these rules are not followed faithfully, the SQL code that is generated may fail. If you receive a TOKEN error after executing a report, the problem is most likely a missing table name in a custom expression, or a name that cannot be processed correctly without double quotes.

Resolving Token Errors

Shazam Report Wizard automatically checks for over 270 reserved words, spaces and punctuation when fields are selected from the tables outline.  However, if a TOKEN error does occur on a field name chosen from the tables outline, you can use custom field expressions to work around the problem.  This is a two step process:

•	Add quotes to the Header if it matches the field name.

•	Select the field from custom field expressions and add quotes.

NOTE:  If you already have an existing database field selected, and you create a Custom Expression by switching tabs, the original field will be replaced in the query grid when you press [OK].  This allows you to convert a specific field to a custom expression without leaving the "Edit Field Properties" dialog.

See Also

•	Edit Fields

•	What is Query-By-Example?


Summarize Data

The Show property determines how 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 occurrences in a set of records.

Average: Display the minimum value in a set of records.

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

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

Understanding Show with Aggregate Calculations

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:

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

•	"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, but they must have a Show setting of Group or Hide.

Key rules governing Show

Key points to remember when using the Show property:

•	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.

See Also

•	Edit Field

•	Filter Records


View SQL

The View SQL tab displays the "Structured Query Language" (SQL)  code that is generated by Shazam Report Wizard based on your query definition.  The SQL language is the defacto standard for searching databases.

You will not normally need to interact with the View SQL tab, unless you are interested in the SQL code that Shazam is writing for you.  If available, you can also use the "Edit | Copy" menu to copy generated SQL code from Shazam Report Wizard to another SQL-compliant program.  This tab is also helpful if the Borland Database Engine (BDE) reports an error within the SQL command.

Note:  Manual changes made to SQL code are not be recognized by the visual Query-By-Example or Layout engines, nor are changes to SQL code saved in the report.  Manual changes should only be used to modify the WHERE clause or test for syntax errors.

See Also

•	What is Query-By-Example?


Format Dates

The Format Date property determines the manner in which a Date, Time or DateTime field will be displayed.  Valid formatting masks include the following:

c

Displays the date using the format given by the ShortDateFormat system setting, followed by the time using the format given by the LongTimeFormat system setting. The time is not displayed if the fractional part of the DateTime value is zero.

d

Displays the day as a number without a leading zero (1-31).

dd

Displays the day as a number with a leading zero (01-31).

ddd

Displays the day as an abbreviation (Sun-Sat) using the strings given by the ShortDayNamessystem setting.

dddd

Displays the day as a full name (Sunday-Saturday) using the strings given by the LongDayNames system setting.

ddddd

Displays the date using the format given by the ShortDateFormat system setting.

dddddd

Displays the date using the format given by the LongDateFormat system setting.

m

Displays the month as a number without a leading zero (1-12). If the m specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.

mm

Displays the month as a number with a leading zero (01-12). If the mm specifier immediately follows an h or hh specifier, the minute rather than the month is displayed.

mmm

Displays the month as an abbreviation (Jan-Dec) using the strings given by the ShortMonthNames system setting.

mmmm

Displays the month as a full name (January-December) using the strings given by the LongMonthNames system setting.

yy

Displays the year as a two-digit number (00-99).

yyyy

Displays the year as a four-digit number (0000-9999).

h

Displays the hour without a leading zero (0-23).

hh

Displays the hour with a leading zero (00-23).

n

Displays the minute without a leading zero (0-59).

nn

Displays the minute with a leading zero (00-59).

s

Displays the second without a leading zero (0-59).

ss

Displays the second with a leading zero (00-59).

t

Displays the time using the format given by the ShortTimeFormat system setting.

t

Displays the time using the format given by the LongTimeFormat system setting.

am/pm

Uses the 12-hour clock for the preceding h or hh specifier, and displays 'am' for any hour before noon, and 'pm' for any hour after noon. The am/pm specifier can use lower, upper, or mixed case, and the result is displayed accordingly.

a/p

Uses the 12-hour clock for the preceding h or hh specifier, and displays 'a' for any hour before noon, and 'p' for any hour after noon. The a/p specifier can use lower, upper, or mixed case, and the result is displayed accordingly.

ampm

Uses the 12-hour clock for the preceding h or hh specifier, and displays the contents of the TimeAMString system setting for any hour before noon, and the contents of the TimePMString system setting for any hour after noon.

/

Displays the date separator character given by the DateSeparator system setting.

:

Displays the time separator character given by the TimeSeparator system setting.

'xx'/"xx"

Characters enclosed in single or double quotes are displayed as-is, and do not affect formatting.

Format specifiers may be written in upper case as well as in lower case letters--both produce the same result. If the string given by the Format parameter is empty, the date and time value is formatted as if a 'c' format specifier had been given.


Format Numbers

The Format property determines the manner in which a numeric fields will be displayed.  Valid formatting masks include the following:

0

Digit placeholder. If the value being formatted has a digit in the position where the '0' appears in the format string, then that digit is copied to the output string. Otherwise, a '0' is stored in that position in the output string.

#

Digit placeholder. If the value being formatted has a digit in the position where the '#' appears in the format string, then that digit is copied to the output string. Otherwise, nothing is stored in that position in the output string.

.

Decimal point. The first '.' character in the format string determines the location of the decimal separator in the formatted value; any additional '.' characters are ignored. The actual character used as a the decimal separator in the output string is determined by the DecimalSeparator system setting. The default value of
DecimalSeparator is specified in the Number Format of the International section in the Windows Control Panel.

,

Thousand separator. If the format string contains one or more ',' characters, the output will have thousand separators inserted between each group of three digits to the left of the decimal point. The placement and number of ',' characters in the format string does not affect the output, except to indicate that thousand separators are wanted. The actual character used as a the thousand separator in the output is determined by the ThousandSeparatorsystem setting. The default value of ThousandSeparator is specified in the Number Format of the International section in the Windows Control Panel.

E+

Scientific notation. If any of the strings 'E+', 'E-', 'e+', or 'e-' are contained in the format string, the number is formatted using scientific notation. A group of up to four '0' characters can immediately follow the 'E+', 'E-', 'e+', or 'e-' to determine the minimum number of digits in the exponent. The 'E+' and 'e+' formats cause a plus sign to be output for positive exponents and a minus sign to be output for negative exponents. The 'E-' and 'e-' formats output a sign character only for negative exponents.

'xx'/"xx"

Characters enclosed in single or double quotes are output as-is, and  do not affect formatting.

;

Separates sections for positive, negative, and zero numbers in the format string.


Layout Page


What is a report?

Creating a report is a like writing a letter.  You start with an opening paragraph followed by topics, sub-topics, detailed comments and final conclusions.  Of course, letters are not required to follow this or any structure.  Reports, however, are structured documents.  They follow a strict outline that helps you turn raw data into meaningful information. For example, a report may begin with a Report Header, which summarizes the purpose of the report and only appears on the first page.  Likewise, when the report has completed, you may want a Report Footer, which summarizes the entire report.
The Report header and footer are called sections or bands of the report.  As the graphic above shows, all sections with a Header have an alter ego, the Footer.  Headers are like an opening paragraph in a letter, while footers are the closing paragraph.  Reports have several types of opening a closing paragraphs.

The second type of section is the Pager Header, which always appears at the top of the page.  Its alter ego, the Page Footer, always appears at the bottom of the page.  These sections are especially useful for displaying page number, report title and other information that must appear on each page of the report.  Page Header and Footer produce the same results as the "View, Header and Footer" menu in Microsoft Word but are defined quite differently.

The third type of section, Group Header combines records from a database that are of the same type and presents them as a single package of information.   For example, suppose you have Customer and Order tables.  As the graphic at right shows, the customer name is repeated for each order when a Group Header is not used.  By adding a Group Header, we are now able to see orders for each customer more clearly. If we added a Group Footer, we could total the [AmountPaid] field by customer.  The fourth and final type of section in a report is the Detail Section.  Its purpose is to display individual records at the lowest level of detail in your report.  The example at right shows order information (SaleDate and AmountPaid) as the detail section of the report.		Customers without Group Header Customers with Group Header

See Also

   •	What is Query-By-Example?

   •	Using Define Search

   •	Using Layout Page


Using Layout Page

Layout Page is like a painter's canvas, and the Page Designer [image] dialog is like a painter's palette and brush set.  As a painter transforms simple colors into works of art, you will use Layout Page to transform rows and columns of raw data into professional business reports.

The graphic below shows a picture of Layout Page with key elements highlighted.  Information following the graphic describes how to use Layout Page.

Arrange Sections

You may add, edit and delete Sections by displaying the Arrange Sections tab of the Page Designer dialog.

Resize Sections

To change the height of a section, follow these steps:

1.  Point to the blue separate line, waiting until your mouse changes to an up-down (resizing) arrow.

2.  Press the left mouse button

3.  Move the section up or down as desired

4.  Release the left mouse button.

All objects below the current section will move accordingly.

Note:  When decreasing section height, the lowest object within the section determines the minimum height you can resize to.

Place Objects

You may place fields, labels, memos, shapes, images and other objects from the Place Objects tab of the Page Designer dialog.

Customize Objects

You may customize the characteristics or properties of layout objects from the Customize Objects tab of the Page Designer dialog.

Align Objects

To align objects, see the Align Objects topic.

Select Objects

To select objects, follow these steps:

1.  Point to an object with your mouse.

2.  Press the left mouse button to display the object's selection and resizing handles.

3.  Release the mouse button.

Select Multiple Objects

To select more than one object object at the same time, choose one of the following options

•	Hold the shift key while selecting objects.

•	Point to the layout page, press the left mouse button and drag the mouse down and to the right to contain or lasso the desired objects.

•	Select the "Edit | Select All" menu option.

Move Objects

To move objects, follow these steps:

1.  Select one or more objects.

2.  Point to the middle of any selected object (away from its selection handles).

3.  Press the left mouse button, drag the objects to the desired location and release the left mouse button.

Unselect Objects

To unselect objects, choose one of the following options:

•	To unselect all previously selected objects, point to any area of the layout page that does not contain objects and press the left mouse button.

•	To unselect one previously selected object, hold the shift key, point to the object, press and release the left mouse button.

Delete Objects

To delete objects, follow these steps:

1.  Select one or more objects.

2.  Press the Del key or select the "Edit | Clear" menu option

Cut, Copy and Paste Objects

To cut, copy and paste objects, follow these steps:

1.  Select one or more objects.

2.  To cut objects, press Ctrl-X or select the "Edit | Cut" menu option.

2.  To copy objects, press Ctrl-C or select the "Edit | Copy" menu option.

3.  To Paste objects, press Ctrl-V or select the "Edit | Paste" menu

Note:  If you close the main Shazam Report Wizard window before pasting objects, the clipboard will be cleared and you will not be able to paste previously copied objects when you open Shazam Report Wizard again.  If you are trying to copy objects from one report to another, simply open the second report without closing the main Shazam Report Wizard window.

Resize objects

To resize objects, follow these steps:

1.  Select one object (resizing does not work when multiple objects are selected).

2.  Point to the resizing handles that appear when an object is selected.

3.  Press the left mouse button, drag the object to its new size and release the left mouse button.

You can also resize objects by changing the Left, Top, Right and Bottom properties within the Customize Objects tab of the Page Designer dialog.

Context-Sensitive Menu

You can display the context-sensitive menu at any time by pressing the right mouse key.  This menu gives you quick access to the individual tabs of the Page Designer, as well as the Caption property.  You can also use this menu to cut, copy and paste objects, display report setup and activate object alignment.

See Also

   •	Arrange Sections

   •	Place Objects

   •	Customize Objects


Creating your first report


Arrange Sections

To arrange sections, press the Page Designer icon [image] or right mouse click over the layout and select the "Arrange Sections" menu to display following dialog:

The Arrange Sections tab of the Page Designer displays all active sections of a report in a hierarchical format.  If a section has been suppressed from printing (see Edit Section), the word "(Suppressed)" will appear to the right of the section name.

You may also perform the following actions from the Add Section dialog:

Edit

Pressing the button to the immediate right of a section displays the Edit Section dialog, allowing you to edit properties of existing sections, and add footers to existing headers.

Add

Pressing the Add button displays the Add Sections Wizard, which walks you through the process of adding one or more sections to a report.

Delete

The Delete button removes a section and all of its objects from a report (use with caution).

Accessing pop-up menu options

Press the right mouse button over the Page Designer dialog to display its pop-up menu.  This menu displays Report Setup, sets the default font, and sets the Page Designer's "Stay On Top" status.

See Also:

•	Place Objects

•	Customize Objects

•	Using @ Functions


Edit Section

The Edit Section dialog allows you to change properties of a section.  You can access this dialog from the Arrange Sections tab of the Page Designer dialog.

This dialog offers the following options:

Suppress Printing

When checked, this section will not print and the section will appear with the word "(Suppressed)" in the Arrange Sections tab of the Page Designer dialog.  This allows you to display a Report Footer even if your Report Header has no objects.  It also allows you to suppress the Detail Section of a report, displaying only summary information by Group.

Header has Footer

When unchecked, the current header section does not have a footer.  You can add a footer by checking this option.  Note: Unchecking a checked "Header has Footer" option will delete the footer and all of its objects.

Start new page

When checked, a new page will be started when the current group changes.  This can be useful to force selected groups to start on a new page.

Start new page number

When checked, a new page number will be started when the current group changes.

See Also

   •	Arrange Sections


Add Section

The Add Sections Wizard is a powerful tool to add one or more sections to a report at the same time.  You can access this wizard from the Arrange Sections tab of the Page Designer dialog.

The Add Sections Wizard displays the following panels:

Add Sections

The Add Sections panel displays all possible sections that can be added to a report.  Disabled sections cannot be added again to the report because the maximum number of that section type have already been added to the report.  In the example above, Page Header and Footer have already been added to the report and cannot be added again.

When selecting section Headers, also decide if you want section footers to appear.

Note: You cannot add section footers from the Add Sections Wizard for section headers that already exist in the report.  Use the Edit Section dialog to add footers to existing headers.

When you have selected the sections you want, press the [Next] button.

Add Groups

If you selected Group Header from the Add Sections panel, the Add Groups panel will be displayed.

The Add Groups panel displays "Available Fields" that have not already been used as groups in the report, and allow grouping (e.g. memo and graphic fields do not appear).

To add groups, press the right arrow ( > ).  To remove groups that were selected within the Add Sections Wizard, press the left arrow ( < ).

Note: To remove groups that already exist within the report, return to the Arrange Sections tab of the Page Designer dialog, and press the [Delete] key.

When you have selected the groups you want, press the [Next] button.

Confirm Choices

The final panel of the Add Sections Wizard displays the number of sections that you have requested.  Press [Finish] to add your selected sections, [< Back] to make changes or [Cancel] to stop adding sections.

Note: As a result of adding new groups, the section order in Layout Page and sort order in Define Search may not match.  If they do not, Shazam Report Wizard will inform you of the problem when the query is executed and offer to automatically change the Define Search sort order to match the Layout Page section order.

See Also

   •	Arrange Sections

   •	Edit Section


Place Objects

To place objects, press the Page Designer icon [image] or right mouse click over the layout and select the "Place Objects" menu to display the following dialog:

Placing Objects

The left-hand side of the Page Designer's "Place" tab displays all the objects available within the designer.  To place these objects, press an icon and point to the layout page (pick-and-plop).  To place multiple copies of an object, hold your shift key and click on the layout page as many times as needed.  To cancel the placement of an object, select the Pointer icon.

Information about specific objects follows:

Object	Comments
Label	Displays 1 to 245 characters
Memo	Displays 1 to 32,767 characters.  For maximum performance, use labels where possible.
Rectangle, Circle, Lines	Displays various shapes.
Image	Displays Windows Bitmap (BMP).
Date, Time	Label object with @Date or @Time function.
Page Number, Page Count	Label object with the @PageNumber or @PageCount function.
Barcode	Label object that displays its contents as a barcode (requires third-party add-on; contact your software provider to have barcodes activated).
Fields	Fields are label objects containing a [Field Header] in square brackets.  The [Field Header] must have been defined in Define Search before field data will be displayed.

Placing Fields

The right-hand side displays all the fields that were previously selected from Define Search.  To place fields, press the left mouse button over the selected field, move your mouse over the layout page and release the left mouse button (drag-and-drop).  To place multiple fields, hold your shift key, select all desired fields and drag-and-drop them as described above (holding the shift key throughout the process).

Placing Fields without Captions

Captions are normally added when placing fields.  To turn this off, uncheck the "Automatically Caption Fields" option.

Placing Fields with Aggregate Calculations

To place fields with aggregate calculations, select Sum, Count, Minimum, Maximum or Average from the "Calculate Field" combo box.  This will cause the field name to be surrounded by a calculation function.  See Using @ Functions for more information.

Accessing pop-up menu options

Press the right mouse button over the Page Designer dialog to display its pop-up menu.  This menu displays Report Setup, sets the default font, and sets the Page Designer's "Stay On Top" status.

See Also:

•	Arrange Sections

•	Customize Objects

•	Using @ Functions


Customize Objects

To customize objects, press the Page Designer icon [image] or right mouse click over the layout and select the "Customize Objects" menu to display the following dialog:

All objects on a page have characteristics or properties that you can change.

To change the properties of multiple objects at the same time, hold the shift key, click the desired objects, release the shift key and change their properties.

If all selected objects are of the same type (e.g. label), all properties except General will be available.  If you mix object types, only common properties (e.g. Dimensions) will be available.

Captions and Memos

When Label or Memo objects are selected, a second tab will appear at the bottom of the Properties list.  The Caption or Memo tab displays the current value in a wider area than other properties to improve readability.  The display also includes the current font and color, which can be changed from the Properties tab.

If you want to reference a field name in the caption of a label or memo, surround the field alias or header from Define Search in square brackets, as in [AmountPaid].  If you want to perform aggregate calculations on a field, surround the field alias in one of the supported functions, such as @Sum([AmountPaid]).  If you want to suppress all information between a starting and ending point if the field is blank, use the @IsData function.  See Using @ Functions for more information. Always reference a field name by its Header in Define Search.  If the physical field name is "Paid" but its Header is "AmountPaid", you must use [AmountPaid] in Layout Page.

Note: Label objects are limited to 245 characters, while memo objects are virtually unlimited in the number of characters they can hold.  For maximum performance, use label objects where possible.

Tip:  For quick access to this property, select an object, press the right mouse key and choose the "Edit Caption" menu option.

When you have completed your updates, point at the layout page or select Preview Report to apply your changes.

Selecting objects by name

To select an object by name, use the Object Name combo box that appears at the top of the "Customize" tab.  This is also a helpful method of viewing all object names within your layout.

Accessing pop-up menu options

Press the right mouse button over the Page Designer dialog to display its pop-up menu.  This menu displays Report Setup, sets the default font, and sets the Page Designer's "Stay On Top" status.

See Also:

•	Arrange Sections

•	Place Objects

•	Using @ Functions


Align Objects

Shazam Report Wizard offers desktop-publishing style alignment guides.  This gives you extremely powerful features to align all objects horizontally and vertically.

Creating Alignment Guides

To create alignment guides, do the following:

1.  From Layout Page, point to the horizontal or vertical ruler.

2.  Press your left mouse button.

3.  Drag an alignment guide to the page.

4.  Release your mouse.

Using Alignment Guides

To use alignment guides, move any object within 5 pixels of the guide and it will snap to position when you release the mouse.

Deleting Alignment Guides

To deleting alignment guides, do the following:

1.  Point to the alignment guide and press the left mouse button.

2.  Drag the alignment guide to its corresponding ruler.

3.  Release the mouse button.

See Also:

•	Arrange Sections

•	Customize Objects

•	Using @ Functions


Using @ Functions

Shazam Report Wizard supports 10 special functions available in Layout Page, as follows:

@DATE

Displays the current date according to the system clock.

@TIME

Displays the current time according to the system clock.

@PAGENO

Displays the current page number.

@PAGECOUNT

Displays total pages in the report.

@ISDATA~strings and [fields] and strings~

Displays strings and fields between delimitors ( ~ ) if the [fields] within the delimitors contain data. This function is especially helpful for mailing labels, such as:

[FirstName] @ISDATA~and [SpouseName] ~[LastName]

where "and [SpouseName] " will only be displayed if the [SpouseName] field contains data.

@SUM([FieldHeader])

Totals numeric fields within the current section.

@MIN([FieldHeader])

Displays the minimum field value within the current section.  If the field is a string value, only the first 15 characters are used to determine the minimum value, without regard to case.

@MAX([FieldHeader])

Displays the minimum field value within the current section.  If the field is a string value, only the first 15 characters are used to determine the maximum value, without regard to case.

@AVG([FieldHeader])

Displays the average value of numeric fields within the current section using the formula @SUM divided by @COUNT.

@COUNT([FieldHeader])

Displays the number of records within the current section.

Using @ Functions

All functions are case insensitive and can be used as part of a field or label caption.  Examples include:

Page @PageNo of @PageCount

This caption displays "Page 1 of 10"

The minimum value is @MIN([FieldHeader])

This caption displays "The minimum value is 10"

Report printed @DATE at @TIME

This caption displays "Report printed May 7, 1998 at 12:30 pm (the actual date and time formats depend on how Shazam Report Wizard was configured by your software provider).

See Also:

•	Place Objects


Preview Report


Preview Report

The "Preview Report" tab offers a way to view your report while you are designing it.  This not only saves paper but it also saves time.

To preview a report in progress, press the "Preview Report" tab or select the "View | Preview Report" menu.

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

Press the Print [image]  toolbar button or select "File | Print Report" to print the report.

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

See Also:

•	What is Query-By-Example?


Preview Records

You can preview the records of a report anytime by selecting the "Preview Report" tab and the "Preview Records" icon on the toolbar.  You can adjust the title, sub-title, footer and fonts used when printing the grid from the Grid tab of Report Setup.

See Also

•	Preview Report

•	Grid Setup


Export Reports

You can export files from Shazam Report Wizard while previewing a report by selecting the File, Export menu or the "Print To File" option when printing a report.

The Export To File dialog supports a variety of file formats.

The *.SHZ format is useful for sending reports by disk, e-mail and the Internet (requires the SHZ16.EXE or SHZ32.EXE programs to view).  These files are automatically compressed by Shazam Report Wizard and ready for immediate distribution.  When exporting to the SHZ format, please keep in mind that you cannot export a range of pages; you must export the entire report.

The Paradox and dBase formats save the records of the report without any formatting.  The fixed field text file is useful for viewing records as plain text.  The delimited ASCII format is useful with mail-merge programs.

See Also

•	Preview Report


Report Viewer

Start here


Report Setup


Page Settings

To format your report, press this [image]  button or select the "Edit | Edit Report Properties" menu.  This will display the Report Setup dialog:

The Page tab consists of the following elements:

Paper Size

Determines the Height and Width of the page.  Selecting Custom allows you to enter a custom page size.

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.

See Also

•	Report Setup: Label

•	Report Setup: Grid

•	Report Setup: Options


Label Settings

The Label tab allows you to enter all the information required to define a label.

This tab consists of the following elements:

Choose current type of report

Shazam Report Wizard supports two types of reports:

•	Standard with Headers, Footers, Groups, Totals, and

•	Label with a repeating format across and down the page.

Label reports do not support the banding features of Standard reports.  When you define the current report using the label format, the "Layout Page" tab changes to a single band called "Label."  When you press [Ok], Shazam Report Wizard will confirm that you want to convert the report to a label format.  You can always return to this dialog and simply reset the report to a standard format if you accidently selected this item.

Label Dimensions

Labels are defined with eight data elements as listed above, plus the page size.  The Top and Left Margins are automatically transfered to the "Page" tab and its Bottom and Right Margins are calculated automatically. If you enter label dimensions manually, you must confirm that the page size is correct.

Choose Standard Label Format

Instead of entering label dimensions manually, select the [Choose Standard Label Format] button to display the following dialog:

This dialog offers over 150 pre-defined label formats.  Simply choose the Category and double-click on a label format.  The label's dimensions will be transfered to the label dimensions grid in the Report Setup dialog.

See Also

•	Report Setup: Page

•	Report Setup: Grid

•	Report Setup: Options


Grid Settings

The Grid tab allows you to setup row and column reports when a layout has not been created or you want a fast printout.

This tab 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

•	Report Setup: Page

•	Report Setup: Label

•	Report Setup: Options


Report Options

The Options tab allows you to define query and layout options.

This tab consists of the following elements:

Query

The "Suppress Duplicates" option informs Shazam Report Wizard to exclude more than one instance of a record with identical field values.

Layout

The Layout options determine whether Margins, Sections and Guides will be displayed when the Layout Page tab is selected.

See Also

•	Report Setup: Page

•	Report Setup: Label

•	Report Setup: Grid


Explore Reports


Getting Started
Getting Started with Reports Explorer

start here


Opening Objects

start here


Creating Objects

start here


Saving Objects

start here


Moving Objects

start here


Deleting Objects

start here


Frequently Asked Questions

   •	How do I display Customers who have ordered Products A and C and D?

   •	How do I calculate values and group the calculations by other fields?

   •	How do I search where Field1 is True or Field2 is True?

   •	How do I search for blank or non-existent data?

   •	How do I search for today's date?

   •	How do I prevent "token" errors?

   •	How do I convert data types?


How do I display Customers who have ordered Products A and C and D?

It is common to search for Customers who have ordered products A, C or D, as follows:

This will return customers who have ordered either product A, C or D.

We need a query that will return customers who have ordered all three products, and exclude customers who have ordered none, one or two of the listed products (customers who have ordered additional products besides the listed ones are also acceptable).  The following report achieves the goal for all database systems except Paradox and dBase in Windows 3.1:

The solution is to add three "instances" of the ORDERS table, joining each one to the same CUSTOMERS table and searching for a specific code within each instance of the ORDERS table.  Only customers with all three products will be included in the report.

The Paradox and dBase solution for Windows 3.1

The technique above does not work in Windows 3.1 when searching against local Paradox and dBase tables (a BDE 2.x limitation).  The solution: Break the combined query above into three separate reports, each using CUSTOMER and ORDERS once and each searching for a specific product code.  Then export the results to ORDER-A.DB, ORDER-C.DB and ORDER-D.DB.  Finally, create a fourth report joining CUSTOMER to all three temporary tables in the same fashion pictured above.  The results will be identical.


How do I calculate values and group the calculations by other fields?

A field's Show property controls how fields are processed and displayed.  Consider the query below:

This query calculates the Sum of Orders Paid by Company and Sale Date, excluding any orders that were not sold between 1/1/94 and 12/31/94.  Once this part of the report is completed, additional records are excluded where the Sum of Total Paid is less than $1,500.  This means that Shazam Report Wizard and your database management system are filtering on the values of records (Sale Date) and on the results of Calculations (Sum of Total Paid) in the same report!

Many database management systems do not permit additional fields in this type of query unless they have a Show property of Group or a calculation (Sum, Count, Min, Max, Average).  As a general rule, keep the number of fields for this type of report to a minimum.  You can also export the results of this report to a temporary table, and use that table in a second report to display additional data.

See Also

•	Edit Fields

•	Summarize Data

•	Export Records


How do I search where Field1 equals True or  Field2 equals True?

See What is Query-By-Example? regarding single and multi-line queries.


How do I search for blank or non-existent data?

Type BLANK as a filter value.  See Filter Records for more information.


How do I search for Today's date?

Type TODAY as a filter value.  See Filter Records for more information.


How do I prevent "token" errors?

Token errors are usually caused by invalid or incomplete field expressions, such as a missing parenthesis or an unquoted table or field name that is also a reserved word in the SQL language.  See Custom Expressions for more information.


How do I convert data types?

If you need to multiply two fields by each other, you can create a custom expression:

Table1.Field1 * Table2.Field2

However, if the first field contains numeric data but is stored as text, you must convert it before using it in a mathematical calculation.  Many database management systems support a conversion function.  Thus, if you are using Paradox or dBase in Delphi or C++ Builder, you would define the expression above as follows

CAST(Table1.Field1 AS integer) * Table2.Field2

If you are using SQL Server, you would use the CONVERT function.

See your database management system's documentation for more information about converting data types.

Note: The CAST function is not supported in Delphi 1.0 against Paradox and Dbase data sources.


How do I search on dates without years?

Question:

How do I select customers whose birth dates fall between January 20 and March 15 regardless of the year?

Answer:

Add the following custom expressions to your report:

EXTRACT(MONTH FROM Customer.DateofBirth) AS FilterMonth

EXTRACT(DAY FROM Customer.DateofBirth) AS FilterDay

Next, add the following three filters:

FilterMonth	FilterDay

-----------	---------

Filter 1	Equals 1	>= 20

Filter 2	Equals 2

Filter 3	Equals 3	<= 15

This query will return all records that fall:

1.	On or after January 20.

2.	Anytime in February.

3.	On or before March 15.

If you need a larger range of dates, say between January 20 and September 15, your query will look like this:

FilterMonth	FilterDay

-----------	---------

Filter 1	Equals 1	>= 20

Filter 2	Between 2 And 8

Filter 3	Equals 9	<= 15

If you need a range of months regardless of day or year, your query will look like this:

FilterMonth

-----------

Filter 1	Between 1 And 9

where Filters 2 and 3 plus the FilterDay column have been removed.

Note for Delphi 1.0 users:

Delphi 1.0 does not permit filtering on custom expressions for local Paradox and dBase data.  To work around this limitation, you need to break the report described above into three steps:

1.	Create a report with the EXTRACT functions described above but do not add filters in the FilterMonth and FilterDay columns.

2.	Export the results of the first report to a local temporary table.

3.	Create a second report based on the local temporary table to filter on the FilterMonth and FilterDay columns as described above.

The results will be identical.


Database Information

   •  Paradox and dBase

   •  Microsoft Access

   •  Microsoft SQL Server, Sybase SQL Server

   •  InterBase

   •  Oracle

   •  Sybase SQL Anywhere


Paradox and dBase

Windows 3.1 Only

If you include Filter Values on calculated columns, such as (PARTS.Qty * ITEMS.Cost), Shazam Report Wizard will display the following message:

"The Borland Database Engine cannot perform searches on calculated columns when using Paradox and dBase tables.  See 'Troubleshooting Tips and Tricks in SRWHELP.HLP for a workaround."

You will see a similar message if you try sorting on calculated columns.

To work around this limitation, do the following:

•	Remove all filter values and sorts from calculated fields only.

•	After previewing the report, select "File | Export" and export the data to a temporary table.

•	Create a second report using the temporary table, and enter filter values and sorts on the previously calculated fields.


Microsoft Access

Microsoft Access is fully in Delphi 3.0 or higher and C++ Builder 1.0 or higher via Borland's native Access driver.

Delphi 1.0 and 2.0 can support Access via ODBC.  The only requirement is that the ODBC DSN setting contain the word "ACCESS" (as in "MS Access 2.0 Databases", which is the default ODBC Access driver name).


Microsoft and Sybase SQL Server
SQL Server

Only one database may be accessed at a time with SQL Server.  Left and Right joins are fully supported.  Use views in a single database to access tables in a second database.


InterBase

Only one database may be accessed at a time with InterBase. Use views in a single database to access tables in a second database.  Left and Right joins are supported for two tables at a time.  Be careful not to select field headers which use reserved words or contain reserved characters.  InterBase will not accept these.


Oracle

Only one database may be accessed at a time with Oracle.  Use views in a single database to access tables in a second database.  Left and Right joins are fully supported.


Watcom/Sybase SQL Anywhere

Watcom is fully supported via ODBC.  The only requirement is that the ODBC DSN setting contain "WATCOM" or "ANYWHERE"  Only one database may be accessed at a time with Watcom.  Use views in a single database to access tables in a second database.  Left and Right joins are supported for two tables at a time.


Menus


File Menu

The File menu consists of the following items:

Menu	Explanation
New	Clear existing report
Open Report	Open a saved report
Save	Save current report to current name
Save As	Save current report to a new name
Export As Table	The "Export Table" menu option offers you the ability to save the data in a report to a Paradox or dBase file.  This gives other programs access to the data.
Export As Delimited	The "Export Delimited" menu option offers you the ability to save the data in a report to a delimited ASCII file.  This gives other programs access to the data.
Print Report	Print report (only available when Preview window is active)
Printer Setup	Change printer settings
Close Report	Close the main report window

See Also

•	Edit Menu

•	View Menu

•	Help Menu


Edit Menu

The Edit menu consists of the following items:

Menu	Explanation
Cut	Removes highlighted text from the "View SQL" tab
Copy	Copies highlighted text into the "View SQL" tab
Paste	Copies text into "View SQL" from the clipboard
Edit Report Properties	Change page margins, titles and query options
Edit Field Properties	Change field header, show status, filter values, etc.
Add Table	Display the "Add Table" dialog
Remove Table	Removes the currently selected table
Unlink Tables	Removes the currently selected link
Insert Field	Inserts a blank field in the query grid
Delete Field	Removes the current field from the query grid

See Also

•	File Menu

•	View Menu

•	Help Menu


View Menu

The View menu consists of the following items:

Menu	Explanation
Define Search	Displays tab with Query-By-Example interface for adding tables, selecting fields and defining search filters.
View SQL	Displays the optimized Structured Query Language (SQL) code that Shazam Report Wizard automatically generates from the "Define Search" tab.
Preview Report	Displays the printed page on screen, without printing.
First Page	Displays the first page of the report (Preview only)
Next Page	Displays the next page of the report (Preview only)
Previous Page	Displays the previous page of the report (Preview only)
Last Page	Displays the last page of the report (Preview only)

See Also

•	File Menu

•	Edit Menu

•	Help Menu


Help Menu

The Help menu consists of the following items:

Menu	Explanation
Contents	Displays the contents page of the help file in Windows 3.1, or displays the Contents outline in Windows 95/NT.
Search Help	Displays the help file's search dialog, allowing you to enter keywords and fine the topic of interest.
About	Displays information about the current program.

See Also

•	File Menu

•	Edit Menu

•	View Menu