Solved

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

Posted on 2012-03-20
11
380 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 100

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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 250 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 100

Assisted Solution

by:mlmcc
mlmcc earned 250 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

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…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

752 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