Dashboard: How to add Date parameter or range of Dates
How to add date parameters to the Dashboard query
You have a dashboard query for which you want your users to enter a date (From and To) and the user should be able to query for records that fall between those two dates.
Here are the steps we need to follow
We will discuss approaches for MySQL and Oracle databases
The approaches are identical except for the handling of date and time components. If you want to use it for other database then just use the appropriate date functions for that database.
- Create a new parameter (Menu Create à Parameter)
- Parameter Name : From Date
- Display Column : ‘2007-10-15 00:00:00′
- The display column indicates the format in which the user will type the date in this field.
- This is a free form text field so providing a default format like above helps the user to type the correct date
- Type ‘From dual’ in the text area. Dual is a dummy table in MySQL and Oracle. In some databases you may keep this blank
· Similarly create a new Parameter : ToDate
- Now add a ‘GO’ button. Since these date fields are typed parameters, it is not possible to trigger a dashboard refresh automatically. Once the user has typed the date, they can click on the ‘Go’ button to initiate a dashboard or query refresh.
As shown above
- Keep the ‘Parameter Name’ blank
- Type ‘Go’ in the ‘Display Name’ or any other descriptive button name
- Manually Type ‘BLET’ in the ‘Data Type’ property
- Select ‘Y’ in the ‘Run Dynamic Java Script’ property
- Then click on the ‘Dynamic Java Code’ field
- A window will popup as shown below
· Type the following text in it
import InfoPurple.Global;
Global.superParent.refreshDashboard();
· Click OK
· Type ‘null’ in the text area of the button parameter
· Click on ‘Full Apply’
Now link the date parameter to the Portlet
We will try to add the date filter to this chart below
WHERE results.service_date >=
and results.service_date <=
· Right click on the text editor, a parameter list will show up.
· Select the ‘From Date’
· Select the ‘Get Display’ (first value)
· The code will look like below
WHERE results.service_date >= ‘G_PARAM<p36:Display>’ /*’2007-10-1500:00:00′*/
and results.service_date <=
· You may delete the part between /*…*/ or keep for reference.
· In MySQl, use the following syntax to convert a string to a datetime type
WHERE results.service_date >= {ts ‘G_PARAM<p36:Display>’ } /*’2007-10-1500:00:00′*/
and results.service_date <=
· Similarly repeat the steps to apply filter for the To_date
results.service_date >= {ts ‘G_PARAM<p36:Display>’ }
and results.service_date <= {ts ‘G_PARAM<p37:Display>’ }
In Oracle you would need to use the To_date function as below
results.service_date >= to_date(’G_PARAM<p36:Display>’ ,’YYYY-MM-DD HH24:MI:SS’)
and results.service_date <= to_date(’G_PARAM<p37:Display>’ ,’YYYY-MM-DD HH24:MI:SS’)
