Solved

Trouble selecting records in crystal reports 9

Posted on 2011-02-19
26
423 Views
Last Modified: 2012-05-11
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
Comment
Question by:GeorgeSalet
  • 12
  • 9
  • 5
26 Comments
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34934243
(
{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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34934297
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
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34934355
That was my first thought, too, until I saw he was actually using two different fields against the {?emp} parameter.

~Kurt
0
 

Author Comment

by:GeorgeSalet
ID: 34934489
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
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34934534
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
 

Author Comment

by:GeorgeSalet
ID: 34934557
Yes in most cases the salesemp.emp and sales.salesman would be the same except in quotes
0
 

Author Comment

by:GeorgeSalet
ID: 34934621
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
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34934712
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
 

Author Comment

by:GeorgeSalet
ID: 34934789
Sales links to salesemp by invoice number.
Not much experience in sql

George
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34934914
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
 

Author Comment

by:GeorgeSalet
ID: 34935140
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34935744
Do the quote invoices have dates or are they NULL?

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 34935939
The quote dates have both invoice dates and period dates, nothing null in the search perimeters, has to be something basic??
Thanks
George
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 100

Expert Comment

by:mlmcc
ID: 34937806
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
 

Author Comment

by:GeorgeSalet
ID: 34937909
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34937961
Do quote records have a value in sales.period?

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 34937992
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
 

Author Comment

by:GeorgeSalet
ID: 34938001
Yes I have approximately 6 records, checked it with utility that views tables amd allows you to see all fields in record
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34938234
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
 

Author Comment

by:GeorgeSalet
ID: 34945201
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
 
LVL 100

Expert Comment

by:mlmcc
ID: 34945342
How are the tables joined?

You need to use a LEFT OUTER JOIN

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 34945562
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
 
LVL 100

Accepted Solution

by:
mlmcc earned 500 total points
ID: 34946333
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
 

Author Closing Comment

by:GeorgeSalet
ID: 34946663
Thanks For All the help
George
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 34947593
Glad you got it working! Good catch on the joins, mlmcc!

~Kurt
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34947704
Finally got the right clues.

mlmcc
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

910 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

20 Experts available now in Live!

Get 1:1 Help Now