Author Topic: NEW production report  (Read 305 times)

0 Members and 1 Guest are viewing this topic.

Offline DebAmstutz

  • Scalliwag
  • ******
  • Posts: 4281
  • Karma: +479/-8
  • It's never too late, until it's really too late...
NEW production report
« on: April 28, 2020, 04:04:41 pm »
The owner here ran a summary production report for NEW business since January 1, but it pulled in production from policies written last year that still have a NEW status.  None of the production reports appear to have "First Written Date" in the criteria, so am I correct in thinking that a search of customer transactions where "First Written Date" is part of the criteria is about the only way to get an accurate report on New production?  He is wanting to base producer compensation on NEW business written, and an accurate report is needed for this.

For whatever reason, nobody codes rewrites as REW, so until I run a report and find them, they are included as NEW business.  We have no designated CSRs and even when people scan the dec sheets they don't change policy statuses to what is correct.  Woe unto policies that don't download, as they rarely have been changed.  I've found NEW policy status on business that is several years old because the company doesn't download.
Deb Amstutz
Back in the TAM saddle again

Offline Todd Arnold

  • Frequent Flyer
  • ***
  • Posts: 174
  • Karma: +112/-0
    • AB Solutions, Inc.
Re: NEW production report
« Reply #1 on: April 28, 2020, 06:00:33 pm »
Hi Deb!

It's been a minute since I've looked at a TAM report, so I have a couple of questions on things I don't remember anymore. 

In the production report extra criteria area, can you choose Policy in the "from table" area? 
And if you can do that, can you see the first written field in there? 
If so, there might be at least a glimmer of hope to setup extra criteria to do this. 

I don't remember what date functions TAM has in extra criteria at all.  But if that 1st written is accessible in there then something along the lines of
[Eff Date] - [1st written date] < 364
might work.
Todd Arnold
AB Solutions, Inc.
800-753-7785 x111

Offline Alice Mooney

  • Scalliwag
  • ******
  • Posts: 2537
  • Karma: +507/-2
Re: NEW production report
« Reply #2 on: April 28, 2020, 08:17:02 pm »
I remember the Search report where you can pick just about any criteria you wanted (man, I sure do miss that) and I know I used that for first written date. It's an available option and you can select a specific date range.
Epic 2018 Online
1000+ users

Offline Todd Arnold

  • Frequent Flyer
  • ***
  • Posts: 174
  • Karma: +112/-0
    • AB Solutions, Inc.
Re: NEW production report
« Reply #3 on: April 28, 2020, 08:45:40 pm »
Typing that reply to Deb made me miss extra criteria. Epic doesn't have an equivalent. :(
Todd Arnold
AB Solutions, Inc.
800-753-7785 x111

Offline Mark

  • ScurvyDawg
  • *******
  • Posts: 7471
  • Karma: +651/-7
  • meh.
Re: NEW production report
« Reply #4 on: April 29, 2020, 10:04:13 am »
Typing that reply to Deb made me miss extra criteria. Epic doesn't have an equivalent. :(

I miss extra criteria, too!  And the search report was my main go-to!  Closest thing is having BDE, but without a base report to start from it's not as easy as it could be.
Mark Piontek, MBA
Director of IT
BS in Information Systems Security

Offline Todd Arnold

  • Frequent Flyer
  • ***
  • Posts: 174
  • Karma: +112/-0
    • AB Solutions, Inc.
Re: NEW production report
« Reply #5 on: April 29, 2020, 03:53:47 pm »
What are you using for your final report output from BDE Mark? 

Did I ever go over how I use Crystal reports to "cheat" a bit to create my BDE SQL Views with you?  Even if Crystal won't be part of my final output solution I setup all the links, filters, fields, etc. in a basic crystal report and then copy the SQL expression it builds so that I can copy and paste that back into MS SQL Studio to create a new View.  Really helps smooth over how lame I am at SQL queries.  I like the table selection and linking view inside crystal a lot better too, but that might just be familiarity.
Todd Arnold
AB Solutions, Inc.
800-753-7785 x111

Offline Mark

  • ScurvyDawg
  • *******
  • Posts: 7471
  • Karma: +651/-7
  • meh.
Re: NEW production report
« Reply #6 on: April 29, 2020, 04:30:27 pm »
Most of the reports we actually use are through SQL reporting services and other people run them.  I do have some queries I might do right in SQL studio (I often use a lighter client from my workstation called SQL Buddy) just to get what I'm looking for or figure something out.

I like your crystal reports method!  Most of the reasons I go outside of Epic Reports is because that lack of extra criteria.   This means that most of my reports *could* start with an existing Epic report as a base and I really wish I could get my hands on that SQL when I'm making them.  It sure would speed things up!  It would also make me more confident that I'm thinking of every required detail for an accurate report.  I can't tell you how many times I've run reports through BDE and realized later that I wasn't getting the data I thought I was getting for various reasons.  Can't think of an example right now, but I'm sure in the beginning it was simple things like remembering to specify active clients.

Now, all that said, one thing I'm kind of proud of is a specific process that we started in TAM, then modified to work with Epic.  This is an activity report that we would run to Excel, then we had an Excel macro made that would format the report into a client deliverable product.  I've since built an add-in for Excel that prompts for a client code and some dates, then pulls all the data from BDE and creates the report directly in Excel.  Don't even need to be logged into Epic for it to work!  Though, I do have user restrictions on the SQL server, of course.  Doing it this way allows me to account for some things that I couldn't figure out how to do with a standard Epic report.  Mainly that when there is a note on an activity, that activity is duplicated in Excel output.
Mark Piontek, MBA
Director of IT
BS in Information Systems Security

Offline DebAmstutz

  • Scalliwag
  • ******
  • Posts: 4281
  • Karma: +479/-8
  • It's never too late, until it's really too late...
Re: NEW production report
« Reply #7 on: April 30, 2020, 03:58:32 pm »
Back to Todd's original question in TAM - the only table available in Extra Criteria is Transact.  Policy doesn't appear in the drop down list.  If it had appeared there, I'd have tried to get the first written date into the report that way.  I can formulate a search and save it as user-defined, but the owner isn't crazy about using the user-defined reports.  I had just wondered if there was a "normal" report that would give the info. 

Thanks!
Deb Amstutz
Back in the TAM saddle again

Offline FWA

  • Moderator
  • Scalliwag
  • *****
  • Posts: 2860
  • Karma: +299/-1
Re: NEW production report
« Reply #8 on: May 04, 2020, 04:00:13 pm »
Without getting into a separate report writer, and if you don't object to importing into Excel, you could create a multiple step data extraction.  TAM reports can save to Excel, comma delimited etc. but also to another DBF file.  That DBF file, as long as you included a common field (such as the policy number, rec, pol_idx), could be used in conjunction with another TAM data file.  For example, you could run a search and safe as DBF to find all policies effective 1/1/2020.  Then use that with the Report Wizard as one of two DBF files.  The other possibly being the TRANSACT file.  Then if it didn't exist in the first, it could not match a record from the second.  The results could go to Excel and columns could be eliminated by hand at first, but by a recorded Excel macro after it was found to be of use.

Just my suggestion.  This could all be done in Excel, but I find using the reporting facilities to be easier.
Fantasy is more important than knowledge, because knowledge has its boundaries - Albert Einstein

Another day, another clay.  All that matters is the next bird.

Offline Ric

  • Gold Member
  • *****
  • Posts: 1870
  • Karma: +267/-2
  • "Fall down seven times, get up eight" Japanese Pro
    • Mariano Agency
Re: NEW production report
« Reply #9 on: May 05, 2020, 10:25:57 am »
The owner here ran a summary production report for NEW business since January 1, but it pulled in production from policies written last year that still have a NEW status.  None of the production reports appear to have "First Written Date" in the criteria, so am I correct in thinking that a search of customer transactions where "First Written Date" is part of the criteria is about the only way to get an accurate report on New production?  He is wanting to base producer compensation on NEW business written, and an accurate report is needed for this.

For whatever reason, nobody codes rewrites as REW, so until I run a report and find them, they are included as NEW business.  We have no designated CSRs and even when people scan the dec sheets they don't change policy statuses to what is correct.  Woe unto policies that don't download, as they rarely have been changed.  I've found NEW policy status on business that is several years old because the company doesn't download.

Deb,

Rather than invent a report, my suggestion to the owner would be to pay Producers on transacted and paid items (direct & agency bill) and also to have CSRs or whomever update all billing screens to reflect what is real and accurate.    Transactions need to be entered accurately using the correct status of the policy, New, Renewal etc.
Ric Tucker
Manager of Information Systems
Past President, New Jersey Chapter

J A Mariano Agency
TAM 14.0.2, 13 users, Windows 2012 Server, Windows 7 Pro 32-bit workstations
fax@vantage 9.0.5,
Acoustic guitar, drums, percussion
Internet Exploder 11, Office 2010

Offline DebAmstutz

  • Scalliwag
  • ******
  • Posts: 4281
  • Karma: +479/-8
  • It's never too late, until it's really too late...
Re: NEW production report
« Reply #10 on: May 05, 2020, 04:58:14 pm »
Now the owner wants to use the BOB report because first written date is there.  That's all well and good until someone "forgets" that a rewrite is not NEW biz.  Companies will download as NEW but that's because it's NEW to them.  I will have to watch those each month I guess. 
Deb Amstutz
Back in the TAM saddle again

Offline Todd Arnold

  • Frequent Flyer
  • ***
  • Posts: 174
  • Karma: +112/-0
    • AB Solutions, Inc.
Re: NEW production report
« Reply #11 on: May 05, 2020, 05:36:05 pm »
So using a Search of Transactions is out of bounds, but using a policy-based BOB for producer compensation is OK?

Looking past the more fun to address reporting topics, Ric is right: Put in place processes to enter / audit accurate data so that the system can be used as intended to compensate producers.  Sometimes, reports based solutions are just enabling. :(
Todd Arnold
AB Solutions, Inc.
800-753-7785 x111