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.

This issue actually goes back to the very early days of QueryPlus, when the fields for the Customer Account Exec in the AMS360 version of QueryPlus got erroneously named Customer Prod1...

Why did this happen? Well, because the field in the database is called 'prod1', and we didn't know any better at the time.

It's been an annoyance (and a bit of an embarrassment) ever since to have to explain that the Customer Account Exec fields are called Customer Prod1, but the really dumb part of all of this is that the fix has always been available, and when I finally got around to doing it, it only took about 5 minutes.

So now there is a set of fields that starts with Customer Account Exec Employee..... (Name, Address, City, State, Zip, etc).

The old set that begins with Customer Prod1 Employee.... has been left in place, because they are used in a large number of queries and reports. Either set of fields works equally well, and they can be mixed in the same query if desired.

Oh, sorry, got carried away!

A QueryPlus user bought neat little holders for those auto insurance cards that always get lost, and they wanted to know how many to send to each Customer. After we created a new field in QueryPlus, they were able to do it easily. Here is the query:

Filter Policy Line of Business for 'equals Private Passenger Auto'

Filter Personal Auto LOB Vehicle Information Count of Vehicles for 'greater than 0' [zero]

 Run the query and either output the dataset to Excel or to the QueryPlus mailing label template, which will print a mailing label for each Customer.

And if you're really creative, you can stick the number of vehicles in the corner of each mailing label, so the person who gets to stuff the envelopes will have everything they need to do it right on the mailing label.... except for the card holders themselves, of course!

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