Solved

Crystal Reports include two books

Posted on 2013-01-31
11
281 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

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…
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 Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

832 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