SmartLists are great, aren’t they…

SmartLists are a powerful tool in Microsoft Dynamics GP. Their ability to take you straight in to the detail you require, and export to Excel for further filtering, is a powerful aid to users, and very popular.


One particularly useful feature is the ability to save a report with a reminder so that it functions like an alert: you can select a key customer, for example, and a set of boundary criteria around that customer, and the SmartList reminder can tell you when those boundaries have been breached. Maybe it’s a credit limit, maybe it’s delivery time or a stock level—if the data is there in Dynamics GP then you can create a reminder about it.

We regularly see users who have twenty or thirty queries stored in their SmartLists and are using reminders to keep tabs on the most important ones.

SmartLists are great…until they swamp you.

Unfortunately, what we also see are users who have twenty or thirty—or fifty—SmartList queries and reminders working away for them and a homepage cluttered with alerts of impending doom.

Or the SmartList works well, but what is the next level of reporting?

We also see organizations where the parts of the organization that are run by Dynamics GP are all in control and running well, but the other systems in the organization are out of sight because SmartList sends reminders but the other systems don’t.

What’s needed is a top-down view

The great power of Microsoft Dynamics GP and functions like SmartList is that they focus on the detail and process detail very well. When we see people with problems coming from their SmartList, it is usually because they are trying to use SmartList to give them an overview when its real strength is to focus on the detail.

Power BI to the rescue

If you are looking for a top-down view of the data in your organization, or you are looking at a system to sit across several systems to give a view of what is in all of them, Power BI could be the answer.

When do you know you need Power BI?

It’s hard to give a generic answer to the very specific question of when you need to make a change, because every business is different. But there are typical symptoms we observe that lead people to say “Enough is enough – something has to change!”. Here are some questions to consider:

  • How many queries do you have in your Dynamics GP SmartList? Are they getting unmanageable?
  • How much time do you spend in, using, or responding to your SmartList every day? If it’s taking over your life, it may be time for a change. Often SmartLists are used to create regular and ad hoc operational reports for non-GP users.
  • How complicated has it become to do what you need to do: are you layering Excel on top of Excel; are you trying to join data from multiple tables; are regular month end reporting operations taking too long?

Examples from the real world

Here’s an example from one of our clients based overseas. Their operation is quite large and had almost 90,000 fixed assets which were tracked and depreciated in Dynamics GP. Just calculating the monthly depreciation on those assets every month was taking over six hours. Extracting their fixed asset data from a SmartList into Excel also took hours.

We implemented Power BI to take advantage of its analytical and data management capabilities. The large data set was easily brought into Power BI and the speed of analysis and aggregations brought significant time savings to the month end reporting cycle.

Another example of where Power BI is an excellent fit is with our customers within the medical research industry. These organizations are funded by a mixture of grants and donations. It is generally a requirement that the costs of each research project is funded by grants that are closely monitored, and reporting is often a government requirement.

Grants may be attached to types of treatment, or different medical conditions; there may be a grant for heart disease, and another grant for research into stents funding a project that explores the impact of stents on heart disease. You can imagine how tangled their financial rules are within the organization.

These organizations use multiple systems connected to GP to track costs through a purchasing workflow of request, approval, purchase order, and finally invoice matching.

Existing reporting tools using SmartLists, database queries, and Excel are complex, time consuming, and often siloed.

Power BI gives us the ability to achieve the following:


  1. Connect and join the multiple system data to create a total income and cost picture for each project. A single Power BI report can consolidate multiple reports from the various systems.
  2. Reduce monthly reporting cycles by consolidating multiple business processes (usually in separate Excel files) and automatically refreshing the data. Often these reports are only produced monthly and relate to historical data.
  3. Enables scientists to quickly and easily review and analyses their project financial data at any given time.

Retaining the ability to drill down to the detail

One of the powerful features that Power BI shares with Dynamics GP is the ability to drill down from the top-level view (Management Reporter within Dynamics GP)—like you can from a financial report in Dynamics GP—into the detail and data underlying the report. In our medical research example, it would be possible to click on a project at the top level and drill down into the grant and costs details. It would also be possible to look at projects by scientist and drill down to which grants are providing the funding.

In the end, the moment you need to move away from your SmartList to something more functional is up to you. But when you realize that your existing reporting is both time consuming and frustrating, you will find yourself saying ‘This needs to change”.