Horizontal Bar Chart

[aomara]

I have a table includes a list of all Contracts (Includes contact Extensions) like bellow:

Contract Code , Start Date , End Date , Original Contract Code(for extensions)
--------------------------------------------------------------------------------

"A0001", 01/01/2007, 31/12/2007, ""
"A0001-E1", 01/07/2008, 30/06/2009, "A0001"

"A0002", 01/01/2008, 31/03/2008, ""
"A0002-E1", 01/04/2008, 30/06/2008, "A0002"
"A0002-E2", 01/07/2008, 30/09/2008, "A0002"
"A0002-E3", 01/10/2008, 31/12/2008, "A0002"
"A0002-E4", 01/01/2009, 31/03/2009, "A0002"
"A0002-E5", 01/04/2009, 30/09/2009, "A0002"

No, My manager request a report that show the contracts that starts or ends in the given period (Ex, from 01-01-2007 to 01-01-2010).

the data should be shown in a Horizontal Bar chart that show each contact plus all the extensions in one row. and each extension in different color.

X axis show the contract+Extensions, Y axis show the date.

Thanks, Please try to replay ASAP

 

[lbass]

You can use a selection formula like this:

{table.startdate} <= {?EndPeriod} and
{table.enddate} >= {?StartPeriod}

This would include all active contracts during the selected period, not just those that started or ended in the period. If you really need only those starting or ending in the period, then use:

{table.startdate} in {?StartPeriod} to {?EndPeriod} or{table.enddate} in {?StartPeriod} to {?EndPeriod}

Then create a formula {@origcode} like this:

if {table.originalcontractcode} = "" then
{table.contractcode} else
{table.originalcontractcode}

Insert a bar chart and choose horizontal->stacked and then use {@origcode} as your first "on change of field" and use {table.contractcode} as your second "on change of" field.

I'm not sure what the "show value" field should be, since you have two sets of dates.

-LB

 

[aomara]

lbass, Thank you for your fast response.

I try it and it shows what I need, but the point is that I need the Date to be in the Y axis, but because you select "In Change Of", the value field (date) must be summarized.

Any idea how to show the date without summarization?

 

[lbass]

But the question is WHICH date? You have the potential for two different dates to fall within the period. You could either use a minimum or a maximum to return one date, but I don't know which date matters to you.

-LB

 

[aomara]

End_Date is more important in this case.
The summary field shows only the "Count" and "Distinct Count", no any other summerization option avaialble.

 

[lbass]

I'm not sure this can be charted like this. You are right that you can only get a count or distincount as a summary field.

The other issue is that you have multiple dates per contract, and the only way you could stacked values is if you referenced the contract date and disregarded the extension dates. While you could use the following as a summary:

minimum({table.startdate},{@origcode})

...assuming you insert a group on {@origcode}, you would not get a display of the date on the axis. If you add this as an "on change of" field, you could get the date on the axis, but then you would lose one of the other "on change of" fields, since it will only take two.

You could explore the use of a Gantt chart, but I don't think you can stack values with that. Not sure.

-LB

 

[aomara]

Any way, Thanks for you concern

 

[lbass]

You might want to check with

http://www.threedgraphics.com.

They used to do the charting for Crystal Reports and still work closely with BO I believe. They might have some chart enhancements that would help with this.

-LB