How to Create Time-based Date Filters in Bold Reports
You do not have predefined options for selecting the current week, previous week, current month, previous month, current year, or previous year in Bold Reports. However, you can fulfill this requirement by utilizing Date Filters and expressions for time-based filtering in Bold Reports. These features allow users to define specific date ranges for filtering data. Date filters are employed to filter data based on specific dates or date ranges. They can be applied in a report or utilized to create a custom report that displays data only for a specific time period. On the other hand, expressions are utilized for creating customized calculations and formatting data. They enable the addition of new fields, modification of data display, and creation of custom calculations in a report. The following examples showcase how to implement various time-based filters using expressions.
To use date filters and expressions in Bold Reports, you will need to create a parameter for the date or date range that you want to filter by. You can then use the expression to filter the data in the report.
Getting the Today:
To get data for the today, you can use the following expression:
=Today()
Getting the Yesterday:
To get data for the yesterday, you can use the following expression:
=DateAdd("d", -1, Today())
Getting the Last Seven Days:
To get data for the last seven days, you can use the following expression:
=DateAdd("d", -7, Today())
Getting the Last 30 Days:
To get data for the last seven days, you can use the following expression:
=DateAdd("d", -30, Today())
Current Month First Date:
To get data for the first date of the current month, you can use the following expression:
=DateAdd("m", 0, DateSerial(year(Today), month(Today), 1))
Current Month Last Date:
To get data for the last date of the current month, you can use the following expression:
=DateAdd("m", 1, DateSerial(year(Today), month(Today), 0))
Last Month First Date:
To get data for the first date of the previous month, you can use the following expression:
=DateAdd("m", -1, DateSerial(year(Today), month(Today), 1))
Last Month Last Date:
To get data for the last date of the previous month, you can use the following expression:
=DateAdd("m", 0, DateSerial(year(Today), month(Today), 0))
Current Year First Date:
To get data for the first date of the current year, you can use the following expression:
=DateSerial(Year(Today), 1, 1)
Current Year Last Date:
To get data for the last date of the current year, you can use the following expression:
=DateSerial(Year(Today), 12, 31)
Last Year First Date:
To get data for the first date of the previous year, you can use the following expression:
=DateAdd("yyyy", -1, DateSerial(year(Today), 1, 1))
Last Year Last Date:
To get data for the last date of the previous year, you can use the following expression:
=DateAdd("d", -1, DateSerial(year(Today), 1, 1))
By incorporating these expressions into your reports, you can effectively filter data based on specific time periods, such as today, yesterday, the last seven days, last thirty days, current month, or previous month, current year, or previous year. Follow the below steps for how to use it reports.
-
Create a parameter “DateFilterParam” of type “DateTime” and specify the available values as in the following snap.
-
Drag and drop a text-boxes for StartDateand EndDate and set the following expressions in it.
StartDate:
=Switch(Parameters!DateFilterParam.Value = "Today", Today(),
Parameters!DateFilterParam.Value = "Yesterday", DateAdd("d", -1, Today()),
Parameters!DateFilterParam.Value = "Last 7 Days", DateAdd("d", -7, Today()),
Parameters!DateFilterParam.Value = "Last 30 Days", DateAdd("d", -30, Today()),
Parameters!DateFilterParam.Value = "This Month", DateAdd("m", 0, DateSerial(year(Today), month(Today), 1)),
Parameters!DateFilterParam.Value = "Last Month", DateAdd("m", -1, DateSerial(year(Today), month(Today), 1)),
Parameters!DateFilterParam.Value = "This Year", DateSerial(Year(Today), 1, 1),
Parameters!DateFilterParam.Value = "Last Year", DateAdd("yyyy", -1, DateSerial(year(Today), 1, 1))
)
EndDate:
=Switch(Parameters!DateFilterParam.Value = "Today", Today(),
Parameters!DateFilterParam.Value = "Yesterday", DateAdd("d", -1, Today()),
Parameters!DateFilterParam.Value = "Last 7 Days", Today(),
Parameters!DateFilterParam.Value = "Last 30 Days", Today(),
Parameters!DateFilterParam.Value = "This Month", DateAdd("m", 1, DateSerial(year(Today), month(Today), 0)),
Parameters!DateFilterParam.Value = "Last Month", DateAdd("m", 0, DateSerial(year(Today), month(Today), 0)),
Parameters!DateFilterParam.Value = "This Year", DateSerial(Year(Today), 12, 31),
Parameters!DateFilterParam.Value = "Last Year", DateAdd("d", -1, DateSerial(year(Today), 1, 1))
)
- Now, select the parameter value to set the range and preview the report. By utilizing these date filters and expressions, you can enhance your reporting capabilities and retrieve data based on custom time ranges.