Bar chart with x-axis based on multiple columns

Hello everyone, Sorry if this is a repeat question I am new to BIRT. I am creating a BIRT (4.2.2) report in RTC where I want to compare the number of tickets submitted every month with the number of tickets resolved every month. My data set has columns such as CreatedDate, ResolvedDate, id, work item count, and some custom columns CreatedMonth, ResolvedMonth, and resolved so that I can use those for the bar chart. I tried having the resolved tickets and created tickets in the same bar graph but I cannot seem to get that to work properly. Please look at the attachment for the charts that I currently have

Find more posts tagged with

Comments

GLO_FR

Hi, as far as I know it's not possible, and my personnal opinon is that this kind of chart/layout is not correct.You can have several Y axis , but you need do have a common X-axis. In your case, I suggest to have data like this[font="'courier new', courier, monospace;"]MONTH | ID | Status |[/font][font="'courier new', courier, monospace;"]

+[/font][font="'courier new', courier, monospace;"]2014-01 | 123 | Created |[/font][font="'courier new', courier, monospace;font-size:14px;"]2014-02 | 421 | Created |[/font][font="'courier new', courier, monospace;font-size:14px;"]2014-03 | 234 | Created |[/font][font="'courier new', courier, monospace;"]2014-01 | 745 | Resolved |[/font][font="'courier new', courier, monospace;font-size:14px;"]2014-02 | 532 | [/font][font="'courier new', courier, monospace;"]Resolved [/font][font="'courier new', courier, monospace;font-size:14px;"]|[/font][font="'courier new', courier, monospace;font-size:14px;"]2014-03 | 888 | [/font][font="'courier new', courier, monospace;"]Resolved [/font][font="'courier new', courier, monospace;font-size:14px;"]|[/font] <div> </div> So on the same X-axis (Month) you will have two series based on Status which will represent the number of ID

screspo

Ok I understand your response but in order to do this I need to fix my data set. Which right now looks something like this: 
[font="'courier new', courier, monospace;"]ID | CREATION DATE | RESOLUTION DATE |[/font][font="'courier new', courier, monospace;"]

+[/font][font="'courier new', courier, monospace;"]123 | 2014-01-10 | 2014-01-20 |[/font][font="'courier new', courier, monospace;font-size:14px;"]692 | 2014-02-12 | 2014-03-25 |[/font][font="'courier new', courier, monospace;"]678 | 2014-02-15 | 2014-02-20 |[/font][font="'courier new', courier, monospace;"]890 | 2014-05-06 | 2014-05-29 |[/font][font="'courier new', courier, monospace;font-size:14px;"]091 | 2014-06-05 | [/font][font="'courier new', courier, monospace;"] 2014-08-30 [/font][font="'courier new', courier, monospace;font-size:14px;"]|[/font][font="'courier new', courier, monospace;font-size:14px;"]328 | 2014-06-23 | [/font][font="'courier new', courier, monospace;"] 2014-12-01 [/font][font="'courier new', courier, monospace;font-size:14px;"]|[/font] In order to try your solution I created two separate data sets one for created tickets and one for resolved tickets now each of those looks similar to this: [font="'courier new', courier, monospace;"]MONTH | ID | Status |[/font][font="'courier new', courier, monospace;"]

+[/font][font="'courier new', courier, monospace;"]2014-01 | 745 | Resolved |[/font][font="'courier new', courier, monospace;font-size:14px;"]2014-02 | 532 | [/font][font="'courier new', courier, monospace;"]Resolved [/font][font="'courier new', courier, monospace;font-size:14px;"]|[/font][font="'courier new', courier, monospace;font-size:14px;"]2014-03 | 888 | [/font][font="'courier new', courier, monospace;"]Resolved [/font][font="'courier new', courier, monospace;font-size:14px;"]|[/font] [font="'courier new', courier, monospace;"]MONTH | ID | Status |[/font][font="'courier new', courier, monospace;"]

+[/font][font="'courier new', courier, monospace;"]2014-01 | 123 | Created |[/font][font="'courier new', courier, monospace;font-size:14px;"]2014-02 | 421 | Created |[/font] I believe now I need to UNION these tables in order to create the report. First is this the right direction? Secondly I looked up that I cannot UNION two datasets in BIRT and I tried inner joins on these tables and that just gives me what I had originally. So do you have a suggestion on how to overcome this issue? 
 
Thanks so much!

GLO_FR

What version of BIRT are you using ?

screspo

I am using BIRT 4.2.2

GLO_FR

If UNION doesn't exist, it's not a problemSolution 1 : Are you using a data base ? If yes, do your union in SQLSolution 2 : Use scripted dataset (example join) I don't have 4.2.2. so I made the example in 3.7.2

screspo

Thanks for the help! I ended up creating a joint data set between my two created and resolved data sets. I did a full outer join on the status columns. Lastly I added some computed columns in the joint data set to represent the Month, WI_ID, and Status. Using those computed columns I was able to create the graph I was looking for.

Is there a way to give you points for helping me solve my problem?

GLO_FR

<blockquote class="ipsBlockquote" data-author="screspo" data-cid="128801" data-time="1403535960"><div>Is there a way to give you points for helping me solve my problem?</div></blockquote> Thanks for asking, but no

That's how the community works : everybody helps each other and gives free help according to the time and the knowledges they have

AMF

@GLO_FR said:
If UNION doesn't exist, it's not a problem

Solution 1 : Are you using a data base ? If yes, do your union in SQL

Solution 2 : Use scripted dataset (example join)

I don't have 4.2.2. so I made the example in 3.7.2

who i pass optional parameter for each union in where statement ??

wwilliams

Here is an example (the dots are the plus sign)
if(params["prefix"].value) {
prefix = "(" + clause + ") or " + clause1 ;
} else {
prefix = " etonum NOT LIKE 'PR%' OR remark NOT LIKE '%PR%'"
}

" ponum, polinenum, orderunit, rfqnum, rfqlinenum, enterdate, REQDELIVERYDATE, linecost, holdstatus, 0 deleted "
" from prline "
" where siteid = 'ATDC' and " + prefix
- " union all"
- " Select prnum, prlineid, 'ATDC' siteid,description, orderqty, unitcost, etonum,itemnum, prlinenum, gldebitacct, remark, finalizeddate,"
" ponum, polinenum, orderunit, rfqnum, rfqlinenum, enterdate, REQDELIVERYDATE, linecost, holdstatus, 1 deleted "
" FROM a_prline"
- " WHERE nvl(A_PRLINE.EAUDITTYPE,'*') = 'D' and " + prefix