Solved

Trouble selecting records in crystal reports 9

Posted on 2011-02-19
26
422 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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

759 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

19 Experts available now in Live!

Get 1:1 Help Now