Bar chart with x-axis based on multiple columns

screspo
edited February 11, 2022 in Analytics #1
<p>Hello everyone,</p><p> </p><p>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.</p><p> </p><p>Please look at the attachment for the charts that I currently have</p><p>

Comments

  • <p>Hi,</p><p> </p><p>as far as I know it's not possible, and my personnal opinon is that this kind of chart/layout is not correct.</p><p>You can have several Y axis , but you need do have a common X-axis.</p><p> </p><p>In your case, I suggest to have data like this</p><p>[font="'courier new', courier, monospace;"]MONTH    | ID   | Status   |[/font]</p><p>[font="'courier new', courier, monospace;"]
    +
    +
    +[/font]</p><p>[font="'courier new', courier, monospace;"]2014-01  | 123  | Created  |[/font]</p><p>[font="'courier new', courier, monospace;font-size:14px;"]2014-02  | 421  | Created  |[/font]</p><p>[font="'courier new', courier, monospace;font-size:14px;"]2014-03  | 234  | Created  |[/font]</p><p>[font="'courier new', courier, monospace;"]2014-01  | 745  | Resolved |[/font]</p><p>[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]</p><p>[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]</p><p> </p><div> </div><p> </p><p>So on the same X-axis (Month) you will have two series based on Status which will represent the number of ID </p>
    Warning No formatter is installed for the format ipb
  • <p>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:<br />
     </p><p>[font="'courier new', courier, monospace;"]ID | CREATION DATE   | RESOLUTION DATE   |[/font]</p><p>[font="'courier new', courier, monospace;"]
    +
    +
    +[/font]</p><p>[font="'courier new', courier, monospace;"]123 | 2014-01-10   | 2014-01-20   |[/font]</p><p>[font="'courier new', courier, monospace;font-size:14px;"]692 | 2014-02-12 |   2014-03-25   |[/font]</p><p>[font="'courier new', courier, monospace;"]678 | 2014-02-15 |   2014-02-20   |[/font]</p><p>[font="'courier new', courier, monospace;"]890 | 2014-05-06   |   2014-05-29   |[/font]</p><p>[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]</p><p>[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]</p><p> </p><p>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:</p><p> </p><p>[font="'courier new', courier, monospace;"]MONTH    | ID   | Status   |[/font]</p><p>[font="'courier new', courier, monospace;"]
    +
    +
    +[/font]</p><p>[font="'courier new', courier, monospace;"]2014-01  | 745  | Resolved |[/font]</p><p>[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]</p><p>[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]</p><p> </p><p>[font="'courier new', courier, monospace;"]MONTH    | ID   | Status   |[/font]</p><p>[font="'courier new', courier, monospace;"]
    +
    +
    +[/font]</p><p>[font="'courier new', courier, monospace;"]2014-01  | 123  | Created  |[/font]</p><p>[font="'courier new', courier, monospace;font-size:14px;"]2014-02  | 421  | Created  |[/font]</p><p> </p><p>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?<br />
    <br />
    Thanks so much!</p>
  • <p>What version of BIRT are you using ?</p>
    Warning No formatter is installed for the format ipb
  • <p>I am using BIRT 4.2.2</p>
  • <p>If UNION doesn't exist, it's not a problem</p><p>Solution 1 : Are you using a data base ? If yes, do your union in SQL</p><p>Solution 2 : Use scripted dataset (example join)</p><p> </p><p> </p><p>I don't have 4.2.2. so I made the example in 3.7.2</p>
    Warning No formatter is installed for the format ipb
  • <p>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?</p>
  • GLO_FR
    edited June 23, 2014 #8
    <blockquote class="ipsBlockquote" data-author="screspo" data-cid="128801" data-time="1403535960"><div><p>Is there a way to give you points for helping me solve my problem?</p></div></blockquote><p> </p><p>Thanks for asking, but no  :)</p><p>That's how the community works : everybody helps each other and gives free help according to the time and the knowledges they have</p>
    Warning No formatter is installed for the format ipb
  • @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
    edited July 26, 2018 #10

    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