Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Crystal Report 2011 (trying to get an average into a Cross Tabs)

Posted on 2012-03-20
11
Medium Priority
?
395 Views
Last Modified: 2012-03-21
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
0
Comment
Question by:wshcraft70
  • 6
  • 2
  • 2
  • +1
11 Comments
 
LVL 1

Author Comment

by:wshcraft70
ID: 37743898
Attached is the data VIEW in EXCEL format that I'm working with.  Although the actual data is in SQL..  I just wanted to give you a visual of the data should it help..
view.xlsx
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 37744479
WHat average are you trying to show?

Avrage of calls in the 8AM period for the date duration?

mlmcc
0
 
LVL 1

Author Comment

by:wshcraft70
ID: 37744493
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)
0
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
LVL 18

Expert Comment

by:vasto
ID: 37744630
What is the database type ? Can you attach the script for the view ?
0
 
LVL 1

Author Comment

by:wshcraft70
ID: 37744649
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
0
 
LVL 35

Accepted Solution

by:
James0628 earned 1000 total points
ID: 37745925
Your chart is based on your cross-tab, and your cross-tab doesn't make sense.  You do a Count of {@GROUP CALLS PER HOUR}, which is the same as doing a count on any field in the datasource, so you're just getting a record count.  {@GROUP CALLS PER HOUR} is checking Hours and doing DistinctCount's, but the cross-tab isn't looking at the value produced by the formula.  It's just counting the values, which is just going to give you a record count.

 How about just having the cross-tab do a distinct count on BaseID?


 Also, while I strongly suspect that your {@GROUP CALLS PER HOUR} formula isn't really useful (at least as written), FWIW, it could be simplified to:

IF {NC_Call_Report_Stats.GroupID} = {?Group} and
 {NC_Call_Report_Stats.Hours} in [ 8, 9, 10, 11, 12, 1, 2, 3, 4, 5] then
  distinctcount ({NC_Call_Report_Stats.BaseID})


 James
0
 
LVL 1

Author Comment

by:wshcraft70
ID: 37747357
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?
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 1000 total points
ID: 37747449
Do you need all the data from the records?

You could build a subreport that does the cross tab and base it on just the call id and date fields so that you don't get duplicate records.

mlmcc
0
 
LVL 1

Author Comment

by:wshcraft70
ID: 37747459
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...
0
 
LVL 35

Expert Comment

by:James0628
ID: 37747897
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
0
 
LVL 1

Author Comment

by:wshcraft70
ID: 37749760
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!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Loops Section Overview
Suggested Courses

972 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question