How to generate chart for sparse data

Let's say I have some data in the database similar to

date count

11/1/2008 1
11/5/2008 2
11/7/2008 4

Suppose today is 11/30/20008, Now I want to generate last 30-day bar chart for those data, it may look like:
11/1/2008 1
11/2/2008 0
11/3/2008 0
11/4/2008 0
11/5/2008 2
11/6/2008 0
11/7/2008 4
....
11/29/2008 0
11/30/2008 0

How can I do it in the BIRT?

Find more posts tagged with

Comments

mwilliams

Hi mengbo,

So you want the bar chart to have all 30 days of the month even though you only have data for 3 days from the month?

mengbo

Is there any way to achieve it using BIRT script or other?

Davolio

In your sql query for the data order it by DESC then do a filter on the date (which is probably your x-axis) for the top n values (assuming your dates are distinct i.e. one row per date). If you do top n such that n = 30 then you'll get the last 30 days. You'll find the filter button next to the data preview in the chart selection window.

If you want to get the last month that'd be a little more work, but you can write your own custom filters in the ('build expression window')in the filter dialogue. using javascript and the javascript date object, if you decide to go that route.

Hope that's slightly helpful ,

Dave

mengbo

Davolio, the problem is that i do not have data for each day. Some day has data, some not. As I mentioned in the example, I only have 3-day data for November, so after the filtering, I still get 3 rows return.

mwilliams

mengbo,

One way might be to come up with a dataset with all dates in it, filter it to get the dates you want and then use a joint dataSet to join your current dataset with the new dataSet, then use a computed column to give values of 0 to the dates with no count.

A non-dynamic way to get the scale at the bottom of the chart to list all dates is in the chart editor under format chart: x-axis: scale, you can set a scale and a step value to include every date. You'll have to uncheck the "Is Category Axis" box on the x-axis page too. You can probably make this way dynamic by finding a way to set the scale in script.

Davolio

ah so you have rows in your data with 0's in them, I didn't realize that, I'd listen to Mike lol i'm kind of a noob at this myself. You could do a filter where top 30 and your expression for n could be row["count"] !=0 to filter out the zeros and give you the last 30 days with data.

Unless the row data is 10/10/2008 0 then you'd have to straight string parse it and that'd get messy.. quick eh well.

I just happen to be working on a report like this for my own company where we grab the data and aggregate it based off of months,days,quarters, etc, I'm going to try to post an example after I finish the ones on my work deadline but that probably won't be for two weeks.

Good luck!

edit... I get it

You want bars of 0 but you can't get them because you don't have data for them and you want the whole month.

so if you have 30 days but only 3 data points you want 27 bars of 0 and 3 bars of values... disregard all my words lol sorry.