BIDA0056 – Business Central Dimensional Queries Sending Results To Excel

0
30

Please watch the video in 1080p because it is recorded in High Resolution.
Please note the script of the presentation is below so you can read along with the demonstration if you like.


Hello and welcome to our latest blog post.

I am Sofi, your BIDA AI Assistant.

I will be reading this blog post for you today, on behalf of Mihai.

I am really pleased to be able to bring you some news that I think will really interest you.

If you are a business person using Business Central On Premise?

If you are frustrated that your I T department is telling you it takes days, or weeks, to get answers to questions that are urgent for you?

Then you will want to watch this video.

We are using Business Central twenty twenty three in our demo.

This video is a follow up on the blog post BIDA Fifty One.

You can review that blog post on the button below.

BIDA0051 Blog Post

So, as ever, on with the demo!

Demonstration

In this demo we want to show you the end result of creating an Excel report from Business Central on premise.

Of course, this is a demo.

You can create any report you want from Business Central on premise now.

Please note that this is not a freebie. The Meta five license does have to be paid for.

So, here we are on our demo machine.

We are inside an Excel report that is pretty simple.

Please note that you can download this Excel workbook using the button below.

Download Report

Anyone who uses Business Central will recognize the data in this report.

We are presenting sales lines for Sell to, Bill To, and Ship To.

This is a very simple report to make it easy to understand the demonstration.

So, I will click on the global slicers for Bill to countries for Great Britain and Germany.

Please note that the N A currency is actually the default currency for the Business Central implementation.

This is a known problem that we have not solved yet just because we wanted to get the demo done more quickly.

When we have clicked on Great Britain and Germany we can see the customers for sell to and bill to.

We can drill through to the detailed reports using the arrows to the top right, or just by clicking on the tabs at the bottom of the Excel workbook.

So, now I will drill through to the, sell to, customers.

You can see sales lines for such customers as the Guildford Water Department and the Auto House in Mielberg.

As I said, this is a very simple report, and it is intended to be this way.

You can see that we have put such fields as the item number, the item descriptions, and the numeric details like unit price, V A T, total amount, and the number of items sold.

We can drill back up to the global slices using the arrow at the top left of the report.

Now, I will click on that arrow.

I will also click on the Bill To, and Ship To, work sheets just so that you can see them.

Now, I will open up the power pivot model.

Now, I will just click through the tables so that you can see that there is data in the tables.

This data should be very familiar to you.

Now, I will open the diagram view.

You can see that we have the sales invoice lines in the center of the diagram.

You can see that there are detailed keys in the mini fact table.

You can see these detailed keys are linked to the primary keys of the mini dimension tables.

This data came from Business Central, and was put into this Excel Power Pivot Model.

We have added default rows automatically to the mini dimension tables, so it is not possible to lose rows when data is not correct in business central.

When a join fails you get the Not Applicable value.

You do not lose the row.

As I said, this is a very simple report, so that it is easy for you to understand.

It could also contain orders, shipments and returns.

You can put any data you want into these excel reports from your Business Central system.

Now, I will close the power pivot model.

Now, I will close the spreadsheet.

Now, I will delete the spreadsheet from the folder.

Now, we will go to the Meta5 desktop and open the Applications File Draw, to get to the capsule that creates this report.

Now, I will copy the capsule onto the Capsule Service Prompt.

Meta5 will ask me when I want to run the Capsule.

I can run it now or I can run it repeatedly under a specified alias.

You can see we have created default start times, every half hour from midnight to nine A M in the morning.

If we select one of those this capsule will run every day at that time.

In this demonstration, that would include weekends.

Please note, when this capsule runs, it will actually read data from a demonstration Business Central database, on another virtual machine.

Now, I am going to start the capsule.

Now, I will go to the capsule server and show you that it is running.

Now, I will show you the task manager.

In the task manager you will see mostly Excel processing.

Meta5 fetches the data from the Business Central demonstration database.

It then sends it to Excel regions.

It then loads the data into the Excel Power Pivot Model.

This transfer of data to the Excel Regions, and then load into the Power Pivot Model, takes the greatest amount of elapsed time.

The temporary workbooks used in the Excel workbook are then deleted.

Finally, the finished workbook is sent to the output folder, which is actually a one drive folder.

We will not pause the video, or alter the speed in any way.

We would like you to see how long this takes.

This is so much faster than trying to get data into Excel any other way.

These capsules can be run using this Meta five scheduler.

They can also be run using a windows command.

The windows commands can be run on demand, or it can be put into the windows task scheduler.

Now, the job is complete, and it disappears from the queue.

Now I will go back to the reports folder.

You can see the new report is there.

Now, we will just log off the Meta five desktop.

Just as an extra point.

The report start date and end date for sales lines are stored in a parameter database.

Your I T people can easily set these up, and make sure the dates are moved forward each day automatically.

In Summary

I would like to just summarize what we have shown you.

It is now possible for you to buy, or have your I T people create, a capsule that generates an Excel report.

That Excel report can contain any data from Business Central.

It can also contain any data from just about anywhere, that the server for the capsule has access to.

This can include data from web pages and other sources.

It is not limited to data that is stored in databases.

Meta five can then put that data into Excel automatically.

You, as a business user, get the finished report.

You can get it on your one drive.

You can run it on a schedule.

You can run it whenever you like, using a windows command.

And the best thing about all this is that the SQL that is used to get data can be as complex as required.

The only limitations on this solution are.

One. The processing power needed on your business central database.

Two. Excel and the limitations inherent with Excel.

If you want to be able to get Business Central data into Excel reports?

This is a very good way to do that.

And it will be cheaper than any other way you can solve this problem.

Of course, we are also doing this in Power B I, but Power B I uses a different approach to Excel.

If you are interested in trying out Meta five at your company?

We have a training machine available on Azure, and a weeks free training, for qualified prospective customers.

And with that?

I would like to say thank you very much for watching our video today.

I really appreciate your time and attention.

We hope you found this blog post interesting, and informative.

We would love to hear your comments, so please feel free to use the comments section below.

I wish you a great day.

Thank you.

Sofi.

Your BIDA AI Assistant.

We will be emailing our subscribers useful and
valuable information regarding business intelligence.
Please subscribe here to be on our general emailing list.