Hi all.

I'm back with another, "I thought this would be simple," problem.

I am calculating the total incidents to date for my department. I could use Excel and do a lot of post manipulation of the data but I'd prefer to stay in Crystal Reports. With its readability and ability to chart with just a couple of clicks, it's a better solution considering my audience.

So I've pulled the incidents from January 1 until last Friday. I am pulling all fields from the same table. Now, 187,360 runs later I have a report that I've grouped by Monthly (Group #1) and Daily (Group #2) and have summarized all with a Distinct Count based off of the incident number.

I applied the summary to all levels and the report displays, as it should, the total for each calendar day as well as the total for each month and finally a grand total.

I would like to average runs/day in the monthly groups and as a an overall but it seems one cannot average from a summary.

I had thought possibly adding a formula field that counts the rows and then averaging from that but evidently I'm too ignorant to get that far.

Any help would be appreciated.

DBlank,

Thanks for responding.

Report Header Summary (Distinct Count) Grand Total
Group Header #1 Summary (Distinct Count) Monthly
Group Header #2 Summary (Distinct Count) Daily
Details Select ResponseDate MasterIncidentNumber Problem RunDuration

The Actual Records Selected:

• {Response_Master_Incident.Response_Date}
• {Response_Master_Incident.Master_Incident_Number}
• {Response_Master_Incident.Problem}
• {Response_Master_Incident.Elapsed_CallRcvd2CallClosed}

Each day, the records returned range from about 800 to 1000.

I summarized, applied to all levels, using "Distinct Count". My distinct count is based on incident numbers. I have filtered to suppress any records that did not get an incident number (cancelled before assignment).

What I am wanting to do is summarize the average of incidents per day for the Monthly Group (Group #1) and for the overall sample period (report total).

If I can figure out a way to pull in that number and build a summary around it, averaging or if there is a formula that will count the daily records and provide an average summary I can apply to Group #1 and then the report overall.

Ultimately, I will be displaying this in the Group #1 header and the report header.

I hope this is enough information, if not let me know. I am grateful for your help.

Average in Group# 1 (monthly) would be by the number of days in the month.

The total average would be of the data set and not an average of the averages.

Ignorance is the primary reason for the inclusion and suppression of those records. I know how to write a SQL select condition where those records would be ignored (I.E. ...WHERE Master_Incident_Number NOT NULL) but I don't know how to select for NOT NULL in CR.

I also haven't gotten far enough to understand how to use the SQL Expression Fields to get there either. The option for "NOT NULL" does not appear to be available. I found a "IFNULL (,)" but I really don't know what to do with that either. At least not yet.


Thank you for looking at this, it's hard to say how much this help means to me.

Kyle

ADDED IN EDIT: This won't necessarily work in all cases but in this case, all incident numbers in this sample begin with "2016...". I just required that field to be "like" "2016*" and that solves that.

Edited by KCowden - 25 Aug 2016 at 2:12am

Very good. I could not figure why there was an "isnull" built in but not a "notnull" or how to select that way in CR.

As to the calendar functions, how can I use that to get an average per day?