Recently someone asked me, what’s wrong with me report, it’s not showing me the correct data?
After investigating I noticed that the report used filters, applied in several different ways and that the expected data was not showing up in the dataset, and then of course it was also not displayed in the layout.
So, let’s see how you can apply filtering, in the dataset of a report, and let’s do this with a document. A document typically fetches information from two tables: Header and Line.
Let’s take the purchase Header and Purchase Line as an example, but the same is also valid for all other documents.
Example1: PurchaseExample1
In this report I have a dataset with: Purchase Header and Purchase Line:
Also, there’s an empty request page:
In the layout I have the following:
The first table simply contains all fields from the dataset. I always add this, manually, because then I can see if there’s an error in the dataset or an error in the layout, in case something looks strange.
In the past, in the windows client, we could ‘zoom’ on the dataset of any report to see it, but in VSCode with AL and the web client this is no longer possible. If you want this available in the web client, then please vote for this idea:
Report Inspector:
The second table has the same fields, but with a grouping on DocumentNo, and a groupheader/footer and a table footer row.
When you run the report, without any filters, you see this:
And when you apply a filter on a certain document, you get the following:
So, the filter is actually applied, and everything works.
But sometimes you add extra data items to the dataset, with some programming, and so on, and then suddenly the filters from the data items in the request page might not be applied anymore.
A solution for that is the second example, in which I will use a variable in the request page and use its value to filter the data item in the OnPreDataitem trigger.
Example2: PurchaseExample2
This is the dataset of the second example report:
I have hidden the two data items from the requestpage using the sorting property and by disabling the reqfilterfields.
In the OnPreDataItem() trigger of the purchase header I’m filtering, using a global variable, that is also on the request page:
When you run the report, you see this:
When you don’t provide a Purchase Header No, then no data is returned:
Otherwise you get this:
And voila, the filter is applied, and can be managed in AL-code.
So here you have two examples of how you can add filters to reports, as a developer in Business Central.
You can download the code/report examples here:
Discover more from think about IT
Subscribe to get the latest posts sent to your email.
Thanks a lot for this post! It solves my issue.