Solved

Crystal XI selection criteria for last record based upon date.

Posted on 2014-03-19
6
377 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
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 100

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
Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

 
LVL 34

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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Suggested Solutions

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…
The way I use Experts Exchange to assist me in analyzing and diagnosing a problem is I first enter a Verbose Question at Experts Exchange like: Office 2007 will hang when opening and saving files I then launch WordPad (any text editor will do) an…
Windows 8 comes with a dramatically different user interface known as Metro. Notably missing from the new interface is a Start button and Start Menu. Many users do not like it, much preferring the interface of earlier versions — Windows 7, Windows X…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

777 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