Parameter in Select declaration of SQL QUERY using JDBC

[allen_ajd3]

Hi i have a problem using a parameter into select declaration

I have created a radio button with a parameter declared. I have to use this parameter to execute the query with a determinated colum(bytes,flows). This parameter is returned with string value

I have this SQL query and consult t.bytes or t.flows but it doesn´t work:

SELECT s.nombre elem, sum(t.${data}) val
FROM TAGR_5M_SUB t, trad_appgroup s
WHERE t.elemento = s.id AND t.id_agregado = 5 AND t.id_vista = ${view}
and t.fecha >= str_to_date(CONCAT(${dateIni},' 00:00'),'%Y-%m-%d %H:%i')
and t.fecha < str_to_date(CONCAT(${dateFin},' 23:59'),'%Y-%m-%d %H:%i')
GROUP BY s.nombre
ORDER BY 2 asc

I tried use CONCAT like in fecha WHERE clause but it wasn´t correct


Any idea to do this?

Thanks

[hansva]

Hey there.

CDA uses prepared statements to execute the SQL queries. and these do not allow you to use parameters in select,group by, order by clause.
so there are 2 options using a union and an extra where clause to select 1 of the 2 unions. or 2 separate queries and change the datasource when changing the radio button.
(you can also look around this forum a bit there are more people who had your problem.)

Greetz,
Hans

[allen_ajd3]

Thanks @hansva.. i would have to change it in the post change of my radio button. Isn´t it?.

But i have read something for the property "render_Chartname.queryDefinition.dataAccessId" but i don´t understand it. I haven´t found more information.

Could you tell me more about it?

Thanks a lot
Alberto

[hansva]

If you open up a javascript debugger and load you dashboard. you will be able to see that the datasource is indeed defined under dataAccessId.
This is a string pointing to the correct CDA datasource (the same string you use in the CDE editor to point to the correct query)
by manipulating that string in the post fetch and then triggering an render_Charname.update() it will use the other query.

[allen_ajd3]

Yes i have seen this declaration:

var render_pie_chart = { type: "cccPieChart", name: "render_pie_chart", chartDefinition: {

width: 400, height: 400, dataAccessId: "ds_pie_chart_bytes",
.............
postFetch: function () { alert("after "+render_pie_chart.dataAccessId); }

I manipulates this string at post change of the Radio button component with this:
function changeData(){
var cadDataSource="ds_bar_chart_"+data;
render_pie_chart.dataAccessId = cadDataSource;
alert(render_pie_chart.dataAccessId);
render_pie_chart.update();
}

The alerts show me that the dataAccessId changes but the result of the chart it´s the same. Always uses the first declaration.

I uses render_Charname.update(). Why is showing the same?

[allen_ajd3]

Thanks for the replies. I change the declaration from postFech to preExecution of the chart:

function changeData(){
var cadDataSource="ds_bar_chart_"+data;
render_bar_chart.chartDefinition.dataAccessId = cadDataSource;
}

And it works. I hope it can help someone.

I ´ve found it here: http://forums.pentaho.com/showthread...in-a-CCC-chart

Thanks