Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Average Total Record Count by Groups Post Reply Post New Topic
Author Message
KCowden
Newbie
Newbie


Joined: 15 Jun 2016
Location: United States
Online Status: Offline
Posts: 13
Quote KCowden Replybullet Topic: Average Total Record Count by Groups
    Posted: 23 Aug 2016 at 9:45am
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.
There is no situation so terrible that you can't make it worse.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 23 Aug 2016 at 10:03am
sample data and how you want to display/average it?
IP IP Logged
KCowden
Newbie
Newbie


Joined: 15 Jun 2016
Location: United States
Online Status: Offline
Posts: 13
Quote KCowden Replybullet Posted: 24 Aug 2016 at 1:10am
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.
There is no situation so terrible that you can't make it worse.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 24 Aug 2016 at 4:03am
1. Are you averaging based on
-number of days in the month or
-days in the data set or
-days in the data set that also had incident numbers assigned
2. is your total average an average of the monthly averages or just an average of the data set
3. is there a reason you are including and suppressing rather than omitting the rows with no incident numbers?
IP IP Logged
KCowden
Newbie
Newbie


Joined: 15 Jun 2016
Location: United States
Online Status: Offline
Posts: 13
Quote KCowden Replybullet Posted: 25 Aug 2016 at 1:45am
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
There is no situation so terrible that you can't make it worse.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Aug 2016 at 11:04am
For crystal you can use ISNULL(field)
your select would be something like
NOT(isnull({table.Master_Incident_Number}))

You can also make the selection formula use "default values for nulls" and just use
{table.Master_Incident_Number} <> ""

The month days if at all possible I would get in the stored proc using a calendar table or a function

Edited by DBlank - 25 Aug 2016 at 11:04am
IP IP Logged
KCowden
Newbie
Newbie


Joined: 15 Jun 2016
Location: United States
Online Status: Offline
Posts: 13
Quote KCowden Replybullet Posted: 29 Aug 2016 at 1:00am
Originally posted by DBlank

For crystal you can use ISNULL(field)
your select would be something like
NOT(isnull({table.Master_Incident_Number}))

You can also make the selection formula use "default values for nulls" and just use
{table.Master_Incident_Number} <> ""

The month days if at all possible I would get in the stored proc using a calendar table or a function


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?
There is no situation so terrible that you can't make it worse.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.017 seconds.