When a QueryPlus user wanted to 'merge' 2 fields - Broker Name and Employee Name - in a report, he found he couldn't sort the fields correctly. Here's how to do it:

1.   The string in the text box with the names is:
           {IIF(QueryResults.[Invoice Commission Broker Name] = "", QueryResults.[Invoice Commission Employee Name], QueryResults.[Invoice Commission   
                 Broker Name])} 
        This statement tells the report designer to use the Employee Name if no Broker Name exists, or the Broker Name if it does exist.
        The trick is just to use this string in the Group Header.

2.       Open the Text Editor for Text1 and copy the entire string:
          
3.       Double-click GroupHeaderBand1, click on the Expression tab, and paste the string:
           

5.       Run it and it should do what you want. Remember: Always check your data.

A QueryPlus user wanted a list of all the Employees who had access to the various 'secured' Customers in their AMS360 system.

Here's how to do this in QueryPlus:

1.       Start a new query and choose the Basic Agency Information module:
        

2.       Click Fields and set the drop-boxes as shown below:
         

3.       Select these fields (at a minimum, any other fields in this Component can be used):
         

4.       This will return a list of all Employees, with the ‘secured’ Customers they are authorized to access, if any.
         

5.       Filter this query as required, for instance:
          a.       To get a list of all Employees authorized to access a single Customer, filter for that Customer.
          b.      For a list of all Customers a particular Employee can access, filter for that Employee.
          c.       Any combination of Customers or Employees.

6.       For a formatted report, send the output dataset to the QueryPlus Report Designer.

When we showed this new set of fields at the latest QueryPlus LIVE! Webinar, a user requested to be able to see the most recent date that Agency Commission had been received from each Company. So - surprise - we created a new field to show just that:

Company Latest Agency Commission Received Date

 Put this field in the same query:

Now you can tell when you last received Agency Commission from each Company.

A QueryPlus user wanted to easlily identify Writing and Parent Companies which weren't being used by her Agency. Here's a query that will help with that.

1.    Start a new query.... Reports - New Report.

2.    Give the query a name and choose the Basic Agency Information module.

3.    Set the System/SubSystem/Component drop-boxes as shown below.
      

4.    Choose these fields. 
      

5.    Click Preview - your dataset will look like this. 
      

For both Parent and Writing Companies, a number in the Count field indicates 1 or more Active Policies assigned to that Company.

In the '..Latest Policy Expiration Date' field, a blank indicates that Company never had an Active Policy with your Agency. A date with a blank in the Count field indicates when the most recent Policy Expiration Date occurred - which may be helpful in determining whether to make that Company inactive.

Company Type values:

N = Insurance
B = Brokerage
F = Finance
E = Fee
W = Writing

Company Type can be filtered, but since this field can be changed, a filter like 'equals N or W' would exclude Companies that were N or W at the time a Policy was created, but have been changed since.... not particularly likely, but possible, and for this type of query, it is much better to leave a field like Company Type un-filtered to be sure to get all of the entries of interest.

Export this dataset to Excel, mark the Companies to be inactivated, then go to your AMS360/AfW interface to verify the results and make the corrections.

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.