In this blog, we highlight a lesson learned when we used a Dynamics 365 Customer Engagement online data source connector, called Instance Web API, for Power BI reports.
There are many ways to access transaction history within Business Central. For instance, what if you are looking for cash transaction history for a month, a week, or even just one day? There are multiple reports you could run with date filters on the desired G/L account(s). However, if you want to see this data for a few different date ranges you’ll have to enter new dates and re-run the report each time. A quick and easy method to skip reports altogether while also allowing you to drill down to the data you’re looking for is to filter on a calculated field. Using the steps below, we’ll show you how to quickly get the data you need.
Using that cash account example, you’ll start with accessing your chart of accounts and selecting the G/L account for which you want to view history. Click on
If you click on Net Change now, you will see the entire transaction history for that account. However, we’re only looking for the transactions in a specific date range, so select the filter button instead.
This will bring up the filter pane. You’ll now see two filtering choices. The first is Filter list by:. The second option is the one you’re looking for – Filter totals by…
Select the second option and then click on the filter you would like to use. For this example, we’ll stick with date ranges and click on Date Filter.
Let’s say that you want to only see cash transactions for the 2019 calendar year. You can either enter the exact dates or use the formula “1/1/2019..T” (tip: T is a shortcut to signify “today” within a date field in Business Central. If you’re someone who tends to forget what date it is, this is a safe way to make sure that today’s transactions are included in the desired date range). You will see that your account balance is unchanged, but your Net Change has recalculated. You can click on this if you’d like to drill down and see the individual transactions.
You now have detailed information on only the transactions that add up to the Net Change for 2019.
If you want to change your view again and see the Net Change for only January of 2019? Enter those dates into the Date Filter and recalculate. Once again, you can click on Net Change and see a list that contains only those transactions.
Reporting tools are great but can sometimes be overkill or too slow when you’re looking for quick access to transactional data over multiple date ranges. In a pinch, filtering on a calculated field can be the tool you’re looking for.
Happy Dynamics 365'ing!