donderdag 24 oktober 2013

Crystal Reports: Gantt Chart With Different Colors Before and After a Given Date

In Crystal Reports it is very easy to create a Gantt Chart. You just need a table with a starting date and an ending data. And there you go. In the given example I used the data from the Orders table from the database Northwind. I just took the data from July 1996.

The regular chart looks like this:

I just chose Color by series to get the same color for all the bars.

Now I created a Gantt Chart with different colors before and after a given date (July 15 1996, in this case).

Here Orange stands for Done and Green for To Do.

How did I create this one?
  • First I created a parameter GivenDate with 15-7-1996 as Default value.
  • Next I created a Formula Field BeforeGivenDate:
if {Orders.OrderDate}<{?GivenDate} and {Orders.ShippedDate} < {?GivenDate} then
    {Orders.ShippedDate} - {Orders.OrderDate}
else
    (if {Orders.OrderDate}< {?GivenDate} and {Orders.ShippedDate}>= {?GivenDate} then
        {?GivenDate}- {Orders.OrderDate}
    else
        0)
  • And then a Formula Field AfterGivenDate:
if {Orders.OrderDate}>= {?GivenDate} and {Orders.ShippedDate}>= {?GivenDate} then
    {Orders.ShippedDate}-{Orders.OrderDate}
else
    (if {Orders.OrderDate}< {?GivenDate} and {Orders.ShippedDate} >= {?GivenDate} then 
        {Orders.ShippedDate}- {?GivenDate}
    else
        0)
  • Creating the chart I picked a regular Horizontal Stacked Bar Chart instead of a regular Gantt chart.
  • For the Data Axis I picked a minimum of 35245 and a maximum of 35305.
  • I switched off the legend.
  • The Data should be like this:
  • I just chose Color by series to get the same color for the same set.
  • For the bar OrderDate I chose as a color Transparant.
  • @BeforeGivenDate I colored orange and @AfterGivendate green.
  • The format of the horizontal Y-axis I changed like this, Category: Date and Type: 8-2-2002

The file I created you can download here: www.walmar.nl/zips/graphganttcolors.zip

You can also download the file through this link:

https://drive.google.com/folderview?id=0B7HgkOwFZtdZVmhRQUZFM28yc1U&usp=sharing
Een reactie plaatsen