A question I got recently was, how can I visualize or create a dataset that can show/fetch information on a timeline? For example, I have a time-sheet and want to visualize the hours employees worked at certain projects, per day, week, month, year, and so on.
In this example I will create an order intake report. For the simple reason that for this report you can use the standard demo data in Business Central, and for time-sheets you first need to go threw a setup and then create the demo data.
The order intake can be defined in different ways, the definition that I will use is:
the amount or quantity of sales that has not yet been posted to the ledgers.
In the case of Business Central, it means that we will fetch the information from the Sales Line table.
This information needs to be presented on a timeline.
The question is, how are we going to do that?
You might think, that’s easy, just take the PostingDate or ShipmentDate and use that to create a timeline. Well, if you follow that approach then you will get gaps or jumps in your timeline. For example, if there are sales planned on day x and on day x+ 3, then day x+2 will not be shown on the timeline.
What you need is to have a value for every date in every month, use that as the timeline, and show a value for the dates on which there are sales.
In many systems, for example in the business intelligence world, you can use a date or time dimension to accomplish this. But, in Business Central, we don’t have a date dimension. I will use the integer table instead. The integer table is a virtual table in Business Central.
So, I need to use the integer table, then filter it on a specific period, loop over those days and, for every day in that period, go and find the sales and add them to the dataset. This will require some programming magic in AL.
As a side note, an alternative is to use the virtual Date table instead. This is also a very good approach, but I did not do that, for the simple reason that I find the integer table solution easier 😉
First start by creating a new report and create the following dataset:
As you can see, I’m using the integer table twice as a data item, once for the dates and once for the sales lines. Then, in the AL code, I use the report triggers to filter on a certain period:
When the report runs, I will initialize the variables, StartDate and EndDate. I will also add these two fields to the request page, so that the user is able to modify the period.
Then, I need to count the number of days in between the start and end date and that will become the NoOfLoops. The number of loops value is then used to filter the integer table from 1 to NoOfLoops.
Next, I need to find the sales for every day, returned by the dataset (integer). I will do that using another integer data item, named SalesLineLoop, which I indented below the dataset integer data item, as shown in the following screenshot:
You see in the AL code here that I have filtered the Sales Lines on the Shipment Date with the date from the dataset, and I’ll count the sales to determine how many times I need to loop in the SalesLineLoop integer data item. Then, in the inner loop, I’ll add the Sales Line information to the dataset.
Next we’ll create an RDLC layout:
In the RDLC layout I simply add a table and in that table put all of the dataset fields. This will then be a preview of the runtime dataset:
Now we are ready to build the report layout and visualize this information in a matrix:
In the matrix I have created groups on: CustomerName, ShipmentYear and ShipmentMonth:
And voila the result:
Now all the report need is some more formatting, and maybe some totals and subtotals, but I think that’s not so difficult to figure out yourself…
You can download the example report here:
(you might need to add your launch.json file and download symbols.)
Wait a minute…
You might be thinking now, why do I need the separate integer data item in this report??? Can’t I simply use the sales line table and group on the shipment date in the matrix?
Yes you can, and in this case you will get exactly the same result in the matrix, but not in the chart.
And, if you also need to fetch information from other tables, per day, then this is a better solution. Imagine you want to present Invoices, Returns, Credit Notes, … on a timeline, then you need a separate timeline as in this example.
Also, if you need to present the timeline, for example from Monday to Sunday and you want to include and see dates on which there’s no transactions, then you need a separate timeline.
You can create this simple report with for example two data items: DateLoop and Transactions. That way, your data item names are generic and you can use this even as an AL snippet or template.
In PowerBI this is much easier. There you can add a date table using CALENDARAUTO(), but that’s an example for a later post 😉