Need to only show companies if within a specific date range 1

[chonchos]

I have an issue that I believe I may be over thinking. It seems like it should be so simple.

Here's the deal.

We have a filing cabinet of customer files that is packed to the brim. We want to thin it out a bit. So I wanted to utilize Crystal Reports to determine which accounts are no longer active so I could pull their folders.

So in my report I have the Company Names and the Sales Order dates. I want the report to show any company that hasn't had a sale in the past 5 years. I also want it to filter out any companies that ordered before 2006 (we have companies in the database all the way back to the 90's but didn't start keeping hard files in the cabinet until 2006).

So basically, I don't want any company names to show if they ordered before 2006. And I don't want any companies to show if they ordered after 2008.

It seems so easy but I just cant get it. I've googled and submitted to other forums and so far I've come up with nothing solid.

I was given this formula in another forum (a variation I had to clean up) but it's not been helpful so far (in select expert or suppress details). The user was taking a stab in the dark and couldn't help me further with it, maybe you can?

"MAXIMUM ({SO_DETAIL.ENTRY_DATE}) < DATETIME (2008, 04, 30, 00, 00, 00)
AND MINIMUM ({SO_DETAIL.ENTRY_DATE}) >= DATETIME (2006, 01, 01, 00, 00, 00)"

If this description of my issue isn't clear enough I'll happily provide more information or attempt at clarifying further.

Thank you in advance for your help.

 

[CoSpringsGuy]

If the DB you are pulling data from holds multiple order dates for clients ....

Create a group on your {clientfield}

from menu select Report-Selection Formula-Group and enter this formula

MAXIMUM ({SO_DETAIL.ENTRY_DATE}, {clientfield}) < DATETIME (2008, 04, 30, 00, 00, 00) 
AND MINIMUM ({SO_DETAIL.ENTRY_DATE}, {clientfield}) >= DATETIME (2006, 01, 01, 00, 00, 00)

From Menu bar select Report-Section Expert-details-hide

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[chonchos]

Thanks for the help CoSprings... I had a feeling this was going to be the golden ticket.

Unfortunately, it didn't work to filter out the companies that placed orders outside of 06-08. It just shows orders/companies placed within those dates, even if they ordered last year or what have you.

I was hoping over the weekend I'd get a sudden "AHA!" moment on this dilemma... but no such luck.

 

[CoSpringsGuy]

Trying to see a reason that this method wouldn't work. I tested it on my own database with a table of client names and multiple date entries and it worked fine.

Tell more about your database structure. Maybe that will spark some ideas

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[chonchos]

My database structure?

I apologize for being so daft or "newbish" but I'm not sure what you're asking.

Do you want to know the database type? Or how the tables are structured and what the linking is like?

I'm not extremely technical and have been teaching myself Crystal over the course of the last year or so by "jumping in" so please be patient with me!

 

[CoSpringsGuy]

click this
Link

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[CoSpringsGuy]

try this one.. if interested
Link

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[chonchos]

Just in case someone else comes along and is looking for an answer in this post to a similar issue... I wanted to comment and say that the original formula COSpringsGuy sent ended up working correctly.

The problem was, I had old Selection Criteria set in Select Expert.

Here is the code again... and learn from my mistake... start with a clean slate!

 MAXIMUM ({saledate}, {clientfield}) < DATETIME (2008, 04, 30, 00, 00, 00) AND MINIMUM ({saledate}, {clientfield}) >= DATETIME (2006, 01, 01, 00, 00, 00)