Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Crystal Reports include two books

Posted on 2013-01-31
11
Medium Priority
?
295 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
11 Comments
 
LVL 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 664 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
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
LVL 101

Accepted Solution

by:
mlmcc earned 672 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 35

Assisted Solution

by:James0628
James0628 earned 664 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 35

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

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

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. …
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

636 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