• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • 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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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