If you are working with reports that need to display dates in chronological order, you might be tempted to create a separate table just for that purpose. For example you might want to show the actual and planned production output for each day, week, month, quarter, year, etc. However, creating and maintaining such a table can be tedious and time-consuming. Fortunately, there is a better way to handle dates in Business Central.
Business Central has a built-in table called “Date” (Table 2000000007) that belongs to the System Utilities. This table contains a lot of useful information about any date you can think of. You can use this table to access the following data:
- The day of the week for any date
- The week number for any date
- The start and end dates for any week, month, quarter or year
If you are like me, just looking at this code, you are probably wondering how this table will be helpful.
Open you AL Explorer (ctrl + shift + F12) and look for the Date table. Once you find it, click Run to run the table in your Business Central environment.
I would then suggest within the Filter pane setting a filter (I did 1/1/24..1/31/24 to show me the month of January, 2024). The following screenshot is when I scrolled to the bottom of the table.
As you can see in the screenshot above, there are a few different examples of data displayed in the Date table.
For the Period Type of Date (inside the pink box), you can see that the Period No. corresponds with the day of the week (with Monday being the first day of the week, and Sunday being the last day of the week). You can also see the Period Name is the name of the day of the week.
For the Period Type of Week (highlighted in yellow), you can see the date ranges included in each week with the Period Start and Period End dates. You can also see the numerical week number in the Period No. field.
For the period of Month (highlighted in pink) you can see the date ranges included with the Period Start and Period End dates. You can also see the number of the month in the Period No., and the name of the month in the Period Name.
For the period Quarter (highlighted in blue) you can see the date ranges included with the Period Start and Period End dates. You can see which numerical quarter it is within the Period No.
Lastly, for the Period Type Year (highlighted in green) you can see the date ranges within the Period Start and Period End dates. You can also see what year it is within the Period No.
You may now be asking, how can I utilize the Date table in my development.
I created a report that had the top-level data item using the Date table. In the PreDataItem trigger I SetRange on the Period Type of Date (so I would only get the dates instead of all the different types). I then set a filter on the Period Start for the date range that I was going to be using. I calculated the month StartofMonth / EndofMonth based on the ReportDateFilter from the request page. The ReportDateFilter defaults to the Work Date, so it should show you the entire month of dates.
For this specific example, I decided to show the count of Purchase Invoices with Due Dates for the month.
You can see the Subtotal rows are breaking up data based on the Week Number. Instead of referencing the Date table for not only the date, but the week, I just used System.Date2DWY("Period Start", 2) This function takes a date, and then you can have it return the numerical representation based on
1. Day of the Week,
2. Week Number or
3. Year
In the RDL layout, you will need to add a couple groups to achieve this layout.
I grouped by “TheDate”, and then grouped by “WeekNo”
This allowed me to display each date with related data, and then create a subtotal line for the week.
See my entire code sample here
Let me know if this table is “news” to you, or if you have created something referencing the Date table.
#msdyn365bc #businesscentral #reporting
BC Development Notebook 
This is helpful! Thanks!
LikeLike