PDA

View Full Version : Several charts with different queries in the same report, help!



tanny
05-24-2007, 09:26 AM
Hi , I need to display several charts in the same page but with a different query for each, so a different dataset. Is it possible to do it with Pentaho Design Studio?
Thanks a lot.

ingo.klose
05-24-2007, 04:25 PM
Hi,

you could have a look at the Dashboard examples. Each item (chart report etc.) is based on a different action sequence. But to implement a dashboard you need to create a new JSP page and call you action sequences from there. You can have a look at :

Pentaho Dashboard Buildinghttp://downloads.sourceforge.net/pentaho/Pentaho_Dashboard_Building-1.5.3.pdf?modtime=1177507326&big_mirror=0
or
Pentaho AJAX Guidehttp://downloads.sourceforge.net/pentaho/Pentaho_AJAX_Guide_1.2.0.pdf?modtime=1172071153&big_mirror=0

You could also experiment with the new subreport features that are part of JFree Report 0.8.8 and Pentaho 1.5.3 I believe, but they are not supported by the report designer, yet.

Hope that helped.

All the best,
Ingo

tanny
05-29-2007, 04:13 AM
Hey thanks a lot, that is exactly what I did, but 35 charts in the same page=> 35 xaction files, but it works anyway.
I have another problem : do you know how to create a stacked chart bar?
Cheers.

ingo.klose
05-29-2007, 04:28 AM
Hi,

you should be able to set a chart attribute in the bar chart definition xml part. I think it is called: <is-stacked>true</is-stacked>

tanny
05-30-2007, 07:41 AM
I tried to use it but I did not get what I want.

Indeed, with the following query
select type1,
type2,
count(call_req.id) "NB"
from
call_req, ctct
where
call_req.group_id = ctct.id
group by type1,type2,
order by type1,type2

and the property <is-stacked>true</is-stacked> I have a chart (see image1) which displays only NB splitted by type1.

But what I want is NB splitted by type 1 AND type2 (see image 2).
Can you help me please?

Thanks a lot.

ingo.klose
05-30-2007, 07:53 AM
Hi,

try changing your query. Something like this should work for sure.

Select
type2,
count all I,
count all R, ...

Basically you need one domain column (type2) and one count column per type1.

All the best,
Ingo

tanny
05-30-2007, 09:30 AM
Hey it works!!! Thank you so much to answer so fast, you're the best.

Best Regards.

tanny
05-30-2007, 09:42 AM
Another little question do you know if it is possible to have the figures on the stacked bar as in image2?
Thanks a lot.

Tanny

xsabatea
08-03-2007, 04:47 AM
Hi,

try changing your query. Something like this should work for sure.

Select
type2,
count all I,
count all R, ...

Basically you need one domain column (type2) and one count column per type1.

All the best,
Ingo

How do you manage your solution if you don't know much items are in type1?

Is it possible to build a crosstab with a report engine and use the result as a dataset for the stackbar chart?

Anyone knows a nice solution?

Thanks.

XS

ingo.klose
08-03-2007, 05:42 AM
Hi,

you can use a SQLLookupRule and a JavascriptRule action to construct the query dynamically.

There is a tech tip that describes the concept in detail and how you can use it to create reports:

http://wiki.pentaho.org/display/COM/Dynamically+Generating+a+Cross+Tab+Report%2C+Part+1
http://wiki.pentaho.org/display/COM/Dynamically+Generating+a+Cross+Tab+Report%2C+Part+2

But in short you need to do the following in your action sequence:

1. Get all Items in type1 with an SQLLookup query (output poarameter = query_resultset in this example)
2. Use the result set in a JavascriptRule to construct the query and pass it on as output parameter



xTabQry =" SELECT type 2";
for (var i=0; i <query_resultset.getRowCount(); i++) {

//Here goes the syntax for the count using query_resultset.getValueAt(i,0)
// e.g xTabQry +=" sum(if(type1 = '" + query_resultset.getValueAt(i,0) +"',1,0)) as metric_" + i;

}
xTabQry +=" FROM ....."



3. Use xTabQry as query string in the actual SQLLookupRule that gets the data for the chart.

All the best,
Ingo

xsabatea
08-03-2007, 08:19 AM
Thanks Ingo!

I'm gonna try your solution.

Right now I'm writing a xaction with the ResultSetCrosstab component.