Solved

Multiple benefits on formula

Posted on 2013-01-21
18
412 Views
Last Modified: 2013-02-27
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.rpt
COBRA-report-issues.docx
0
Comment
Question by:Grapelady
  • 7
  • 6
  • 3
18 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 38803850
Check the change I made to the QB Classificatiion formula.
The tests don't match the selection filter

mlmcc
COBRA-Annette-New.rpt
0
 

Author Comment

by:Grapelady
ID: 38806268
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.

[QB Plan],Legal Sea Foods LLC 18555,Corporate,Delta Dental Basic,EE,
[QB Plan],Legal Sea Foods LLC 18555,Corporate, Cigna Medical Basic,EE,
[QB Plan],Legal Sea Foods LLC 18555,Corporate, Vision Basic,EE,

Do I need to make multiple formulas in order for this to occur?  Or is there a way to setup the While reading or While printing to make this happen?
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 25 total points
ID: 38808172
Here is another update

You have an ELSE issue.  You have code like this


If A=B then
   If C=D then
        1
   Else If A=C then
        If C=E then  
             2

However the IF A=C is really the else for A=B.  You need to use ( ) to define which IF the ELSE belongs to.

If A=B then
(
   If C=D then
        1
)
Else If A=C then
(
   If C=E then  
        2
)        

I also added some troubleshooting so you don't get just a blank after it

mlmcc
COBRA-Annette-Newrev1.rpt
0
 

Author Comment

by:Grapelady
ID: 38845670
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
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 38853621
Can you highlight the problem formula?

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 38854240
I'm not sure what you're trying to do, but I think there are some problems with your groups.

 You have groups on {@Group 1: Employee}, {@Group 2: Employee Event}, {@Group 4: QB Dependent Plan 1 Den}, {@Group 5: QB Dependent Plan 2 Vis} and {@Group 6: QB Dependent Plan 3 Med}.

 {@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.

 James
0
 

Author Comment

by:Grapelady
ID: 38857885
I have revised the report to now have proper grouping and a word document on the what I am trying to do.
COBRA-Annette-New-02013.rpt
QB-Classification.docx
0
 
LVL 34

Expert Comment

by:James0628
ID: 38858831
I still don't see any need for two groups on the employee.  <shrug>

 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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

Author Comment

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

Author Comment

by:Grapelady
ID: 38928210
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
0
 
LVL 34

Accepted Solution

by:
James0628 earned 475 total points
ID: 38929058
> 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.

 Here's the grouping from the beginning of your sample report:

G1 - 11032
G2 -   11032-0  (DEP_NUMBER = 0)
G3 -     False
G4 -       False

G2 -   11032-1  (DEP_NUMBER = 1)
G3 -     True
G4 -       True

G2 -   11032-2  (DEP_NUMBER = 2)
G3 -     True
G4 -       True

G2 -   11032-3  (DEP_NUMBER = 3)
G3 -     True
G4 -       True

G2 -   11032-4  (DEP_NUMBER = 4)
G3 -     True
G4 -       True

G1 - 20664
G2 -   20664-0  (DEP_NUMBER = 0)
G3 -     False
G4 -       False


 So, how does that compare to what you're really trying to do?

 James
0
 
LVL 34

Expert Comment

by:James0628
ID: 38929215
Oh.  Forgot about the export part.

 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
 

Author Comment

by:Grapelady
ID: 38931347
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.
0
 
LVL 34

Expert Comment

by:James0628
ID: 38931902
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.

 James
0
 

Author Closing Comment

by:Grapelady
ID: 38932269
Thank you again for your guidance.
0
 
LVL 34

Expert Comment

by:James0628
ID: 38933412
You're welcome.  Glad I could help.

 James
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now