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

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)

Here's an interesting query request: Show me a list of all my Customers who do NOT have an Active Workers Compensation Policy. (This works equally well with any other type of Policy, BTW)

1.       Create a query with these fields:

Check the Return Distinct Records box:

Open the Filter Wizard and add a filter:

Click OK to close the Filter Wizard, then Preview to run the query:

a.       Only show Active Customers (take care because I don’t think this field is well regulated by the AMS interface)

Only show Commercial Lines Customers

AfW:

AMS360:

(For further information on the amazing Customer “Business with Agency” Checkbox Flag field, see the QueryPlus Mapping Document for AMS360, page 27) 

AMS360:

(In case anyone is interested:
     Yes, AMS changed the name of the field from ‘Status’ in AfW to ‘Type’ in AMS360
     No, there is no ‘Status’ field in the Customer table in AMS360
     Yes, this is the type of thing that makes database analysts grind their teeth in their sleep J)

Remember that each filter ADDS to all the other filters in restricting the number of records returned.

Be sure that the set of filters is correct for the desired list – and never neglect to verify and test your data!

Profile questions and answers are a great way to save information about AMS360/AfW Customers, but running queries and getting good data is a little bit complex.

Use these fields along with Customer Name to create a list of Customers who answered a specific Profile question, or answered it a certain way. For instance, if the question is 'Smoker?' and the desired answer is Yes:

  Profile Answer Description  Filter 'equals Smoker?'

  Profile Answer Description  Filter 'equals Y' (Yes is mostly stored as Y)

That takes care of specific questions and answers, but what about this request we recently got: I want to see a list of all of my Customers who have NOT answered the Profile question 'Decision Maker'. That gets a little complicated, but QueryPlus has a solution.

One complication is that until the question is asked and answered, there is no record of it in the AMS database. Adding to the complexity is the factor that all Profile questions and answers are Agency-specific, so the QueryPlus support team cannot just 'create a field for every possible Profile question and answer combination'.

In the case of a query like the one described 2 paragraphs above, just contact the QueryPlus support team with the exact text of the Profile question, and we will create the fields you need, almost always in a couple of hours. Let's see how that works for the example above.

We created 2 fields:

   Profile Question Decision Maker Text

   Profile Question Decision Maker Count

Filtering the Count field for 'is Null' in a query with Customer Name will give a list of all Customers who have NOT been asked the Decision Maker question. Here are some ways this query can be enhanced.

Add these fields for filtering:

                Customer Type (P = Personal Lines, C = Commercial Lines)                

                Customer is Active (Y = Yes, N = No) [I urge caution on this field, it does not appear well-regulated by the AfW interface]               

                Customer Open Policy Count (‘is Null’ for no open/active Policies, ‘greater than zero’ for Customers with 1 or more 
                 active Policies)                

                Customer Status (S = Suspect, P = Prospect, C = Customer, B = Broker) [again, not well-regulated by the AfW interface]

Those are the most common Customer-level fields to filter on, but there are many more. If you want to filter your list and can’t figure out how to get what you are looking for, just let us know. 

You can also add fields for information, if you are sending the list to CSRs to have them call the Customer:                

                 Customer Resident Area Code/Phone 

                Customer Account Rep Last Name/First Name

As with all QueryPlus queries, the possibilities are endless, so this isn't intended to be the complete solution, merely a template to start with.

Images and graphics can really spice up a report, and the QueryPlus Report Designer allows a wide variety of graphics and images to be imported. Here's how to do it:

1. Click and hold on the Image button in the Toolbox, then drag the box to the desired location on your report:(be sure it is within the borders of a band)

2. Double-click in the box just created to open the Image dialog box. Images can be imported from many sources, including the Internet (by using the 'Image URL' tab), but for this example we'll stay simple and use 'File'. Click the ... box at the top right, then navigate to where the image to be imported is stored, then click  Open, then OK.

3. Now you should see the image in the box.

 4. The image may be way too large for the box it's in (it may even look like the box is still empty). Open the Properties window (View - Properties). There are lots of properties to play work with (go for it!). For this example, we'll just set the 'Stretch' property to True. That causes the image to 'auto-size' for the box it is in.

5. That's all there is. Save the report, click Preview, and the image should be visible on the report, as in the example below. From here, adjust the size, position, border, etc. to suit the report it is in.