Report Writer

CV Pro

This guide is only applicable to enterprise clients using CV Pro or CV9.

There are three types of reports in ClearVantage: Hard-Coded Reports, Standard Reports and Xtreme Reports. Each of these reports has different functionality and purposes in ClearVantage.

Hard-Coded Reports

Some reports in ClearVantage are built into the application and therefore cannot be modified by general users or ClearVantage Support Team staff. Making changes to these reports requires input from Euclid’s developers. Because these reports are built into the application, they are not available in Information Central. Some examples of hard-coded reports are:

• Comprehensive Event Report

• Hard-Coded Invoice Report

• Event Badge Business Card with Detail

• Individual and Organization Profile reports

Standard Reports

This guide provides information on using and creating Standard Reports in ClearVantage. The easy-to-use ClearVantage Standard Report Writer allows you to build reports to be printed or exported in either a spreadsheet or layout fashion. You can create the queries for the report using the built in query builder as is also found in the Shared List Manager and the Email Blast Tool. Reports are stored in Information Central and Can be accessed also through various menus throughout ClearVantage.

Xtreme Reports

ClearVantage Xtreme Reports (CVxR) are an additional type of report available in Information Central. The CVxR report writer allows you additional functionality beyond what is possible in the standard report writer including multipage layouts, wrapped columns of data, selecting input values from a dropdown and the ability to directly enter SQL queries. Detailed information on using CVxR is available from the ClearVantage Learning Resources page.

Working with Information Central

Informational Central allows you to organize all your custom-built reports in ClearVantage. Information Central can be divided into different folders related to a particular project. You can also sort reports into folders which cause the reports to appear in specific notebooks or lists in ClearVantage.

To access Information Central:

1. Click on Pro Tools in the top right-hand corner of CVPro | Information Central.

   

Creating a New Folder

You can create new folders either in the main level of Information Central or as a sub-folder.

1. Select Pro Tools | Information Central.

2. Click on the folder in which you want to create the new folder.

3. Right click on the folder and select New Folder to open a New Folder window.

   

4. Type the name of the folder in the Folder Name field and Press OK.

Running a Report

Reports can be run from various parts of ClearVantage but also from within Information Central. Running reports from different locations may provide different results depending on the filters in place on the report.

1. From Pro Tools, click on Information Central to open Information Central. From here, you can find all reports already in the system.

   

2. Click on the arrow on the left navigation panel to view the folder contents in the Contents of Info Central Panel on the right.

3. When you find the report you want to open, double click on the report in the Contents of Info Central panel on the right and the Report Wizard window will open.

4. When you find the report you want to open, click on the report in the Contents of Info Central panel on the right and select Design Report and the Report Wizard window will open.

   

5. Click on the Preview tab to preview the report. If the report is designed to prompt you to input, the Filter Values window will appear. Enter the requested information and press OK.

   

6. Select the Close icon in the top right-hand corner of the report when you are done.

Creating a New Report

Though it is often easiest to modify an existing report, sometimes you will need to create a report from scratch.

1. From Pro Tools, select Information Central to open Information Central.

   

2. Click on the arrow on the left navigation panel to view the folder contents in the Contents of Info Central Panel on the right.

3. When you find the folder where you want to create a new report, right click in the Contents of Info Central panel on the right.

   

4. Select Add Report from the menu and a Report Settings window will appear.

5. Enter the title of the new report in the Report Title field.

6. Select the appropriate display option from the Display Option dropdown. This is to determine whether the report should return a CSV data export or a PDF of the layout.

7. Select where the report should flow through using the Access Location radio buttons. If you choose the Reports Menu radio button, select the appropriate report button option from the dropdown.

8. Select the Save Button.

9. Subscribe Users to see this report by clicking the Add for ALL Users button or the Add User button to select individual users.

   

10. Press Save to save the Users List. Click on the close icon in the top right-hand corner to close the Report Settings window.

11. Click on the Design Report button to begin entering your report criteria and formatting.

Reports on the Report Main Menu Dropdown

Adding Reports to the Report Main Menu Dropdown

The ClearVantage Reports Main Menu Dropdown allows you to place reports from Information Central into the Reports Main Menu Dropdown.

To place a report on a ClearVantage Reports Main Menu Dropdown:

1. Open Information Central and navigate to the report you wish to add to the Reports Main Menu Dropdown.

2. Click on report and select Report Properties. This will open the Report Settings window.

3. Select the Report Menu radio button under the Access Location Category.

4. Select the report category by name from the dropdown menu. This will place the report under the dropdown option you choose.

   

5. Click Save. The report will now appear under the Reports Main Menu Dropdown under the category you chose. For example, if you select the Membership category from the dropdown the report will show under the Membership Reports button.

Removing Reports from the Reports Main Menu Dropdown

Use the following instructions if you no longer want a report to appear on the Reports Main Menu Dropdown it is assigned to.

1. Open Information Central and navigate to the report you wish to remove from a Main Menu Dropdown.

2. Click on report and select Report Properties. This will open the Report Settings window.

3. Click on the dropdown under the Access Location radio buttons and click No Value.

   

4. Uncheck the Available in Browser? Flag.

5. Click on Save. Select the close icon in the top right-hand corner to close out of the Report Properties window.

6. Reload CVPro by clicking on Pro Tools | Reload App. This will refresh ClearVantage.

   

7. The report will no longer appear under the Reports Main Menu Dropdown.

Reports on Notebooks and Lists

You can run reports from the Reports menus within various lists and notebooks. For example, you can display reports on the Individual, Organization, Event, Campaign and Committee Lists and Notebooks.

Adding Reports to Notebooks and Lists

Reports can also be placed in a notebook or on a list by placing the report in the appropriate Access Location.

To add a report to a notebook or a list screen:

1. Open Information Central and navigate to the report you want to place on the notebook or list screen.

2. Click on Report Properties to open the Report Settings Window.

3. Click the List/Notebook Report radio button under Access Location.

4. Select the appropriate dropdown option on where you want the report to appear in CV.

• ACTION ITEM LIST REPORTS: Displays report in the Action Items List. This is useful for running reports of action items by customer, organization or staff member.

• APPEAL NOTEBOOK REPORTS: Displays report in the Appeal Notebook. This is useful for running email marketing reports.

• CAMPAIGN NOTEBOOK REPORTS: Displays report in the Campaign Notebook. This is useful for running marketing reports for all appeals within a campaign.

• CONTRACT LIST REPORTS: Displays report in the Advertising Contract List. This is useful for running reports of current advertising insertion orders.

• CONTRACT NOTEBOOK REPORTS: Displays report in the Advertising Contract Notebook. This is useful for running a report of the insertion order for the advertiser.

• CUSTOMER LIST REPORTS: Displays report in the Individual List. This is useful for running reports of membership rosters.

• CUSTOMER NOTEBOOK REPORTS: Displays report in the Individual Notebook. This is useful for running individual profile reports.

• EMPLOYEE LIST REPORTS: Displays report in the Employee List. This is useful for running employee roster reports.

• EMPLOYEE NOTEBOOK REPORTS: Displays report in the Employee Notebook. This is useful for running employee profile reports.

• EVENT LIST REPORTS: Displays report in the Event List. This is useful for running reports of upcoming events.

• EVENT NOTEBOOK REPORTS: Displays report in the Event Notebook. This is useful for running detailed reports of a specific event.

• INVOICE LIST REPORTS: Displays report in the Invoice/Order Notebook. This is useful for running custom printable invoice reports.

• INVOICE NOTEBOOK REPORTS: Displays report in the Invoice/Order Notebook. This is useful for running financial reports such as posting details for a specific invoice.

• ORGANIZATION LIST REPORTS: Displays report in the Organization Notebook. This is useful for running organization profile reports.

• ORGANIZATION NOTEBOOK REPORTS: Displays report in the Organization Notebook. This is useful for running organization profile reports.

• PAYMENT RECEIPT REPORTS: Displays report in the Payment Notebook using the Print and Email Receipt buttons. This is useful for printing and emailing receipts.

• PRODUCT LIST REPORTS: Displays report in the Product List. This is useful for running inventory reports on a list of products.

• PRODUCT NOTEBOOK REPORTS: Displays report in the Product Notebook. This is useful for running inventory reports on a particular product.

• PROJECT NOTEBOOK REPORTS: Displays report in the Project Notebook. This is useful for running task reports for a project.

• PUBLICATION NOTEBOOK REPORTS: Displays report in the Publication Notebook. This is useful for running reports of advertisers in a publication.

• REGISTRATION LIST REPORTS: Displays report in the Registration List. This is useful for running event roster reports.

• REGISTRATION NOTEBOOK REPORTS: Displays report in the Registration Notebook. This is useful for running reports detailing a single registration.

• SUBSCRIPTION NOTEBOOK REPORTS: Displays report in the Dues/Subscription/Donation Notebook. This is useful for running reports of donation details.

• Check the Available in Browser? Flag.

• Click on Save. Select the close icon in the top right-hand corner to close out of the Report Properties window.

• Reload CVPro by clicking on Pro Tools | Reload App. This will refresh ClearVantage.

  

Removing Reports from Notebooks and Lists

Reports can be removed from a notebook or on a list.

To remove a report from a notebook or a list screen:

1. Open Information Central and navigate to the report you want to remove from the notebook or list screen.

2. Click on Report Properties to open the Report Settings Window.

3. Uncheck the Available in Browser? Flag.

   

4. Click on Save. Select the close icon in the top right-hand corner to close out of the Report Properties window.

5. Reload CVPro by clicking on Pro Tools | Reload App. This will refresh ClearVantage.

   

Exporting Report Files

You can export a Standard Report file from one database to transfer it to another database such as from a test database to a live database. This requires exporting the report file from the existing database and importing it into the new database. You can also export a report to be able to save a copy outside of CV.

1. Open Information Central and navigate to the report you want to move to the new database.

2. Right-click on the report and select Export Report. An Export Report As window will open.

   

3. Navigate to the location where you want to save the report.

4. Enter the name you want to use to save the report in the File Name.

5. Confirm that the Save as type field lists Report Files (*.SRW) as this is the format you will need to import the report back into ClearVantage.

6. Press Save to save the report export file.

Importing Report Files

To import a report format that has been saved outside of ClearVantage:

1. Open Information Central and navigate to the folder where you want > to place the report you are importing.

2. Right-click in the folder and select Import Report. An Import Report > window will open.

   

3. Navigate to the location where the report is saved.

4. Click on the report and press Open.

Creating Report Queries

The Query tab in the ClearVantage Report Writer allows you to select which tables and fields are included in the report, how those tables and fields are joined to each other, and to filter for a subset of the data included in those tables and fields. You can also group records, hide fields and find the sum, maximum or minimum value for a field.

Tables and Joins

The first part to creating any report is to select the tables you would like to include in your report. Tables are where data is stored in the database and you must add the tables that contain the data you want to the report. For example, the CUSTOMER table stores information on individuals and the ORGANIZATION table stores information on organizations.

All the tables included in your report must be joined together to connect the data in the various tables. Joins link tables where there is common information in each table. For example, you can link the CUSTOMER table to the ORGANIZATION table because both contain the ORGCD. This allows you to link individuals to the organizations they work for.

If you are unsure what tables contain the data you are looking for, you can reference your organization’s custom data guide or the ClearVantage Field Mapping Guide.

Adding Tables

In order to place a field into a report, the table containing that field must be included in the query and joined to other tables in the query.

1. Click Pro Tools | Information Central to open Information Central.

2. Click on the report that you have created or wish to modify and click the Design Report button.

   

3. Click on the Query tab.

4. Click on the Add Table button or select Insert | Table and the Add Table window will appear.

   

5. You can add all the tables you need to your query by either double-clicking on the table name or clicking on the table name and pressing the Add Table button.

6. Once all the necessary tables have been added, press the Close button.

7. You can now drag and drop the tables on the query tab so that they can be easily seen and worked with. Tables are added one atop the other so you will need to rearrange them by dragging and dropping the windows to see all of the added tables.

Removing Tables

If a table is added inadvertently or is no longer needed in a report, you can remove it.

1. From the Query tab, click on one of the fields in the table window that you want to remove to select that table.

2. Either right-click on the table and select Remove Table or select Edit | Remove table from the menus.

3. A pop-up will ask if you want to delete the table. Press Yes to remove the table from the query or No to leave the table as it is.

Issue Displaying Tables

If a report is opened using multiple monitors, it is possible that the tables added to the report will continue to be formatted to appear on the secondary monitor. This will appear that the tables either do not display in the query tab or that some tables appear off the right edge of the screen. This happens most commonly when a laptop is used with an external monitor and can be addressed in a few ways.

Using a Second Monitor

1. If you have a second monitor or projector available, the simplest solution is to attach the second monitor.

2. Log into ClearVantage and drag the entire application to the larger monitor or extend the display of ClearVantage to span both monitors.

3. Open the report. You may now be able to see some or all of the tables of the report.

4. Drag the tables of the report to the other side of the report.

5. Save and close the report and then bring ClearVantage to the other monitor.

6. Reopen the report and you should be able to see all of the tables.

Editing the Report Code

If the above instructions to not allow you to view the tables of the report, the following will allow you to view the tables of the report.

1. Right click on the report in Information Central and select Export Report.

2. Save the .srw file to your computer.

   

3. Open the file using a text editor such as Notepad.

4. Just below the header of the report file you will see the code that determines the layout of the tables in the report. The code will look like this for each table in the report:

   object TspqQueryTable

   TableAlias = 'Employee'

   TableName = 'Employee'

   Left = 1983

   Top = 25

   Height = 272

   Width = 260

   end

5. For each table, change the number labeled as Left to 300 or a similar number. This number represents the number of units from the left side of the window the table will appear. Numbers over 1000 tend to cause issues with the display.

6. Save your changes and close the text file.

7. Import the file back into ClearVantage.

8. Open the report and the tables should be visible. You may need to adjust the tables a bit so they are not overlapping.

Adding Table Joins

If you need to add more than one table to your report, you must join each of the tables on the report. Joining tables connects the data in one table to the data in another table. This allows you to place information from separate tables onto the same report and pull related information. Fields from a table cannot be used in the report unless the table is joined to the other tables in the report.

1. Determine which tables you need to join in your query and what fields contain the corresponding data in the two tables to join on. Here are some examples:

   1. To report on Organizations and list all the Individuals who work for those Organizations

      1. Add the ORGANIZATION and CUSTOMER Tables

      2. Join the ORGCD from the ORGANIZATION Table to the ORGCD on the CUSTOMER table because the ORGCD on an Individual notebook is what indicates the organization that person works for.

   2. To report on Organizations and list details from the Individual notebook of the Organization’s primary contact

      1. Add the ORGANIZATION and CUSTOMER Tables

      2. Join the CUSTOMERCDLINK from the ORGANIZATION Table to the CUSTOMERCD on the CUSTOMER table because the CUSTOMERCDLINK on an Organization notebook is what indicates the primary contact of that organization.

   3. To report on all the Individuals registered for a particular Event

      1. Add the CUSTOMER and INDSESSION Tables

      2. Join the CUSTOMERCD from the CUSTOMER Table to the CUSTOMERCD on the INDSESSION table because the CUSTOMERCD on a Registration is what indicates the person registered for the event.

2. Click on the field that you are joining from one table and hold down the mouse button.

3. Drag and drop the field you have selected onto the corresponding field from the table to which you are joining.

4. Release the left mouse button. This will join the two tables and you will see a join line connecting the two tables/fields.

   

5. To modify the join, right click on the join line and select Edit Join to open a Join Tables window.

   

6. In the Join Tables window select the relationship between the joining fields from the dropdown menu. Possible selections are:

   • Is Equal To: Finds records that have the same value in both fields. This is the most common relationship.

   • Is Greater Than: Finds records where the value in the field listed above is greater than the value in the field listed below.

   • Is Greater Than or Equal To: Finds records where the value in the field listed above is greater than or equal to the value in the field listed below.

   • Is Less Than: Finds records where the value in the field listed above is less than the value in the field listed below.

   • Is Less Than or Equal To: Finds records where the value in the field listed above is less than or equal to the value in the field listed below.

   • Is Not Equal To: Finds records where the value in the field listed above is not equal to the value in the field listed below.

7. Select one of the following Join Types by clicking on the text and the checkmark will indicate the selected Join Type:

   • Return matching records from both tables: This is an INNER JOIN which will return only records that have matching records in both tables. For example, by joining the Customer table to the Organization table on the ORGCD field, only individual records that have an Organization CD which matches an Organization record will appear in the results. Individual records that do not have an Organization CD will not appear in the list.

   • Return all records from "Customer" AND only matching records from "Organization": This is a LEFT OUTER JOIN where all the records from the first table listed will be included with the matching value from the second table if there is one. If there is not a matching value in the second table, the query will return all of the records from the first table and list NULL where there is not a matching record in the second table. For example, by joining the Customer table to the Organization table on the ORGCD field with the LEFT OUTER JOIN, all individual records will be listed with the matching Organization CD and individuals that do not have a matching Organization record will list NULL as the values from the Organization table.

   • Return all records from "Organization" AND only matching records from "Customer": This is a RIGHT OUTER JOIN where all the records from the second table listed will be included with the matching value from the first table if there is one. If there is not a matching value in the first table, the query will return all of the records from the second table and list NULL where there is not a matching record in the first table. For example, by joining the Customer table to the Organization table on the ORGCD field with the RIGHT OUTER JOIN, all organization records will be listed with the matching Organization CD and Organizations that do not have matching individual records will list NULL as the values from the Customer table.

8. Press OK when you have made your changes to the join.

Removing Tables Joins

1. Click on the join to be deleted so that the line is bold.

2. Right click on the join.

3. Select Remove Join from the menu and a pop-up will ask you if you want to delete the join.

   

4. Click Yes to remove the join between the tables.

Tables Commonly Used in Reports

The following tables are some of the more commonly used tables in reporting. More detailed information on fields and joins to other tables can be found in the ClearVantage Field Mapping Guide.

Organization Information

Table Name	Description
ORGANIZATION	Main organization information
REGIONALTDETAIL	Information on regions where the organization operates
ORGNOTE	Contains main note, 25 word description, buy statements, and service description
ORGFINANCEHISTORY	Historical financial information
ORG_PRODUCT_INVENTORY	Information on products/brands linked to an organization.
ORGTYPEREF	Reference table for the types or organization.
ORG_MORE	A custom table linked to the organization.

Individual Information

Table Name	Description
CUSTOMER	Main individual information
CUSTOMERDESIGNATION	Designations for an individual on the designations tab rather than the profile tab
CUSTOMERADDRESS	Additional addresses stored for the individual
CUSTOMERNOTE	Contains main note, 25 word description, buy statements, and service description
CUSTOMEREDUCATION	Education
CUSTOMER_MORE	A custom table linked to the individual

Events

Table Name	Description
SESSION	Contains general information on any event
SESSIONFUNCTION	Contains information on functions for any event
SESSIONLOCATION	Contains detailed information on various event locations
INDSESSION	Contains individual registration information
INDFUNCTION	Contains function registration information
PROGRAM	Contains information on various program types
PRICELIST	Contains price information for any event
EVENTBOOTH	Contains information on booths being used
EVENTTRAINER	Contains speaker information for any event

Dues/Subscriptions/Donations

Table Name	Description
SUBSCRIPTIONREF	Contains information on available dues/funds/subscriptions
SUBSCRIPTION	Contains information on distinct dues/donations/subscriptions enrollments

Financials

Table Name	Description
INVOICE	Main invoice information (date, number, etc.)
INVOICEITEM	Details for invoice items
PAYMENTS	Contains all payment information
INVOICEPAYMENT	Indicates what invoice(s) a payment is applied to

Committees

Table Name	Description
COMMITTEE	Main committee information. Holds information about each committee.
INDCOMMITTEE	Contains current committee member information
INDCOMMITTEEH	Contains committee member history information
INDCOMMITTEEPOSITIONS	Contains concurrent current committee positions

Other Important Tables

Table Name	Description
LIST	Action lists
LISTMEMBERS	Action items
ACTIONDETAIL	Sub-Actions
WHOKNOWSWHO	Relationships

Query Values

By adding and filtering Query Values you can determine what data appears in your report. The values are the fields of data in ClearVantage. You can filter the fields to show only records that have certain information in that field. For example, you can add the ISMEMBERFLG field to your report and then set the Filter to Y so that only member records appear in the report.

Adding Values to the Query

In order for a particular field to be available in the report, you must first add the value to the query.

1. In the appropriate table window, left click on the field that you wish to add to the query.

2. Drag and drop the field into the query grid in the lower section of the window.

   

3. Once you have released the left mouse button, the field will appear in the query grid. If you have fields with the same name but from different tables added, the second value added will be automatically numbered in the header.

4. Drag successive fields to the query grid until you have added every field that you wish to appear in your report or any field that you wish to use to filter the data returned in the report.

Rearrange Values

It is important to place the values in the query grid in the appropriate order. The order of the fields in the query gird is important in the following instances:

• If you are creating a grid-style report that will be exported, the order of the grid fields in the query tab will determine the order in the exported file. Therefore, if the fields in the exported file need to be in a specific order, the field values need to be in the same order.

• If you are sorting the records returned in your report you need to place the fields in the order in which you want the sort applied. The sort is applied from left to right. If you want to first sort by last name and then first name, the fields need to be last name, first name in order.

• If your report will use sections to organize the data, the order of the fields in the query tab must match the order of the fields that you group by in the sections.

• If the group header is not sorted in the Query tab, you will get an error message like this:

  

• If the order of the fields does not match the order to the grouping, you will receive an error like this:

  

To rearrange the order of values in the query grid:

1. Click on the value header to select the correct column.

2. Click again with your left mouse button and drag and drop the field to the left or right. A dark line will appear between the fields where the moved field is going to be placed.

3. Once you have released the left mouse button, the field will appear in the new location on the query grid.

Edit Values

The Values Editor allows you to modify characteristics of the field within the report such as the display label, sort order and whether the field should display in the report. This does not control the data values in the report or in the ClearVantage Notebooks.

1. To modify field characteristics or properties, double-click directly on the field in the query grid, or select View | Value Editor from the menu. This displays the Value Editor window.

   

2. Modify the following elements of the value as needed.

   • 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 but this can be modified by typing in a new header as you want it to appear on the report. The report writer will automatically adjust for headers that contain spaces or reserved words so you are free to use these as needed.

   • Type: The Type property is the data type of the field which can be selected from the dropdown. The Filter values that you enter must be consistent with the data type. This property is assigned at the time the value is added to the query, so changing the type of the field in your SQL database later will not change the type in previously created queries. Though this property rarely needs to be changed, some examples of commonly used types available in the dropdown are:

     • String: Used for text fields.

     • Float: Used for currency and decimal fields. This type should be used instead of Currency for monetary amounts.

     • Integer: Used for integer (whole number) fields. This type will not show decimal places.

   • Show: The Show property determines how a field will be displayed in your report. The two most common settings are as follows:

     • Show: Use to include the field in Preview Records.

     • Hide: Use to hide the field from Preview Records and Preview Report and to search on fields that you do not need to display.

   • Sort: The Sort property determines if the field will be used to arrange 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 as follows:

     • Unsorted for no sort

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

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

   • Format: The Format property determines the manner in which the field will be displayed. This should generally be left blank but can be used in certain circumstances. For example, entering 00.00 will format the number with two decimals showing.

   • Width: The Width property determines how wide the field will appear on the report.

   • Alignment: The Alignment 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.

3. To modify another field, select the field from the dropdown button at the top of the window. Changes to previously selected fields are automatically saved.

4. Press the x button in the upper right corner of the window to close the Value Editor window.

Sorting Values

You will need to add a sort to your report to display records by organization, alphabetically by last name or in chronological order. The sort is used from left to right and so you will need to order your columns in the order in which you want the sort to be applied.

1. To modify field characteristics or properties, double-click directly on the field you want to sort by in the query grid, or select View | Value Editor from the menu. This displays the Value Editor window.

   

2. Click on the Sort field and select whether you want to sort Ascending (0-9, A-Z) or Descending (9-0, Z-A).

3. Close the Value Editor window and the report will now be sorted by the selected field.

4. To sort by more than one field:

   1. Open the Value Editor window for the second field.

   2. Select the sort order.

   3. Close the Value Editor window.

   4. If necessary, rearrange the order of the Values so that the first field to be sorted by is the left of the second field.

Filtering Values

Filtering a report allows you to choose which records appear in the report. For example, you can add filters that exclude nonmembers or include only individuals from a particular region. You can add multiple filters with either an AND or an OR between them.

A filter consists of a Filter Command and a search value. You can select from a variety of Filter Commands to find exact matches, exclude records, compare values or find similar records. Values are entered as free text and can be words, letters, or numbers and can use special values and wildcards as well.

The Filter tab of the Value Editor window displays a list of 1 to 20 individual filter values that you can filter by. The number of values that can be entered depends on the command chosen. You can also add up to 20 filters to any report.

Adding a Single Filter

Sometimes you need to filter a report by a single value. This might be to only include members or individuals in a particular state or paid invoices.

To filter by a single field's value:

1. Double-click directly on the field in the query grid or select the Edit Value button from the top menu. This will open the Value Editor.

2. Click on the Filter tab.

   

3. Confirm that Filter 1 is set in the Number field.

4. Select a Command to use for your filter. Valid Filter Commands are:

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

6. Is Between: Include records that are within a range created by 2 values. If you select Is Between, only the rows for Value 1 and Value 2 are available.

7. Is Greater Than: Include records that are greater than a single value. Only the row for Value 1 is available.

8. Is Greater or Equal To: Include records that are greater than or equal to a single value. Only the row for Value 1 is available.

9. Is Less Than: Include records that are less than a single value. Only the row for Value 1 is available.

10. Is Less or Equal To: Include records that are less than or equal to a single value. Only the row for Value 1 is available.

11. Is Like: Include records that contain up to 20 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.

12. Is Not Equal To: Exclude records that equal up to 20 matching values.

13. Is Not Between: Exclude records that are within the range of 2 values. Only the rows for Value 1 and Value 2 are available.

14. Is Not Like: Exclude records that contain a value (see Is Like above).

15. Enter your search values as appropriate for the Filter Command that you have selected. See the section on Common Filters for detailed information.

16. Click on the X to close the Value Editor and save your changes.

Adding Multiple Filters

When you need to filter a report by multiple values you will need to decide whether to use an AND or an OR. To filter by multiple fields:

1. Double-click directly on the field in the query grid or select the Edit Value button from the top menu. This will open the Value Editor.

2. Click on the Filter tab.

3. Enter the first filter as you would if only adding a single filter to the report.

   

4. Close the Value Editor window.

5. To enter a second filter to the report:

   1. Double-click directly on the field in the query grid or select the Edit Value button from the top menu. This will open the Value Editor.

   2. Click on the Filter tab.

   3. Select Filter 2 in the Number field.

   4. Choose the appropriate Filter from the Number field:

      • If you want to put an AND between your filters, select Filter 1 in the Number field. The two filters will then appear in the same row in the Query tab.

      • If you want to put an OR between your filters, select Filter 2 in the Number field. The two filters will then appear in different rows in the Query tab.

   5. Close the Filter window.

6. If you have multiple filters, you may need a combination of conjunctions. This may require using the same value in multiple filters. For example, if you want to filter the report to only include:

   • Individuals who are Members and have a primary or alternate address in NY.

     • Filter 1:

       • ISMEMBERFLG = Y

       • STATECD = NY

     • Filter 2:

       • ISMEMBERFLG = Y

       • ALTSTATE = NY

         

   • Individuals who are members with a primary address in NY or anyone who has an alternate address in NY.

     • Filter 1:

       • ISMEMBERFLG = Y

       • STATECD = NY

     • Filter 2:

       • ALTSTATE = N

Common Filters

There are several filters that are often used when creating reports in ClearVantage. Here are a few tips for entering filters:

• You can enter numbers or letters in the Value fields.

• States are generally stored using the 2-letter postal abbreviation code.

• For fields that are checkboxes, you should enter Y to indicate records where the box is checked and N for records where the box is unchecked.

• Entering multiple search values will serve to include records with any one of the values. For example, entering ID, IA, IL and IN as values 1 through 4 will allow you include or exclude records that list Idaho, Iowa, Illinois and Indiana.

• Dates should always be entered in the format MM/DD/YYYY such as 12/31/2016.

Source Codes

Various tables in ClearVantage may contain details that refer to multiple types of transactions (i.e. INVOICEITEM, NOTETRANS, etc.). In these cases there is usually a SOURCECD and an ITEMALTCD where the source informs the type of data the ITEMALTCD refers to. In order to filter for a particular type of transaction (i.e. Products on an Invoice):

1. Add the field that stores the SourceCD to the grid on the report query tab.

2. Open the Value Editor for the field and click on the Filter tab.

3. Select the appropriate Command in the Filter tab.

4. Enter the appropriate Filter Value from the list below. These codes are most widely used in the INVOICEITEM table that is the detail level of an invoice.

5. P: Product

6. S: Event

7. F: Event Function

8. H: Shipping and Handling

9. D: Dues/Subscriptions/Donations

10. C: Cancellation Fees

11. V: Advertising

12. Press the x in the upper right corner of the window to close the Value Editor.

13. Continue with your report as needed.

Special Keywords

There are two special keywords that can be used as filters: TODAY and BLANK.

1. Add the field that you wish to filter to the grid on the report query tab.

2. Open the Value Editor for the field and click on the Filter tab.

3. Select the appropriate Command in the Filter tab.

4. Enter one of the special filter values from the list below.

   • TODAY: Indicates today's date.

   • BLANK: Indicates that the field has no value (NULL).

5. Press the x in the upper right corner of the window to close the Value Editor.

6. Continue with your report as needed.

Parameters: Prompting for Values

You can create a filter for a field that prompts the person running the report to enter the parameters for the filter values. Prompts will allow you to enter letters, words, numbers or dates but will not allow you to select from a dropdown. The Xtreme Report Writer allows you to create select from dropdowns in prompts.

Note

Parameter names can be repeated in multiple filters rows and columns of a query, but they are treated as the same value. This is helpful if you have a multi-filter query where one column must contain the same value and that value happens to be a parameter.

1. Add the field that you wish to filter to the grid on the report query tab.

2. Open the Value Editor for the field and click on the Filter tab.

3. Select the appropriate Command in the Filter tab.

4. In the Filter Value fields, enter the parameters:

   1. Place the cursor in the Value 1 field and enter a word or phrase to indicate the information that should be entered. The word or phrase must be surrounded by square brackets.

   2. Enter additional values in the same format, again surrounded by square brackets.

   

5. If you wish to include a default value, enter the parameter using square brackets followed by an equal sign and the default value.

   

6. Press the x in the upper right corner of the window to close the Value Editor.

7. Continue with your report as needed.

8. When you preview or run the report, the Filter Values window will appear asking you to enter the parameters for your filter. All parameters must contain a valid value before the query will be executed.

9. Enter the parameters and press OK to view the report.

Aggregate Functions: Sums, Counts, Averages, Maximums & Minimums

Sum, Count, Average, Minimum and Maximum values are options in the Show property field and are called aggregate functions. Aggregate functions calculate a single result from a collection of input values. In essence, the Show property field allows you to create summaries of information. For example, these summaries can be counts of the number of records, sums of the numbers, or maximums of the data.

Adding Aggregated Fields to a Report

Adding aggregate fields to a query is useful when you want to display a count or a total of records such as the number of individuals or the total amount of a group of invoices.

1. Add all of the fields that you wish to include in your report the grid on the report query tab.

2. Open the Value Editor for the field you wish to summarize and click on the Show field.

3. Select one of the Show values from the list below. If you select an option that includes (Hide) in the name, the column will aggregated but will also be hidden from the display when the report is previewed.

   • Sum or Sum (Hide): Totals all the values in a set of records. The field must be numeric in order to add the values.

   • Count or Count (Hide): Counts the number of occurrences in a set of records.

   • Average or Average (Hide): Calculates the average value of a set of records.

   • Min or Min (Hide): Finds the minimum value in a set of records.

   • Max or Max (Hide): Finds the maximum value in a set of records.

4. Press the x in the upper right corner of the window to close the Value Editor.

5. For all the other fields in the set the value of the Show field to Group. Group is used in place of Show when one or more fields in a report have a Show setting of Sum, Count, Average, Minimum or Maximum. Any fields that do not have one of these settings or Hide must be set to Group or you will receive the following error message.

   

   Note

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

6. Save and close your report when finished.

Example for Count

You can create a report that counts the number of individual members by adding the field CUSTOMERCD to the query tab and setting the Show property to Count.

You are counting the number of CUSTOMERCDs found in the query results.

You can add additional fields to the query. If you set the Show property to Hide, they will not appear on the report. If you mark the Show property as Group, you will group the records and the count will be the count of that group.

By adding the field CITY and setting the Show property to Group, you are grouping the records by city. By adding the ISMEMBERFLG field and setting the Show property to Hide, you are not displaying the ISMEMBERFLG field.

Rather than the count being for the entire report, the count is for records in that group. There will therefore by multiple counts, one per group.

Similar reports can be created that Sum or Average all of the records in a report or group within the report.

Example for Sum

You can total the balances of invoices for a particular payee by adding the BALANCEDUE field with a Show Property of Sum and grouping by the PAYEECD.

The balancedue column will display the total balance due of all invoices for that payee.

Example for Average

When you want to determine the average of the fields, you must be aware of the sum and the count being used to calculate the average. For example, if you want to find the average balance due for all invoices of a payee and then filter the report to only show payees where the average is greater than zero, the Show Property of Average and the filter for greater than zero would need to be placed in the same field.

If you instead want to limit the report to invoices that have a balance due greater than 0 and then find the average of those invoices, you will need to add the BALANCEDUE field to the report twice. The Show Property of Average should be set on one of the Search Values and the filter for greater than zero should be set on the other.

The difference in these two reports is clear when you look at a payee who has 1 invoice with a balance due of $500 and 9 invoices with a balance due of $0. In the first example, the payee will have a balance due column display of $50 because the $500 is being divided by the 10 total invoices for that payee. In the second example, the payee will have a balance due column display of $500 because the $500 is being divided by the 1 invoice with a balance due.

See the section on Filtering Query by Aggregated Fields for detailed information.

Example for Minimum and Maximum

You can create a report that displays the highest value in a field from any of the records selected by the query. For example, you can add the field OrgCD to the query tab and setting the Show property to Max.

This will display the highest OrgCD from organizational members.

A similar report can be created using the Show property of Min.

Filtering Query by Aggregated Fields

You can filter your query so that your report includes only records where the data from the aggregate function meets the criteria you set. Most often, this allows you to select results where the sum, count or average value reaches a certain level.

1. Add the field that you wish to summarize to the grid on the report query tab.

2. Open the Value Editor for the field and click on the Show field.

3. Select one of the Show values from the list below.

   • Sum: Totals all the values in a set of records. The field must be numeric in order to add the values.

   • Count: Counts the number of occurrences in a set of records.

   • Average: Displays the average value of a set of records.

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

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

4. Click on the Filter tab in the Value Editor window.

   

5. Enter the filter that you want to apply to the records in the query. Keep in mind that your filter is being applied to the aggregated value and not a specific record.

6. Press the x in the upper right corner of the window to close the Value Editor.

7. Select each of the other fields in the grid on the report query tab and set the value of the Show field to Group. Group is used in place of Show when one or more fields in a report have a Show setting of Sum, Count, Average, Minimum or Maximum. Any fields that do not have one of these settings or Hide must be set to Group or you will receive the following error message.

   

   Note

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

8. Continue adding fields to your report as needed.

Example of Filtering an Aggregated Field

If you create a report that displays the count of organizational members by city, you may find that you have many cities with only a handful of members. You can then filter your report by the count of organizational members.

Your report will now display only those cities where the count of organizational members is greater than the specified number.

You can apply any filter that you can apply to an integer.

Custom Fields

Standard SQL server functions can be used to generate custom fields within the ClearVantage Standard Report Writer. These custom fields can be used for calculations, dates, altering text, and even case (if/then) statements.

Adding Custom Fields

To add a custom field to a report:

1. Double click on the first empty column in the Query tab of the Report Writer. The Value Editor window will open to the Expression tab.

   

2. Enter the desired function or field in the type area at the bottom of the window. Note that this field has a maximum number of characters that will run, regardless of the number of characters entered.

   • A list of available tables and fields will display on the Tables tab. Click on the arrow next to the table name to expand the list of fields.

   • A list of useable Functions is available on the Functions tab.

   • A list of possible Operators is available on the Operators tab.

   • Additional SQL functions that can be used are outlined below.

3. Press the x button at the top of the Value Editor to close the window when you are finished.

Date Functions

Microsoft SQL Server uses the following basic date functions that can be useful in creating reports in ClearVantage:

• Getdate(): Returns the current date and time.

• Month([DATEFIELD]) or DATEPART (m, [DATEFIELD]): Returns the month of specified date field.

• Year([DATEFIELD]) or DATEPART (yy, [DATEFIELD]): Returns the year of specified date field.

• Day([DATEFIELD]) or DATEPART (d, [DATEFIELD]): Returns the day of specified date field.

The DATENAME function extracts verbal names for portions of a specified date. Uses include:

• DATENAME(m,[DATEFIELD]): Returns the full name of the month for a specified date (January, February, etc.).

• DATENAME(dw,[DATEFIELD]): Returns the full name of the day of the week for a specified date (Monday, Tuesday, etc.).

The DATEDIFF function returns the difference between two specified dates. Uses include:

• DATEDIFF (m,[DATEFIELD1],[DATEFIELD2]: Returns the months between the first specified date and the second specified dates.

• DATEDIFF (d,[DATEFIELD1],[DATEFIELD2]: Returns the days between the first specified date and the second specified dates.

• DATEDIFF (yy,[DATEFIELD1],[DATEFIELD2]: Returns the years between the first specified date and the second specified dates.

When combining multiple date functions, use the plus sign (+) to combine multiple date functions. However, some of the date functions (such as year or month) will return a numeric value. To combine the numeric values, the user must cast the fields as a varchar value). Possible uses of these combinations include:

• Create a full date (with month name etc.)

  • datename(dw,[DATEFIELD])+' '+datename(m, [DATEFIELD])+' > '+cast(month([DATEFIELD]) as varchar)+', > '+cast(year([DATEFIELD])as varchar)

• Create a field to summarize month and year for reports

  • datename(m,[DATEFIELD])+'-'+cast(year([DATEFIELD]) as > varchar)

Text Functions

The following text functions can be used for modifying the case of letters, replacing NULL fields with text, or finding a string of data within a field among other options.

Alpha Case Functions

The case of the data in a specified field can be changed using SQL functions:

• UPPER([TEXTFIELD]): Returns data in the specified text field > in all upper case letters.

• LOWER([TEXTFIELD]): Returns data in the specified text field > in all lower case letters.

ISNULL Function

The ISNULL function allows a user to specify an alternate value for fields with a null value (Note: This function also works for numeric fields). The basic format for this function is ISNULL([SELECTEDFIELD],[ALTERNATEVALUEFORNULL]).

Possible uses for this function include:

• ISNULL([SELECTEDFIELD],'NONE'): Display the word NONE when a value is blank.

• ISNULL([SELECTEDFIELD],''): Display an empty string when a value is blank.

• ISNULL([SELECTEDFIELD],[ALTERNATEFIELDNAME]): Display an alternate field when a value is blank (e.g. display First Name if Nickname is blank)

Extraction Functions

SQL allows the extraction of specific parts of a field using the following functions:

• LEFT([SELECTEDFIELD],[NUMBEROFCHARACTERS]): Displays the left characters for a given field and number of characters.

• RIGHT([SELECTEDFIELD],[NUMBEROFCHARACTERS]): Displays the right characters for a given field and number of characters.

• SUBSTRING([SELECTEDFIELD],[FFIRSTCHARACTER],[LENGTHOFSTRING]): Displays a range of fields for a given start and end point. For example, SUBSTRING (LASTNAME,3,2) will start by returning the 3rd character and will return 2 characters of the lastname. So for Smith, the function will return ‘it’. For Johnson, the function would return 'hn'.

CHARINDEX Function

The CHARINDEX function returns the character position within a field of a specified value. This function is useful for determining if a field includes a value or for separating parts of a field (e.g. separate portion of name before hyphen and after hyphen). The basic construct for this function is CHARINDEX([VALUETOSEARCHFOR],[FIELDTOSEARCH]). Examples of how to use this function include:

• CHARINDEX('-',LASTNAME): Determine the location of a character within a field (e.g. locate a hyphen within lastname).

• LEFT(LASTNAME,CHARINDEX('-',LASTNAME)-1): Display information to the left of specified character (e.g. display information to the left of hyphen in lastname). Note that when selecting everything to the left of the hyphen, the left is minus one to exclude the hyphen from the value returned.

• RIGHT(LASTNAME,LEN(LASTNAME)-CHARINDEX('-',LASTNAME)): Display the information to the right of a specified character (e.g. display information to the right of hyphen in lastname). Note that to determine the number of characters to the right of the hyphen, we must subtract the location of the hyphen from the total length of the field.

Case Statements

The CASE function in SQL Server allows for conditional use of values (similar to an IF statement in access or other reporting tools). The base construct for the function i'

CASE WHEN [ENTER CRITERIA] THEN [ENTER VALUE] ELSE [ALTERNATEVALUE] END

Multiple WHEN criteria can be included in the same case statement such as

CASE WHEN [ENTER CRITERIA] THEN [ENTER VALUE] WHEN [ENTER CRITERIA2] THEN [ENTER VALUE2] WHEN [ENTER CRITERIA3] THEN [ENTER VALUE3] ELSE [ALTERNATEVALUE] END

There are a number of useful applications for the case function within ClearVantage reports, including:

• CASE WHEN CUSTOMER.ISMEMBERFLG = 'Y' THEN 'MEMBER' ELSE 'NONMEMBER' END: Displays the words Member and Nonmember based on the ISMEMBERFLG.

• CASE WHEN CUSTOMER.PREFNAME IS NULL THEN CUSTOMER.FIRSTNAME ELSE CUSTOMER.PREFNAME END: Displays the first name is the nickname field is null.

• CASE WHEN ORGANIZATION.COUNTRY IN ('US','U.S.','USA','U.S.A.') THEN 'United States' WHEN ORGANIZATION.COUNTRY IS NULL THEN 'United States' ELSE ORGANIZATION.COUNTRY END: Displays United States when the country field is US, U.S., USA, U.S.A. or NULL. In other cases the name of the country is displayed.

Create a Grid Format Report

Viewing the Grid Format

By default, the Report Writer will display the records selected by the query in a grid format. The grid layout provides a simple format similar to a spreadsheet. The grid format allows you to see the results of your query without the formatting or grouping of a page layout.

To view a report in the grid layout:

To view a report in the grid layout:

1. Open your report from Information Central.

2. Click on the Preview tab and preview the report.

3. If no page layout has been entered, the report will automatically display in the grid format. If a page layout has been entered, click on the Preview Grid button to view the query results as a grid.

   

   

4. Press the Preview Page button to return the page layout.

Note

MEMO fields in the database such as Change Log information can only be displayed in the Page Layout format.

Editing the Column Width

The width of the columns that appear in the grid format will vary depending on the length of the SQL field. This can be modified in the display of the report without impacting the data.

1. Open the report to the Query tab.

   

2. Double-click on the column of which you wish to modify the width and the Value Editor window will open.

   

3. Change the Width of the field to the number of characters you would like displayed in the grid layout report. You must enter a whole number.

4. From the Alignment field dropdown, select whether you wish the data to be justified to the left, justified to the right, or centered.

5. Press the x in the upper right corner to close the Value Editor window and return to the Query tab.

6. You may now view the changes in the grid layout report.

Create a Page Layout Report

Viewing the Page Layout

Page layouts in the report writer allow you to group data and present results in a visually organized format including headers and footers. Reports in a page layout format can be easily exported to a pdf to retain their structure.

To view a report in the page layout:

1. Open your report from Information Central.

2. Click on the Preview tab and preview the report.

3. If a page layout has been entered, the report will automatically display in the page layout format.

Page Setup

The layout report allows you to format the fields and information on your report as it will be printed. It is important to confirm or modify your page setup prior to laying out your report as this determines the space you have available for the report fields.

1. Open the report for which you want to confirm the page setup.

2. Select File | Page Setup and a Setup window will open.

   

3. Select the Page Size from the dropdown and the Width and Height fields will automatically populate. If you select Custom as the Page Size, you can manually adjust the Width and Height fields.

4. Select the page orientation, either Portrait or Landscape, from the dropdown menu.

5. Enter the Top, Bottom, Left, and Right Page Margins. These margins will be represented on the page layout report by a red line around the edge of the page layout unless you are creating a label report.

6. If you want to create labels from your report data:

   1. Click on the Labels tab and select the radio button “This is a label report.”

   

7. Press the Choose Label Format button to select a predefined set of label dimensions.

8. Select the label format and press OK. The margin values will populate.

   

9. If your label does not match one of the predefined label formats, enter the following dimensions manually:

   1. Top Margin: The measurement between the top of the sheet and the top of the first label.

   2. Left Margin: The measurement between the left edge of the sheet and the left edge of the first label.

   3. Label Height:. The height of the printable label area.

   4. Label Width: The width of the printable label area.

   5. Gutter Across: The width of the space between the labels from left to right.

   6. Gutter Down: The height of the space between the labels from top to bottom.

   7. Number Across: The number of labels from left to right.

   8. Number Down: The number of labels from top to bottom.

10. Press OK to save your page setup selections.

11. If you entered any label formatting, the following message will appear.

    

12. Press Yes to reformat your report.

Sections and Groups

Creating a page layout report is like writing a letter. You begin with an opening paragraph followed by topics, sub-topics, detailed comments, and final conclusions. In a report, paragraphs are replaced with sections such as headers, footers, and details. Of course, letters are not required to follow this or any structure. Reports must follow this structure in order for your data to appear in a meaningful way.

Each section is defined by horizontal lines and labeled along the left side of the report. When sections are made shorter to limit the space used in a report, these labels can be abbreviated as RH and RF for Report Header and Report Footer, PH and PF for Page Header and Page Footer, and GH and GF for Group Header and Group Footer. For Group Headers and Group Footers, the field used to group by is also listed.

Headers are like an opening paragraph in a letter; footers are the closing paragraph. Though you may need to create Headers and Footers in order to structure your data, it is possible to suppress the printing of them so that they are not visible. All sections with a Header can also have a Footer but it is not required.

Report Header and Footer

The report header and report footer are two sections of the report. Information that should appear at the top of the first page of the report should be placed in the Report Header section. This could include report titles, authors, or general information about the report. The report footer could include information to conclude the report such as a final comment or signature.

Page Header and Footer

The Page Header always appears at the top of each page of the report. The Page Footer always appears at the bottom of the page. These sections are useful for displaying page numbers, report titles, and other information that must appear on each page of the report.

Group Header and Footer

Group Headers combine records that are of the same type and presents them as a single package of information. By adding a Group Header, we are now able to see report data more clearly. Though the Group Header and Footer are not created in the report by default, many reports do add them. You can add up to 10 Group Headers and Footers to your report.

Report without Group Header

Report with Group Header

Detail

The purpose of the Detail section is to display individual records at the greatest level of detail in the report. This is where a list of records is generally displayed.

Add a Group Header and Footer to a Report

In a report, like a letter, it’s a good idea to plan your structure before you begin to write. The first step is to determine the groupings that you will need and add the appropriate Group Headers and Footers to your report.

To add Group Headers and Footers to a report:

1. Select the Layout Tab.

2. To add a group header, press the Page Designer icon on the top menu bar or right click in the layout page and select Arrange Sections to open the Page Designer window.

   

3. Press the Add button to open the Sections Builder Window.

   

4. Check Group Header (and Group footer if desired) and press the Next button. You will now have the opportunity to select the available fields to group.

   

5. Select the field(s) you wish to make a Group Header from the Available Fields section and use the arrow buttons to move them to the Selected Fields section. The field should be the field by which you want to group. For example, if you want to group all of the payments in the same batch, you will want to move batchcd field to the Selected Fields section. Whichever field you group by must also be sorted in the Query tab.

6. Press the Next button and you will now be asked to confirm your selections for the report.

   

7. Click Finish when you are done to return to the Page Designer window which will now list the Group Header and Footer.

   

8. Press the x in the upper right corner to close the Page Designer window.

Configuring Sections

Each section of a report can be configured based on your needs for the report.

1. From the Layout tab, press the Page Designer icon on the top menu bar or right click and select Arrange Sections to open the Page Designer window.

   

2. Click on the name of the section that you wish to configure.

3. Click on the arrow button to the right of the section name for the section you selected, and the Edit Section window will open.

   

4. For the Report Header and Page Header, you will only be able to update the following options for your sections:

   1. Suppress Printing: Check if you do not want the section to be printed.

   2. Header has Footer: Uncheck if you wish to delete the related footer for a heading.

5. For the Detail, Report Footer, Page Footer, and Group Footer(s), you can check the Suppress Printing option if desired.

6. For the Group Header, you can also update the following options if needed:

   1. Start new page: Check if this Group Header should start each group on a new page.

   2. Start new page number: Check if this Group Header should start as a new page number.

   3. Group Value: You can select a different field from the dropdown if you want to change the field you are grouping by.

7. Press OK to save and close the Edit Section window when you are done.

8. Press the x in the upper right corner to close the Page Designer window when you are finished.

Add Fields to a Page Layout Report

Once the sections of your report have been determined, you must add the fields you wish to display in the page layout report.

1. From the Layout tab, press the Page Designer icon on the top menu bar and select the Place tab or right click in the layout page and select Place Objects to open the Page Designer window.

   

2. Click on the field that you want to add to the report and hold down the mouse button.

3. Drag the field to the section of the report where you want to place it. Both the merge field and a caption field will appear on the report.

   Note

   If you do not want the caption field to appear when you drag a field onto the report, uncheck the box labeled Automatically caption fields.

4. Drag and drop all the fields that you want to appear to the report from the Page Designer window. Be sure not to place fields touching the blue lines that separate sections or the red lines that indicate margins.

5. Press the x in the upper right corner to close the Page Designer window when you are finished.

Field Customization and Placement

There are two ways to easily adjust fields in the report layout. You can use the Dimensions Section of the Page Designer or you can use your mouse to drag one or multiple fields to make adjustments.

Field Placement, Font and Sizing

To move and resize fields on a report:

1. Click on the field to select it. Squares will appear in the four corners of the field.

   

2. Right click in the field and select Customize Objects. This will open the Page Designer window to the Properties tab within the Customize tab.

   

3. In the Dimensions section, confirm or adjust the values of the following fields. Note that the numbers entered refer to coordinates on the page. Each inch is approximately 95 coordinate units.

   • Top: Enter the number of units below the top of the page the top of the field should be placed

   • Left: Enter the number of units from the left page edge the left edge of the field should be placed.

   • Height: Enter the number of units below the top of the field the bottom of the field should be placed.

   • Width: Enter the number of units to the right of the left edge of the field the right edge of the field should be placed.

   Note

   Entering the same top alignment and height for each field will place the fields in the same row. Entering the same left alignment and width will place fields in the same column.

4. In the Color section, enter the desired colors for the background and the foreground from the respective dropdown menus. The background color will display on the report within the dimensions of the field and the foreground color will be the color of the text.

5. In the Label section, enter the alignment for the text within the field from the alignment dropdown.

6. Click on the dropdown arrow in the font field and a Font window will open.

   

7. Select the font size, color and style and press OK when you have finished.

8. Close the Page Designer window to return to the report.

Adjusting Fields using the Mouse

You can also drag the corners of a field to adjust the field size.

1. Click on a field and hold down the mouse button to select it.

2. If you wish to move multiple fields, hold down the shift key and click on the additional fields you would like to select. Selected fields will have black squares in the corners of the fields.

   

3. Release the shift key.

4. While holding down the left mouse button, drag the selected fields on the layout. Release the mouse button once you have placed the fields in the correct position.

5. Click on each field individually to select it and drag a corner of the field in order to change the size of the field.

Note

The coordinates of the mouse will be displayed at the bottom of the window as X and Y.

Captions and Memos

When you drag a field from the Page Designer window onto the report layout tab, a label field is automatically added as a caption if the Automatically caption fields check box is checked. To add a missing or additional caption:

Caption Fields

When you drag a field from the Page Designer window onto the report layout tab, a label field is automatically added as a caption if the Automatically caption fields check box is checked. To add a missing or additional caption:

1. Right-click in the report layout and select Place Objects to open the Page Designer window.

   

2. Click on the L button on the right of the window to select adding a label or caption.

3. Click on the report layout window to place the label field on the report. Do not try to drag and drop the L button onto the report.

4. Click on the Customize tab in the Page Designer window and click on the Caption tab at the bottom of the window.

   

5. Type the text you want to appear in the label field on the report in the open text field on the Page Designer window.

6. If the text you wish to add is quite long, make the Page Designer window wider as this can impact the width of the label field in the report.

7. Close the Page Designer window when you are done.

Combining Captions and Data Fields

To set specific spacing between a caption and the data, you may wish to place the caption within the same field as the data.

1. Click on the field to select it. Squares will appear in the four corners of the field.

   

2. Right-click and select Edit Caption to open the Page Designer window.

   

3. Type the text you want to appear in the label field on the report in the open text field on the Page Designer window. For the report data to appear you must maintain the field name surrounded by square brackets in the open text field.

   

4. Close the Page Designer window when you are done.

Memo Fields

Memo fields serve a similar purpose to label fields, but they do not have a character limit. To add a Memo field:

1. Right-click in the report layout and select Place Objects to open the Page Designer window.

   

2. Click on the Memo button on the right of the window to add a Memo field.

3. Click on the report layout window to place the Memo field on the report.

4. Click on the Customize tab in the Page Designer window and click on the Memo tab at the bottom of the window.

   

5. Type the text you want to appear in the label field on the report in the open text field on the Page Designer window.

6. Close the Page Designer window when you are done.

Calculations on a Layout Report

In addition to using aggregate functions to calculate values based on a group, calculations can be done within the layout report. You can also use aggregate functions in conjunction with calculations on the report layout. For example, you can add the @SUM function to the layout to total the count that you created as an aggregate function.

1. Open the layout report with all the fields added to the report that should be displayed.

2. Right-click on the report layout and select Place object to open the Page Designer window.

   

3. From the Calculate Field dropdown, select the type of calculation that you wish to add to the report.

   

4. Left-click on the field you wish to add to the report and drag and drop it onto the layout report. The placement of the field on the report will determine what values are being included in the calculation. For example, placing the field in a page footer will calculate based on the records on that page whereas placing the field in the report footer will calculate based on the records in the entire report.

   

Adding Page Numbers, Counts, Dates and Times

The page layout report format allows you to add page numbers, dates, and times to your report. These are generated at the time the report is run and are not stored except by exporting or printing the report data.

1. On the layout tab of the report, right-click and select Place object to open the Page Designer window.

   

2. Click on the appropriate icon and then click on the report layout to add the following fields to the report:

   • Date: Enters the date the report is run.

   • Time: Enters the time the report is run.

   • Page Number: Enters the page number. For the page number to appear correctly on each page, you must place the field in either the Page Header or Page Footer sections.

   • Page Count: Enters the total number of pages in the report.

3. Adjust the placement of the fields on the report as needed.

Adding Pictures and Shapes

The page layout report format allows you to add shapes and pictures to your report. This functionality is useful for delineating sections and adding logos to reports.

1. On the layout tab of the report, right-click and select Place object to open the Page Designer window.

   

2. To add shapes and lines to the report, left click on the appropriate icon and then click on the report layout to add the following fields to the report:

   • Rectangle: Creates a rectangle.

   • Circle:Creates a circle.

   • Horizontal Line: Creates a horizontal line.

   • Vertical Line: Creates a vertical line.

3. To add a picture to the report:

   1. Left click on the image icon.

   2. Left click on the report layout and Select Image window will open.

   

   1. Select the bitmap (.bmp) image that you want to add to the report.

   2. Press Open to add the image to the report.

   Note

   When adding images to reports, it is critical to save separate versions of your report frequently. The report writer has a maximum memory size per report that can be quickly exceeded if large images are added. If you exceed the memory size, your report may be rendered unreadable.

4. Adjust the placement of the shapes and pictures on the report as needed.

5. To adjust the color, size or line of rectangles, circles, or lines:

   1. Left click on the object to select it.

   2. Right-click and select Customize objects to open the Page Designer window.

   

6. Adjust the following fields as needed:

   • Top: Enter the number of units below the top of the page the top of shape should be placed.

   • Left: Enter the number of units from the left page edge the left edge of the shape should be placed.

   • Height: Enter the number of units below the top of the field the bottom of the shape should be placed.

   • Width: Enter the number of units to the right of the left edge of the field the right edge of the shape should be placed.

   • Background: Select the fill color for the shape from the dropdown menu.

   • Foreground: Select the line color for the shape from the dropdown menu.

   • Line Style: Select the line style for the shape from the dropdown menu.

   • Line Width: Enter the number of units for the thickness of the line.

7. Close the Page Designer window.

8. Save your report.

Layout Hints

Placing Fields in a Section

When placing fields on a report layout, be sure to place the field completely within a section. When fields are placed touching a line that separates sections, the data is misrepresented.

For example, if the field [FIRSTNAME] is supposed to be in the detail section of the report layout, all the first names will appear in the detail section of the report.

If the field [FIRSTNAME] is placed on the line between the Page Header and Detail section of the report layout, the first name in the data will show up as a header and only one first name will appear.

Avoiding Spaces between Records

When you are placing fields on a report layout, proper spacing between the fields is important to make the report easily readable. In addition to placing appropriate spacing between fields for a record, it is important to place the right amount of space between records.

To control the space between each line of a report:

1. Place all fields on the report layout.

   

2. Click on the Preview tab to confirm the amount of space between the rows of data. The larger the detail section is on the layout; the more space will appear between rows.

   

3. Hover the mouse cursor over the blue line that indicates the bottom of the detail section. When the mouse is properly hovered over the blue line the cursor icon will appear as two parallel lines.

4. Left click the mouse button and drag the blue line to set the correct amount of space for the Detail section. Be sure not to drop the blue line on a field.

   

5. Click on the Preview tab to confirm the amount of space between the rows.

   

6. Continue until you are satisfied with the spacing.

7. Save your report.

@ISDATA Function

The @ISDATA function in the report writer is used to string together fields only when there is data in the fields. This function is used frequently for badges or address information. The structure is as follows:

@ISDATA ~ strings and [fields] and strings~

For example, suppose you wanted to include multiple lines of an address, however, only have the address line show up if it contains data. The format would be as follows:

[ADDRESS1] @ISDATA~

[ADDRESS2]~

[CITY], [STATECD] [ZIP]

With this example, [ADDRESS2] will only show up in the report if it contains data. If data exists in ADDRESS2 it would look like this:

8120 Woodmont Avenue

Suite 710

Bethesda, MD 20814

With this example, if there is no data in ADDRESS2 it would look like this:

8120 Woodmont Avenue

Bethesda, MD 20814

A second example would be for name badges. Suppose you wanted to include the job title of an individual on a name badge for an event only if the data exists. The format would be as follows:

[FIRSTNAME] [LASTNAME] @ISDATA ~

[JOBTITLE]~

[ORGNAME]

With this example, [JOBTITLE] will only show up in the report if it contains data. If data exists in JOBTITLE it would look like this:

John Smith

Reporter

ABC Company

With this example, if there is no data in JOBTITLE it would look like this:

John Smith

ABC Company

1. Open the layout report and right-click on the field where you want to combine the fields with the @ISDATA function.

2. Select Edit Caption to open the Page Designer window.

3. Enter the @ISDATA statement. Note that you cannot enter carriage returns between the titles or before the @ISDATA function.

   

4. Close the Page Designer window.

5. Adjust the size of the field to display the text the way you wish. Note that because there is no carriage return in the caption, the text will wrap to the next line if needed.

   1. Setting the field width so that the @ISDATA~ and the closing ~ are on the same line will show both the information preceding and following the @ISDATA on the same line if space allows.

   

6. Setting the field width so that the @ISDATA~ and the closing ~ are split will not force the field enclosed in the tildes onto the second line. Whether the text flows to the second line is the determined by the length of the first line in relation to the width of the field.

   

7. Save your report when you are finished.

Printing and Saving Reports

Printing Reports

Once you have completed creating your report you may wish to print it.

1. Open your report from Information Central.

2. Click on the Preview tab of the report and preview your report.

3. Either press the printer icon or select File | Print from the dropdown menu and a Print window will open.

   

4. Enter your print specifications and press OK.

Exporting Report Data

ClearVantage allows you to export data from a report into a variety of formats.

1. Open your report from Information Central.

2. Click on the Preview tab of the report and preview your report.

3. Either press the export icon or select File | Export from the dropdown menu and an Export window will open.

4. Select the File Type you would like to create from the dropdown menu. You can select:

   • dBase table (*.dbf).

   • Paradox table (*.db).

   • MS Access (*.mdb)

   • Adobe Acrobat (*.pdf).

   • Shazam Report (*.shz).

   • Comma Delimited (*.csv).

   • Tab Delimited (*.tab).

   Note

   Comma Delimited files should be used if you want to open your exported report in a spreadsheet such as Excel.

5. Click on the ellipses button in the File Name field and browse to the location where you want to save the file.

   

6. Enter a name for the file and include the proper extension in the file name.

7. Press Save to close the Select File Name window and return to the Export window.

8. Press OK to create your export file.