Manual Cross tab? 300 + fields?? 1

[gyfu]

Guys,
I need your help in giving your 2 cents with my report here. I figured that I have to do a manual cross tab report of over 300 formula fields. Please take a look and let me know if there is a more efficient way of doing this report.

        ACTIONS/HR		1000hrs     1100     1200     1300     1400     1500     1600     1700     1800
        No of Transactions
        Attempt		    20           20     20           20           20          20         20          20         20
        No. of Transactions
        Completed                            10                10         10          10            10           10        10           10         10 
        Attempt:completed
        Ratio                                      50%             50%        50%     50%        50%       50%     50%      50%        50%

This is a portion of the report. As you can see, the columns are the the action by the hr and the actions are actually derived by various formulas from various tables.
In total there are about 35 rows, and 10 columns, which would result in 350 fields if I were to do a manual cross tab. Any suggestions?

Thanks.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[MisterMo]

what about the database, any idea of the tables and fields might be useful.

You could let MS-SQL do some of the leg work using Stored Proc, Temp Tables and Cursors.



Mo

 

[gyfu]

MisterMo,
Perhaps, that is a way that I could approach. The only problem I have is that my skill set in Stored Procedure is pretty limited. At first I envision creating a similar table in MSSQL.With columns as such...

RowAction
CellHr10
CellHr11
CellHr12 -- CellHr12 would be for columns at 1200hrs and I will use a sql statement.

The problem with this is that, I don't know how I could use a loop to make stored procedure efficient.
I could only end up making 350 select statements or so.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[MisterMo]

could you give a sample of the raw data from your table/s

Mo

 

[gyfu]

How can I give you this?

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[MisterMo]

somthing like:

Hours	Desc			Value
1000	Attempt			12
1100	Completed               15
1200	Attempt:completed	20
1100	Attempt			10

and so on

by knowing the structure I/We can help devising a solution weather from CR or SQL

I still think that a SP would the job but without the structure is difficult to advise

Mo

 

[gyfu]

Ok Mo.. here are the tables..

[b]ACCOUNTACTION TABLE[/b]			

RecordID 	UserID	ActionID	DateTime
1	        1074	1	19/07/2005 16:23
2	        1074	1	19/07/2005 16:26
3	        1074	2	19/07/2005 16:37
4	        1074	129	19/07/2005 16:37
5	        1074	1	19/07/2005 16:38
6	        1074	5	19/07/2005 16:38
7	        1074	1	19/07/2005 16:42
8	        1074	5	19/07/2005 16:44
9	        1075	1	19/07/2005 16:33
11	        1074	1	19/07/2005 16:53
12	        1074	5	19/07/2005 17:04
17	        1074	5	19/07/2005 17:27
18	        1074	1	19/07/2005 17:35
19	        1074	2	19/07/2005 17:39
20	        1074	5	19/07/2005 17:40
21	        1074	1	19/07/2005 17:46
22	        1074	2	19/07/2005 17:49
23	        1074	1	19/07/2005 17:49
24	        1074	2	19/07/2005 17:51
25	        1074	1	19/07/2005 17:51

[b]ACTION TABLE[/b]			

Action ID	Description	Category	Product
1	Note Written	1	0
2	Promise Taken	1	0
3	Demographic Info Modified	1	0
4	Letter Ordered	1	0
5	Status Code Changed	1	0
6	Agent Changed	1	0
7	Account Info Modified	1	0
102	Dispute Invoice Related	2	2
103	Dispute Other	2	2
104	Action No Answer	3	3
105	Dispute Network	2	2
108	RTP - Refuse to Pay	2	2
111	Action Phone off	3	3
112	Action Busy	3	3
113	Action Wrong Number	3	3
114	Action Skip	3	3
121	Other	3	4
122	Account Review - incoming	1	2
126	RTP - Can Not Pay	2	2
127	Dispute Product/ Service	2	2

Sorry, with the bad format. But basically these are the 2 tables, where I will summarize the actions and counts by userID. The Hour column is derived from the hour of the datetime column.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[MisterMo]

Based on your sample try this query on query analyser

select AA.UserId, A.ActionName, count(AA.ActionID) as Totals, datepart( HH, AA.DateTime)
from ACCOUNTACTION AA join
ACTION A ON AA.ActionID = A.ActionID
where Datetime = convert(datetime, '19/07/2005',103)
group by AA.UserId, A.ActionName, datepart( HH, AA.DateTime)

I can't test it because I don't have the tables

Mo

 

[gyfu]

I got an error that says..

Type DateTime is not a defined system type.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[MisterMo]

sorry

select AA.UserId, A.ActionName, count(AA.ActionID) as Totals, datepart( HH, AA.DateTime)
from ACCOUNTACTION AA join
ACTION A ON AA.ActionID = A.ActionID
where (AA.Datetime = convert(datetime, '19/07/2005',103))
group by AA.UserId, A.ActionName, datepart( HH, AA.DateTime)

you could however get some error as both ACTION and DATETIME are SQL Reserved words and might conflict with the name of your table and field

Mo

 

[MisterMo]

Is your DateTime field actually DateTime Type?

Mo

 

[gyfu]

It is smallDateTime type..

I know about the other fields. As I type it out, I was being lazy with the real field names, therefore I used ACTION and DATETIME when they are suppose to be AVAILABLEACTION and DATECOMPLETED respectively.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[MisterMo]

well, in that case you need to change the nam of the table and field accordingly

select AA.UserId, A.ActionName, count(AA.ActionID) as Totals, datepart( HH, AA.DATECOMPLETED)
from ACCOUNTACTION AA join
AVAILABLEACTION A ON AA.ActionID = A.ActionID
where (AA.DATECOMPLETED = convert(datetime, '19/07/2005',103))
group by AA.UserId, A.ActionName, datepart( HH, AA.DATECOMPLETED)

this schould do

Mo

 

[gyfu]

Mo, I did that already and got the error.

However, assuming that I did get this to work. What is it suppose to do cos I forsee another problem.

This will return the result for the hr of 4 and 5.. but I will also need a '0' value return for the other hrs of 08 to 21 hr.

Unless I am missing something here, please advise. Appreciate your help here. Thanks.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[MisterMo]

you worry about the 0 to 21 hours on the second phase,
first get some good data to work with

try this

select AA.UserId, A.ActionName, count(AA.ActionID) as Totals, datepart( HH, AA.DATECOMPLETED)
from ACCOUNTACTION AA join
AVAILABLEACTION A ON AA.ActionID = A.ActionID
where datediff(DD,'19/07/2005',AA.DATECOMPLETED)=0
and (AA.UserId = 77023)
group by AA.UserId, A.ActionName, datepart( HH, AA.DATECOMPLETED)

Mo

 

[gyfu]

Now I got this error..

The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.


I double check my table and it does show that DateCompleted is a smallDateTime data type.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[gyfu]

ok thanks, I just had to make sure that we are on the right track. Not trying to doubt you.



-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[MisterMo]

remove the single quotes form the date

is trial and error

select AA.UserId, A.ActionName, count(AA.ActionID) as Totals, datepart( HH, AA.DATECOMPLETED)
from ACCOUNTACTION AA join
AVAILABLEACTION A ON AA.ActionID = A.ActionID
where datediff(DD,19/07/2005,AA.DATECOMPLETED)=0
and (AA.UserId = 77023)
group by AA.UserId, A.ActionName, datepart( HH, AA.DATECOMPLETED)

Mo

 

[gyfu]

Ok, got it to run now. Both the one that you just post and the previous one I tried, but both return with 0 results.

Zero Row affected.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[MisterMo]

you need to check for an existing date and and user as they will be your input parameters

Mo