InfoCaptor

  1. Home
  2. Docs
  3. InfoCaptor
  4. How to create dashboard filters and prompts
  5. Create data picker control

Create data picker control

Date picker is similar to the Textbox control and contains a calendar helper to pick the date  quickly. 

How to add Date Picker

Just drag the “Date Picker” and drop in the canvas. NOTE: You can directly type the date or pick from the popup wizard

How to change the title 

Double click the widget

 Change the header text to any name you want. 

How to change the id/name Every html control passes information to the entire dashboard using a specific channel. And all the data widgets would selectively listen to these channels for any change in values. So when the user selects a different value in the control, this new value is broadcasted to this channel and every widget that is subscribed to this channel is notified of the change.

The widget will then update its data based on the new channel value. The name of the channel is defined using the widgets name property. 

1. Right click on the Widget
2. Select ‘Edit Properties’
3. Scroll up to the top and find “name”
4. Change the value for the name property and give a descriptive name   

Edit properties

Change the name  
In the name, we just appended with a description “start_date” to indicate that this control is for start date. You can change the entire name but in this case we just chose to append it and keep the other information as it is. 

How to define a date range Usually dates work in pair of begin and end date. On dashboards and reports it is always useful to provide date ranges so users can specify both the start and end dates. We can simply drag a new date and call it an end date, or right click on the current date and select “Duplicate”

Next, rename the title to end date. And we need to also change the channel ID/name for the end date

How to use the Date inside data widgets 

As seen, whatever you type or select the date, the value is passed as a string to the data widget. So within the SQL, we will need to format or convert to appropriate date value depending on the type of database.

We add a grid and change the SQL to show the full name and hire date

  In Excel, you can use the format function on the date for specific formatting. Now we will try to attach the start and end date to the hire date 

 We change the SQL to include a condition on the hire date as below  

select “full name”,format(“hire date”,’dd-mmm-yyyy’) as hiring_date
from [emp_data$]
where “Hire Date”>= format(param<html_datebox_d17_start_date>,’mm/dd/yyyy’)and “Hire Date”  <= format(param<html_datebox_d18_end_date>,’mm/dd/yyyy’)

Here are some variations you may use for different types of database  for the format of mm/dd/yyyy

Microsoft Excel
format(param<channel_name>,’mm/dd/yyyy’)

 Microsoft Access
format(param<channel_name>,’mm/dd/yyyy’) 

Oracle
to_date(param<channel_name>,’mm/dd/yyyy’) 

SQL Server
convert(datetime ,param<channel_name> , 101) 

MySQL
str_to_date(param<channel_name>,’%c/%e/%Y’)  

How to set default value You can set a default value for each control. 

1. Right click on the widget
2. Select “Edit Properties”
3. Go to the top and there is a property “default_value”
4. Set a value  

Date picker has an extremely powerful functionality of providing default values. You can provide default values in variety of formats. Typically this is useful for setting the start and end dates to a certain date boundaries when the dashboard launches. The date boundaries are first day of month, last day of month, first day of week, quarter, year etc The default value for date picker is specified within square brackets as below. 

 Allowed values for Date boundary (D1)

  • fdm : first day of month
  • ldm : last day of month
  • fdw : first day of week
  • ldw : last day of week
  • fdy : first day of year
  • ldy : last day of year
  • fdq : first day of quarter
  • ldq : last day of quarter
  • today : today’s current date

 Allowed values for Modifier Unit (D3)

  • d = day
  • m = month

 Allowed values for Modifier Qty (D2)

  • Any positive or negative integer

Assuming today is 8-Apr-2012,
Dates without modifiers (Note: if D2=0 then D3 is ignored)
1. Get first day of current month (note: D2=0)D1=fdm , D2=0 , D3=d [“fdm” , “0” , “d”] == “2012-04-01”  

2. Get last day of current month (note:  D2=0)D1=ldm , D2=0 , D3=d[“ldm” , “0” , “d”] == “2012-04-30” 

3. Get first day of current year (note:  D2=0)D1=fdy , D2=0 , D3=d[“fdy” , “0” , “d”] == “2012-01-01” 

4. Get first day of current year (note:  D2=0)D1=ldy , D2=0 , D3=d[“ldy” , “0” , “d”] == “2012-12-31” 

5. Get first day of current quarter (note:  D2=0)D1=fdq , D2=0 , D3=d[“fdq” , “0” , “d”] == “2012-04-01” 

6. Get last day of current quarter (note:  D2=0)D1=ldq , D2=0 , D3=d[“ldq” , “0” , “d”] == “2012-06-30” 

7. Get first day of current week (note:  D2=0)D1=fdw , D2=0 , D3=d[“fdw” , “0” , “d”] == “2012-04-08” 

8. Get last day of current week (note:  D2=0)D1=ldw , D2=0 , D3=d[“ldw” , “0” , “d”] == “2012-04-14”

9. Get today’s date (note:  D2=0)D1=today , D2=0 , D3=m[“today” , “0” , “m”] == “2012-04-08”  

Examples with Day manipulation
10. Get first day of current month and add 3 days  (note:  D2=3 and D3 = d, signifying add 3 days )D1=fdm , D2=3 , D3=d[“fdm” , “3” , “d”] == “2012-04-04” 

11. Get last day of current month and subtract 3 days  (note:  D2=-3 and D3 = d, signifying subtract 3 days )D1=ldm , D2=-3 , D3=d[“ldm” , “-3” , “d”] == “2012-04-27” 

12. Get first day of current year and subtract 5 days  (note:  D2=-5 and D3 = d, signifying subtract 5 days )D1=fdy , D2=-5 , D3=d[“fdy” , “-5” , “d”] == “2011-12-27” 

13. Get last day of current year and add 5 days  (note:  D2=5 and D3 = d, signifying add 5 days )D1=ldy , D2=5 , D3=d[“ldy” , “5” , “d”] == “2013-01-05” 

14. Get first day of current quarter and add 5 days  (note:  D2=5 and D3 = d, signifying add 5 days )D1=fdq , D2=5 , D3=d[“fdq” , “5” , “d”] == “2012-04-06” 

15. Get last day of current quarter and minus 5 days  (note:  D2=-5 and D3 = d, signifying minus 5 days )D1=ldq , D2=-5 , D3=d[“ldq” , “-5” , “d”] == “2012-06-25” 

16. Get first day of current week and minus 3 days  (note:  D2=-3 and D3 = d, signifying minus 3 days )D1=fdw , D2=-3 , D3=d[“fdw” , “-3” , “d”] == “2012-04-05” 

17. Get last day of current week and add 5 days  (note:  D2=5 and D3 = d, signifying add 5 days )D1=ldw , D2=5 , D3=d[“ldw” , “5” , “d”] == “2012-04-19” 

18. Get today’s date and go backwards 5 days  (note:  D2=-5 and D3 = d, signifying minus 5 days )D1=today , D2=-5 , D3=d[“today” , “-5” , “d”] == “2012-04-03” 

Examples with Month manipulation

19. Get first day of current month and add 3 months ie go forward 3 months  (note:  D2=3 and D3 = m, signifying add 3 months )D1=fdm , D2=3 , D3=m[“fdm” , “3” , “m”] == “2012-07-01” 

20. Get last day of current month and go backward 3 months  (note:  D2=-3 and D3 = m, signifying subtract 3 months )D1=ldm , D2=-3 , D3=m[“ldm” , “-3” , “m”] == “2012-01-31” 

21. Get first day of current year and minus 5 months  (note:  D2=-5 and D3 = m, signifying minus 5 months )D1=fdy , D2=-5 , D3=m[“fdy” , “-5” , “m”] == “2011-08-01” 

22. Get last day of current year and add 5 months  (note:  D2=5 and D3 = m, signifying add 5 months )D1=ldy , D2=5 , D3=m[“ldy” , “5” , “m”] == “2013-05-31” 

23. Get first day of current quarter and add 5 months  (note:  D2=5 and D3 = m, signifying add 5 months )D1=fdq , D2=5 , D3=m[“fdq” , “5” , “m”] == “2012-09-01” 

24. Get last day of current quarter and minus 5 months  (note:  D2=-5 and D3 = m, signifying minus 5 months )D1=ldq , D2=-5 , D3=m[“ldq” , “-5” , “m”] == “2012-01-30” 

25. Get first day of current week and minus 3 months  (note:  D2=-3 and D3 = m, signifying minus 3 months )D1=fdw , D2=-3 , D3=m[“fdw” , “-3” , “m”] == “2012-01-08” 

26. Get last day of current week and minus 5 months  (note:  D2=-5 and D3 = m, signifying minus 5 months )D1=ldw , D2=5 , D3=m[“ldw” , “5” , “m”] == “2012-09-14” 

27. Get today’s date and minus 5 months  (note:  D2=-5 and D3 = m, signifying minus 5 months )D1=today , D2=-5 , D3=m[“today” , “-5” , “m”] == “2011-11-08”    

Value Prefix and Postfix In the properties, at the very bottom there are two important fields

 These are valuePrefix and valuePostfix By default they are set to a single quotation [ ‘ ] value. In case if you want to pass numeric values or some other value like double quotes etc then you can set the value to empy like”valuePrefix”:”” to pass no quotes around the value. Same for valuePostfix

Was this article helpful to you? Yes No

How can we help?