Hello Team,
I am using Helical Insight enterprise edition 4.1 GA. The dashboard is having few filters (like start date and end date). What I want to do is getting the mindate and maxdate from a column of the db and passing it directly to the startdate and enddate dynamically. Is something like that possible?
Thank You,
Netta.
Hello Netta,
Below we will explain about getting minimum and maximum date from sql query and the result of that query is min and max date and that date is set to date filter using HWF in Dashboard. HWF means Helical Workflow. In hwf we write logic to get data from database returns response when we call using ajax. In the HWF only we have written the code also to fetch the mindate and maxdate and pass that to the dashboardvariable name. That dashboardvariable name is used at dashboard to set dashboard variable.
We use ajax call to get hwf response then we set it in the dashboard.
Steps to be followed :
Create a dashboard and add date filters(START_DATE_BEG AND START_DATE_END).
Dashboard View
After completing the steps save the file in respective location by click on save option on top right of dashboard
The JS Code which is put is below:
dashboard.setVariable('START_DATE_BEG','2020-01-01 00:00:00');
dashboard.setVariable('START_DATE_END','2050-12-31 00:00:00');
function updateMinMaxDateParams(){
var formData = '{"report":"Season_Buy_Sheet"}';
var fileName = "setDBfilters.hwf";
var hwfURL = DashboardGlobals.baseUrl+"workflow.html?dir=hwf";
var params = {"fileName":fileName,"formData":formData};
$.ajax({
url: hwfURL,
type: 'POST',
contentType: 'application/x-www-form-urlencoded',
dataType: "json",
xhrFields: {
withCredentials: true
},
data: params,
success: function( data, textStatus, jqXHR) {
var minMaxvalues=data.response.re.response;
var minMaxvalues_arr=minMaxvalues.split(",");
var startDate=minMaxvalues_arr[0].substring(0,11)+" 00:00:00";
var endDate=minMaxvalues_arr[1].substring(0,11)+"00:00:00";
dashboard.setVariable('START_DATE_BEG',startDate);
dashboard.setVariable('START_DATE_END',endDate);
console.log(startDate);
},
error: function(e) {
//console.log(e);
alert(e.statusText);
}
});
}
updateMinMaxDateParams();
HWF:
1.Create a folder as hwf in the hi-repository from backend and create a file with an extension .hwf, in that file write the below code and save the file
<HWF mandatory="true" type="com.helicalinsight.HWFEngine">
<input mandatory="true">
<fetchData default="fetchData" type="helical.String">fetchData</fetchData>
<formData default="" type="helical.String">formData</formData>
</input>
<flow>
<execution alias="getQueryData" id="1" type="job.executeGroovy">
<input>
<ref for="functionName">fetchData</ref>
<ref>formData</ref>
</input>
<code>
<![CDATA[
import net.sf.json.JSONObject;
import net.sf.json.JSONArray;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.CallableStatement;
import java.sql.Types;
public static JSONObject fetchData(JSONObject req, JSONObject responseJson) {
JSONObject formData = JSONObject.fromObject(req.formData);
String query4="SELECT TO_CHAR(MIN(trunc(START_DATE)),'YYYY-MM-DD HH:mm:ss') AS START_DATE,TO_CHAR(MAX(START_DATE),'YYYY-MM-DD HH:mm:ss') AS END_DATE FROM MAROLINAWH.SEASON_BUY_SHEETS sbs";
if(formData.report == "Season_Buy_Sheet"){
responseJson.put("response",getmin_max_dates(query4));
}
return responseJson;
}
public static String getmin_max_dates(String query) {
System.out.println("*****Started*****");
Connection conn = null;
Statement stmt = null;
ResultSet resultSet=null;
String response = "" ;
try {
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
} catch (Exception e) {
System.out.println(e);
}
conn = (Connection) DriverManager.getConnection(
"jdbc:oracle:thin:@marolina-datawarehouse.cdlzplbbmgzg.us-east-1.rds.amazonaws.com:1521:ORCL", "admin", "Marolina2021!!");
System.out.println("Connection is created successfully:");
stmt = (Statement) conn.createStatement();
resultSet=stmt.executeQuery(query);
while (resultSet.next()) {
String startDate = resultSet.getString("START_DATE");
String endDate = resultSet.getString("END_DATE");
response=startDate+","+endDate;
}
System.out.println("getting the min and max date from response..................");
//response = resultSet.getString("START_DATE");
//response="test"
} catch (SQLException excep) {
excep.printStackTrace();
} catch (Exception excep) {
excep.printStackTrace();
} finally {
try {
if (stmt != null)
conn.close();
} catch (SQLException se) {}
try {
if (conn != null)
conn.close();
} catch (SQLException se) {
se.printStackTrace();
}
}
System.out.println("*****Ended*****");
return response;
}
]]>
</code>
<output>re</output>
</execution>
</flow>
<output mandatory="false">
<re type="com.helicalinsight.print">response</re>
</output>
</HWF>
Once we apply js code , it will set default min and max values from DB table :
Thank You,
Helical Insight.