Matrix report like a phone bill

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

Matrix report like a phone bill

Author

Topic

bushfoot
Yak Posting Veteran

53 Posts

Posted - 2009-07-30 : 16:10:09

I have posted two mails and inscope and some issues I was having with it. Altho they got many reads, I am not getting any responses.. So I decided to start from scratch and see if the problem is somewhere else in my implementation

So I thought I would try it this way instead...

Here is my select statement
select Employee,
CallStartTime,
Duration,
TimeToAnswer,
CallerID,
ANI
from tblPhoneData where len(Employee) > 0
order by employee

I want to create matrix report that will look like this
emp callstarttime dur tta
1100 2006-11-15 08:07:00.000 20 7
1100 2006-11-15 09:04:00.000 79 498
1100 2006-11-15 09:18:00.000 86 61
1100 2006-11-15 11:29:00.000 319 3 1145
SUBTOTAL Sum(duration) sum(timeToanswer) --
1102 2009-05-21 11:14:34.000 134 3 6135918208
1102 2009-05-21 11:51:19.000 80 4 6132549465
1102 2006-11-15 15:16:00.000 3431 7 6135921234
1102 2006-11-15 16:08:00.000 14 10
1102 2006-11-15 16:30:00.000 198 4 6135921234
SUBTOTAL Sum(duration) sum(timeToanswer) --
1105 2009-05-21 14:01:24.000 5118 -1
1105 2009-05-21 15:31:58.000 866 -1
1105 2009-05-21 13:37:08.000 178 5 17147544000
SUBTOTAL Sum(duration) sum(timeToanswer) --
GrandTotal Sum(Subtotal) sum(subtotals) --

What row groupings do I use? It is possible that a person can have two calls in the same start time (some pbx's dont put out the
seconds value)

sachya45
Starting Member

25 Posts

Posted - 2009-07-31 : 09:33:39

I think you just need one group
create group for employee(=Fields!employee.Value)
and you should get subtotal for each employee at group footer,use
sum(Fields!duration.Value, "group_1")and
Grand total for all employee at table footer as sum(Fields!duration.Value, "data set")
I hope this will work for you.

bushfoot
Yak Posting Veteran

53 Posts

Posted - 2009-07-31 : 09:43:04

The problem with one group is that it sums all the durations and time to answers.
I tried again through the wizard and managed to generate a report I wanted. So now the hurdle I am facing is formatting (lines, back colors, fonts).

I am trying to find a way to figure out if the row is the subtotal row. I want to format the subtotal row with borders and back colors to make it visually different from the data rows.

I tried the inscope function (InScope(Group1) and InScope(Group2) are ALWAYS true)

Thanks for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources