Kavi Help CenterKavi® Members Help
| Chapter 27. Creating Custom Reports in Report Builder | ||
|---|---|---|
 |
Part I. Kavi Members Concepts and How-tos | 
|
Table of Contents
Kavi's Report Builder provides default reports for different applications and allows custom reports to be designed to suit the organization's needs. Each Report Builder comes with a set of Report Types that run predefined queries against specific tables in the database, and each of these Report Types is accompanied by a default report that includes all available fields in the search and downloadable results.
Custom reports are based on one of these Report Types, but the Super Admin can select some subset of available fields for searches to create a more targeted and efficient report, use fixed-field queries if desired, and select which fields are displayed in the online results versus the downloadable results.
All active reports are available in the Reports Area where they can be run by anyone with Reports Area access, including Report Admins and Organization Admins.
Back to topUnderlying each report is a Report Type that queries a specific set of database tables and is calibrated to return the broadest possible set of data fields related to a specific topic. Click the following links to the Kavi Members Report Types document in the Appendix.
Kavi Members Report Types:
Super Administrators can access the Add a Report tool through the Report Builder, which is available on the Super Admin menu.
Super Admins can click here to visit the Report Builder tool.
Select the Report Type that provides the all fields you need for your report. You may narrow the set of fields available in your report for streamlined use and improved performance, but you cannot add more fields. If you aren't sure which Report Type you need, select the one that seems most likely and check to see whether all the fields you need are available. If not, select a different Report Type.
You may find that there is no Report Type that returns every field you want in your report. Database queries can be resource-intensive to process, and the broader the query, the larger the potential drain on system resources. Each Report Type query is optimized to return the widest possible results without placing undue strain on system resources. That said, even the Report Type queries can affect system performance if the report is run without narrowing search criteria. Tips you can use to further optimize your reports are provided at the end of this section.
Once you have selected the Report Type, all the fields available in that Report Type are displayed so that you can select those you want for your Search Form, which you want displayed online in the Viewable Results and which are included in the Downloadable Results. Before you proceed, be sure to set the name and description and the filename. The checkbox that appends the date to the filename is checked by default. It's a good idea to leave this option enabled to make it easier to identify the date that a report was generated and to create unique filenames so that successive reports don't overwrite earlier reports.
Options:
- Set Any Fixed Fields (Optional)
-
Use a fixed field when you want the query to be restricted to a certain set of results. The selected field is "fixed" to a specific value.
Besides saving the administrator a step when running the report (because there's no need to set the 'Contact Type' field), fixing a field narrows the query, which makes the report more efficient and less of a drain on system resources. On the other hand, reports with a fixed field have limited uses and are less versatile than reports without fixed fields. It is rare to fix more than one field in a report.
The most common use cases are reports that only retrieve information about Primary Contacts or reports that specifically retrieve only active or inactive accounts. For example, a Primary Contact report would be based on the 'User' Report Type. The 'Contact Type' field would be selected as a fixed field. On the next step, the field value would be set to 'Primary Contact'. The query only searches for data on users who are assigned the 'Primary Contact' Contact Type.
- Select Search Form Fields
Fields that are selected in the 'Searchable' column appear on the Search Form when the report is run. You should select every field that administrators will want to search upon. You can build reports with no searchable fields. In this case, the Report Admin simply clicks the Run Report button to generate the report.
- Select Viewable Fields for Online Results
Select the fields you want in the Viewable Results, which are displayed online when your report is run. You should limit the number of viewable fields to 15 or less so the fields are sortable when displayed. If the number of Viewable fields exceeds this limit, they won't be sortable.
- Select Fields for the Downloadable Report
Select the fields you want to include in the Downloadable Results when the report is run. This can include a much larger set of fields than the Viewable fields. You can use the handy Select All button to automatically select all the fields, then click to uncheck any fields that you don't want in the report.
Restricted fields:
If 'N/A' is displayed, the field isn't available in every column, but is available for at least one kind of use. For example, Company Lists and User Lists are searchable only; item key fields are downloadable only.
If 'Disabled' is displayed, options in the Set Organizational Properties that control field configuration are set to disable these fields.
The Report Builder includes custom fields, which are displayed in the context of their data type (company, user or membership). They appear at the bottom of each section, in alphabetical order. You can set the order in which these fields will be displayed in the report's Search Form and Results Form.
Now that you've selected the fields for the report you can set default operators, values and field display order.
Options:
- How to Set Operators
-
Operators are set to determine whether the search returns values that include or exclude a specified value or range of values. Operators are configured at several points in the process of designing the report. If Fixed Fields are set when selecting report fields, the next step is to provide default values and configure operators for these Fixed fields. Default values and default operators can also be set for many Searchable Fields.
The set of available operators depends on what type of data is valid for a field (e.g., textual, numerical, dates, etc.). Operators may include 'is' or 'is not' if valid field values are predefined rather than free-text, or 'contains' or 'does not contain' if the value is free-text. If the specified value is a date, operators may also include 'is before' and 'is after'.
- When to Set Default Values
It isn't necessary to set a default value for a Searchable Field, but you may want to if this field is usually set to a certain value when the report is run. For example, the Status field is often defaulted to 'Active' in reports because organizations are usually most interested in managing active users, companies and memberships. The Report Admin can always set this value to 'Inactive' to generate a report on deactivated users or companies, or set the field value to 'Any Status' to retrieve companies or users irregardless of status. On the other hand, a report created specifically to identify inactive companies or users so they can be contacted or removed from the database would have this field defaulted to 'Inactive' (or the Status field could even be fixed to 'Inactive').
- Set Display Order
The Display Order is prefilled according to the order in which fields are displayed in the field selection steps. Edit the display order as you like.
When you designate a field as searchable and it displays 'Show results for' as an operator, the values are displayed as a set of check boxes on the Search Form. As you'd expect, any check boxes you select while configuring the Search Form are prechecked when displayed to the Reports Admin.
Unless an operator is displayed, these fields are in an 'or' relationship, so when a report is run, the results will include records that contain any of the selected values. If none of the check boxes are preselected by the Super Admin and the Report Admin doesn't check any when running the report, the field is ignored (i.e., the results aren't restricted by the values in this field). The result of running a report with all the field values selected is the same as the result of running a report with none of the field values selected.
Check box fields that allow you to set an operator include the 'Membership Status' and 'User Purpose' fields, so check boxes aren't necessarily set to 'or'.
As soon as a report is added it becomes available at the Reports Area. When the Report Admin clicks a link to the report, the report's Search Form is displayed.
The Search Form presents 'Searchable' fields with the default operators and values configured by the Super Admin who built the report. The Report Admin sets search parameters to generate whatever kind of report is needed, then runs the report.
The online Results Form displays the 'Viewable' fields. If there are less than 15 fields, the column headings can be clicked to sort the results. If there are more than 15 fields, the column headings aren't clickable and the Report Admin has to click a link to view columns that don't fit on the main part of the screen.
The 'Downloadable' fields are all included in the Downloadable Results. The downloadable report results are in the comma-separated value (CSV) file format, which can be viewed and manipulated through any spreadsheet application.
Back to topTips:
- To Optimize Performance
-
The demand on system resources is directly proportional to the size of the query. The more restricted the query, the lower the drain on resources. For optimized queries, the Super Admin should consider creating more targeted reports that make use of fixed fields and a smaller number of search fields. The Report Admin can reduce the load on the system by setting search criteria to return the smallest set of results that meets his or her needs.
Routine reports can also be engineered to run automatically during off-peak periods. For more information, contact support.
- Clone a Report
The fastest way to create new reports is to spin-off a similar report that already exists. So, instead of using the Add a Report tool to create a report from scratch, select the report most like the one you want to create and clone it. You won't be able to change the Report Type, so be sure you select a report that uses the desired Report Type.
- Test Your Report
When you are done building your report, run it a few times using different criteria to see how it performs. Download the results files (be sure to edit the filenames or subsequent downloads will overwrite the results files of previous downloads. You might want to time these tests to see how long it takes to get the results. This will give you some idea of which kinds of reports are relatively quick and efficient, and which are slow and consume more system resources.
These examples include only the basic set of fields that would be needed in these kinds of reports. Your reports are likely to include searchable, displayable and downloadable fields that aren't listed here.
Report on all companies with memberships starting in this quarter.
Create a quarterly report by setting a fixed date range, then clone this report as the basis for other quarterly membership reports, resetting the date range for each quarter.
Table 27.1. Quarterly Membership Report Settings
| Options | Settings |
|---|---|
| Basic Details | Include the quarter in the name of the report, so you can distinguish each report from the others in this set (e.g., 'Q1_memberships'). |
| Report Type | To build a quarterly company membership report that returns only the most recent membership for each Member Company, select the Company Data Report Type. Even though the Company Membership Report Type would seem to be the appropriate Report Type, it returns membership-centric data with each record (i.e., row) containing one membership so that membership data for a Member Company could be spread out over multiple rows. The Company Data Report Type returns one company per row. |
| Fixed Fields |
Fix the Membership Start Dates Range and set it to the date range for that quarter. For example the first quarter of the year would be 'Jan 1 - March 31'. If you want to screen out any companies that have been manually deactivated by an administrator, fix the Status to 'Active'. |
| Searchable Fields |
Company Name and Company Types are included as search fields for most company reports. Membership Types should be included for any report that pertains to memberships. |
| Viewable and Downloadable Fields |
Appropriate fields for viewing and downloading include Membership Types, Membership States, Membership Start Dates, Membership Expiration Dates, Membership Renewed On. |
The report in this example displays all Primary Contacts, with the Primary Contact's company and contact information.
Table 27.2. Primary Contacts Report Settings
| Options | Settings |
|---|---|
| Report Type | Use the User Report Type to build a Primary Contact report. |
| Fixed Fields |
Fix the Contact field to 'Primary Contact'. Because this value is fixed, you may omit User Purpose, which would necessarily be 'Company Representative'. If your organization only allows a 'Member Company' to have a Primary Contact, you can also omit Company Purpose from this report. |
| Searchable Fields |
A basic set of searchable fields would include Username, Company Name, Primary Email, Company Types and User Types. You probably want to include User Status and Company Status and set the values to 'Active' by default. Last Login Date can help identify Primary Contacts whose information may be out of date. |
| Viewable and Downloadable Fields |
The user's full name, plus all appropriate contact information (e.g., email addresses, phone and fax numbers, but probably not the company's postal address). You may want to include Company Membership information, but the Individual Membership Fields won't apply (unless your organization assigns Primary Contacts to companies whose Company Purpose is 'Company for Individuals'. |
The membership Report Types provide the most complete information on membership renewals, so this report would be based on the Individual Membership Report Type, rather than the User Report Type. Membership Report Types display one membership record per row, so a renewing members expiring membership appears in one row and the renewed membership in another.
Table 27.3. Individual Membership Renewals Report Settings
| Options | Settings |
|---|---|
| Report Type | The Individual Membership Report Type provides membership-centric data. Each record (i.e., row) contains one membership, so that membership data for a Member Company could be spread out over multiple rows. |
| Fixed Fields |
None. |
| Searchable Fields |
The most important searchable fields in the membership renewal report are the Membership Expiration Dates Period and Membership Expiration Dates Range. Set the Membership Expiration Dates Period default value to 'Last Month' or whatever makes most sense in your organization. The Membership Renewal Info field shows whether the membership was renewed or not, and can be used as a Searchable field. User Status is a useful filter and is also valuable information when interpreting renewal status, so it should probably be selected as a searchable, viewable and downloadable field. The organization may want to use membership renewal information to track membership trends, so Membership Types and Membership States should be selected as Searchable fields. To allow administrators to find membership renewal data for a specific user, select User List and Primary Email as Searchable fields. Other useful search fields include Membership Start Dates Period and Membership Start Dates Range. |
| Viewable Fields |
If you want to present the administrator with all the information needed to contact a member about the status of their membership renewal, you will want to include the person's First Name, Middle Name, Last Name, Primary Email, Work Phone Number, and possibly preferred salutation. You will want the displayable results to provide a snapshot of renewal status, so you'd definitely include the Membership Renewal Info field. Membership State, Membership Type and Membership Expiration Dates are also useful. If you have less than 15 fields selected, you'll probably want to include User Status so that the administrator can see which members have been deactivated. |
| Downloadable Fields |
Select all relevant member contact information for downloadable fields, including the name fields, preferred salutation, phone numbers and email addresses. If the organization collects user mailing addresses, you could select address fields as well. You probably want to make sure the downloadable fields include all available membership and renewal data so that the administrator can determine what's happening in the exceptional cases, such as when a membership was replaced. This includes the Membership Comment field, which shows whether the member has been contacted and declined to renew. If your organization has billed memberships, select the relevant membership bill fields for the download. |
A report is the most accurate way to get list or count of current organization members. As in this example, this can be an extremely simple report that relies entirely on fixed fields, so all that an administrator has to do to run the report is click the Run button.
In this example, a company-based organization uses a report on Member Companies to identify member companies, and the number of records in the report results equals the number of current organization members.
Table 27.4. Current Member Companies Report Settings
| Options | Settings |
|---|---|
| Report Type | Use the Company Data Report Type, which returns one company per row. |
| Fixed Fields |
Company Status is set to 'Active', Company Purpose is set to 'Member Company', Membership States is set to 'Current', |
| Searchable Fields | None. |
| Viewable Fields |
Company Name, Company Types and Membership Types are the most important. Membership Start Dates and Membership Expiration Dates are also interesting. The Membership Types and membership dates fields show every membership in the membership history. To restrict this to the most recent memberships only, you can set the Number of Memberships to Display to 2 memberships. This way you'll see the two most recent memberships: either a current and expired membership, or a current and pending membership (or if this is a new membership, just the new membership). If you want just the current membership, you'll have to download the report and edit the data in a spreadsheet application. |
| Downloadable Fields |
Use all the Viewable Fields plus any other fields that are of interest to your organization. |
|
|
 |
|
| Chapter 26. How to Change Purpose |  |
Chapter 28. Kavi Membership |