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

 

[gyfu]

Yes, I did use the existing userid and date..

select AA.UserID, A.Description, count(AA.ActionID) as Totals, datepart(HH, AA.DateCompleted)
from ACCOUNTACTIONs AA join
AvailableACTIONs A ON AA.ActionID = A.ActionID
where (AA.DATECOMPLETED = convert(datetime,19/07/2005,103))
and (AA.UserID= 1074)
group by AA.UserID, A.Description, datepart(HH, AA.DateCompleted)

I am going off soon for the day. Is there anything else I can do?


-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[MisterMo]

I'll have to built a couple of tables on my system to do some tests but I am working as well and today is my last day until next year. I'll do my best



Mo

 

[MisterMo]

here is the working one it was a conversion issue

select AA.UserID, A.Description, count(AA.ActionID) as Totals, datepart(HH, AA.DateCompleted)
from ACCOUNTACTIONs AA join
AvailableACTIONs A ON AA.ActionID = A.ActionID
WHERE     (DATEDIFF(DD, AA.DateCompleted, CONVERT(datetime, '19/07/2005', 103)) = 0)
and (AA.UserID= 1074)
group by AA.UserID, A.Description, datepart(HH, AA.DateCompleted)

Mo

 

[MisterMo]

I have played a little with the data and I Hvae created a cross tab with the query that I have given you without having to write 300 formulas.

The query will give you the count(actions) for each hour from 0 to 23 of a given day for a given user and a given action place the fields on a cross tab

ActionName on the row section the Hour in the Column Section and the total in the details.

I need to know how you want to calculate the ratio if CR can't do it then we can put the lot in a Stored Proc and do it from there.


Good Luck


Mo

 

[MisterMo]

Last one I promise

If you still have issue using a Command and want to use the tables;

create a formula eg Hours

'Hr' & cstr(DatePart ("h" , {AccountActions.DateCompleted}))

and use this in the cross tab for the column option

the rest is just as I said on the previous post.

Mo

 

[gyfu]

Mo,
I am still not able to get this done. If you have the time, could I somehow contact you and probably give u a more detail example how I am required to achieve this.

At the moment I am still trying and at the same time, doing it manually, is a pain to modify later on, when a new request comes in to sort it by days and month.

Please let me know. thanks.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[MisterMo]

Hi gyfu, and Happy New Year and sorry for the delay I have only just got back.

What exactly is the problem, I have run some tests both from MS-SQL and CR10, they both have the datediff option so if you want you can create Date and UserId parameters (which can be changed at will) and on the selection formula do your computation.

You can also create these parameters from the command option for both the Date and the UserId

just post how you wish to proceed and we'll do our best to help you.

Keep psoting on the forum, trust me every expert here is reading this and they will intervene if they think they can give additional help.





Mo

 

[gyfu]

Hey Mo. Happy NewYear to you too. I am done with the report but am still trying to get this method to work. It will be great if you could still help me.

I didn't understand your instruction when you are talking about the command option.Let me try and give you the examples again with tables.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[gyfu]

The two tables are part of what I extracted for the first 3 rows.

ACCOUNTACTION TABLE          

RecordID     ResponsibleParty    ActionID    DateCompleted
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 		        8    		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


ACTION TABLE            

Action ID    Category    ProductivityId
1               1           0
2               1           0
3               1           2
4               1           1
5               2           2
6               2           3
7               2           1
8               3           1

My expected output layout will be like this.

                                8 9 10 11 12 13 14  15 16 17 18 Total
Number of calls                 0 0 0  0   0  0  3  0   2 1  0    6
Number of contacts              0 0 0  0   0  0  3  0   1 0  0    4 
Ratio Calls : contacts          0 0 0  0   0  0 100 0  50 0  0    67%

The query for Number of calls = {AvailableActions.Category} = (2 to 3)
The query for number of Contacts = ({AvailableActions.Category} = 2 and {AvailableActions.ProductivityID} = (1 to 2))
The formula for Ratio = (NumberofContacts/numberofCalls) * 100


I hope this is something what you got figured Mo. Let me know if anything else is unclear.

Thanks.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[MisterMo]

Sorry but this is slightly different from what I have understood a year ago

what you need is a union query. I'll try and buld you a sample in a minute or two


Mo

 

[MisterMo]

Try

select AA.UserID, 'Number of calls' as Description, count(AA.ActionID) as Totals, datepart(HH, AA.DateCompleted) as Hrs
from ACCOUNTACTION AA join
AvailableACTION A ON AA.ActionID = A.ActionID
WHERE     (DATEDIFF(DD, AA.DateCompleted, CONVERT(datetime, '19/07/2005', 103)) = 0)
and (AA.UserID= 1074) and (A.CategoryId in(2,3))
group by AA.UserID, datepart(HH, AA.DateCompleted)
union
select AA.UserID, 'Number of Contacts' as Description, count(AA.ActionID) as Totals, datepart(HH, AA.DateCompleted) as Hrs
from ACCOUNTACTION AA join
AvailableACTION A ON AA.ActionID = A.ActionID
WHERE     (DATEDIFF(DD, AA.DateCompleted, CONVERT(datetime, '19/07/2005', 103)) = 0)
and (AA.UserID= 1074) and (A.CategoryId = 2)and (A.ProductivityID in (1,2))
group by AA.UserID, datepart(HH, AA.DateCompleted)

see if this make sense

Mo

 

[gyfu]

Mo,
The output I got is like this.

ResponsibleParty        Description             Totals          Hrs
1074                    Number of calls           1             16
1074                    Number of Contacts        1             16

Is this on the right track so far?

How would I actually have the 0 count for the rest of the hours?
I am sorry if I am asking a whole lot of dumb questions here.

thanks for your patience.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[MisterMo]

Hi gyfu,

What do you mean 0 count ?

Do you intend to display all 24 hours even if there is no data aginst it?

Mo

 

[gyfu]

Yes Mo. this is true. I will need to display 0 for the hrs that has no data against it.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[MisterMo]

gyfu, I can't think of a simple way of doing this.

The best solution is to use cursor and temp tables, I am writing a sample Stored Proc which will give you what you need but it will take a little time

Mo

 

[MisterMo]

Here is a sample Stored Proc that will do what you need.

NOTE: this SP only cover Number of calls and Number of contracts using the union, these options are hard coded. If you need more dynamic values you need to change the select statement.


1)Run the script from query analyzer

2) add the stored proc to a new report

3) the parameters will be generated automatically

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[SP_GetActionHours]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[SP_GetActionHours]
GO
CREATE Procedure SP_GetActionHours
@UserId int = 0, 
@Date varchar(10)
as 
begin
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
--*************************************** Declare Temp Table ********************************************************
create table #TransSet(UserId int, ADesc Varchar(150), Hrs00 int, Hrs01 int, Hrs02 int, Hrs03 int, Hrs04 int, Hrs05 int, 
Hrs06 int, Hrs07 int, Hrs08 int, Hrs09 int, Hrs10 int, Hrs11 int, Hrs12 int, Hrs13 int, Hrs14 int, Hrs15 int,
Hrs16 int, Hrs17 int, Hrs18 int, Hrs19 int, Hrs20 int, Hrs21 int, Hrs22 int, Hrs23 int)
--*************************************** Declare Variables for cursor **********************************************
declare @UsrId int, @ActionDesc Varchar(150), @TotAction int, @Hour int
declare @UsrId2 int, @ActionDesc2 Varchar(150), @TotAction2 int, @Hour2 int

--************************************* Declare Variables for Temp Table ********************************************
declare @00Hours int, @01Hours int, @02Hours int, @03Hours int, @04Hours int, @05Hours int, 
@06Hours int, @07Hours int, @08Hours int, @09Hours int, @10Hours int, @11Hours int, @12Hours int, @13Hours int, @14Hours int,
@15Hours int, @16Hours int, @17Hours int, @18Hours int, @19Hours int, @20Hours int, @21Hours int, @22Hours int, @23Hours int

Declare @ActionFlag Varchar(150)
--**********************(************ Set Variables for Temp Table to Zero ******************************************
set @00Hours = 0 set @01Hours = 0 set @02Hours = 0 set @03Hours = 0 set @04Hours = 0 set @05Hours = 0 set @06Hours = 0 
set @07Hours = 0 set @08Hours = 0 set @09Hours = 0 set @10Hours = 0 set @11Hours = 0 set @12Hours = 0 set @13Hours = 0
set @14Hours = 0 set @15Hours = 0 set @16Hours = 0 set @17Hours = 0 set @18Hours = 0 set @19Hours = 0 set @20Hours = 0 
set @21Hours = 0 set @22Hours = 0 set @23Hours = 0 
--Set the cursor 'SC' which will be used to populate temp table
DECLARE SC CURSOR FAST_FORWARD READ_ONLY FOR 
select AA.UserID, 'Number of calls' as Description, count(AA.ActionID) as Totals, datepart(HH, AA.DateCompleted) as Hrs
from ACCOUNTACTION AA join
AvailableACTION A ON AA.ActionID = A.ActionID
WHERE     (DATEDIFF(DD, AA.DateCompleted, CONVERT(datetime, @Date, 103)) = 0)
and (AA.UserID= @UserId) and (A.CategoryId in(2,3))
group by AA.UserID, datepart(HH, AA.DateCompleted)
union
select AA.UserID, 'Number of Contacts' as Description, count(AA.ActionID) as Totals, datepart(HH, AA.DateCompleted) as Hrs
from ACCOUNTACTION AA join
AvailableACTION A ON AA.ActionID = A.ActionID
WHERE     (DATEDIFF(DD, AA.DateCompleted, CONVERT(datetime, @Date, 103)) = 0)
and (AA.UserID= @UserId) and (A.CategoryId = 2)and (A.ProductivityID in (1,2))
group by AA.UserID, datepart(HH, AA.DateCompleted)

OPEN SC
FETCH NEXT FROM SC into @UsrId, @ActionDesc, @TotAction, @Hour
set @UsrId2 = @UsrId set  @TotAction2 = @TotAction set @Hour2 = @Hour
set @ActionFlag = @ActionDesc

WHILE @@FETCH_STATUS = 0
Begin
If @ActionFlag = @ActionDesc
	begin
	set @UsrId2 = @UsrId set  @TotAction2 = @TotAction set @Hour2 = @Hour

	if @Hour2 = 0 set @00Hours = @TotAction2
	if @Hour2 = 1 set @01Hours = @TotAction2
	if @Hour2 = 2 set @02Hours = @TotAction2
	if @Hour2 = 3 set @03Hours = @TotAction2
	if @Hour2 = 4 set @04Hours = @TotAction2
	if @Hour2 = 5 set @05Hours = @TotAction2
	if @Hour2 = 6 set @06Hours = @TotAction2
	if @Hour2 = 7 set @07Hours = @TotAction2
	if @Hour2 = 8 set @08Hours = @TotAction2
	if @Hour2 = 9 set @09Hours = @TotAction2
	if @Hour2 = 10 set @10Hours = @TotAction2
	if @Hour2 = 11 set @11Hours = @TotAction2
	if @Hour2 = 12 set @12Hours = @TotAction2
	if @Hour2 = 13 set @13Hours = @TotAction2
	if @Hour2 = 14 set @14Hours = @TotAction2
	if @Hour2 = 15 set @15Hours = @TotAction2
	if @Hour2 = 16 set @16Hours = @TotAction2
	if @Hour2 = 17 set @17Hours = @TotAction2
	if @Hour2 = 18 set @18Hours = @TotAction2
	if @Hour2 = 19 set @19Hours = @TotAction2
	if @Hour2 = 20 set @20Hours = @TotAction2
	if @Hour2 = 21 set @21Hours = @TotAction2
	if @Hour2 = 22 set @22Hours = @TotAction2
	if @Hour2 = 23 set @23Hours = @TotAction2

	FETCH NEXT FROM SC into @UsrId, @ActionDesc, @TotAction, @Hour

	end
else
	begin
	--**********************(************ Copy values to temp table *****************************************
	Insert into #TransSet select @UsrId2,@ActionFlag,@00Hours,@01Hours,@02Hours,@03Hours,@04Hours,@05Hours,@06Hours,
	@07Hours,@08Hours,@09Hours,@10Hours,@11Hours,@12Hours,@13Hours,@14Hours,@15Hours,@16Hours,@17Hours,
	@18Hours,@19Hours,@20Hours,@21Hours,@22Hours,@23Hours
	--********************************** Reset Variables for Temp Table to Zero *****************************************
	set @00Hours = 0 set @01Hours = 0 set @02Hours = 0 set @03Hours = 0 set @04Hours = 0 set @05Hours = 0 set @06Hours = 0 
	set @07Hours = 0 set @08Hours = 0 set @09Hours = 0 set @10Hours = 0 set @11Hours = 0 set @12Hours = 0 set @13Hours = 0
	set @14Hours = 0 set @15Hours = 0 set @16Hours = 0 set @17Hours = 0 set @18Hours = 0 set @19Hours = 0 set @20Hours = 0 
	set @21Hours = 0 set @22Hours = 0 set @23Hours = 0 
	--Reset Action Flag
	set @ActionFlag = @ActionDesc
	end
END
CLOSE SC
DEALLOCATE SC
Insert into #TransSet select  @UsrId, @ActionDesc,@00Hours,@01Hours,@02Hours,@03Hours,@04Hours,@05Hours,@06Hours,
@07Hours,@08Hours,@09Hours,@10Hours,@11Hours,@12Hours,@13Hours,@14Hours,@15Hours,@16Hours,@17Hours,
@18Hours,@19Hours,@20Hours,@21Hours,@22Hours,@23Hours

select * from #TransSet

END

To test this from query analyzer

SP_GetActionHours 1074, '19/07/2005'

Mo

 

[gyfu]

Mo,
My apologies for getting back so late. I have tested this and it looks great.

This works great and I really appreciate your efforts thanks a lot.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[MisterMo]

Glad it worked

Mo

 

[gyfu]

Mo,
here I would like to put in a little workaround that I did previously. I actually put all my selection into a one formula field and assign it to shared variables. From there I created the fields and just place it in the reports. works well for me..

Shared NumberVar outCall1;
Shared NumberVar outContact1;

If {@TimeByHr}<="08" then 
(
if {AvailableActions.Category} = (2 to 3) then outCall1:=OutCall1+1; //Outbound # of calls
if ({AvailableActions.Category} = 2 and {AvailableActions.ProductivityID} = (1 to 2)) then outContact1:=outContact1+1; //Outbound # Contacts
)

this way, just like the stored procedure, if I need to change anything, i can just do it at one place.

-- gyfu --
Crystal Ver 10
Microsoft SQL

 

[MisterMo]

Hi gyfu, sorry but I don't understand what you mean and what are you trying to do with the variables.



Mo