Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

Trouble selecting records in crystal reports 9

I am using the following selection in CR9 and it will not return the 3rd selection ( {Sales.Salesman}= {?Emp}) I have tried moving the code so that quote is last and then it will not selects quotes

{Sales.InvDate} in {?date} to {?edate} and {SalesEmp.Emp} = {?Emp} or
{Sales.Salesman} = {?Emp} and uppercase({Sales.InvType}) = "QUOTE" and {Sales.Salesman}= {?Emp}

Thanks
George
0
GeorgeSalet
Asked:
GeorgeSalet
  • 12
  • 9
  • 5
1 Solution
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
(
{Sales.InvDate} in {?date} to {?edate}
and {SalesEmp.Emp} = {?Emp}
)

or

(
{Sales.Salesman} = {?Emp}
and uppercase({Sales.InvType}) = "QUOTE"
)


The parentheses are necessary in order to enforce criteria being evaluated together.

On a side note, your uppercase({Sales.InvType}) won't be passed to the database for processing, since it's basically a runtime formula.  If you are connected to a SQL-based database (SQL Server, Oracle, etc..) then I'd recommend you replace it with a SQL Expression field.  If you list the database you're using, we can help with the syntax.

~Kurt
0
 
mlmccCommented:
How about trying it this way

{Sales.Salesman} = {?Emp}
and
(
   uppercase({Sales.InvType}) = "QUOTE"
   OR
   {Sales.InvDate} in {?date} to {?edate}
)

Can any of the fields be NULL?
If so you have to handle that case by testing for it before you test the field.

mlmcc
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
That was my first thought, too, until I saw he was actually using two different fields against the {?emp} parameter.

~Kurt
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
GeorgeSaletAuthor Commented:
I made those changes but I still can't get the second group to show, here is my code
(({Sales.InvDate} in {?date} to {?edate}) and ({SalesEmp.Emp} = {?Emp})) or
(({Sales.InvDate} in {?date} to {?edate}) and ({Sales.Salesman} = {?Emp}) and ({Sales.InvType} = "Quote"))
Thanks
george
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
You have way too  many parentheses - - you don't need to encapsulate a single filter in parentheses.  This is much easier to read:

(
  {Sales.InvDate} in {?date} to {?edate}
  and {SalesEmp.Emp} = {?Emp}
)

or
(
  {Sales.InvDate} in {?date} to {?edate}
  and {Sales.Salesman} = {?Emp}
  and {Sales.InvType} = "Quote"
)

That being said, try this:

{Sales.InvDate} in {?date} to {?edate}

and

(
{SalesEmp.Emp} = {?Emp}

or
  (
  {Sales.Salesman} = {?Emp}
  and {Sales.InvType} = "Quote"
  )
)

As for the second condition not evaluating, are there any circumstances in which both the {SalesEmp.Emp} and {Sales.Salesman} will ever be the same?  That could cause problems.

~Kurt
0
 
GeorgeSaletAuthor Commented:
Yes in most cases the salesemp.emp and sales.salesman would be the same except in quotes
0
 
GeorgeSaletAuthor Commented:
I have two main tables in this report 1, is the invoice table that contains the salesman number and the 2 is the salesemp table that keeps track of the time spent on the invoice by multiple employees.
When a quote is completed no records are in the salesemp table just a employee number in the sales.salesman field.  I can't do a query on just the sales.salesman field because multiple employees will wrk on the on under salesemp. I am open to other options
Thanks
George
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
It seems like you might benefit from a UNION statement - query each of the two tables independently of each other and append their recordsets to each other.  Do you have any experience with SQL?

Also, what do your table joins look like?

~Kurt
0
 
GeorgeSaletAuthor Commented:
Sales links to salesemp by invoice number.
Not much experience in sql

George
0
 
mlmccCommented:
Missed the different fields also.

Try them separately
First try the first part.  Do youget the records you expect
{Sales.InvDate} in {?date} to {?edate} and  {SalesEmp.Emp} = {?Emp}

Then try the second part.  Do you get the records you expect?
{Sales.InvDate} in {?date} to {?edate} and {Sales.Salesman} = {?Emp} and {Sales.InvType} = "Quote"

mlmcc
0
 
GeorgeSaletAuthor Commented:
Well Something is a miss
I selected the file by period (month) no other qualifiers and every quote invoice is skipped, I am stumped
George

0
 
mlmccCommented:
Do the quote invoices have dates or are they NULL?

mlmcc
0
 
GeorgeSaletAuthor Commented:
The quote dates have both invoice dates and period dates, nothing null in the search perimeters, has to be something basic??
Thanks
George
0
 
mlmccCommented:
You are saying you used

    {Sales.InvDate} in {?date} to {?edate}

and got no sales of type QUOTE?

Did you try just
   {Sales.InvType} = "Quote"

What do you get?
Do the records have {Sales.InvDate} dates in the range?

If we take a step back -
What records are you trying to pull in words.

mlmcc
0
 
GeorgeSaletAuthor Commented:
because I was having trouble getting quotes to show, I used" sales.period =201101" which should show every invoice in the sales table for the month of January. All invoices were shown except quotes. Something else must be conflicting??

George
0
 
mlmccCommented:
Do quote records have a value in sales.period?

mlmcc
0
 
GeorgeSaletAuthor Commented:
I didn't answer your question. I am trying to list all the invoices and quotes for a date range for a specific employee. Quotes only are listed in the sales table and the employee is the sales.salesman field. when the quote becomes a job the employee time is in a table called Salesemp the employee is listed in salesemp.emp.

The propose of the report is to quantify how much a employee brought in, how many quotes, callbacks,warranty and how many hours they got paid for, how many hours worked, traveled, lost etc and of course closing rate.

Sorry about being so long winded
George
0
 
GeorgeSaletAuthor Commented:
Yes I have approximately 6 records, checked it with utility that views tables amd allows you to see all fields in record
0
 
mlmccCommented:
Do quotes have a date in the InvDate field?
If not then do they have a date in some field or do quotes that don't go final just get deleted?

If I understand this, the SalesEmprecord isn't built until the quote changes to a job.  If that is the case the SalesEmp.Emp is NULL for quotes.

Try
{Sales.InvDate} in {?date} to {?edate}

and
(
    (
    Not IsNull({SalesEmp.Emp})
    AND
    {SalesEmp.Emp} = {?Emp}
    )
    or
    (
    {Sales.Salesman} = {?Emp}
    and
    {Sales.InvType} = "Quote"
    )
)

mlmcc
0
 
GeorgeSaletAuthor Commented:
I have started a new report to find out what is happening, when I add anything from "salesemp" table it removes the quotes from the report (a quote does not have a record in "salesemp")

I have tried this and it does not work but I think I am on the right track
if isnull({SalesEmp.Credit})then 0 else {SalesEmp.Credit}

Thanks
George
0
 
mlmccCommented:
How are the tables joined?

You need to use a LEFT OUTER JOIN

mlmcc
0
 
GeorgeSaletAuthor Commented:
I looked up left outer join in the manual but it doesn't explain how to do it??  I think we are getting to the bottom of this.
ALL your help is appreciated
George
0
 
mlmccCommented:
Are you joining tables in Crystal?
If so
Open the report
Click DATABASE --> DATABASE EXPERT
Click the LINK tab
Right click the join between the tables
CLick CHANGE JOIN
Choose LEFT OUTER

mlmcc
0
 
GeorgeSaletAuthor Commented:
Thanks For All the help
George
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
Glad you got it working! Good catch on the joins, mlmcc!

~Kurt
0
 
mlmccCommented:
Finally got the right clues.

mlmcc
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 12
  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now