A dynamic spreadsheet is one that has a link to a database to pull the latest data. Excel is a great tool for displaying data, graphs and dashboards, but it can be a pain if you have to regularly copy and paste data. By creating a database connection the spreadsheet can be simply refreshed to get the latest data.
The Data tab in Excel lets you select various data sources. Select Get Data > From Database > From SQL Server Database
At this point you will need to enter the path to the SQL server and security information which is beyond the scope of this article, but the IT person who set up the SQL server should be able to help.
Once you have a connection established it should be straightforward to select a table. In the 123 MRP system there are some views created for popular items, an example being ExcelExport_SalesOrders_View that will report all sales orders. It’s a good place to start.
The tables are useful but return a lot of data. The SalesOrders_View table has over 100 columns and will give you all sales orders, including those that have been delivered and completed. Perhaps you only want some specific data for open sales orders.
This can be done by creating a SQL query to prepare the data you need in SQL so the spreadsheet has just what you need.
This makes it far easier to analyse in Excel to produce tables and graphs.
Creating SQL queries requires a good understanding of the database structure and is the type of work Tailored MRP can help you with.
There are several options for refreshing the data.
The simplest is to right click in the table and select Refresh.
Another option is to create a simple macro that refreshes the data. A button can be added to the spreadsheet that has the macro assigned, making it very easy for other users to refresh the data, like in the example on the left.
Finally you can configure the spreadsheet to refresh the data on opening. This can be useful if you want to make sure it is always showing the latest information, but can slow down opening a document if there is a lot of data to refresh.