Friday, 3 May 2013

How to create dashboard in pentaho community edition

 create action sequence file and define databse connection and query according to requirement.



<?xml version="1.0" encoding="UTF-8"?>
<action-sequence>
  <title>ABHISHEK DASH</title>
  <version>1</version>
  <logging-level>ERROR</logging-level>
  <documentation>
    <author/>
    <description>Empty blank action sequence document</description>
    <help/>
    <result-type/>
    <icon/>
  </documentation>

  <inputs>
    <principalRoles type="string-list">
      <sources>
        <security>principalRoles</security>
      </sources>
    </principalRoles>
    <UserRole type="string">
      <sources>
        <request>UserRole</request>
        <runtime>UserRole</runtime>
      </sources>
      <default-value><![CDATA[corporate]]></default-value>
    </UserRole>
    <DirectorID type="string">
      <sources>
        <request>DirectorID</request>
        <runtime>DirectorID</runtime>
      </sources>
      <default-value><![CDATA[1234]]></default-value>
    </DirectorID>
    <TerritoryID type="string">
      <sources>
        <request>TerritoryID</request>
        <runtime>TerritoryID</runtime>
      </sources>
      <default-value><![CDATA[95]]></default-value>
    </TerritoryID>
    <SalesID type="string">
      <sources>
        <request>SalesID</request>
        <runtime>SalesID</runtime>
      </sources>
      <default-value><![CDATA[1233]]></default-value>
    </SalesID>
  </inputs>

  <outputs>
    <out type="string">
      <destinations>
        <response>content</response>
      </destinations>
    </out>
  </outputs>

  <resources/>
 
  <actions>     
    <actions> 
      <action-definition>
        <component-name>SQLLookupRule</component-name>
        <action-type>Relational</action-type>
        <action-inputs>
          <SalesID type="string"/>
        </action-inputs>
        <action-outputs>
          <query-result type="result-set" mapping="query_result"/>
        </action-outputs>
        <component-definition>
          <live><![CDATA[false]]></live>
          <driver><![CDATA[net.sourceforge.jdts.jdbc.Driver]]></driver>
          <connection><![CDATA[jdbc:jtds:sqlserver://localhost:1433/pentaho;instance=abhi]]></connection>
          <user-id><![CDATA[abhi]]></user-id>
          <password><![CDATA[your password]]></password>
          <query><![CDATA[execute dbo.policyprocess 0;]]></query>
        </component-definition>
      </action-definition>

    </actions>

    <action-definition>
      <component-name>SQLLookupRule</component-name>
      <action-type>Relational</action-type>
      <action-outputs>
        <query-result type="result-set" mapping="query_result_TopRepNewBusiness1"/>
        </action-outputs>
        <component-definition>
          <live><![CDATA[false]]></live>
          <driver><![CDATA[net.sourceforge.jdts.jdbc.Driver]]></driver>
          <connection><![CDATA[jdbc:jtds:sqlserver://localhost:1433/pentaho;instance=abhi]]></connection>
          <user-id><![CDATA[abhi]]></user-id>
          <password><![CDATA[your password]]></password>
          <query><![CDATA[execute dbo.policyprocess 0;]]></query>
        </component-definition>
      </action-definition>
    </action-definition>
  
      <action-definition>
      <component-name>SQLLookupRule</component-name>
      <action-type>Relational</action-type>
      <action-outputs>
        <query-result type="result-set" mapping="query_result_topterritory_c"/>
  
        </action-outputs>
        <component-definition>
          <live><![CDATA[false]]></live>
          <driver><![CDATA[net.sourceforge.jdts.jdbc.Driver]]></driver>
          <connection><![CDATA[jdbc:jtds:sqlserver://localhost:1433/pentaho;instance=abhi]]></connection>
          <user-id><![CDATA[abhi]]></user-id>
          <password><![CDATA[your password]]></password>
          <query><![CDATA[execute dbo.policyprocess 0;]]></query>
        </component-definition>
      </action-definition>
    </action-definition>
  
  
  
     <action-definition>
      <component-name>SQLLookupRule</component-name>
      <action-type>Relational</action-type>
      <action-outputs>
        <query-result type="result-set" mapping="query_result_topterritory_t"/>
       </action-outputs>
        <component-definition>
          <live><![CDATA[false]]></live>
          <driver><![CDATA[net.sourceforge.jdts.jdbc.Driver]]></driver>
          <connection><![CDATA[jdbc:jtds:sqlserver://localhost:1433/pentaho;instance=abhi]]></connection>
          <user-id><![CDATA[abhi]]></user-id>
          <password><![CDATA[your password]]></password>
          <query><![CDATA[execute dbo.policyprocess 0;]]></query>
        </component-definition>
      </action-definition>
    </action-definition>
  
    <action-definition>
      <component-name>SQLLookupRule</component-name>
      <action-type>Relational</action-type>
      <action-outputs>
        <query-result type="result-set" mapping="query_result_TopTerritoryNewBusiness_current"/>
      </action-outputs>
   
        <component-definition>
          <live><![CDATA[false]]></live>
          <driver><![CDATA[net.sourceforge.jdts.jdbc.Driver]]></driver>
          <connection><![CDATA[jdbc:jtds:sqlserver://localhost:1433/pentaho;instance=abhi]]></connection>
          <user-id><![CDATA[abhi]]></user-id>
          <password><![CDATA[your password]]></password>
          <query><![CDATA[eselect city,country from policy;]]></query>
        </component-definition>
      </action-definition>
    </action-definition>
  
    <action-definition>
      <component-name>SQLLookupRule</component-name>
      <action-type>Relational</action-type>
      <action-outputs>
        <query-result type="result-set" mapping="query_result_TopTerritoryNewBusiness_previous"/>
      </action-outputs>
       <component-definition>
          <live><![CDATA[false]]></live>
          <driver><![CDATA[net.sourceforge.jdts.jdbc.Driver]]></driver>
          <connection><![CDATA[jdbc:jtds:sqlserver://localhost:1433/pentaho;instance=abhi]]></connection>
          <user-id><![CDATA[abhi]]></user-id>
          <password><![CDATA[your password]]></password>
          <query><![CDATA[eselect city,country from policy;]]></query>
        </component-definition>
      </action-definition>
    </action-definition>
    </action-definition>
  
    <action-definition>
      <component-name>JavascriptRule</component-name>
      <action-type>JavaScript</action-type>
      <action-inputs>
        <query_result_TopRepNewBusiness1 type="result-set"/>
          <query_result_TopTerritoryNewBusiness_current type="result-set"/>
          <query_result_TopTerritoryNewBusiness_previous type="result-set"/>
          <query_result_topterritory_c type="result-set"/>
          <query_result_topterritory_t type="result-set"/>
        <query_result type="result-set"/>
      </action-inputs>
      <action-outputs>
        <thedata type="string"/>
        <rowCount type="integer"/>
        <syCount type="integer"/>
      </action-outputs>
      <component-definition>
        <script><![CDATA[thedata='';
rowCount=query_result.getRowCount();
var abhi=[];

var Current_Year= query_result_TopTerritoryNewBusiness_current.getValueAt(0,5);
var previous_year= query_result_TopTerritoryNewBusiness_previous.getValueAt(0,5);


abhi.push("<table id='myTable' border='2' class='tablesorter' width='300px !important;'>");
abhi.push('<tr align="Center" !important;><th>heading</th>');
abhi.push('<th align="Center" !important;>heading</th>');
abhi.push('<th align="Center" !important;>heading</th>');
abhi.push('<th align="Center" !important;>heading</th>');
abhi.push('<th colspan="2" align="Center" !important;>heading</th>');
abhi.push('<tr align="Center" !important;><th>'+Current_Year+'</th>');
abhi.push('<th align="Center" !important;>'+Current_Year+'</th>');
abhi.push('<th align="Center" !important;>'+Current_Year+'</th>');
abhi.push('<th align="Center" !important;>'+Current_Year+'</th>');
abhi.push('<th align="Center" !important;>'+Current_Year+'</th>');
abhi.push('<th align="Center" !important;>'+previous_year+'</th> </tr>');

for(row=0;row<rowCount;row++)
{
           var Rep_name=query_result.getValueAt(row,0);
           var Tname=query_result.getValueAt(row,1);
           var Dollar=query_result.getValueAt(row,2);
         
           var Rep_name1=query_result_TopRepNewBusiness1.getValueAt(row,0);
           var Tname1=query_result_TopRepNewBusiness1.getValueAt(row,1);
           var Dollar1=query_result_TopRepNewBusiness1.getValueAt(row,2);
         
           var Rep_name2=query_result_TopTerritoryNewBusiness_current.getValueAt(row,0);
           var seniorname_cur=query_result_TopTerritoryNewBusiness_current.getValueAt(row,2);
           var salesAmount=query_result_TopTerritoryNewBusiness_current.getValueAt(row,4);
         
           var Rep_name3=query_result_TopTerritoryNewBusiness_previous.getValueAt(row,0);
           var seniorname_pre=query_result_TopTerritoryNewBusiness_previous.getValueAt(row,2);
           var Sales_Amount_previous=query_result_TopTerritoryNewBusiness_previous.getValueAt(row,4);
      
           var seniorname_c=query_result_topterritory_c.getValueAt(row,1);
           var TerritoryId_c=query_result_topterritory_c.getValueAt(row,0);
      
           var seniorname_t=query_result_topterritory_t.getValueAt(row,1);
           var TerritoryId_t=query_result_topterritory_t.getValueAt(row,0);
         
           var Sales_Amount_current_k = parseFloat(salesAmount/1000);
           var Sales_Amount_previous_k = parseFloat(Sales_Amount_previous/1000);
         
           var Amount_k = parseFloat(Dollar/1000);
           var Amount1_k = parseFloat(Dollar1/1000);
    
    {
         
           abhi.push('<tr><td>'+Rep_name+'&nbsp;-&nbsp;$'+parseFloat(Amount_k).toFixed(2).replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1,")+'k&nbsp;('+Tname+')</td>');
           abhi.push('<td>'+Rep_name1+'&nbsp;-$'+parseFloat(Amount1_k).toFixed(2).replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1,")+'k&nbsp;('+Tname1+')</td>');
           abhi.push('<td>'+seniorname_c+'&nbsp;-&nbsp;'+TerritoryId_c+'</td>');
           abhi.push('<td>'+seniorname_t+'&nbsp;-&nbsp;'+TerritoryId_t+'</td>');
           abhi.push('<td>'+seniorname_cur+'&nbsp;-&nbsp;$'+parseFloat(Sales_Amount_current_k).toFixed(2).replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1,")+'k</td>');
           abhi.push('<td>'+seniorname_pre+'&nbsp;-&nbsp;$'+parseFloat(Sales_Amount_previous_k).toFixed(2).replace(/(\d)(?=(\d\d\d)+(?!\d))/g, "$1,")+'k</td></tr>');
    }
}
abhi.push('</table>');
thedata=abhi.join('');  
]]></script>
      </component-definition>
    </action-definition>
 
    <action-definition>
      <component-name>TemplateComponent</component-name>
      <action-type>Message Template</action-type>
      <action-inputs>
        <thedata type="string"/>
      </action-inputs>
      <action-outputs>
        <output-message type="string" mapping="out"/>
      </action-outputs>
      <component-definition>
        <template><![CDATA[<html>
  <head>   
    <title>sabhishek</title>   
       <link rel="stylesheet" type="text/css" href="/pentaho/js/jquerytablesorter/themes/blue/style.css">
    <SCRIPT type="text/javascript" src="/pentaho/js/jquerytablesorter/jquery-latest.js"></SCRIPT>
      <script type="text/javascript" src="/pentaho/js/jquerytablesorter/jquery.tablesorter.js"></script>
      <!--    <script type="text/javascript" src="/pentaho/js/jquerytablesorter/jquery.tablesorter.widgets.js"></script> -->
    <script type="text/javascript">
</script>

</head>
  <body>   
 {thedata}
 </body>
</html>]]></template>
      </component-definition>
    </action-definition>

  </actions>
</action-sequence>
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)