Several Agencies have asked to be able to analyze Producer Commission specifically for Policies on which more than 1 Producer has earned Commission. To facilitate this, we have created a new field: 

This field works best with one of the provided Commission reports: Commission Report Policy Level or Producer Report Commission Details, although it can be added to any query or report where it is useful. To use it:

1.       Open the report in QueryPlus

2.       Find the new field in the filter bar:

3.       Add the field to the query.

4.       Open the Filter Wizard, select the new field and add this filter to it:

5.       Set your date range, Policy GL Dept, Div, etc filters as you want them (remember, this report may return a whole lot of data).

6.       Click Preview, then when the dataset appears, click Design Report and open the finished report.

7.       Most important: test and validate the data returned by the query on your database. For a whole lot of reasons, we cannot vouch for the data that a query returns on your database, so you must check your data well enough to gain confidence that you’re seeing what you should be.

There are 2 types of files associated with QueryPlus:              

Query files (.qpe)              

Report Designer files (.mrt)

Query files are stored in the QueryPlus SQL Server database and will be backed up when and if the database is.
Query files can be manually backed up within QueryPlus by selecting Reports – Backup Reports and selecting a location at the prompt.
All of the Query files will be exported to that locationReport files are stored at a location determined by the QueryPlus user, usually a network or local hard drive.

My recommendation for a backup plan would be 3-fold:                                                              

1. Set up a regularly scheduled backup of the QueryPlus SQL Server database.                                                            
2. Store the Report Designer files on a network drive that is automatically backed up.                                                           
3.  Periodically perform a manual backup of the Query files from within QueryPlus.

A QueryPlus user wanted to be able to easily find the Writing Company with the highest default Agency Commission for a prospective Customer. Here's how to find this information through QueryPlus:

1.  Start a new query and choose the Basic Information Module, then Fields.

2.  Set the drop-boxes as shown.

 3.  Choose from the fields in the Available Fields list. Remember that it isn't possible to mix fields from different Components in the Stand-Alone Tables System. A typical query would look something like this:

4.  Filtering is important, because the number of records can be huge: It is possible (although not likely) to have a different default commission for every Writing Company, LOB, Plan Type AND Transaction Type. In the Daily Querier's test database, there are 679 Writing Companies, 181 LOBs, 26 Transaction Types and 162 Plan Types, yielding a possible (if improbable) total number of records of..... 679 x 181 x 26 x 162 = 517,650,588..... so give your filters some thought.

Usually, a filter on a particular Line of Business will yield the best results, and perhaps a particular Transaction Type. For instance, an LOB of HOME and Tran Type of NBS will give you all of the Writing Companies that DO have a default Agency Commission for that combination. Keep in mind that filtering on both LOB and Tran Type will only return records where BOTH exist. That means that any Writing Company that has a default Agency Commission for HOME, but not for HOME and NBS in the same record, will be excluded by this query.

To further restrict the listings for a particular set of records, set the Top X filter in QueryPlus to pull only the Top 10 or Top 20, to only see the best deals.

5.  The best way to learn about this query is to experiment with it - run it and observe the dataset to refine your fields and/or filters.

When searching for Customers who should be made Inactive, this query has been available:

Filter as follows:

                Customer is Active                                          equals Y

                Customer Open Policy Count                      equals zero

                Customer Latest Policy Expiration Date filter for desired date limit

A QueryPlus user wanted to see the Line of Business of the latest Policy to expire, for re-marketing purposes. So this field has been added:

                Customer Latest Policy Line of Business

We created a new field:

                Policy Line of Business Code with Non-Coded LOBs

This field returns BOP for BOP, PKG for Package and….. MONO for Monoline. For all other LOBs, it returns the expected code.

A QueryPlus user wanted to be able to track down Invoice Transactions that occurred long after a Policy Expiration Date, so we created a new field.

The name of the field is Invoice Transaction Days Past Policy Expiration Date.

If you put the fields below in a query, and filter that field for a number (I used 365 to identify transactions more than a year after expiration date), you should get what you are looking for:

The output will show:

Of course, this query will only identify the 'universe' of possible erroneous invoice transactions – it's up to you to decide which are in fact proper and which aren't.

Add fields from Policy, Invoice and Invoice Transaction as necessary to provide adequate information to identify individual transactions and determine if they are erroneous.

A Suspense record can be attached to 1 of 7 AMS360 Centers: Bank, Broker, Company, Customer, Employee, User or Vendor.

To access all of the Suspense records, start a new query, and on the Step 1 page, choose the Basic Agency Information module, then click Fields. Make the following choices from the drop-boxes:

These are the fields available for Suspense queries:

These fields form a ‘Standalone Unit’, which means they cannot be combined with other fields in a query.

Use the ‘Suspense Center’ to see or filter on the particular Center desired.

Since the most common type of Suspense record is one attached to a Customer record, QueryPlus contains a set of fields that can be queried along with any other Customer field(s). To access these fields, start a query using the Customer/Policy module, and filter the Available Fields list for ‘Customer Suspense’:

These fields will return details on only the Suspense records attached to the Customer Center, and will be linked to the Customer they were created for.

This group of fields, as mentioned above, can be included in any query of Customer records. It is important to understand, however, that because the Customer Suspense records are attached to the Customer, they will possibly cause duplicate records if they are combined with other fields which can return multiple records per Customer, such as Policy, Invoice, etc.

Here's one more data-scrubbing query: Identify all Customers who are coded as Prospects or Suspects in the AMS database, but who have one or more Active Policies.

For AMS360:

For AfW:

(Yes, the name of the field changed. Yes, that is annoying.)

Filters:

Customer Open Policy Count 'greater than 0' (zero)

Customer Status or Customer Type 'does not equal C'

Add Customer Account Rep Name and export the list to Excel to split it up and mail it to the various CSRs.

When I work with QueryPlus users in their AMS databases, I often see odd and/or conflicting data fields, and QueryPlus is great for finding data that needs to be fixed - I call these the 'data-scrubbing' queries. The query below is an excellent one for data-scrubbing, and there are many variations that are useful as well.

Start with this basic query:

Filters:

Customer is Active 'equals N'
Customer Open Policy Count 'greater than 0' (that's zero)

Run this query and you'll get a list of Customers with conflicting data - Inactive Customers shouldn't have Active Policies. The Customer Account Rep Name (that's the CSR) is included, so this query can be exported to Excel and each CSR can receive a list of Customers that need to be fixed.

For AMS360 users, the Customer Automatically Check Business with Agency Indicator field will show you whether a Customer's 'Business with Agency' checkboxes are being automatically updated. Add a filter for this field to identify Customers whose Business with Agency flags aren't being correctly updated.

Customer Type is different for AMS360 and AfW:

In AMS360, Customer Type can be C = Customer, P = Prospect or S = Suspect. Those choices in AfW are in the field called Customer Status.

In AfW, Customer Type can be C = Commercial or P = Personal Lines. In AMS360, this is handled in the Customer 'Business with Agency' Checkbox Flag.

Many QueryPlus users need a query like 'Show me all my Customers who have a Private Passenger Auto Policy with our Agency but who do NOT have a Homeowners Policy with us'. The query below returns this list, and can be used for any combination of two Policy Lines of Business as well, just by changing a couple of fields.

Filters:

Second Policy Effective Date 'before [today's date]'
Second Policy Expiration Date 'after [today's date]'
Second Policy Line of Business 'equals [the LOB that the Customer DOES have]' (use OR to include more than one LOB)
Second Policy Renewal Report Flag 'equals A or equals I'
Second Policy Status 'does not equal D'
Second Policy Sub Type 'does not equal S'
Policy Homeowners Count 'is null' (use appropriate field for the LOB that the Customer does NOT have)

Substitute any of the fields below in place of Policy Homewowners Count to filter for any specific LOB. If the LOB needed does not appear, contact the QueryPlus support team and we will quickly create the field you need. (there are effectively an unlimited number of possible LOBs, so we cannot create them all - the list will grow as users request them)