How to find transactions that have NOT occurred 3

[SteveBell]

The database table looks like this:

Shipping
{Item#} {Date}

How do I show every item whose latest shipping date is more than 90 days ago? Our DB guy said he'd do it in SQL with a HAVING clause.

We're using Crystal Reports v8.0, and the report will be published in Crystal Enterprise. The data is stored in a Baan ERP database, so we can't use SQL Designer (Crystal Enterprise for Baan hooks CR directly to the Baan interface).

Thanks in advance.

 

[kenhamady]

Are you trying to include items that never shipped at all, or just those whose minimum EXISTING dates are back 90 days. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[SteveBell]

In this case they're the same thing. The table has at least one record for every part the company sells, and they've all shipped at one time or another.

I already grouped on the item number, put the maximum date in the footer, and suppressed the details. That gets me 90% of the way, but I can't figure out how to hide group footers with a maximum date prior to CurrentDate-90.

Thanks for your help.

 

[dgillz]

Click on the date field in question and click insert summary. It should default to item number, and make it a maximum summary. This will show the last date shipped in your group footer.

Then go to report-edit selection formula-group and enter the formula: maximum({datefield},{itemnumber})<Dateadd(&quot;d&quot;,Currentdate,-90)

This will totally eliminate all parts shipped within the last 90 days from your report.
Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[kenhamady]

As long as every item has at least one shipped record, Dgilz is correct, except there is an error in his formula. It should be:

maximum({datefield},{itemnumber})<Dateadd(&quot;d&quot;,-90,Currentdate)

Also make sure that any grand totals are running totals, or they will include the products that aren't selected. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[SteveBell]

You guys are great! I've learned two new parts of Crystal today, and the report works just the way you said.

There's only one problem now -- when I hide the details, my summary field in the group footer (maximum of date) changes from &quot;01-August-2000&quot; to &quot;01-January-4294962583&quot;. Switching between hiding and suppressing makes no difference, nor does changing the date format, nor turning the group selection formula on and off.

Any ideas?

 

[dgillz]

Is this happening everywhere or just on certain items?

You could have some bad data in your database. Can you look at the raw data without using crystal and see if there is some wierd data in there? Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[SteveBell]

The raw data is OK. It displays correctly in the detail section (when that section is not suppressed). The summary in the group footer properly displays the latest date in the footer (for every group) as long as the details section is not suppressed. As soon as details are suppressed, every summary field shows &quot;01-January-4294962583&quot;.

 

[kenhamady]

Weird, is the field in the GH a summary field or a formula? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[dgillz]

See if the database field is a date/time field. If so convert it to a date with a formula -- Date({DTfield}) -- and then group on the formula and see if that works. Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[SteveBell]

OK, I'm gonna quit messing around. Here are the pertinent parts of the report definition:

Crystal Report Professional v8.0 (32-bit) - Report Definition

1.0 File Information
Report File: Version: 8.0

4.2 Group Selection Formula
Maximum ({tdssc007.ddat}, {tdssc007.item}) < DateAdd (&quot;d&quot;, -90, CurrentDate)

5.5 Group Header Section
Visible
GroupName ({tdssc007.item})
String, Visible, Default Alignment, Top Alignment, Keep Together, Using System Default Formatting, Word Wrap

5.6 Group Footer Section
Visible
Maximum ({tdssc007.ddat}, {tdssc007.item})
Date, Visible, Default Alignment, Top Alignment, Keep Together, Windows Default Type: Not Using Windows Defaults, Date Order: Day Month Year, Year Type: Long, Month Type: Long Month Name, Day Type: Leading Zero Numeric Day, Leading Day Type: None, First Separator: '-', Second Separator: '-', Leading Day Separator: ''

GroupName ({tdssc007.item})
String, Visible, Default Alignment, Top Alignment, Keep Together, Using System Default Formatting, Word Wrap

I don't see anything here that points to a standard problem. Have I uncovered a bug? I'm using version 8.0.1.0 / 8.0.0.441 if it helps.

 

[dgillz]

Is {tdssc007.ddat} a date field or a date time field?

I think a bug is a distinct possibility Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[kenhamady]

I think I have it. You probably have the option checked to &quot;Perform Grouping on Server&quot;. This only takes effect on summary reports, and changes the SQL to eliminate the detail fields.


If I am wrong, here are things I would try as workarounds:

1) Write a formula field that is just the date field, and then create a summary (MAX) of the formula.

2) Write a formula field that is:
Maximum ({tdssc007.ddat}, {tdssc007.item})

3) Create a SQL expression that is just the date field, and then create a summary (MAX) of that SQL expression. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[SteveBell]

Turning off &quot;Perform Grouping on Server&quot; did the trick.

dgillz: {tdssc007.ddat} is a date field

Is this &quot;functionality&quot; by design? Should I submit a bug report to Seagate?

SQL query with server grouping:
SELECT
tdssc007.cuno,
tdssc007.item
FROM
tdssc007
ORDER BY
tdssc007.cuno ASC,
tdssc007.item ASC

SQL query without server grouping:
SELECT
tdssc007.cuno,
tdssc007.item,
tdssc007.ddat
FROM
tdssc007
ORDER BY
tdssc007.cuno ASC,
tdssc007.item ASC,
tdssc007.ddat ASC

 

[dgillz]

Good one Ken!

I've always understood the &quot;perform grouping on server&quot; is for reports without details sections displayed -- to display details defeats the purpose of this option. However I do think this is a bug. Your thoughts? Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[SteveBell]

In this case, the Crystal doesn't seem to perform any differently whether server grouping is on or off--other than the error, that is. Both settings read the same 217,000 records before finishing.

Speculation alert:
This might be a result of using the Baan interface. It might not be able to pass grouping information and SQL statements through efficiently. CR talks to the Baan DLL which talks to the Baan interface/data dictionary on the Unix server which talks to the underlying Oracle database. With a path that long, there's a lot of room for trouble.

 

[kenhamady]

The feature is supposed to return only one record per group, but that record should have included the MAX value, so you could call this a bug. The BAAN interface could be part of the problem behavior, especially if you are reading the same number of records into CR's memory either way. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com

 

[SteveBell]

I just faxed off a problem report to Seagate; we'll see what they say. I'll try to report back here.

They might blow me off because my 60 days of free support expired long ago.

 

[SteveBell]

Not only did Seagate blow me off, they said they don't even support the support-by-fax option any more. I'm not going to spend money to let them know they have a problem, so that's the end of the line.

Thanks to everyone who supported me in solving this problem.