Creating Parameters in Pentaho Dashboard using SQL Query

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