PDA

View Full Version : specifying columns for pie chart in CDE



vipin.goyal
05-23-2013, 05:31 AM
I need to create a pie chart that includes selected columns from my data set.
From my sql dataset, I am getting 4 columns. I need to specify the columns to pick for the pie chart in pentaho CDE. How could I do that?

timfu83
05-23-2013, 04:32 PM
Hi vipin.goyal,
you have to set Output options in data sources for the needed query. Index is the number of column, which should be displayed. I mean, it starts with 0 ...so if you add 1, the 2 column values will be displayed.
Regards.

vipin.goyal
05-24-2013, 05:06 AM
Hi timful83,
Thanks for your reply. I guess my questions is not properly explained earlier.
I have a query resulting into 4 columns. 3 columns are to be used for bullet chart and 2 for pie chart. Is there a way to specify column name or index at component level for the respective charts.

timfu83
05-25-2013, 04:41 AM
Hi vipin.goyal,
now I can understand. I was looking for that but I switch to 2 different datasources with same SQL query and different columns. I know, it is not optimal but it works. Maybe someone has an idea how to make it better.

duarte.leao
05-27-2013, 12:57 PM
Can you give an example of such a resultset and of how it would map to each of the charts?

sadakar
08-22-2013, 03:21 AM
Hi Forum,
I need to create 2 bar charts from single query result set of 3 columns.. Is it possible in CDE ?
1,2,3
1,2 ----> Chart1
1,3------> Chart 2

1 is on X-axis and 2,3 are Y axis respectively ?
Can some one please share your knowledge ??

Thanks,
Sadakar

duarte.leao
08-22-2013, 04:56 AM
Hi,

please be aware that specifying the same data source in two components does not make them fetch a single copy of the data set, at the client-side. However, you will benefit from CDA cache.

There are basically two ways of selecting the columns to be used in a chart:


Use the "postFetch" handler to change the data set, before it is given to the chart
Use the CCC chart's readers (http://www.webdetails.pt/ctools/charts/jsdoc/symbols/pvc.options.charts.Chart.html#readers) to select desired columns


Option 1 should be avoided at all costs, cause it requires you to know and hold to the specific format and intricacies of the data source format.

Option 2 is the preferable way.

There's one catch though. The bullet chart is one of those charts whose update to CCC2 was minimal; it doesn't yet support user defined readers.
If, like you stated on your first message, you'd like that all three columns be supplied to the Bullet chart, then you'll have no problem, cause there's no configuration needed for the bullet chart. If, otherwise, only one of the value columns is to be supplied, then option 1 must be used.

Most every other chart supports Option 1.

Assume a data set, with no Series, and the following layout:

| Category | Value1 | Value2 |


Bullet Chart
In the postFetch handler, do:

function (cdaData) {
var valueColIndex = 2;

// Remove metadata column
cdaData.metadata.splice(valueColIndex, 1/*remove count*/);

// Remove resultset column, from each row
cdaData.resultset.forEach(function(row) {
row.splice(valueColIndex, 1/*remove count*/);
});

// Return modified cda data set
return cdaData;
}

Bar Chart
Specify the following CDE properties:


crosstabMode = false
seriesInRows = false -- the default


And, in the preExecution handler:

function() {
this.chartDefinition.readers = [
// First column is the category dimension
// Could also be written as: {names: 'category', indexes: 0},
'category',

// Nth column is the value dimension
{names: 'value', indexes: 1 /* Value1 */},

// Ignore any other columns (prevent auto/ reading for other roles)
{indexes: 2 /* Value2 */}
];
}


If, on the other hand, you'd like to also read Value2, but only to show it in the tooltip, for example, then, just read it into a some other dimension name (not "value2"!).
It won't be automatically bound to the "value" visual role and it will show in the tooltip:

function() {
// This line will do, as every column is being consumed, in order.
this.chartDefinition.readers = ['category, value, businessNameOfValue2'];
}

sadakar
08-22-2013, 06:31 AM
Thanks a lot duarte.leao... You saved my life for the day :o :p :)
Now,I'm able to use Single query with 3 columns result set.
1&2 are on panel 1
1&3 are on panel 2.


Once again thank you a lot :)

sadakar
08-22-2013, 09:50 AM
I used this code in pre excution

Bar Chart -1(Pre Execution)
function() {
this.chartDefinition.readers = [
{names: 'category', indexes: 0 },
{names: 'value', indexes: 1 }
];
}

Bar Chart -2(Pre-Execution)
function() {
this.chartDefinition.readers = [
{names: 'category', indexes: 0 },
{names: 'value', indexes: 2 }
];
}

It is working absolutely fine.. but again a small issue..
On the second chart value is coming as shown in figure for categories along with Y-axis... What is the probable cause for it ? How to overcome this ??

11674

Thank you :)

duarte.leao
08-22-2013, 08:01 PM
I think that what's happening is that the second column (Value1) is being interpreted as a second category column.

To prevent that the Value1 column is automatically read as an additional category dimension (named "category2"), you should, either:

Prevent reading of the column

function() {
this.chartDefinition.readers = [
{names: 'category', indexes: 0 },

// The following prevents column 1 from being read, as we explicitly map it to no dimension...
{indexes: 1},

{names: 'value', indexes: 2 }
];
}

Read it, but name it not like a category
Read it into a dimension whose name is not like "category2", "category3", etc.:

function() {
this.chartDefinition.readers = ["category, myOtherMeasure, value"];
}
This way it won't automatically bind to the "category" role. By default, the "category" role is bound to by every dimension whose name starts with "category", optionally followed by a number.

Read it, but explicitly bind the "category" role
to the first "category" dimension, by specifying the chart option:

categoryRole = "category"


By default, it would be as if «categoryRole = "category, category2"» had been specified.

sadakar
08-24-2013, 03:32 AM
Hi.. It was working successfully . Thanks a lot....

But left with a small issue again.. that is
Order by values on two charts... First chat is capable of taking order by but the second chart is not taking order by..
i.e., bars on 1st chart are displaying in order wise but not on the second chart..

Is there any way to make bars order by with in CDE with out touching the query ??

Thanks

sadakar
08-26-2013, 05:55 AM
Hi duarte.leao,
Could you please give a reply for the order by post for the bar charts.. ?

A B C
abc 10 4
pqr 40 2
xyz 20 1

When use select A,B,C from TABLE order by 2,3.
It only sorts 2nd column... B'z we are having values in two columns.

When I use this single data source for two different bar charts only the first chart bars are coming in order but not the second chart bars..
How can I design query or write some scripting so that the second chart bars also should come in order using single source ?

Thanks,
Sadakar

duarte.leao
08-26-2013, 07:28 AM
Hi,

I've answered the post (http://forums.pentaho.com/showthread.php?148352-Order-by-on-bar-chart-with-out-touching-the-query-in-CDE&p=351538#post351538) about how to change the sort order of data, directly at the CCC chart.

Anyway, I don't quite understand why you're having the problem you describe, in one of the charts.
If you'd like me to look at it, I need a bit more information:

the CDA data set -- get it from the HTTP response of the "?doQuery?", in the debugger
the type of each chart
the options you are supplying to each of the charts -- get these from the debugger's JS console, it's one of the first logged infos, when you activate debugging (specify the following URL query string parameters: «debug=true&debugLevel=5»)

KHauser
08-29-2013, 11:28 AM
Hi there,

I have also a problem with column selection from SQL for more than one Chart. In my case I want to select more than one Column for each result chart.

If you look here I have the following statment:

SELECT
YearMonth
, Col1
, Col2
, Col3
, Col4
FROM table

YearMonth looks like 2013-01 and Col1-4 are integer values.

What I like to do is to build two CCC Linecharts:

one for YearMonth (x-axis), Col1 and Col2 (as values on y-axis) and
one for YearMonth (x-axis) , Col3 and Col4 (as values on y-axis)


Since the PreExecution path seems righter for me I have tried it that way but can't add more than one 'value' entry to the readers-array.

Is there a way how I can implement this or should I use the PostFetch step to delete columns from the result?

Thanks for you help!

Best Karsten

KHauser
09-05-2013, 08:29 AM
I have done it with the PostFetch method and excluding columns from my result dataset.. It also works fine.. Many thanks for the code!

Ashokkumar A
12-16-2014, 04:01 AM
Here i am freshers for pentaho reporting tool. could anyone tell, how to create pie chart in pentaho step by step?? plz