| |
Dear RSGuru
Ralph Hooper, Manager of Client Services, San Rafael, CA
Dear RSGuru,
I’m a fixit kind of guy who never has enough tools. I’m slowly turning my garage into paradise and having a blast. Tim Allen, eat your heart out! My birthday’s coming up, and I’m hinting big-time to my wife that I want some new Craftsman tools. I love my professional tools, too, especially Oracle® Discoverer. Alas, if only I could say good old OD was perfect. I use OD to download large amounts of data sliced and diced my way, but every time I do it, I run into the 2000 record whammy! I get the dreaded “Click to retrieve next 2000 rows.” OD can only query 2000 records at a time. Talk about tedious! Good thing I don’t have a hammer handy when this happens! Is there a tool that can fix this tool? —Mr. Fixit
Dear Fixit Guy,
It so happens that OD has a number of neat built-in tools. Go to the menu bar and click on <Tools>, then on <Options>, and finally on the <Query Governor> tab. This is one Governor that delivers every time! Down toward the bottom of the Options window, you will see “Retrieve data incrementally in groups of:” and a box for entering a number. It is set by default to 2000. Change it to the number you expect to query. For example, if you need to query around 40,000 records, enter 40000 and click <OK>. That’s it! The query will return 40,000 rows at a time. It will stay that way whether or not you save the .DIS file. It’s a good idea to reset it to 2000 if you execute a lot of smaller queries. But for the big times, the Query Governor is in your tool rack ready to deliver!
Dear RSGuru,
I’m an amateur inventor who loves creating things from scratch. I’m always working out new ideas and designs in my head, on the bus or in line at the store. Right now I am working on an indestructible umbrella! I know. I hear you laughing, but I think I’m on to something! During the rest of the time I work in the Risk Management department of my company. And here I also try to be innovative. I’m charged with running and designing all of the reports. One of the things I love about Oracle® Discoverer, aside from creating everything from scratch, is creating clever calculations. I continually impress my co-workers (and my RSG Account Manager) with my calculation prowess. But there is one problem I keep running into. When I create a financial calculation, I can’t get the Total and Subtotal tool to work. I’ll create a fabulous financial report, with standard and calculated columns, and the standard columns will subtotal beautifully, but my oh-so-neat created financials will not. This is hurting my inventor’s pride. —Amateur Inventor
Dear Inventor,
In Oracle® Discoverer, go to the menu bar and click on <Tools>, then <Options>, and finally on the <Aggregation> tab. In the center of the Options window, uncheck the box next to “Use the default aggregation behavior.” Below, check the radial button next to “Show the sum of the values displayed in the contributing cells.” This will total and subtotal your calculated columns. Your co-workers-and, more importantly, your boss-will gasp in delight at the Edison in their midst.
Dear RSGuru,
I’m a Risk Manager who needs to get a handle on time ... the time it takes us to respond to accidents and to adjust claims. We are using four different TPAs, and I need to assess their temporal performance. My sense is that we are too slow, but I need specific stats before I can respond with a clear plan for the future. —Student of Time
Dear Student of Time,
The stats you need can be assembled using a lag report. But first we need to review the three kinds of Report Dates that Sigma EncoreSM offers.
First, there is Client Notified Date, the date that the accident is reported to your company, usually to a local manager. Second, there is the Carrier Notified Date, the date your department reports the accident to one of your TPAs. Finally, you may also be collecting Insurance Company Notified Date.
In our new Oracle® system, we have hard-coded Client Lag Days (Client Notified Date – Accident Date), Carrier Lag Days (Carrier Notified Date – Accident Date), and Insurance Company Lag Days (Insurance Co. Notified Date – Accident Date). We also now have a First Close Date field. This is quite useful if you want to capture the lag between Client Notified Date and First Close Date to measure the initial life of the claim in your system.
In Oracle® Discoverer, you can then create a lag summary report-by division, department, or some other useful location level-with these fields:
- Location Level
- Claim Count
- Average Client Lag Days (Standard Data Item)
- Average Carrier (TPA) Lag Days (Standard Data Item)
- Average First Close from Reported Days (Calculation: First Close Date – Client Notified Date)
- Average First Close from Accident Days (Calculation: First Close Date – Accident Date)
This summary report can be accompanied by a detailed lag report including:
- Location Level
- Claim ID
- Claimant Name
- Accident Date
- Client Notified Date
- Carrier Notified Date
- Client Lag Days
- Carrier Lag Days
- First Close Date
- First Close from Client Notified Lag Days
- First Close from Accident Lag Days
|
