I. Overview
1.1 Requirement
Sometimes we need to get data of the current week, month, quarter or year, but it is too cumbersome to manually set the time range every time. Here we introduce an effective approach that will meet your needs. With just one click on a certain button, the time range will be automatically and accurately found.
Effect 1: Get the start/end date of the week, month, quarter and year for the current system date.
Effect 2: Set a custom date and get the start/end date of the week, month, quarter and year for it.
1.2 Solution
Add Button widgets onto the parameter pane and assign values to specified parameters by adding Click events using JavaScript.
| You will learn |
|---|
|
II. Sample
2.1 Sample 1: Get the start/end date of the week, month, quarter and year for the current date
2.1.1 Report style
1）Create a new template and add two template parameters start_time and end_time:
2）Insert the following formulas into B1 and B2: $start_time、$end_time. The report style is designed as follows:
2.1.2 Settings of the Date widgets
1）Switch to the parameter pane, add two Label widgets and name them Start time: and End time: respectively.
2）Add two Date widgets, name them start_time and end_time respectively, and bind them to the two template parameters:
2.1.3 Settings of the Button widget
1）Add a Button widget and name it This week:
2）Add a Click event to the Button widget:
Input the following JavaScript codes in the Click event:
// Get the first day of the week for the current date
var K1='${=dateInWeek(TODAY(),1)}';
// Get the last day of the week for the current date
var K2='${=dateInWeek(TODAY(),-1)}';
//Get the value of start_time
var state1= this.options.form.getWidgetByName("start_time");
// Get the value of end_time
var state2= this.options.form.getWidgetByName("end_time");
//Assign a new value to start_time , that is, the first day of the week for the current date
state1.setValue(K1);
// Assign a new value to end_time, that is, the last day of the week for the current date
state2.setValue(K2);
3）Similarly, add three Button widgets, name them This month, This season and This year and add Click events to them.
Input the following JavaScript codes in the Click event added to the Button widget named This month:
// Get the first day of the month for the current date
var K1='${=DATEINMONTH(TODAY(),1)}';
// Get the last day of the month for the current date
var K2='${=DATEINMONTH(TODAY(),-1)}';
// Get the value of start_time
var state1= this.options.form.getWidgetByName("start_time");
// Get the value of end_time
var state2= this.options.form.getWidgetByName("end_time");
// Assign a new value to start_time, that is, the first day of the month for the current date
state1.setValue(K1);
// Assign a new value to end_time, that is, the last day of the month for the current date
state2.setValue(K2);
Input the following JavaScript codes in the Click event added to the Button widget named This season:
// Get the first day of the quarter for the current date
var K1='${=dateINQUARTER(TODAY(),1)}';
// Get the last day of the quarter for the current date
var K2='${=dateINQUARTER(TODAY(),-1)}';
// Get the value of start_time
var state1= this.options.form.getWidgetByName("start_time");
// Get the value of end_time
var state2= this.options.form.getWidgetByName("end_time");
// Assign a new value to start_time, that is, the first day of the quarter for the current date
state1.setValue(K1);
// Assign a new value to end_time, that is, the last day of the quarter for the current date
state2.setValue(K2);
Input the following JavaScript codes in the Click event added to the Button widget named This year:
// Get the first day of the year for the current date
var K1='${=DATEINYEAR(TODAY(),1)}';
// Get the last day of the year for the current date
var K2='${=DATEINYEAR(TODAY(),-1)}';
// Get the value of start_time
var state1= this.options.form.getWidgetByName("start_time");
// Get the value of end_time
var state2= this.options.form.getWidgetByName("end_time");
// Assign a new value to start_time, that is, the first day of the year for the current date
state1.setValue(K1);
// Assign a new value to end_time, that is, the last day of the year for the current date
state2.setValue(K2);
4）Add a Query button. The effect is as shown below:
2.1.4 Preview
Save the template and click [Preview]. The preview effect is as shown below:
2.2 Sample 2: Get the start/end date of the week, month, quarter and year for a custom date
2.2.1 Settings of the Date widgets
1）Switch to the parameter pane, add three Date widgets and name them RQ, B_TIME and E_TIME respectively：
2.2.2 Settings of the Button widgets
Add four Button widgets, name them This week, This month, This season and This year and add Click events to them.
Take the Button widget named This week for example:
Input the following JavaScript codes in the Click event added to the Button widget named This week:
// Get the widget value of the parameter RQ and assign a value to RQ
var RQ=this.options.form.getWidgetByName("RQ").getValue();
//Get the first day of the week in which RQ occurs and assign a value to parameter s
var s=FR.remoteEvaluate('FORMAT(DATEINWEEK("'+RQ+'",1),"yyyy-MM-dd")');
// Get the last day of the week in which RQ occurs and assign a value to parameter e
var e=FR.remoteEvaluate('FORMAT(DATEINWEEK("'+RQ+'",-1),"yyyy-MM-dd")');
//Assign a value to B_TIME
this.options.form.getWidgetByName("B_TIME").setValue(s);
// Assign a value to E_TIME
this.options.form.getWidgetByName("E_TIME").setValue(e);
Input the following JavaScript codes in the Click event added to the Button widget named This month:
var RQ=this.options.form.getWidgetByName("RQ").getValue();
var s=FR.remoteEvaluate('FORMAT(DATEINMONTH("'+RQ+'",1),"yyyy-MM-dd")');
var e=FR.remoteEvaluate('FORMAT(DATEINMONTH("'+RQ+'",-1),"yyyy-MM-dd")');
this.options.form.getWidgetByName("B_TIME").setValue(s);
this.options.form.getWidgetByName("E_TIME").setValue(e);
Input the following JavaScript codes in the Click event added to the Button widget named This season:
var RQ=this.options.form.getWidgetByName("RQ").getValue();
var s=FR.remoteEvaluate('FORMAT(DATEINQUARTER("'+RQ+'",1),"yyyy-MM-dd")');
var e=FR.remoteEvaluate('FORMAT(DATEINQUARTER("'+RQ+'",-1),"yyyy-MM-dd")');
this.options.form.getWidgetByName("B_TIME").setValue(s);
this.options.form.getWidgetByName("E_TIME").setValue(e);
Input the following JavaScript codes in the Click event added to the Button widget named This year:
var RQ=this.options.form.getWidgetByName("RQ").getValue();
var s=FR.remoteEvaluate('FORMAT(DATEINYEAR("'+RQ+'",1),"yyyy-MM-dd")');
var e=FR.remoteEvaluate('FORMAT(DATEINYEAR("'+RQ+'",-1),"yyyy-MM-dd")');
this.options.form.getWidgetByName("B_TIME").setValue(s);
this.options.form.getWidgetByName("E_TIME").setValue(e);
2.2.3 Preview
Save the template and click [Preview]. The preview effect is as shown below:
Previous：JS Show or Hide Data by Clicking the Parameter Pane Button
