Multiple benefits on formula

Attached is a COBRA report I am working on that has multiple benefits for an employee. The report is to list the Employee information, then the event of the employee's action (i.e., termination, reduction in hours, dropping a dependent, leave of absence, changing benefit levels), then the plan line will show the benefits that are changing (may they be multiple or singular changes), and the dependent information, and finally the dependent plan that is impacted.
I have the report working to the employee and dependent information, but can't seem to get the plans to appear on the report.
There are multiple layers to the formulas, see attached report and document sheet.COBRA-Annette-New.rptCOBRA-report-issues.docx

I see that the QB Plan is appearing but not the remaining verbiage. What I want to see is something like below the Field Formula for QB Plan + Field Formula Benefit Plan 2. QB Plan has Field Formula Benefit Plan 2 imbedded in it and this not appearing.

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Sorry I have been a little slow on the response. Ok, so first I need to filter by QB Classification, the QB Plan is not only looking for the QB Classification it is also referring to the Benefit Plan 2 formula which has encompasses all benefit type plans. Since there are multiple benefit types of plans, I have created a formula for first the QB Classification and then a formula for each of the benefit types and finally the QB Plan and grouping for each benefit type but only the first benefit shows all the information. What to do?COBRA-Annette-New-02013.rpt

{@Group 1: Employee} and {@Group 2: Employee Event} are exactly the same. They both convert EMP_NUMBER to a string. So, you've just got 2 groups on EMP_NUMBER. That's unusual, but if that's what you really want, that's fine.

{@Group 4: QB Dependent Plan 1 Den}, {@Group 5: QB Dependent Plan 2 Vis} and {@Group 6: QB Dependent Plan 3 Med} all test DEP_NUMBER and, if it's not 0, compare BENEFIT_TYPE to a string ("DEN", "VIS" or "MED"). So, all 3 of those groups are based on the same fields (which, again, is unusual), and each formula only produces two possible values, True or False, so each of those groups will have a "True" group and a "False" group (the False group will be first). If DEP_NUMBER is 0, each of those 3 formulas produces False.

So, assuming that you have some records for an employee where DEP_NUMBER is 0 and some where it's not 0, all of the 0 records will be in the False group (which will be first). For the records where DEP_NUMBER is not 0, you'll first get the records where BENEFIT_TYPE is not "DEN" (False group 3), and within that, False/True groups for BENEFIT_TYPE "VIS" and "MED". Then there will be a group for the records where BENEFIT_TYPE = "DEN" (True group 3), and within that, False groups for BENEFIT_TYPE "VIS" and "MED" (only False groups, because BENEFIT_TYPE = "DEN" in this group, so it can't be "VIS" or "MED").

If it helps, you might want to try putting your group formulas, especially for the benefit groups, in the corresponding group headers, so you can see the values that the report is grouping on.

For the other 3 groups, you appear to have just changed {EMEB_EMP_BENEFITS.DEP_NUMBER} <> 0 to {EMEB_EMP_BENEFITS.DEP_NUMBER} = 0. You still have the nested True/False groups, which just seems odd to me.

For the last 3 groups, are you looking for DEP_NUMBER <> 0, or = 0? I'm guessing that DEP_NUMBER is dependents, so are you looking for people with benefit type "DEN", "VIS" and "MED" that do, or do not, have dependents?
<> 0 --- They do have dependents
= 0 --- They do not have dependents

Whichever it is, can you test DEP_NUMBER in the record selection formula, so that the report only includes those records?

Normally a report would be grouped on the field itself (EMEB_EMP_BENEFITS.BENEFIT_TYPE), so you'd have one "benefit type" group, which would create groups for "DEN", "VIS" and "MED" (and any other values found in BENEFIT_TYPE).

James

0

GrapeladyAuthor Commented: 2013-02-06

I will take your suggestions in consideration and do something testing. Will let you know by tomorrow, what occurs.

0

GrapeladyAuthor Commented: 2013-02-25

I am back on this project again. I think I have resolved one of the problems, but now am still having another issue with the dependent plan and a question about exporting. A little more information, in the db if an employee is the only one who has a benefit, then the EMP DEP Number = 0, if the employee has dependents then the EMP DEP Number > 0. So the results would be only dependent plans appearing after the dependent information. I have changed from the classification to just placing the QBPLAN field on the detail line. I have suppressed the detail to not include dependent information. I now get my multiple lines of benefits for the employee. Next is the QBDependent, which is working I believe on a grouping 4. I am again experiencing the same issue when I was grouping on the QBPLAN for the QBDEPENDENTPLAN, where only one of the plans appear after the dependent information and not all plans.

Also, on exporting the file it is suppose to be exported as a txt file with comma separators. If I export with as csv there are gaps between the groups. If I export with ttx, there are " delimiters which I need to strip out before sending to vendor. If there a way to eliminate the "s it would be helpful. I export as a txt file it drops data but there are no quotes. So what is the best the solution on exporting? See attached report.COBRA-Annette-New-022513.rpt

> So the results would be only dependent plans appearing
> after the dependent information.

What do you mean by that, exactly?

If you only want the report to include the employees with dependents, you should include {EMEB_EMP_BENEFITS.DEP_NUMBER} > 0 in the record selection formula.

Your groups still seem odd to me.

Group 1 is on EMP_NUMBER.

Group 2 is on a combination of EMP_NUMBER and DEP_NUMBER.
It's not necessary to include EMP_NUMBER in that. You can. It doesn't accomplish anything, since group 1 is on EMP_NUMBER, but it doesn't do any harm either.

But the real issue is that groups 2, 3 and 4 are all really on the same field - DEP_NUMBER.

Group 2 might as well be just on DEP_NUMBER (since group 1 is on EMP_NUMBER).
Groups 3 and 4 are both on the same formula (Group: Dependent), which is just
{EMEB_EMP_BENEFITS.DEP_NUMBER} <> 0

So, once again, you're grouping on a conditional formula, so you end up with a True group (where DEP_NUMBER <> 0) and a False group (where DEP_NUMBER = 0). The False group would be first (but see below). The True/False grouping is repeated for group 3 and group 4.

So, your effective grouping is just:
EMP_NUMBER
DEP_NUMBER

The formula used for groups 3 and 4 will produce either True or False, depending on the value in DEP_NUMBER. Either way, you get one group 3 and 4 for each DEP_NUMBER (either it's 0 or it's not), so groups 3 and 4 don't do any grouping at all.

If by "drops data" you mean that the end of some fields is being cut off, you probably just need to make the fields wider, or maybe use a smaller font. A field may be more than long enough when the report is viewed, but be too short when the report is exported to text.

If you want/need more control over the export format (delimiters, spacing, etc.), the surest thing is to create a formula that combines the fields into a single string and use that formula on the report, instead of the original fields. Then you can format the string however you like. But if you're dealing with really long strings, like your "plan" strings, then it may not be that simple.

James

0

GrapeladyAuthor Commented: 2013-02-26

I have finally have straighten out the grouping issue and the report is now reporting as I would like it to. Thank you for making me think about the grouping it helped with what I wanted the report to do.
Now exporting to *.ttx, seems the best option, but at the beginning and end of each line there is the " mark. I can instruct the user to open in Text Pad and run a find/replace to clear the quotes, but just wondering if there is a way to begin the formula string with an ChrW(**) to prevent this.

I haven't really played with the text exports in a while, but I doubt that you can change the format for a ttx export. If CR is adding quotes to a ttx export, there is probably no way to turn that off. I could be wrong, but I don't recall the text exports having any real format options, at least within CR itself.

I don't think a plain text (txt) export will include quotes, so that might be worth another look. OTOH, if the ttx export gives you exactly what you want, except for some extra quotes, and it's not too much trouble to have the user remove them, then maybe that's the way to go.