wshcraft70
asked on
Crystal Report 2011 (trying to get an average into a Cross Tabs)
I've attached the report..
I'm either over complicating this or completely clueless.. I'd wager on the last option..
At any rate.. I've got a SQL View that I'm building this report from. You may already have seen a previous version of this report as many of you have helped me. Initially we were using subreports but report was running TOO slow so I created a view which is working great except for the chart I'm trying to create which is to calculate:
AVG calls per hour
The VIEW has a field call hours and if the call was made in the 8am time frame it returns an 8. However, there is a BASE ID that repeats itself while the call center is routed to it's final destination. Therefore, I only need to grab the DISTINCT BASE ID and count it if equal to 8 and so on..
8 am = avg calls for given period (given period is a date range parameter)
9 am = avg calls for given period (given period is a date range parameter)
10 am = avg calls for given period (given period is a date range parameter)
11am = avg calls for given period (given period is a date range parameter)
12 pm = avg calls for given period (given period is a date range parameter)
so on..
I've sorted the cross tabs reports in specified order and have created the order of 8am -5pm..
However, I can only do a distinct count.. When I summarize using an AVERAGE I get a confused number.. lol
Any suggestions?
ACD-ICD-VIEW.rpt
I'm either over complicating this or completely clueless.. I'd wager on the last option..
At any rate.. I've got a SQL View that I'm building this report from. You may already have seen a previous version of this report as many of you have helped me. Initially we were using subreports but report was running TOO slow so I created a view which is working great except for the chart I'm trying to create which is to calculate:
AVG calls per hour
The VIEW has a field call hours and if the call was made in the 8am time frame it returns an 8. However, there is a BASE ID that repeats itself while the call center is routed to it's final destination. Therefore, I only need to grab the DISTINCT BASE ID and count it if equal to 8 and so on..
8 am = avg calls for given period (given period is a date range parameter)
9 am = avg calls for given period (given period is a date range parameter)
10 am = avg calls for given period (given period is a date range parameter)
11am = avg calls for given period (given period is a date range parameter)
12 pm = avg calls for given period (given period is a date range parameter)
so on..
I've sorted the cross tabs reports in specified order and have created the order of 8am -5pm..
However, I can only do a distinct count.. When I summarize using an AVERAGE I get a confused number.. lol
Any suggestions?
ACD-ICD-VIEW.rpt
WHat average are you trying to show?
Avrage of calls in the 8AM period for the date duration?
mlmcc
Avrage of calls in the 8AM period for the date duration?
mlmcc
ASKER
Yes..
If HOUR = 8 and within {?Date} then average of total calls within that period (need to use distinct BASE ID) because base ID is duplicated MANY times in some cases and should only be counted as 1 call.
Then for each other period 9, 10, 11, 12, 1, 2, 3, 4, 5 (anything other dropped)
If HOUR = 8 and within {?Date} then average of total calls within that period (need to use distinct BASE ID) because base ID is duplicated MANY times in some cases and should only be counted as 1 call.
Then for each other period 9, 10, 11, 12, 1, 2, 3, 4, 5 (anything other dropped)
What is the database type ? Can you attach the script for the view ?
ASKER
See below.. Was only able to get an ON ALTER QUERY VIEW of the script the the VIEW is using.
USE [ISTT]
GO
/****** Object: View [dbo].[NC-Call Report Stats] Script Date: 03/20/2012 16:42:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[NC-Call Report Stats]
AS
SELECT D1.BaseID, H1.Dates, H1.Abandoned, D2.InternalID, D2.InternalNum, D2.Duration, H1.Hours,
CASE WHEN D1.InternalID = 'cust service' THEN 'Cust Service' WHEN D1.InternalID = 'VIP' THEN 'VIP' WHEN D1.InternalID = 'CA English' THEN 'CA English' WHEN D1.InternalID
= 'CA French ' THEN 'CA French ' WHEN D1.InternalID = 'consumer tech' THEN 'consumer tech' WHEN D1.InternalID = 'CA English Roll Over' THEN 'CA English Roll Over'
ELSE 'NO GROUP' END AS GroupID, CASE WHEN H1.Abandoned = 0 THEN 'Call' WHEN H1.Abandoned = 1 THEN 'Aband' END AS CallType,
CASE WHEN D2.InternalNum BETWEEN '599' AND '700' THEN 'Wait' ELSE 'Talk' END AS QType
FROM dbo.CallDetail AS D1 INNER JOIN
dbo.CallBase AS H1 ON D1.BaseID = H1.BaseID INNER JOIN
dbo.CallDetail AS D2 ON D1.BaseID = D2.BaseID
WHERE (H1.Direction = 'INCOMING')
GO
USE [ISTT]
GO
/****** Object: View [dbo].[NC-Call Report Stats] Script Date: 03/20/2012 16:42:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER VIEW [dbo].[NC-Call Report Stats]
AS
SELECT D1.BaseID, H1.Dates, H1.Abandoned, D2.InternalID, D2.InternalNum, D2.Duration, H1.Hours,
CASE WHEN D1.InternalID = 'cust service' THEN 'Cust Service' WHEN D1.InternalID = 'VIP' THEN 'VIP' WHEN D1.InternalID = 'CA English' THEN 'CA English' WHEN D1.InternalID
= 'CA French ' THEN 'CA French ' WHEN D1.InternalID = 'consumer tech' THEN 'consumer tech' WHEN D1.InternalID = 'CA English Roll Over' THEN 'CA English Roll Over'
ELSE 'NO GROUP' END AS GroupID, CASE WHEN H1.Abandoned = 0 THEN 'Call' WHEN H1.Abandoned = 1 THEN 'Aband' END AS CallType,
CASE WHEN D2.InternalNum BETWEEN '599' AND '700' THEN 'Wait' ELSE 'Talk' END AS QType
FROM dbo.CallDetail AS D1 INNER JOIN
dbo.CallBase AS H1 ON D1.BaseID = H1.BaseID INNER JOIN
dbo.CallDetail AS D2 ON D1.BaseID = D2.BaseID
WHERE (H1.Direction = 'INCOMING')
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
James,
Makes sense.. However, if within the Cross Tabs, I do a distinct count of the BaseID which works, how can I get the average without it counting all the duplicate BaseID's since when changing my crosstabs summary from DISTINCT COUNT to AVERAGE no longer looks at distinct?
Makes sense.. However, if within the Cross Tabs, I do a distinct count of the BaseID which works, how can I get the average without it counting all the duplicate BaseID's since when changing my crosstabs summary from DISTINCT COUNT to AVERAGE no longer looks at distinct?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
No, I just need the crosstabs to look at the data that is being called from the parameter
(?Date Range)
Have never tried building a Subreport to feed a crosstab.. Will I need to declare variables and WHILEPRINTINGRECORDS and such?
My lord this is complicated.. haha...
(?Date Range)
Have never tried building a Subreport to feed a crosstab.. Will I need to declare variables and WHILEPRINTINGRECORDS and such?
My lord this is complicated.. haha...
I think mlmcc was suggesting that you just put the cross-tab (Or maybe just a chart, based on the data, instead of a cross-tab?) in the subreport (as opposed to trying to set variables and somehow use those in a cross-tab in the main report, which you couldn't do directly anyway). The idea is to create a subreport that only reads one record for each BaseID and time, so you don't have any "duplicate" records and don't have to worry about getting Distinct counts.
James
James
ASKER
I give.. This one has gotten the best of me... Thank you all very much for your assistance.. Sometimes, just NEED TO START from scratch.. OYE!
ASKER
view.xlsx