Hello and welcome to our latest blog post here on BIDA Brains.
I am pleased you have come back to see our latest blog post.
Thank you.
This blog post is very technical.
It is really only intended for advanced Business Central developers.
We will have a blog post on our main site for Business Users.
In this post want to show the world how to do something quite complex as a direct query into Business Central.
Basically this is an example to prove that direct query is able to deliver reports up to quite a complicated level.
As we have emphasized, direct query against Business Central is not a replacement for a data warehouse.
But with SSDs and columnar indexes, there is a lot more that can be done using direct query to Business Central than ever could have been done just five years ago.
So, without further ado.
You can click the button below and you can download the actual SQL and a demo spreadsheet for the BOT report.
For those of you who want to be convinced to press the button?
Here are a couple of screen shots of the spreadsheet that you can download.
In this first screen shot you can see that the GL Accounts are listed down the left hand side.
You can see that when there are accounts with GL entries you can see both the bot amount and the actual amount by year.
You can see the plus sign next to the year so you know you can drill down to the month.
On the following image we want to draw your attention to the Operating Equipment Total line.
We have drilled down to the months in 2024.
You can see that the opening amounts are $220,608 in 2023. Of course, you can check the demo data and you will see that it is in late December these transactions are posted.
Then, in January 2024 you have the opening balance of $220,608 and debits of $55.
Then you can see the opening balance for February is $220,553 and so on.
You can see that for each document type, for each month, the opening balance is the opening balance of the last month plus the transactions for the last month.
This is how “beginning of time” GL Balances work.
As we have said, these are very popular among accountants in finance departments of companies.
It gives them the ability to immediately see the relative impact / size of the monthly change versus the full lifetime balance for the account by document type.
The capsule that creates this spreadsheet is as follows.
We will just go through what is in this demonstration capsule as well.
The first line is fetching the SQL from the hosting master database.
One is the SQL for the query against the GL Entries.
One is the SQL for the query to get the GL Account Table itself.
The second line runs the SQL to fetch the GL Entries producing the temporary vf_gl_bot_balances table and sending it into a spreadsheet.
The third line fetches the vm_month_keys so that the posting date can be linked to these keys.
The join icons put the integer keys from the lookup tables onto the data stream for the vf_gl_bot_balances.
The fourth line fetches the vm_gl_document_type_keys to be able to join the gl document types to the vf_gl_bot_balances data stream.
The fifth line runs SQL002 which fetches the GL Account table and puts it into vm_gl_account as a dimension table.
You don’t need to worry about the column select, remove header, compress on the 5th line.
You can just take it to be a small amount of data manipulation.
On the 6th line you can see we fetch vm_month from the host master database.
And on the 7th and last line you can see we fetch the vm_gl_document_type rows from the host master database.
You, as a customer, would have the choice of what other data you put into your own hosting master database.
Each customer would get one of these databases for themselves to set up their installation.
So adding some tables for small amounts of descriptive information would also make sense.
To the bottom right you can see that all the data that is processed is collected into the collect data spreadsheet.
From there it is sent into Excel for the purpose of this demonstration.
To give you an idea we have also copied the capsule run log below.
You can see that it took about 70 seconds total to run this capsule.
This is without the refresh of the data from the workbooks to the Excel PowerPivot Model.
You can see that it took about 16 seconds to run the main SQL query to get the GL Bot Balance rows.
That is running on a VM that has a copy of the Business Central 23 demonstration database on it.
Obviously that would be much more running on your production Business Central system.
This concludes this very technical blog post.
We really hope you liked this because it was a lot of work to develop the sample code for the bot balances.
You are welcome to download the Business Central renaming script from our main site and run this SQL on your own Business Central system.
We would be very interested to see if the boundary at 36 months works properly.
You can now see and understand that any SQL CTE query could be run and the data returned to Excel.
The limitations are the ability of SQL Server to process the query and the ability to send the data into Excel.
You can see from this example that it is possible to create a very wide range of reports in Excel, or PowerBI, using this direct query technique.
If you are a Business Central on premise installed account and you have a report you want that no one has been able to write so far?
Please feel free to contact us and tell us what you want.
We would be interested in developing such reports for free as long as we can resell them to other Business Central Installed accounts.
With that?
We hoped you liked this example.
Thank you very much for your time and attention.
We really appreciate you dropping by to read our blogs.
Best Regards.
Mihai Neacsu.
Business Development Manager.
The BIDA Team.
Ask Us Anything
You are welcome to ask us any question you would like below on our contact form.
We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.
