This release contains a number of enhancements (and a few bug fixes). The most significant of these is the ‘automatic database updater’, which will make life a lot easier for users wanting to run queries/reports that require additions to database connectivity. Here is a sneak peek at the Release Notes for version 3.2, which should be available by 8/1/2008:

This is a significant release which contains an automatic update process for database connectivity, as well as several other enhancements, including all of the user security upgrades contained in version 3.12. The new features:

1. The automatic updater runs each time QueryPlus is opened. QueryPlus sends information about the status if its database connectivity tables to the QualCorp server, which sends back any new or edited records.
   The practical effect of this enhancement will be to greatly shorten the time it takes to get new connectivity and/or fields into the hands of all QueryPlus users. Users often want to run queries which require new connectivity and/or fields, and this new version of QueryPlus will allow Qualcorp to get the new data to all users immediately.
   Users should also note that the automatic update feature can be disabled on the Administration screen, which also contains a manual update button and a date box titled ‘Update Through’. This capability is provided for users who maintain separate testing versions of QueryPlus, so they can update their test environment, test the changes, then update their live QueryPlus only to the date that their test environment was updated to.
2. Sorting can now be specified as ascending or descending.
3. Queries can now be filtered for the ‘top X’ records – for instance, a user can run a query to ‘show the top 25 customers in terms of total premium for active policies’. Just check the ‘Top’ box at the top of the Fields screen and enter a number in the text box. Be sure to use the Sort function, because the ‘top x’ query will use the first field chosen in the Sort screen.
4. The ‘Return Distinct Records’ checkbox is no longer checked by default.
5. Several errors were corrected on the Filter Wizard screen.
6. Please review all of the security upgrades in version 3.12 because that version was only released to one agency.
   Also note that for users who do not wish to utilize any of the new user-level security features, the upgrade process turns them all off, so users will not see any functionality differences if they choose not to activate any of the new security features.
7. User Roles have replaced User Groups. There are 3 Roles available:
   Administrator – has access to all functionality in QueryPlus
   Designer – can access everything except the Administrative screens.
   Viewer – can only open existing reports and modify filters, but not save changes
8. There can now be only 1 user logged into any user account at one time.
9. Numerous database connectivity improvements have been implemented. These are covered in the QueryPlus Knowledge Base.

A QueryPlus user wanted to run a query to produce information about Customers who are classified as Prospects, and their Submissions. Here is the basic query:

  Apply 2 filters:

Customer Type = P

Policy Sub Type = S

A third filter, on the Policy Effective Date, might be helpful to shorten the dataset.   

Running this query will produce a list of Submissions for Customers with a status of Prospect. Now you can add just about any field that pertains to either the Customer or the Policy:

Policy Account Exec Last/First Name

Customer Address

Customer Open Policy Count (to show whether this customer has any other business with your agency, or to identify the Prospects that ‘slipped the hook’)

Keep in mind that not only Prospects can have Submissions – Active Customers can as well, so be judicious with the Customer Type filter.

The possibilities are endless, and really depend on just what you want to know about these Customers/Policies. Once you get this query running, if you have any problems with figuring out fields, all you have to do is QueryPlus Technical Support, and we can help you fill them in.

This is a rather complex issue, but the simple answer lies in the hierarchical nature of relationship database structure. An example:

For each Policy, there may be many Transactions. The Policy data is stored in the Policy table, with one row for a policy. Transaction data is stored in the Policy Transaction table, with one row for each Transaction.  

That means that if we query a Policy Number (from the Policy table) and Transaction amounts (from the Transaction table), the Policy Number will be duplicated:

Notice that the Policy Number shows up on each line, because each of the premium Transactions belongs to that Policy.

It is important to note that this is not a peculiarity of QueryPlus, but is inherent in relational database queries.  

It is also easy to see that for most types of data – Policy Number, Customer Name, Addresses, etc. – this duplication does not cause a problem.

This problem does occur when numerical values from different ‘levels’ are put into the same query. Here is an example from the Invoice, Invoice Transaction and Invoice Commission tables:

In this case, there is only one Transaction for this Invoice ($1281), but because there are 3 records in the Invoice Commission table for that one Transaction, the Transaction Gross Amount is duplicated on all 3 lines.

The practical effect is that when this dataset is sent to the report designer (or Excel, Access, etc) and the amounts are summed, the Invoice Transaction Gross Amount will show up as $3,843, which is wrong.  

OK, that’s the problem, what’s the solution? There are several:

1.      Just being aware of this issue will make a big difference. Select fields and examine the resulting datasets with an eye towards making sure that numerical data isn’t being duplicated.

2.      Make use of QueryPlus’s built-in summed data fields. For instance, in the Invoice example above, QueryPlus provides fields from the Invoice Commission table that are already summed up to the Invoice Transaction level:

Comparing this result with the one above for the same Invoice, we can see that each field is only shown once, so the data will show up properly on a report.

3.      If you can’t find a field at the right level, just ask! Here at QualCorp, we are always working on new fields and database connectivity, and most of them start as ideas brought to us by you, the QueryPlus user community.
So don’t hesitate to ask – we’re happy to work with you to create new fields, queries and report, because once it works for you, we can put it on the Knowledge Base and make it available to ALL QueryPlus users.

You’ll find it in QueryPlus/Technical Documents/AMS360 Field Mapping Documents.

Many more fields are in the pipeline, and in the meantime, if there’s a field you can’t figure out, please contact your QueryPlus Technical Support team – we’re here to help.

Several QueryPlus users have mentioned that matching up data fields on the AMS interface screens with the field names in the long list in QueryPlus has been a challenge.

To help with this issue, and to continue the process of de-mystifying the field selection process, we have started to map the AMS fields to their corresponding QueryPlus names.

Given the large number of screens in the AMS interface (and the factor that there are actually 2 AMS interfaces), this project will take a little while, but we will be posting each page in the QueryPlus Knowledge Base as it is completed.

So be sure to check the QueryPlus Knowledge Base periodically – we post new material as quickly as it becomes available.

Question: Why don’t I get good results when using the field ‘Policy Full Term Premium’?

Answer: Policy Full Term Premium is entered by the AMS user and is not maintained by the AMS interface. When changes are made to the policy, this field requires the user to manually re-calculate and update it.  

Accurate Premium amounts are obtained from the set of fields that begin with ‘Policy Transaction Premium’:

Total Premium equates to ‘Cost’.  

Queries using these fields will return one record for each Policy Transaction Premium record of a Policy, so they must be summed in a report generator to give the total for the policy.

Note: the Daily Querier has taken a task to create QueryPlus fields that sum these fields up to the Policy level. They will be available shortly.

QueryPlus opens up a window into your AMS database that is perfect for ‘data-scrubbing’. Many AMS databases have missing or inaccurate data that is difficult to impossible to identify through the AMS interface. Some of this missing/inaccurate data has the potential to affect reports, and should be identified and corrected.

There are an almost unlimited number of possibilities for data scrubbing. Technical Bulletin 10, available in the QueryPlus Knowledge Base, is full of suggestions, tips and sample queries to get you started in this very useful endeavor.

Having viewed several AMS databases, the Daily Querier has a suggestion regarding data scrubbing: Don’t try to do it all yourself! Consider hiring a summer intern, or include the name of the CSR (Customer Account Rep Last/First Name) in the query and give each CSR the records he/she is responsible for.

The revised version of the Production Report template is now posted in the QueryPlus Knowledge Base.

As previously stated, this version requires a database upgrade. Please contact QueryPlus tech support to schedule your upgrade if you are interested in this report.

The report has been tested and found to have very close correlation with the AMS report of the same name. Due to agency practices, database inconsistencies and other issues, further agency-specific refinements may be required, but these should be minor in nature.

Testing by QueryPlus users has found several critical filtering components for the Production Report which are necessary to create a dataset that matches the AMS report. These items are:

1.                  The ‘Return Distinct Records’ check-box on the Fields screen (upper-right corner) must be un-checked. Please note that this must be done each time the report is opened, until the next release of QueryPlus.

2.                  The query must be filtered to remove all ‘On-Account’ invoices. This is done by selecting Invoice Type and filtering it for ‘1 or 2 or 3 or 4’.

3.                  The proper Line of Business field is Invoice Transaction Line of Business.

4.                  The date range must use a new QueryPlus field called Invoice Production Reporting Date. This date is arrived at by comparing Invoice Date and Invoice Effective Date and taking the more recent of the two.

These changes will be incorporated into Revision ‘A’ of the Production Report Template, which will be posted in the Knowledge Base.

The fourth change will require a database upgrade for all QueryPlus users.