Chart on Date Range

[jay@bplogix.com]

Has the ability to draw a chart with a date range on the X-axis been added? Basically, with my input data, I do not know the dates ahead of time. A simple example of the input date is:

Date Value
Jan/1/2013 10
Jan/3/2013 15
Jan/9/2013 33
Jan/13/2013 29

I would like a line chart that plots the values of each date, but also plots a 0 value for the "empty" days (Jan 2, Jan 4, etc). Is this possible?

Thanks

[HighAley]

Hello.

You should use the Scatter Line Series. Please, look at the attached report template.

Report.mrt

(26.93 KiB) Downloaded 341 times

Thank you.

[jay@bplogix.com]

Great, thanks.



Is there any way to show the Scatter Line Series by month? Basically I want to average all the values in January, Feb, Mar, etc, and then show the Scatter Line Series of each month.

I tried creating a DateTime calculated column on the input datasource with a formula of:
DateSerial(eos_chartsamp.StartTime.Year,eos_chartsamp.StartTime.Month,1)
to create a DateTime column normalized to the first of each month.

Then I tried to create a DataSource from DataSource grouping on the calcuated column, but returns Error #2032 when I run the report with the grouping on the calculated column.

Is this a limitation of the "Group" for the new data source? Is there another way to average the values in each month?

Thanks.

[HighAley]

Hello.

Could you send us a sample report template with data which reproduces the issue for analysis?

Thank you.

[jay@bplogix.com]

I created a sample from the .MRT you attached earlier. Unfortunately I cannot reproduce the Error #2032 yet, but I did run into other problems. So is grouping by a "calculated column" supported?

I created a new column, Orders.OrderMonth which is middle of the month that the order was placed.
DateSerial(Orders.OrderDate.Year,Orders.OrderDate.Month,15)

I then created a DataSource from Other DataSource (named DataSource), grouping by Orders.OrderMonth. Here are the problems I ran into:

1. The grouping did not seem to work correctly. There are duplicate entries of the same value in the results. Look at the DataBand2 ... notice that most months have multiple values ... instead they should have been "grouped" into a single row, right?

2. Even though in the new datasource I specified a Sort as OrderMonth, the DataBand and chart that use this new datasource do not "listen" to that sort. You have to specifically set the sort in the DataBand and Chart to actually sort the data from this datasource.

3. Notice the labels in the second chart. There are two issues ... first is that the labels do not align vertically with the actual month. Additionally, there are duplicate months in the labels (sometimes months in the labels are skipped, sometimes they are duplicated).

[jay@bplogix.com]

Here is the MRT file referenced above ...

[HighAley]

Hello.

Unfortunately, the Calculated columns can't be used in the Grouping or Sorting.

Thank you.

[jay@bplogix.com]

That is unfortunate. Is there any plan to support Calculated columns in Grouping? It seems to "almost" work. Without it, it requires us to modify the SQL tables or VIEWS to do these calucations, which is very difficult to do.
thx

[HighAley]

Hello.

It's better to use SQL query to get such data. You need to create a sample query with grouping.

Thank you.