Create Manual Crosstab

[MsRae]

I am trying to create a manual crosstab that will do the following:

1. Group by day by Category field
2. Evaluate a ReceivedDate field then count the number of tickets based on the TicketID field. This will give me the number of tickets opened.
3. Then I need it to evaluate a CompletedDate field and count the number of tickets based on the same TicketID field to give me the number of tickets closed.
4. Provide grand totals at the end.

I know I will have to do a manual crosstab, but I just need some guidance in getting started with it.

Any suggestions is helpful.

Thanks.

Rae

 

[GJParker]

Basic approach to manual crosstabs is to evaluate if som e criteria is true or false and assign a value accordingly i.e.

to show a crosstab of tickets received for each day of a week

you would creat seven formulas one for each day of the week e.g.

//@Monday
If DayofWeek(table.RecvDate) = 2 Then 1 else 0

//@Tuesday
If DayofWeek(table.RecvDate) = 3 Then 1 else 0

you this wiil then give you fields that you can use to summarise the data at any level of grouping.

Hope thsi gives you enough to get started if not post some example data and exoected outcome.


Gary Parker
Systems Support Analyst
Manchester, England