Solved

Crystal XI selection criteria for last record based upon date.

Posted on 2014-03-19
6
382 Views
Last Modified: 2014-04-09
This record selection formula has me stumped. I want to report on active customers that have had no quote activity in the last 6 months from the current month.

CustCode.Active = Y is the easy part

Quote numbers are sequentially assigned regardless of customer... ideally I would like to list the last 3 quotes for each customer.

Two Tables: CustCode and Quote
Join Fields:  CustCode.CustCode = Quote.CustCode and CusCodec.SalesId = Quote.SalesId

Exclude all customer quotes that have Quote.DateEnt or Quote.DateMod within 6 months of the current month.

If the last Quote.QuoteNo is 6 months or older from current month based on Quote.DateEnt or Quote.DateMod

Include the last 3 records consisting of: CustCode.Active,  CustCode.SalesID, CustCode.CustName, Quote.QuoteNo, Quote.DateEnt, Quote.DateMod
0
Comment
Question by:Lee Ingalls
[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
6 Comments
 
LVL 4

Expert Comment

by:Kent Fichtner
ID: 39940780
This should stop anything that is 6 months or newer.

DateDiff(M,Quote.DateEnt,CurrentDate) <= 6 OR DateDiff(M,Quote.DateMod ,CurrentDate) <= 6;

as for printing out the three records you will probably have to do a formula loop.  

Formula reset in the group header

formula to count to three in the details, then have a suppression on the details that checks if the formula is equal to three.
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 200 total points
ID: 39941229
That doesn't work because you need to also check if they had a quote in the last 6 months.

What database are you using?

You will have to do this with a stored procedure or view in the database or you can use a Crystal command assuming your data source suports them.

Here is a start on the command

SELECT CustCode.Active,  CustCode.SalesID, CustCode.CustName, Quote.QuoteNo, Quote.DateEnt, Quote.DateMod 
FROM CustCode LEFT OUTER JOIN Quote ON  CustCode.CustID = Quote.CustId
WHERE
CustCode.Active = Y
AND
Not (CustCode.CustName IN (SELECT CustCode.CustName FROM CustCode INNER JOIN Quote ON  CustCode.CustID = Quote.CustId 
AND ((Quote.DateEnt >= CurrentDate - 182) OR (Quote.DateMod >= CurrentDate - 182))))

Open in new window

0
 
LVL 8

Author Comment

by:Lee Ingalls
ID: 39941418
The database is SQL Server 2008.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 35

Accepted Solution

by:
James0628 earned 300 total points
ID: 39941855
FWIW, I think this could be done with a report that was just reading the tables, but it would probably be much more efficient to use a special query, as mlmcc suggested.  A report reading the tables directly would probably have to read all of the quotes, going back to the beginning of time, unless there's some rule that could narrow things down, like active customers will always have a quote within the past 2 years.

 FWIW2, I think mlmcc's query could be simplified a bit.  I don't think you need the CustCode table in the sub-query.  You could just read Quote and produce a list of Quote.CustCode values for the quotes that meet your date criteria.

 However, there may be a complication.  If you need both Quote.CustCode and Quote.SalesId to accurately connect the Quote and CustCode tables, then you may need to change the query.  Off the top of my head, I'm not sure how best to handle that.  Two sub-queries, one for Quote.CustCode and one for Quote.SalesId seems inefficient (assuming that it would even work).

 James
0
 
LVL 8

Assisted Solution

by:Lee Ingalls
Lee Ingalls earned 0 total points
ID: 39950465
Working  on a query based on mlmcc's stored procedure suggestion with the input from James0628 as well. Hope to have it resolved today.
0
 
LVL 8

Author Closing Comment

by:Lee Ingalls
ID: 39988229
Using a view and directly from the report were both successful. I re-purposed mlmcc's command and used james0628's suggestion of narrowing the scope of records and NOT needing both the CustCode and the SalesID... CustCode was sufficient. You guys rock!!
0

Featured Post

Ready to get started with anonymous questions?

It's easy! Check out this step-by-step guide for asking an anonymous question on Experts Exchange.

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. …
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 video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …

622 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