Hi, everyone! We’re using SQL S

[fuzzyocelot]

Hi, everyone!

We’re using SQL Server 2000 and Crystal Reports 9.

‘Faxes’ Table:
Handle DateTime Initiated DateTime Sent Difference (in seconds)*
1 2003-09-03 8:02:39 2003-09-03 8:02:50 11
2 2003-09-03 8:47:44 2003-09-03 8:47:45 1
3 2003-09-03 9:05:14 2003-09-03 9:05:29 15
4 2003-09-03 9:31:07 2003-09-03 9:31:11 4

* Note: in the table above, the "difference" field is actually calculated by a SQL expression (%chkDateDiff)within Crystal (see below). It's not in the physical table. I put it in the table above to try and simplify this post.

The data is displayed in a line chart, which is in the Report Header b section. The Report Title is in Report Header a. The chart’s y-axis shows the seconds and the x-axis shows the groupings, such as “09/03/2003 8:00 am”.

Group Header (which is suppressed):
09/03/2003 8:00 am
09/03/2003 9:00 am

The field in the group footer below is a formula which is listed further down the page (@avgDelay).
Group Footer (average seconds at 8:00 am and 9:00 am, respectively; this section is also suppressed):
6.00
9.50

SQL Expression used:
[tt]%chkDateDiff:
{fn IFNULL(DATEDIFF(Second, "Faxes"."DateTimeInitiated", "Faxes"."DateTimeSent&quot, 0)}[/tt]

The expression above should determine the difference (in seconds) between the two date time fields. Also if the result is null, change it to zero.

Formulas used:
[tt]@DTCon:
dateVar conDate := CDate ({Faxes.DateTimeSent});
timeVar ConTime := CTime ({Faxes.DateTimeSent});
CDateTime (conDate,conTime)

@avgDelay:
Average ({%chkDateDiff},{@DTCon},"By Hour&quot

Record Selection formula:
{%chkDateDiff} >= 0 [/tt]

The database returns over 25,000 records, which is correct. The report looks great. How can I have the database return fewer records? I'd like to use the SQL expressions more, but I can't seem to figure out how to code the right expressions. We’ve decided to not use a SQL command because it hard codes the data source in it. So if the dsn changes (which happens too often for my taste), we’d have to rewrite the command. Feel free to tell me if I'm wrong.

Any ideas would be much appreciated.

Thanks!

 

[fuzzyocelot]

Sorry about the subject. I meant to type in "Database Needs to Return Less Records - Use SQL Expressions?"

 

[synapsevampire]

Fuzzy request there, Ocelot

What does return fewer records mean?

Add whatever criteria is required to the record selection formula to get fewer rows, or group on something (or groupo within the graph) and use an average aggregate function to use fewer rows.

Your requirements should define everything, not the amount of data defining your requirements.

Or just add in a criteria to only use rows whose recordnumber is a prime number...

-k

 

[fuzzyocelot]

Thanks

What I meant by "return fewer records" is that we want the database to return less than 25,000 records to Crystal Reports. We're having some issues in Crystal Enterprise 9. If a report contains more than 20,000 records, then it times out when you try to view it in CE. The administrator doesn't want to change the settings (don't really know why) and has asked me to change the report to contain less records. Does that make sense?

 

[fuzzyocelot]

I'm currently grouping on a formula, @DTCon, which is also used in the chart as "on change of".
I've tried to use the average aggregate, but I can't get it to show up in the record selection. It's complicated. =)

My current record selection:
{%chkDateDiff} >= 0 and
{Faxes.FaxStatus} in [6, 9] and
{Faxes.FaxDateTimeSent} >= {@StartDate} and
{Faxes.FaxDateTimeSent} <= {@EndDate} and
not {Faxes.Received}

SQL Expression:
%chkDateDiff:
{fn IFNULL(DATEDIFF(Second, &quot;Faxes&quot;.&quot;DateTimeInitiated&quot;, &quot;Faxes&quot;.&quot;DateTimeSent&quot;), 0)}

Formula:
@DTCon:
dateVar conDate :=  CDate ({Faxes.DateTimeSent});
timeVar ConTime := CTime ({Faxes.DateTimeSent});
CDateTime (conDate,conTime)

@EndDate:
stringVar Dayname := WeekdayName (DayOfWeek (CurrentDate));
select Dayname 
    case  &quot;Monday&quot;    :  CurrentDate - 5
    case  &quot;Tuesday&quot;   :  CurrentDate - 6
    case  &quot;Wednesday&quot; :  CurrentDate - 7
    case  &quot;Thursday&quot;  :  CurrentDate - 1
    case  &quot;Friday&quot;    :  CurrentDate - 2
    case  &quot;Saturday&quot;  :  CurrentDate - 3
    case  &quot;Sunday&quot;    :  CurrentDate - 4

@StartDate:
{@EndDate} - 6

I've tried using average, count and sum (and different variations of) in the sql expressions, but it won't let me do it. It gives me an odbc error. I've tried using average, count, and sum in the record selection, but it won't let me do it there either. It says "this function cannot be used because it must be evaluated later", which makes sense.
I'm totally confused now.

Any ideas?

Thanks!