Eclipse Community Forums: BIRT » Problem charting datetime

Home » Archived » BIRT » Problem charting datetime

Problem charting datetime [message #904363]

Tue, 28 August 2012 06:23

Eclipse User

Hi,

I've created an oda to query and read responses from a webservice. I can get back dates in the results. I parse these results via:

return new java.sql.Timestamp(time.parse(sourceData.get(m_currentRowId - 1).get(index - 1)).getTime())

(m_currentRowId and index point to where in my sourcedata the date is.)

This produces a list of dates such as:

29 Jan 2012 20:40
27 Jan 2012 23:01
27 Jan 2012 21:53
29 Jan 2012 14:54
28 Jan 2012 09:05
27 Jan 2012 21:47
27 Jan 2012 19:45
30 Jan 2012 17:27
29 Jan 2012 12:09
28 Jan 2012 14:11

When I chart this using date time, grouping by day, a Jan 27th bar appears twice and Jan 28th not at all. By looking at the size of the bar chart I can tell it is thinking some of the 27th Jan are the same as 28th Jan and labelling this 27th Jan. There is then another bar of the rest of 27th Jan.

When I set the column type as Date (so all the times are the same) this problem does not occur.

Any ideas to why this is happening?

Thanks,

James

Re: Problem charting datetime [message #904615 is a reply to message #904363]

Tue, 28 August 2012 16:29

Eclipse User

James

Can you post an image of the issue?

Jason

On 8/28/2012 6:23 AM, James Whitehead wrote:
> Hi,
>
> I've created an oda to query and read responses from a webservice. I can
> get back dates in the results. I parse these results via:
>
> return new java.sql.Timestamp(time.parse(sourceData.get(m_currentRowId -
> 1).get(index - 1)).getTime())
>
> (m_currentRowId and index point to where in my sourcedata the date is.)
>
> This produces a list of dates such as:
>
> 29 Jan 2012 20:40 27 Jan 2012 23:01 27 Jan 2012 21:53 29 Jan 2012 14:54
> 28 Jan 2012 09:05 27 Jan 2012 21:47 27 Jan 2012 19:45 30 Jan 2012 17:27
> 29 Jan 2012 12:09 28 Jan 2012 14:11
>
> When I chart this using date time, grouping by day, a Jan 27th bar
> appears twice and Jan 28th not at all. By looking at the size of the bar
> chart I can tell it is thinking some of the 27th Jan are the same as
> 28th Jan and labelling this 27th Jan. There is then another bar of the
> rest of 27th Jan.
>
> When I set the column type as Date (so all the times are the same) this
> problem does not occur.
>
> Any ideas to why this is happening?
>
> Thanks,
>
> James

Re: Problem charting datetime [message #904859 is a reply to message #904363]

Wed, 29 August 2012 05:32

Eclipse User

Hi Jason,

The print screen was taken of charts using 2 datasources based on identical data and set up, with the only difference being I changed the output column type to from date time to date.

I have also uploaded an excel file of the results shown in Preview results for each (thery are in random order but are the same data points).

Thanks for your help,

James

Attachment: chartProblem.JPG
(Size: 64.08KB, Downloaded 248 times)
Attachment: dates.xls
(Size: 34.50KB, Downloaded 323 times)

Re: Problem charting datetime [message #905042 is a reply to message #904859]

Wed, 29 August 2012 11:29

Eclipse User

James

I am trying to recreate the issue, but it does not look like the dates
are the same in the xls sheet. BTW do you have aggregration on the
x-axis disabled? Is the x-axis type set to date time? What are you
using for the y-axis values?

Jason

On 8/29/2012 5:32 AM, James Whitehead wrote:
> Hi Jason,
>
> The print screen was taken of charts using 2 datasources based on identical data and set up, with the only difference being I changed the output column type to from date time to date.
>
> I have also uploaded an excel file of the results shown in Preview results for each (thery are in random order but are the same data points).
>
> Thanks for your help,
>
> James
>

Re: Problem charting datetime [message #905052 is a reply to message #904363]

Wed, 29 August 2012 11:42

Eclipse User

The data may be shuffled up but the dates are the same (if you get excel to sort them into order using data->sort then you should see they match up, with just the time being different.

The x-axis is set to group type: date time, unit: days, interval: 1, aggregate expression: count

y is set to date again but aggregated by count.

Thanks for your help, I have no idea what is going on.

Re: Problem charting datetime [message #905074 is a reply to message #905052]

Wed, 29 August 2012 12:27

Eclipse User

James

I dumped your dates to a csv and I get the exact same charts when I
change the x and y values to be date or datetime. Are you setting the
data types in the oda correctly?

Jason

On 8/29/2012 11:42 AM, James Whitehead wrote:
> The data may be shuffled up but the dates are the same (if you get excel
> to sort them into order using data->sort then you should see they match
> up, with just the time being different.
>
> The x-axis is set to group type: date time, unit: days, interval: 1,
> aggregate expression: count
> y is set to date again but aggregated by count.
>
> Thanks for your help, I have no idea what is going on.

Re: Problem charting datetime [message #905480 is a reply to message #904363]

Thu, 30 August 2012 09:05

Eclipse User

I am setting them via the code fragments:

public Timestamp getTimestamp(int index) throws OdaException {
try {
//retured as sql since this is what this class is required to return to BIRT
return new java.sql.Timestamp(time.parse(sourceData.get(m_currentRowId - 1).get(index - 1)).getTime());
} catch (ParseException e) {
e.printStackTrace();
return null;
}
}

and

public int getColumnType(int index) throws OdaException {
String columnName = sourceData.get(0).get(index - 1);

if (columnName.equalsIgnoreCase("DocID") || columnName.equalsIgnoreCase("ShardID")) {
return java.sql.Types.INTEGER;
}
if (columnName.equalsIgnoreCase("ImportID")) {
return java.sql.Types.BIGINT;
}
if (columnName.equalsIgnoreCase("Score")) {
return java.sql.Types.DOUBLE;
}
if (columnName.equalsIgnoreCase("Date")) {
return java.sql.Types.TIMESTAMP;
}
else {
return java.sql.Types.CHAR;
}
}

It is showing up as date time in the output column window of the data set, so it seems as though it is set properly...

Re: Problem charting datetime [message #905598 is a reply to message #905480]

Thu, 30 August 2012 13:56

Eclipse User

James

I am not sure what is happening with this. In one oda I worked on I
stored all dates as Java date millis using get time and when I need to
convert I did:

public Timestamp getTimestamp( int index ) throws OdaException
{
try{
return new Timestamp(Long.parseLong( getString( index ) ));
}catch (Exception e){
return null;
}
}

Did you try storing the dates in a csv file. Then create the report
off the csv. I would like you to try this to verify you get the same
results as I did.

Jason

On 8/30/2012 9:05 AM, James Whitehead wrote:
> I am setting them via the code fragments:
>
> public Timestamp getTimestamp(int index) throws OdaException {
> try {
> //retured as sql since this is what this class is required
> to return to BIRT
> return new
> java.sql.Timestamp(time.parse(sourceData.get(m_currentRowId -
> 1).get(index - 1)).getTime());
> } catch (ParseException e) {
> e.printStackTrace();
> return null;
> }
> }
>
> and
> public int getColumnType(int index) throws OdaException {
> String columnName = sourceData.get(0).get(index - 1);
>
> if (columnName.equalsIgnoreCase("DocID") ||
> columnName.equalsIgnoreCase("ShardID")) {
> return java.sql.Types.INTEGER;
> }
> if (columnName.equalsIgnoreCase("ImportID")) {
> return java.sql.Types.BIGINT;
> }
> if (columnName.equalsIgnoreCase("Score")) {
> return java.sql.Types.DOUBLE;
> } if (columnName.equalsIgnoreCase("Date")) {
> return java.sql.Types.TIMESTAMP;
> }
> else {
> return java.sql.Types.CHAR;
> }
> }
>
> It is showing up as date time in the output column window of the data
> set, so it seems as though it is set properly...

Re: Problem charting datetime [message #905861 is a reply to message #905598]

Fri, 31 August 2012 04:06

Eclipse User

It still happens when I use the .csv datetime column(see attched) but not when I use the date column (the same as what happens with my oda). So this seems to suggest it isn't a preoblem with my oda.

What in BIRT could be causing this?

Thanks,

James

Attachment: date-time-graph.JPG
(Size: 54.16KB, Downloaded 234 times)

[Updated on: Fri, 31 August 2012 04:07] by Moderator

Re: Problem charting datetime [message #905935 is a reply to message #905861]

Fri, 31 August 2012 06:35

Eclipse User

I just tried this with a fresh install of eclipse and BIRT and still have the same problem..

Re: Problem charting datetime [message #906108 is a reply to message #905935]

Fri, 31 August 2012 12:26

Eclipse User

Can you send me a report with the csv that shows the issue?

Jason

On 8/31/2012 6:35 AM, James Whitehead wrote:
> I just tried this with a fresh install of eclipse and BIRT and still
> have the same problem..

Re: Problem charting datetime [message #907048 is a reply to message #904363]

Mon, 03 September 2012 04:03

Eclipse User

Hi, I have included my .csv too.

Thanks for your help,

James

Attachment: new_report_3.rptdesign
(Size: 43.75KB, Downloaded 296 times)
Attachment: dates.csv
(Size: 3.43KB, Downloaded 184 times)

Re: Problem charting datetime [message #908020 is a reply to message #907048]

Tue, 04 September 2012 10:50

Eclipse User

James

For some reason I can not read the report. Can you zip it an the data
and email to me at jasonweathersby at windstream dot net?

Jason

On 9/3/2012 4:03 AM, James Whitehead wrote:
> Hi, I have included my .csv too.
>
> Thanks for your help,
>
> James
>

Re: Problem charting datetime [message #908066 is a reply to message #908020]

Tue, 04 September 2012 12:31

Eclipse User

sent, thanks for the help

Re: Problem charting datetime [message #908075 is a reply to message #908066]

Tue, 04 September 2012 12:46

Eclipse User

James

I did not get the email?

Jason

On 9/4/2012 12:31 PM, James Whitehead wrote:
> sent, thanks for the help

Previous Topic:	Applet jar requirements for BIRT (4.2) ReportEngine
Next Topic:	[chart]Mixing stacked and unstacked bars

Goto Forum:

-=] Back to Top [=-

Current Time: Sun May 11 12:24:02 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter