Aggregating Combo Chart?

I have an interesting problem and I'm not sure how to accomplish it within BIRT.

I have 2 MySQL tables. 1 has issues, the other is a transition log. As an issue is transitioned through its states, an entry in the transition log is created, capturing the state transitioned into and the date it was transitioned. There are 3 states: assigned, resolved, and closed.

There are 2 charts I'd like to create based upon this information. The wish is to have them superimposed on top of each other also.

The first chart is basically a simple stacked bar chart. For each month across, I want to see the number of assigned, resolved, and closed issues for that month. That chart seemed pretty easy to setup. The X series is my transition date, the y is the count of the issues, and the Y grouping is based upon the state transitioned to.

The second chart seems to be a bit more complicated. The second chart is a line graph, where for each month, I want to display the number of currently open issues (resolved+assigned). This should be a running total where this months total = last months total + new resolved + new assigned - new closed.

So for the second chart, the X series is the event date again, but the Y series is a ???, i guess that's where I get stuck. I know what I want, I just don't know how to express what I want to the tool. It does not appear like I can put aggregation values inside of a chart.

Anyone have ideas on where to go from here?

Thanks

Jeff

Find more posts tagged with

Comments

mwilliams

Hi Jeff,

Can you post a couple simple flat files of sample data to represent your two datasets and a description of what the chart should look like? This way I can mess around with it and see if I can figure something out to help you.

kaiserfro

Thanks for the reply!

Ok, I attached a sample issues list. I didn't bother sending 2 separate tables, since the dataset I'm trying to use is already joined, so the data in this sample csv file is already joined as well. There are 4 columns, problem number, crstatus, state, and event_date. Problem number and crstatus are from the problem table. State and event date are fields from the transition log table. What you'll see in the data is the same problem over and over, since that problem may have transitioned multiple times. crstatus is the current state at the time I pulled the data, where state is the state of the problem at the event_date. Event_date is a Unix timestamp. I added a event_dt computed field to my dataset where event_dt = new Date(row["event_date"]*1000).

As far as the charts:
Chart 1 - Bar chart
1) Simple stacked bar chart.
2) X axis = time, expressed as MMM yy. MAR 09. This date is the event date.
3) Y axis = the count of unique problem_numbers for each state (assigned, resolved, closed) I want these stacked, preferrably in that order with closed on top.

Chart 2 - Line chart
1) Simple line chart
2) X axis - time, again expressed as MMM yy. Again the event date across here.
3) Y axis - The instantaneous count of the unique problems in the assigned plus resolved states in that month.
Basically, I want to see a line that shows that problems are being resolved. Hence the line should go up if the sum of new assigned and new resolved for the month are greater than the newly closed problems. And the line should go down if the assigned + resolved are less than the closed problems.
Another way to express it, if I were to take each problem and chart them on a Gantt chart, where assigned was the initial creation date and the start of the "line" and the closed was the end of the line, for a given month, i would draw a line that took a cross section of the gantt chart, then count the number of lines i cut across.

Final chart - Combination Bar/Line Chart some how combining the two charts into a single chart...if thats possible.

Let me know if there is anything further you would like answered.

Thanks

Jeff