Solved

Crystal Reports Record/Group Selection

Posted on 2010-11-19
4
1,469 Views
Last Modified: 2012-05-10
I'm in need of some help with Crystal Reports (Ver. 2008). I have two tables...Company, and Notes. Company has a one to many relationship with Notes and each note has a "created date." An example view would look like this...

ABC, Inc.
     Note 1, 11/14/2010
     Note 2, 11/12/2010
     Note 3, 10/30/2010
XYZ, Inc.
     Note 1, 11/02/2010
     Note 2, 10/01/2010
     Note 3, 09/15/2010

I want to print only those Companies that have not had a Note added in the last ten days. Using the example above, ABC, Inc should not appear in the report...but XYZ, Inc should. Here are the steps I've taken so far...

1. Joined the tables
2. Created a report group that groups by Company.Comp_Name.
3. Created an "AsOfDate" Parameter field (that defaults to "today").
4. Created a "CompareDates" Formula Field that says...

whilereadingrecords
if {Notes.Note_CreatedDate} >= ({?AsOfDate}-10) then
    formula=False
else    
    formula=True
end if

5. Created a Group Selection formula that says "{@CompareDates} = True."

In a sense, this is working...but not the way I need it to work. Again using the above example, instead of omitting ABC, Inc from the report because one (or more) "Note" records do not meet the criteria, it includes it because "Note 3" does meet the selection criteria.

How can I get this report to omit "Company" records that have any "Note" records that do not meet the criteria?

Thanks for your help.
0
Comment
Question by:Bad_Fish
[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
  • 2
4 Comments
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 34178218
Try this idea

Don't use the group selection but use conditional suppression

in the group header
Right click the left margin
Click SECTION EXPERT
Click the formula button to the right of SUPPRESS
WhilePrintingRecords;
Global BooleanVar SuppressSection;
SuppressSection := {Notes.Note_CreatedDate} < ({?AsOfDate}-10)
SuppressSection

For the details and group footer use this formula for suppression
WhilePrintingRecords;
Global BooleanVar SuppressSection;
SuppressSection

mlmcc
0
 
LVL 35

Accepted Solution

by:
James0628 earned 250 total points
ID: 34179181
I think the basic problem is that you're trying to do a group selection, but the formula is looking at the value in a single record, so the result will probably be based on the first or last record in the group (based on your results, I guess it's the last record in the group).

 Try this for the group selection formula:

Maximum ({Notes.Note_CreatedDate}, {Company.Comp_Name}) < ({?AsOfDate}-10)

 That says to select the groups where the latest date for the group is more than 10 days before {?AsOfDate}.

 James
0
 

Author Comment

by:Bad_Fish
ID: 34201897
Mimcc, thank you. Your statement works fine in doing what I needed. However, without going into too much further detail, James0628's solution produced a "cleaner" result (having to do with suppressing the group footer when the header is suppressed). For the sake of brevity I won't elaborate here, but If you're interested, I'd be happy to host a remotes session to show you why.

James0628, thank you. Your statement did the trick nicely...and I learned a little too.

Thanks again to both of you.
0
 
LVL 35

Expert Comment

by:James0628
ID: 34203068
You're welcome.  Glad I could help.

 James
0

Featured Post

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
display n/a in column 2 45
Null as value for Dynamic parameter in CR2008? 9 67
Timezone help 5 45
How Parameters Work 2 32
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…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

739 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