Pentaho

 View Only

 How to use a Bar Chart with Pentaho Report?

Claudio Battaglino's profile image
Claudio Battaglino posted 03-28-2023 09:49

Hi,

I have to add into a Pentaho report a Chart like this one:

This should be the representation of a query that returns only one row like this:

SELECT
    ROUND(AVG(es.i01), 2) AS risp1,
    ROUND(AVG(es.i02), 2) AS risp2,
    ROUND(AVG(es.i03), 2) AS risp3,
    ROUND(AVG(es.i04),2) AS risp4,
    ROUND(AVG(es.i05), 2) AS risp5,
    ROUND(AVG(es.i07), 2) AS risp6,
    ROUND(AVG(es.i08), 2) AS risp7,
    ROUND(AVG(es.d06), 2) AS risp8,
    ROUND(AVG(es.d07), 2) AS risp9,
    ROUND(AVG(es.d08), 2) AS risp10,
    ROUND(AVG(es.d09), 2) AS risp11,
    ROUND(AVG(es.d01), 2) AS risp12,
    ROUND(AVG(es.d03), 2) AS risp13,
    ROUND(AVG(es.d04), 2) AS risp14,
    ROUND(AVG(es.d05b), 2) AS risp15
FROM valuta.esami AS es
WHERE
    es.id=<id>

Should I use the Bar Chart or the BarXY chart?

I don't know the difference.

And how should I manage the filed category-column and series of the Chart?

Unfortunately there are not many tutorials on this topic.

Thank you very much.

Claudio B.

John Craig's profile image
John Craig posted 03-31-2023 18:55

Hi Claudio,

The data model or the report itself will need to use calculated measures for the various fields you want ROUND( AVG( ... ) ) applied to. What chart you use isn't an issue to be concerned about as you can try both and see which one looks right to you. There is a complexity with ROUND--there's an Average aggregation available in Pentaho, but I don't believe there is a ROUND function, but you should be able to indicate that the number format should show 2 right of decimal values only and then the rounding should happen for you.

Alternatively, if the number and nature of the columns you want are fixed (and only the ID will change), you could set up a SQL VIEW (without the WHERE clause) and allow the <id> value to be set by a filter by the user (assuming that's appropriate). Then you'd just have Measures defined in your data model for each column.

I'm not clear on what you mean by "the filed category-column and series of the Chart." 

When you say you "have to add to a report" I assume you're talking about an "interactive" type report as opposed to an "analysis" type report. Is that true: a report with multiple sections?

John

Claudio Battaglino's profile image
Claudio Battaglino posted 04-04-2023 03:57

Hi John,
thank you for your answer.
Really my problem is a bit simpler.
I already use the data of the query (that uses avg..) to show a table into the header of the report and it works well.
I'd like to use the same data to show a chart.

In Pentaho Report I have this situation:


I don't know how to configure the chart using the query "ContaValutazioni" to have a report like this:


Where on the X-axis I have the labels: "risp1", "risp2", ecc. and on the Y-axis the value of risp1, risp2, ecc.
I can't find examples about the use of charts.

I know it is a simple chart, but I've tried without success.

Thank you

Claudio B.






John Craig's profile image
John Craig posted 04-04-2023 11:43

Hi Claudio,

Is that the Report Designer you're using? I'm afraid we avoid using that, so I'm not going to be much help--and unfortunately, having replied, you may not get others to look at the question. So, I'm doubly sorry.

My approach would be to create a data model (via Schema Workbench) so that you'd have some control over how Pentaho renders the data. As far as trying to pull it directly from the database, I'm sorry not to be able to help you.

John

Claudio Battaglino's profile image
Claudio Battaglino posted 04-05-2023 06:55

Ok John,
don't worry. 
Yes it is the Pentaho Report Designer.
We sometimes use it to create reports in PDF.

What tool do you use to create the PDF report after creating the data Model?

Claudio B.

Related Content