Crystal Report 10: trying to extract Beneficiary from Abra Suite and show it without any duplication

coronoahcoro
coronoahcoro used Ask the Experts™
on
Hi all. My company just bought Abra Suite program that comes with Crystal Report 10 and we're testing to make some custom reports with CR. I ran into a problem when I tried to make a Beneficiary custom report.

Basically I want to show all the employees that are enrolled either in Life Insurance or 401K and their beneficiary. The problem is only the employees that are enrolled either in Life Insurance or 401K and have beneficiary that show up on the report (We use sample data and there are only 2 that have beneficiary). And that's not what I want. I know the SQL code itself is not correct in order to show what I want. So I wrote my own SQL and add the command to Abra Data Access and get this error code:
     Error in select ... (-- the whole SQL statement -- ) _Execute failed with error 221: Command is missing required clause.

If this is in Microsoft Access, I will use two different queries to generate the result that I want. But I really don't know how to do it with CR. this is really frustrating.

Thanks in advance for the help
SELECT hrpersnl._name2,hdepend._name2
FROM hrpersnl OUTER JOIN hdepend ON hrpersnl.p_empno = hdepend.d_empno
WHERE  hdepend.d_empno= 
	(
	SELECT DISTINCT hrpersnl.p_empno, hbene.b_benecode
 	FROM  hbene INNER JOIN hrpersnl ON hbene.b_empno=hrpersnl.p_empno
 	WHERE  hbene.b_benecode='LIFE-BW01' OR hbene.b_benecode='401K(%)'
	)
	AND hdepend.dtype = 'B'
 
ORDER BY hrpersnl.p_empno

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I don't know anything about Abra and have no idea if this is your problem, but looking at your code, where you have

WHERE  hdepend.d_empno=

 I would use

WHERE  hdepend.d_empno IN


 You could give that a try and see what you get.

 I'm also kind of thinking that the subquery should return just one column, hrpersnl.p_empno, and not hbene.b_benecode, but I'm not entirely about that.  I don't use a lot of subqueries, and it may depend on your db.

 James
>  I don't use a lot of subqueries, and it may depend on your db.

 Technically, I meant that I don't use a lot of subqueries like that one, where you're doing column IN (subquery).  I've mostly used them to get a value for a column and that kind of thing.

 James

Author

Commented:
Well the problem is I can't test the SQL statement since it keeps giving me this error message:
" Error in ( --  the SQL statement -- ) _Execute failed with error 221: Command is missing required clause"

I am not sure how to use the SQL statement in CR. I read if I go to Database expert -> choose the datase -> Add command then insert the SQL statement, it should work, but it did not.

Is it possible to use 2 step queries (or reports) in CR like in Access. I'm thinking to get all the employee # that is enrolled in either Life Insurance or 401K then use the result as a parameter to show all their dependents.

I attached the screenshot of the current report and the code that CR produced. You can see that the dependents show up more than once because of the OR clause. I didn't add the rule to show only the beneficiary because that will only select 2 employees.

Thanks for the response

 SELECT DISTINCT `hdepend`.`_name2`, `hrpersnl`.`p_empno`, `hrpersnl`.`_name2`, `hbene`.`b_benecode`, `hdepend`.`d_bene`
 FROM   `hbene` `hbene` INNER JOIN (`hdepend` `hdepend` INNER JOIN `hrpersnl` `hrpersnl` ON `hdepend`.`d_empno`=`hrpersnl`.`p_empno`) ON `hbene`.`b_empno`=`hrpersnl`.`p_empno`
 WHERE  (`hbene`.`b_benecode`='LIFE-BW01' OR `hbene`.`b_benecode`='401K(%)')
 ORDER BY `hrpersnl`.`p_empno`

Open in new window

EE.JPG
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

Author

Commented:
I was just reading the CR Online Help and it says:
" Some of the Crystal Reports native database drivers do not support the Add Command feature:
DB2 Server...etc"

I forgot to mention that my company is using AS/400 database. Is this why I got the error message? Is there a way to get around this?
Senior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
I don't think that is the issue.  I find that when a data source doesn't support the COMMAND feature it isn't available.

One way to get around this would be to group by the beneficiary and then list them in the group header rather than using the details.

mlmcc

Author

Commented:
mlmcc,

I like your solution, but it still gives me duplicate values. I do not know why it gives me duplicate value when I clearly mention to only include distinct records.

I include a screenshot using your solution.
EE.JPG
As you said, you asked for distinct _records_, meaning at least one field is different from every other record.  You included b_benecode in your record and asked for b_benecode = LIFE-BW01 or 401K(%), so if someone has both of those codes, you'll get one record for each, and they're distinct because b_benecode is different in each record.  Of course there could be other fields that are also different, like d_bene, but b_benecode is the obvious suspect.

As for still getting duplicates with the grouping, you didn't try mlmcc's suggestion.  He suggested that you group by the beneficiary, meaning their name or, preferably, some kind of ID number.  Then each beneficiary would be in a separate group and if you put their name in the group header, you'd only see it once (with details like the different b_benecode values for each beneficiary shown in the detail section below that).

 But the screenshot you posted is not grouped by the beneficiary.  It's grouped by some one character field with the values "B", "D" and "O".  The beneficiary names are still in the detail section, so you see each name repeated once for each record with that name.

 James

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial