|
| Re: How to display multiple COUNT results in bar chart? [message #840083 is a reply to message #839430] |
Mon, 09 April 2012 16:34   |
|
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 06:11 |
 Dejan Vujanic
Messages: 67
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 06: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 15:23 |
|
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 #876807 is a reply to message #846658] |
Fri, 25 May 2012 10:03 |
Dejan Vujanic
Messages: 67
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 12: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 14:27 |
|
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 #878417 is a reply to message #878381] |
Tue, 29 May 2012 09:08 |
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
|
|
|
Powered by
FUDForum. Page generated in 4.59897 seconds
] 
Search
Help
Register
Login
Home
