We can parse SQl query in an Xaction file to create a parameter in Pentaho dashboard.
1. Create an xaction file like below:
<?xml version="1.0" encoding="UTF-8"?>
<action-sequence>
<name>ListOperator.xaction</name>
<title>%title</title>
<version>1</version>
<logging-level>debug</logging-level>
<documentation>
<author>Mahadevan</author>
<description>%description</description>
<help/>
<result-type>rule</result-type>
<icon>SQL_Datasource.png</icon>
</documentation>
<inputs>
<FROM type="string">
<sources>
<request>FROM</request>
</sources>
</FROM>
<TO type="string">
<sources>
<request>TO</request>
</sources>
</TO>
</inputs>
<outputs>
<rule-result type="result-set"/>
</outputs>
<resources/>
<actions>
<action-definition>
<component-name>SQLLookupRule</component-name>
<action-type>Query For Operator Info</action-type>
<action-inputs>
<FROM type="string"/>
<TO type="string"/>
</action-inputs>
<action-outputs>
<query-result type="result-set" mapping="rule-result"/>
</action-outputs>
<component-definition>
<jndi>Telecount</jndi>
<query><![CDATA[select count(smskey) AS total, Operator AS Operators from vw_telecount
WHERE Createddate >= ('{FROM}') AND Createddate <=('{TO}')
GROUP BY Operator]]></query>
</component-definition>
</action-definition>
</actions>
</action-sequence>
Step 2: Create an object for parameter in dashboard template as shown below:
ListOperators =
{
name: "ListOperators",
type: "selectMulti",
solution: "PentahoDemo",
path: "Dashboards/FilterA/List",
action: "ListOperators.xaction",
listeners:["startDate","endDate"],
parameters: [["FROM","startDate"],["TO","endDate"]],
parameter:"operators",
htmlObject: "ListOperators_Object",
size: "4",
executeAtStart: true,
preExecution:function(){},
postExecution:function(){}
}
3. Call the object in your Dashbord template.
<td valign = "top" style="width: 5%; text-align: left;"><span
style="font-family: Arial;">Operators:</span><br>
</td>
<td valign = "top" style="width: 20%; text-align: left;">
<div valign = "top" id="ListOperators_Object"></div>
4. Update repository and open your dashboard, you can see the parameter.
Thanks
Mahadevan
