Report Design: Select records question

Print Page | Close Window

Select records question

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=4982
Printed Date: 03 May 2024 at 8:06am

Topic: Select records question

Posted By: bshfdan
Subject: Select records question
Date Posted: 11 Dec 2008 at 6:37am

I need a report to show all of our employees and if they have a certain deduction code to show that deduction otherwise show a blank field. I have two linked tables, one with the emp info and one with the deduction info. If I use selection to select dedcution code 2015 I only get emps who have the deduction. Here is a sampe of my data>

File 1 (employee)

Emp # Name

123 Joe Smith

124 Jane Smith

File 2 (deduction)

Emp # Deduction Amt

123 2015 15

I would like the report to look like this:

123 Joe Smith 2015 15

124 Jane Smith

Any help would be greatly appreciated.

Replies:

Posted By: rahulwalawalkar
Date Posted: 11 Dec 2008 at 6:58am

Hi,

Yes you are correct ,if you select deduction code 2015 you will get only 2015 i.e. what you are filtering in your report,but if you want to include nulls also then you need to check for one more condition

isnull({Table.deductioncode) or {Table.deductioncode} = 2015

Cheers

Rahul

Posted By: lockwelle
Date Posted: 11 Dec 2008 at 7:03am

I saw this for another post, you need to change the link to an outer join.

In the Database Expert/Links tab arrange your tables so that the employee table is on the left and the deduction table is on the left. The you want to right click on the link and select Linking Options. Select the option Left Outer Join...All of the employees will be included in the report, but only the deductions that match the employees will be displayed. Employees that have no deductions will be still be displayed.

It sounds like this is what you are after.

Hope it helps.

Posted By: bshfdan
Date Posted: 11 Dec 2008 at 7:09am

That would work only if there were only 2015 deductions on the deduction table. I should have clarified that there are many different deductions on the deduction table for each employee but I only want to show 2015 on the report. Thanks for your help.

Posted By: lockwelle
Date Posted: 11 Dec 2008 at 7:12am

Then the next step, I would think, is to conditionally suppress the deduction and amount fields if the deduction code is not 2015...at least that is what I would do.

Posted By: rahulwalawalkar
Date Posted: 11 Dec 2008 at 7:18am

if you need to show only 2015 and null deductions then the below code should work..... why you need to reterive all the records and then suppress.......

What happens when you use the code are you getting incorrect results

isnull({Table.deductioncode) or {Table.deductioncode} = 2015

cheers

rahul

Posted By: bshfdan
Date Posted: 11 Dec 2008 at 7:34am

That works but I get two records for each emp if they have the deduction. One with the deduction and one with the null reocrd. I should have clarified that the employee can have many different deductions of the deduction table. Thank you for your help.

Posted By: lockwelle
Date Posted: 11 Dec 2008 at 7:40am

I'm sure that Rahul will respond, but I would create a group by the person then suppress the group header, and details and show the summary for the person. You could sum the deduction code and amount and supress the 0 amounts, this should give the results you are after.

At least that is what I would do, and you did mention the multiple deductions, I just missed it....A report can take so many iterations to get right.

Hope it helps.

Posted By: rahulwalawalkar
Date Posted: 11 Dec 2008 at 7:48am

Thanks http://www.crystalreportsbook.com/Forum/member_profile.asp?PF=3327&FID=5 - lockwelle

http://www.crystalreportsbook.com/Forum/member_profile.asp?PF=6086&FID=5 - bshfdan Can you post the sample data which gives us clear picture i.e. current results and expected results which will help to resolve....

cheers

rahul

Print Page | Close Window