Convert 2 dimensional chart to crosstable

[#1]

Hi, I have a question regarding crosstables. I have searched the forums multiple times and have not found an answer to my question.

Lets say that I have tree tables, two are fetched from my database the third one is an autogenerated date table.

DateTable:
---------------------------
Date
2009-01-01
2009-01-02
2009-01-03

ProjectTable:
---------------------------
ProjectId, ProjectName
1, P1
2, P2

Tasks
--------------------------
TaskId, ProjectId, CreatedDate, ClosedDate
1,1,2009-01-01, null
2,1,2009-01-02, 2009-01-03
3,2,2009-01-01, 2009-01-02


From these tables it is really easy to create a bar chart displaying the number of tasks created each day, grouped by project.

Dimensions:
-------------------------
Date
ProjectName

Expression:
-------------------------
Count(If(InDay(CreatedDate, Date, 0), TaskId, Null())


But it is also easy to create a line chart displaying the number of open tasks each day, grouped by project. The only thing I have to change is the expression.

Dimensions:
-------------------------
Date
ProjectName

Expression:
-------------------------
Count(If(CreatedDate <= Date AND (ClosedDate >= Date OR IsNull(ClosedDate)), TaskId, Null())


Now I would like to transform my charts into crosstables that displays project names vertically and dates horizontally.

P1 1 1 0
P2 1 0 0
2009-01-01 2009-01-02 2009-01-03


P1 1 2 1
P2 1 1 0
2009-01-01 2009-01-02 2009-01-03

(Maybe I got the values wrong!?!)

My question is, if it is so easy to create charts, transform them into straight and pivot tables, why isn't as easy to create crosstables?
Can this be done? or do I have to transform my already existing data into a lot of pre-aggregated tables in the script?
If I have to do this, the flexibility of adding attributes into user-selectable listboxes disapears?

br
P-A Söderqvist

[#2 · Accepted solution]

If your chart is of type, pivot table, this can be done easily by clicking and dragging the date column to the top, where it will appear when dispalyed vertically.

[#3]

ah, thank you very much. I knew there must be an easy way of doing this.