Displaying a timeline in crystal reports or excel

I have an application that keeps rent periods of a parking lot. I have a SQL database with this information:

idLot    dateFrom       dateTo
----------------------------开发者_JAVA技巧--------------
1     01/03/2011       30/07/2011
2     01/01/2011       30/05/2011
3     01/02/2011       30/07/2011   
6     01/02/2011       30/06/2011

And I need to display the information like the image below.

1

I cannot achive the goal with Excel or Crystal Reports. I also use ComponentOne FlexGrid. Any ideas?

Thanks in advance.

---

It's not the prettiest option, but you can do this in Excel using the Sumproduct() function. In the screenshot below, the sumproduct goes through each line of your datatable and does 3 things (the selected cell is B17 by the way):

1. It determines whether each row has the idlot it is looking for (evaluates to true/false)
2. It determines whether DataFrom is <= the column of your display table (evaluates to true/false)
3. It determines whether DataTo is >= the column of your display table (evaluates to true/false).

This results in a 0 or 1 in each cell of your display table. This is close, but not exactly what you're looking for. I then added some conditional formatting:

1. If the result of the equation was 0, then the cell's font is white.
2. If the result of the equation was 1, then cell's font and background are both green.

When you apply the conditional formatting to the entire graph, you get the last 2 rows, which is green for occupied and white for unoccupied.

The only remaining problem is how to account for a changing number of rows in your table. This can be solved by just setting the ranges in your equation to a very high number.