Solved

Crystal Reports include two books

Posted on 2013-01-31
11
285 Views
Last Modified: 2013-09-03
Why can't I do this to look for people who should be under 2 books.  "CARDIOPW"  is one book and I want to see if anyone else is in the other books.  I did test so I know I should get at least a test person but I get nothing.

{BOOK.ABBREV} = "DIOPW" and
{BOOK.ABBREV} in ["DIO", "DIOH", "DIOMI", "DIOR", "DIOSM", "DIOWR", "DIOWS"] and
{APPT.DATE} in DateTime (2012, 01, 01, 00, 00, 00) to DateTime (2012, 04, 01, 00, 00, 00)
0
Comment
Question by:trinisunset
  • 3
  • 2
  • 2
  • +1
11 Comments
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 166 total points
ID: 38844603
it can't be a AND because the abbrev can't be DIOPW and something else at the same time!

can't you include DIOPW inside the IN clause?

or maybe something like (depending on how the date should be treated):
{BOOK.ABBREV} = "DIOPW" OR
({BOOK.ABBREV} in ["DIO", "DIOH", "DIOMI", "DIOR", "DIOSM", "DIOWR", "DIOWS"] and
{APPT.DATE} in DateTime (2012, 01, 01, 00, 00, 00) to DateTime (2012, 04, 01, 00, 00, 00) )
0
 

Author Comment

by:trinisunset
ID: 38845155
The people have to be in the DIOPW book so don't think that the "OR" would work or including it in the "IN" list.  
They have to belong to the mandatory book of DIOPW and I would like to find out if any of them also belong to this list ["DIO", "DIOH", "DIOMI", "DIOR", "DIOSM", "DIOWR", "DIOWS"]
 
I added in a test person so I know, at least, I should pull that test person up but I don't get anything.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 38845165
I don't get it. Somebody can't be it 2 list at the same time (and)! It has to be DIOPW OR something else!

can you show sample data and the expected results?
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
LVL 100

Accepted Solution

by:
mlmcc earned 168 total points
ID: 38845904
Try this

You will have to build a view in the database or use a Crystal COMMAND for the source

SELECT FIELDS YOU NEED
FROM
BOOK  INNER JOIN APPT ON appropriate join
WHERE Book.Name  IN (SELECT Book1.Name FROM Book Book1 WHERE {BOOK.ABBREV} = "DIOPW" )
AND
({BOOK.ABBREV} in ["DIO", "DIOH", "DIOMI", "DIOR", "DIOSM", "DIOWR", "DIOWS"]
{APPT.DATE} in DateTime (2012, 01, 01, 00, 00, 00) to DateTime (2012, 04, 01, 00, 00, 00) )

mlmcc
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 166 total points
ID: 38846533
I'm guessing that the 2 books that you're talking about are in 2 different records.  For example, you've got one record in BOOK where ABBREV is "DIOPW", and a second record in BOOK where ABBREV is "DIO".

 If so, then, as emoreau said, a record selection simply isn't going to work, because it's only seeing one record at a time, so ABBREV can only have one value at a time.

 You could use a query like the one that mlmcc posted (that would be the new datasource for your report).  It uses a sub-query to get a list of the DIOPW books, and then checks to see if the current person is in that list.

 You could probably also do this in a report, in one way or another, but doing it in the db would probably be much more efficient.


 Also, FWIW, I'm a little confused by your first post.  You said ' "CARDIOPW"  is one book and I want to see if anyone else is in the other books', but then you have {BOOK.ABBREV} = "DIOPW".

 Are you looking for "CARDIOPW" or "DIOPW"?

 If "DIOPW" is supposed to match "CARDIOPW", you would need to use Like, instead of = (eg. {BOOK.ABBREV} like "*DIOPW").

 James

 PS: mlmcc, I guess this should be moved to the CR zone?
0
 
LVL 34

Expert Comment

by:James0628
ID: 38847291
I got a "neglected question" email.  I almost missed the zone too.

 James
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 38847310
can you show sample data and the expected results?
0
 

Author Comment

by:trinisunset
ID: 38849942
James, Sorry "CARDIOPW" is another name for  "DIOPW" sorry for the mix up.  I will try what mlmcc suggested and let you know if it works.  Thank you for the suggestions.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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.

Question has a verified solution.

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

Suggested Solutions

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 …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

828 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