|
|
|
| Re: How to display multiple COUNT results in bar chart? [message #840845 is a reply to message #840475] |
Tue, 10 April 2012 11:23   |
Eclipse User |
|
|
|
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 #878417 is a reply to message #878381] |
Tue, 29 May 2012 05:08 |
| Eclipse User |
|
|
|
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
|
|
|
Powered by
FUDForum. Page generated in 0.06111 seconds
Home
