Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » BIRT » How to display multiple COUNT results in bar chart?(How to set bar chart to display result columns from multi COUNTS)
How to display multiple COUNT results in bar chart? [message #839430] Sun, 08 April 2012 14:00 Go to next message
Dejan Vujanic is currently offline Dejan Vujanic
Messages: 66
Registered: October 2011
Member
I want to create this report in BIRT.
index.php/fa/7813/0/

My SQL expression in Data Set is:

SELECT DISTINCT
(select count(*) FROM STATUSTABLE where date (createdate)>'2012-04-01') AS IN,
(select count(*) FROM STATUSTABLE where date (createdate)<'2012-04-01') AS OUT ,
(select count(*) FROM STATUSTABLE where status='CANCELED') AS CANCELED
FROM STATUSTABLE

Result of SQL is on example:

IN|OUT|CANCELED
210|30|4

How to set bar chart to create this kind of report if this is possible?
How to achieve that y-axis will show me this values and on X axis (probbably the bigest problem) to have this Labels IN , OUT , CANCELED separately on the sam bar chart?

Thanks for help
  • Attachment: count.jpg
    (Size: 16.76KB, Downloaded 857 times)

[Updated on: Sun, 08 April 2012 14:22]

Report message to a moderator

Re: How to display multiple COUNT results in bar chart? [message #840083 is a reply to message #839430] Mon, 09 April 2012 12:34 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Generally the chart engine expects one or more rows per category. For
the chart you want the table would look like

Cat Val
in 210
out 30
canceled 4

You can add values in script but it makes it a bit more complicated.
Look at the script on the attached example.

Jason


On 4/8/2012 2:00 PM, cankovicv wrote:
> I want to create this report in BIRT.
>
>
> My SQL expression in Data Set is:
>
> SELECT DISTINCT
> (select count(*) FROM STATUSTABLE where date (createdate)>'2012-04-01') AS IN,
> (select count(*) FROM STATUSTABLE where date (createdate)<'2012-04-01') AS OUT ,
> (select count(*) FROM STATUSTABLE where status='CANCELED') AS CANCELED
> FROM TICKET
>
> Result of SQL is on example:
>
> IN|OUT|CANCELED
> 210|30|4
>
> How to set bar chart to create this kind of report if this is possible?
> How to achieve that y-axis will show me this values and on X axis (probbably the bigest problem) to have this Labels IN , OUT , CANCELED separately on the sam bar chart?
>
> Thanks for help
Re: How to display multiple COUNT results in bar chart? [message #840475 is a reply to message #840083] Tue, 10 April 2012 02:11 Go to previous messageGo to next message
Dejan Vujanic is currently offline Dejan Vujanic
Messages: 66
Registered: October 2011
Member
Jason as always thanks for your efforts and help.

I have two questions.
1. What do I haave to change to your example report be compatible with my sql expression, because you used default values for result.
I suppose that I have to change OPEN script. How to bind it with my SQL expression?

SELECT DISTINCT
(select count(*) FROM STATUSTABLE where date (createdate)>'2012-04-01') AS IN,
(select count(*) FROM STATUSTABLE where date (createdate)<'2012-04-01') AS OUT ,
(select count(*) FROM STATUSTABLE where status='CANCELED') AS CANCELED
FROM TICKET


you used sourcedata=new Array (new Araay(3));
sourcedata[0][0]=210;
....


will this be good?
sourcedata[0][0]="select count(*) FROM STATUSTABLE where date (createdate)>'2012-04-01')";


or what is the correct expression?

2.Can I somehow center In and Canceled values as it is for Out? I suppose this is because they are first and last element in array so I was just wondering?


Thanks

[Updated on: Tue, 10 April 2012 02:13]

Report message to a moderator

Re: How to display multiple COUNT results in bar chart? [message #840845 is a reply to message #840475] Tue, 10 April 2012 11:23 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

Probably the easiest way to make this work in your example is to drag
your data set to the report canvas. This will create a table. Select
the detail row and choose an oncreate script and enter something like this.

var inval = this.getRowData().getColumnValue("in");
var outval = this.getRowData().getColumnValue("out");
var canval = this.getRowData().getColumnValue("cancelled");
reportContext.setPersistentGlobalVariable("In", inval);
reportContext.setPersistentGlobalVariable("Out", outval);
reportContext.setPersistentGlobalVariable("Can", canval);

Then choose the visibility property for the table and hide the table.
Note that your code will still execute.

Next create a simple bar chart with a 1 in the value expression and
"Test" in the category value. Then put the following script on the chart.

importPackage( Packages.java.util );
importPackage( Packages.org.eclipse.birt.chart.model.type.impl );


function afterDataSetFilled(series, dataSet, icsc)
{

if( series.getClass() == BarSeriesImpl ){

var inv =
parseInt(icsc.getExternalContext().getScriptable().getPersistentGlobalVariable("In"));
var outv =
parseInt(icsc.getExternalContext().getScriptable().getPersistentGlobalVariable("Out"));
var canv =
parseInt(icsc.getExternalContext().getScriptable().getPersistentGlobalVariable("Can"));

var narray1 = new ArrayList( );
narray1.add(inv);
narray1.add(outv);
narray1.add(canv);
dataSet.setValues(narray1);
}else{
var catArray = new ArrayList();
catArray.add("In");
catArray.add("Out");
catArray.add("Cancelled");
dataSet.setValues(catArray);
}

}


See attached example.

Jason


On 4/10/2012 2:11 AM, cankovicv wrote:
> Jason as always thanks for your efforts and help.
>
> I have two questions.
> 1. What do I haave to change to your example report change to be
> compatible with my sql expression, because you used default values for
> result.
> I suppose that I have to change OPEN script. How to bind it with my SQL
> expression?
>
> SELECT DISTINCT
> (select count(*) FROM STATUSTABLE where date (createdate)>'2012-04-01')
> AS IN,
> (select count(*) FROM STATUSTABLE where date (createdate)<'2012-04-01')
> AS OUT ,
> (select count(*) FROM STATUSTABLE where status='CANCELED') AS CANCELED
> FROM TICKET
>
>
> you used sourcedata=new Array (new Araay(3));
> sourcedata[0][0]=210;
> ....
>
>
> will this be good?
> sourcedata[0][0]="select count(*) FROM STATUSTABLE where date
> (createdate)>'2012-04-01')";
>
>
> or what is the correct expression?
>
> 2.Can I somehow cener In and Canceled values as it is for Out? I suppose
> this is because they are first and last element in array so I was just
> wondering?
>
>
> Thanks
Re: How to display multiple COUNT results in bar chart? [message #846658 is a reply to message #840845] Mon, 16 April 2012 10:31 Go to previous messageGo to next message
Dejan Vujanic is currently offline Dejan Vujanic
Messages: 66
Registered: October 2011
Member
Thank you for your help for the millionth time!!! This was really hard functionality. All the best!!!
Re: How to display multiple COUNT results in bar chart? [message #876807 is a reply to message #846658] Fri, 25 May 2012 06:03 Go to previous messageGo to next message
Dejan Vujanic is currently offline Dejan Vujanic
Messages: 66
Registered: October 2011
Member
Hi Jason, unfortunately I have big problem with this.
Everything works OK in Birt,and when I try to preview it shows me and table and chart.
But when I import report on the server (IBM Maximo) and when I try to run report I am having error:
+ ReferenceError: "BarSeriesImpl" is not defined. at line 8 of chart script:''

It is the 8-th line in chart onRender script:
importPackage( Packages.java.util );
importPackage( Packages.org.eclipse.birt.chart.model.type.impl );


function afterDataSetFilled(series, dataSet, icsc)
{

if( series.getClass() == BarSeriesImpl ){


var inv =
parseInt(icsc.getExternalContext().getScriptable().getPersistentGlobalVariable("IN"));
var outv =
parseInt(icsc.getExternalContext().getScriptable().getPersistentGlobalVariable("OUT"));
var canv =
parseInt(icsc.getExternalContext().getScriptable().getPersistentGlobalVariable("CANCELED"));

var narray1 = new ArrayList( );
narray1.add(inv);
narray1.add(outv);
narray1.add(canv);
dataSet.setValues(narray1);
}else{
var catArray = new ArrayList();
catArray.add("IN");
catArray.add("OUT");
catArray.add("CANCELED");
dataSet.setValues(catArray);
}

}


So it can not find this class or what?
How to solve this?
Does I have to import something additionaly to this can be recognized? I found out that my system has the same plugin org.eclipse.birt.chart.engine_2.3.2.r232_20090211.jar in which does exist that org.eclipse.birt.chart.model.type.impl.
I am sending you my birt report file. Its really simple and short report and I dont know why it is working in BIRT and not in the system in which I must import

Thanks you if you can help me

[Updated on: Fri, 25 May 2012 08:13]

Report message to a moderator

Re: How to display multiple COUNT results in bar chart? [message #876932 is a reply to message #876807] Fri, 25 May 2012 10:27 Go to previous messageGo to next message
Jason Weathersby is currently offline Jason Weathersby
Messages: 9167
Registered: July 2009
Senior Member

That should work. You have the right import. Try changing the if like:


gbltst = false;
function afterDataSetFilled(series, dataSet, icsc)
{

if( gbltst ){
var inv =
parseInt(icsc.getExternalContext().getScriptable().getPersistentGlobalVariable("IN"));
var outv =
parseInt(icsc.getExternalContext().getScriptable().getPersistentGlobalVariable("OUT"));
var canv =
parseInt(icsc.getExternalContext().getScriptable().getPersistentGlobalVariable("CANCELED"));

var narray1 = new ArrayList( );
narray1.add(inv);
narray1.add(outv);
narray1.add(canv);
dataSet.setValues(narray1);
}else{
var catArray = new ArrayList();
catArray.add("IN");
catArray.add("OUT");
catArray.add("CANCELED");
dataSet.setValues(catArray);
gbltst = true;
}

}


This event is called first for the category series and that is the else.
Then it is called for each of the y-axis series.

Jason


On 5/25/2012 6:03 AM, Missing name Mising name wrote:
> function afterDataSetFilled(series, dataSet, icsc)
> {
>
> if( series.getClass() == BarSeriesImpl ){
Re: How to display multiple COUNT results in bar chart? [message #876955 is a reply to message #876932] Fri, 25 May 2012 11:34 Go to previous messageGo to next message
Dejan Vujanic is currently offline Dejan Vujanic
Messages: 66
Registered: October 2011
Member
Jason thanks, you saved me!!!!
I can not thank you enough!
I looked everywhere but only you knew the solution!

Just one more question if you know:
Is it possible to I create also and this graphic or I must do it 3 times separately for each service (ADSL,VOIP,IPTV) separately.

index.php/fa/9916/0/

I can get one table with grouped 3 rows and 4 columns.
I tried to create bar chart with your suggestion "Test" for x and 1 for y values and I did it three times because I must create 3 Series (New Series ) on Value Y Series.
But new series will take only different column not and different value for row (ADSL,VOIP,IPTV).
Is it possible to I create this report?
If not then I will create three separate charts.


THANK YOU ONCE AGAIN!!!
  • Attachment: bar.jpg
    (Size: 23.70KB, Downloaded 736 times)
Re: How to display multiple COUNT results in bar chart? [message #878381 is a reply to message #876955] Tue, 29 May 2012 03:30 Go to previous messageGo to next message
Dejan Vujanic is currently offline Dejan Vujanic
Messages: 66
Registered: October 2011
Member
Jason for this chart above, this is similar like the last example that you send me (also above) but I need three bar series. But series take from columns and my values are in row (ADSL,VOIP,IPTV). So is it possible to change your example to show 3 series instead of one- somehow with scripting or that is not possible?
Thanks
Re: How to display multiple COUNT results in bar chart? [message #878417 is a reply to message #878381] Tue, 29 May 2012 05:08 Go to previous message
Tomas Greif is currently offline Tomas Greif
Messages: 53
Registered: September 2010
Member
Hi,

I think you should change your SQL script:

SELECT DISTINCT
(select count(*) FROM STATUSTABLE where date (createdate)>'2012-04-01') AS IN,
(select count(*) FROM STATUSTABLE where date (createdate)<'2012-04-01') AS OUT ,
(select count(*) FROM STATUSTABLE where status='CANCELED') AS CANCELED
FROM STATUSTABLE


I am not sure what database you are using, however it would be much easier if output is in one column rather than 3:

  (select 'In' as staus, count(*) as CNT FROM STATUSTABLE where date (createdate)>'2012-04-01') union all
  (select 'Out' as status, count(*)as CNT FROM STATUSTABLE where date (createdate)<'2012-04-01') union all
  (select 'Cancelled' as status, count(*) as CNT FROM STATUSTABLE where status='CANCELED') 


This is valid SQL in postgres and I am quite sure something similar will work in other database engines as well. Because you are using one table only, you can also use "group by":

SELECT
  CASE
   WHEN STATUS = 'CANCELLED' THEN 'Cancelled'
   WHEN CREATEDATE > '2012-04-01' THEN 'In'
   WHEN CREATEDATE < '2012-04-01' THEN 'Out'
  END,
  COUNT(*) AS CNT
FROM
  STATUSTABLE
GROUP BY
  CASE
   WHEN STATUS = 'CANCELLED' THEN 'Cancelled'
   WHEN CREATEDATE > '2012-04-01' THEN 'In'
   WHEN CREATEDATE < '2012-04-01' THEN 'Out'
  END


(I assume you have some reason for not including '2012-04-01')

With such changes you will be able to create your charts without any further scripting in birt.

Tomas
Previous Topic:Split the results in to Financial Quarters
Next Topic:Hide column in a cross tab
Goto Forum:
  


Current Time: Mon Jul 28 18:32:26 EDT 2014

Powered by FUDForum. Page generated in 0.04074 seconds