Cross-Tab Based On Chronological Timeline

[Former Member]

Post Author: BT_Jason

CA Forum: Charts and Graphs

Hello,

I made the mistake of showing my boss a very simple report summarizes support tickets based on a date criteria. He said great, but I wish it could show a history of the 5 support categories per week. Last time I did anything real in Crystal Reports it was included in Visual Basic. I was hoping someone had a sample report or formula to display the following?

WO: 11/10/07

WO:11/19/07

Eval Extension

1

1

Evaluation

1

1

Production

1

1

Pre-Sales

1

1

Post-Sales

1

1

Thanks,

Jason

[Former Member]

Post Author: Lugh

CA Forum: Charts and Graphs

Should be fairly simple.First, you'll obviously need to bring in the data. Taking a wild stab at your data structure, let's assume your query would look something like:SELECT TicketID, TicketDate, TicketCategoryFROM TicketsWHERE TicketDate > DATEDIFF(d,-90,GETDATE())Now, simply create a crosstab in the report header. Go into the Crosstab Expert. In the Columns area, put TicketDate. Click the Group Options button underneath it, and select "for each week." In the Rows area, put TicketCategory. In the Summarized Fields area, put TicketID. It should automatically default to Count. If not, you can change it.Now, of course, there is a caveat. If you have a full report period with, say, no Pre-Sales tickets, that line just won't show up. Similarly, if you have a week with no tickets at all, that week won't show up. If that's going to be a problem, you can always "seed" the data.