Dear RSGuru
By Ralph Hooper, Manager Client Services, San Rafael, CA
Dear RSGuru,
Like the White Rabbit in Alice in Wonderland, I always seem to be late for things. I have a terrible time with time, even though I wear a watch and carry an iPhone loaded with all sorts of time display and management apps! These days, time is a major focus at work. I am my company’s environment, health and safety manager, and I am dealing with a sudden spike in claims that has all of us stumped. One metric I would like to capture is the time of day these accidents occur, to see if a pattern exists that might suggest a solution. Many of the claims are transportation connected and others are tied to our manufacturing and retail operations. How can I do this in Sigma EncoreSM and in your reporting tools?
Dear White Rabbit,
Sigma EncoreSM has a timely field called "Time of Accident." This is a powerful piece of information if you use it properly AND if this field is sufficiently populated in your database. It sounds like your data sources are supplying this information, and that is great! The trick to remember is that time is displayed in this field as a text item, not as a number. For example, 9 AM is entered as either as 09AM or 0900. Noon is entered as 12PM or 1200. 1 PM or later can be displayed either in military time (1300, 1400, etc.) or 01PM, 02PM, etc. Check with your account manager to determine how this field is treated in your database.
If you want to create a report in Oracle® Discoverer to list all open claims that occurred between 6 AM and 9 AM, select Time of Accident from the 03-DATE category (the field is listed last in that group), then select your other fields. When you enter the Time of Accident condition, be sure to enclose the times in single quotes: Time of Accident between '0600' and '0900.' Be sure that your report sorts by Time of Accident. You might also create a summary report that groups claim counts by time.
In SE Reports this field is not among our standard sorting items. However, you could ask your account manager to add the field to the sorting and grouping drop-down list of choices. From your question, it sounds like a time sort would be quite useful in the claim listing and financial summary reports. This field would also make a great addition to the field choices in the Frequency and Severity Report. But I must again caution you to be certain that Time of Accident is being sufficiently populated.
Sorry to be so long-winded, but I am having the time of my life!
Dear RSGuru,
I am becoming quite the Oracle® Discoverer jockey. I regularly use the Point in Time template, and I make extensive use of parameters. I have learned to use Case Statements and to create revealing calculations like counts of open claims and TPD and TTD paid dollars. My company has even asked me to process its state reports!
A lot of my calculated fields are related to Accident and Report Dates, and I typically use Accident Start Date, Accident End Date, Report Start Date, and Report End Date as my great parameter names. There are times when I want to use these parameters in my calculations rather than in my conditions. (Isn't that slick!) When I do this I run into this funky error message: "Error in formula – unexpected end of formula – Error: Syntax error: token End at position 115." Aside from being hypnotized by the two colons and two dashes in that error message, and by its typical "nerd" quality, I am at that point dead in the water. What do I do?
Dear OD Jockey,
Congratulations on your Oracle® Discoverer prowess. The power and flexibility of OD is indeed quite stunning. I am especially pleased that you are finding delight in parameters. They make report running a total joy and save major time.
As for that pesky error message (and I agree that it was not designed for humans), there is a simple workaround. The issue is that the case statement has these critical terms: CASE WHEN ... THEN ... END. Nothing in your formula should duplicate these terms.
Let's take an example. Suppose you want to create a summary report based on Accident Date of all open claims displaying claim counts and total incurred. Suppose that you want to isolate the last six months' claims from the earlier claims, labeling them "Recent Claims" and "Earlier Claims," and that you will need to run this report often going forward. You have created the parameters Accident Start Date and Accident End Date, and then you created the calculated field "Period" using this formula: CASE WHEN Accident Date between Accident Start Date and Accident End Date then 'Recent Claims' else 'Earlier Claims' END. The problem is that OD thinks that the "End" in Accident End Date is the case statement END. You can easily get around this by not using "End" in your parameter name. You could call it Accident Ending Date or simply Ending Date.
The key here is not to use any separate word in a parameter name that duplicates case statement terminology in a situation where you want to use the parameter in a calculation based on a case statement. That was a pretty nerdy sentence, but you get the drift.
Dear RSGuru,
I am a Civil War buff, and these days I find myself thinking a lot about that old political mantra "states' rights" as I wrestle with a states jurisdiction issue of my own. Our company does business in almost every state of the union, and we deliver our products across state lines every day. In tracking my company's claims, I must differentiate the claim jurisdiction state, the state where the accident physically occurred, the state where the claimant resides, and the state of the local office that reported the claim. Can I keep track of this without singing "union forever"?
Dear First Manassas Expert,
Sigma EncoreSM has the four state fields you have just described plus a handy state alias field. You will find both Jurisdiction State Code and Accident State Code on the Claim Summary and Claim Quick Search panels in Sigma EncoreSM and all four state fields in the Location and State folder in Sigma EncoreSM Advanced Search. In the Oracle® Discoverer Claim Current Evaluation and Claim Point in Time business areas, you will find Jurisdiction State, Accident State, and Location State in category 10-STATE and Claimant State in 07-CLMT. In Oracle® Discoverer there is also an alias called State Code. Any of the state fields can be assigned to State Code; the default assignment is Jurisdiction State. When you are creating an ad hoc report in OD, it is quite handy to select State Code from the Favorites category. State Code is also used in the SE Reports Detailed Claim Listing template. Be sure to let your RSG account manager know if you would like a different State Code assignment.
|
