There’s no question that your data, locked away safely in the depths of your GP system, can give you a great deal of insight into the operations of your business. Getting it out of the database and into an intelligible form, on the other hand, can be a daunting task, especially if you have very specific criteria. But then again, one doesn’t invest in an ERP system just to stick with the basics, right?
At GPUG Summit 2018 in Phoenix, one of our very own Dynamics Reporting Specialists, Sean Hunter, showing attendees how to capitalize on SmartLists. This solution helps mere mortals extract just the data they need to do anything from accounts and reporting cleanup, to full-blown decision making at the highest levels. Here’s some highlights from the session:
SmartLists haven’t always been the easiest to configure. There’s a few great add-on tools from ISVs that have cropped up, helping you to tame this beast. But we’re going to talk about one that you might not have known about – and which lives right under your nose, as a default component of Dynamics GP!
SmartList Designer is your secret reporting weapon. Why should you use it? Here are three reasons:
- Creating SmartLists gives you a world of reporting options.
- SmartList Designer is under active development – getting better with every release of GP!
- The price – FREE. Sure to put a smile on every CFO’s face.
How does it work? Take a look:
In the SmartList Designer, start by clicking new. Name the SmartList, choose a product, and select the Series the SmartList will display in.
Check the box next to the Table Name to select all fields in the table, or expand the node to select individual fields. To select more than one table, simply check the boxes next to all desired tables or fields to be included. (If you select more than one table, you must set their relationships also – more on that in a moment!)
Review the Selected Fields: There is no way to change the default order once added in the Designer. You should select the fields in the order you would like them to appear. Removing the fields that are out of order, and adding them back in the order you want allows for changes.
Now, how are the tables in your SmartList related? Use the Relationship area to define it. The Auto Link function can be used most of the time to link tables to one another quickly.
- Inner Join will relate a table as a one to one. In the example, an Inner Join means that the customer number would have to exist in both tables.
- Left Join will relate data from one table to the other, but still return data from the first table even if there is not a match.
- Cross Join means that you join both tables together and return all data without relation to one another.
But wait – you can do more than just pull static data. You can also perform math calculations and even manipulate string data before it is included in the report by means of Calculated Fields! Click the Function icon in the upper-right of the Field Display to access the Create Expression window. Next, select Table Fields to calculate with, Functions to handle arithmetic or strings, and Constants to add in set values for calculation.
As a side note, there is even a Go-To feature to link to other SmartLists. It’s basic though – there’s no passing of parameters or other manner of controlling the flow – You would have to filter further once you access the next Go-To.
All of this sound like a lot of work? Are all the options sending you reeling? Try cutting your teeth on a more bite-size project – Look through the built-in lists, and find one that is similar to a report you want to produce, and customize the existing SmartList! That’s a lot easier, right? Here’s how: Simply select an existing SmartList in the Designer before you click New – and voila! This will bring in all of the fields from the existing SmartList to begin modifying.
All of these neat features can aid you in drilling down to the information you need. However, not all the decision makers can make sense of a page full of rows of data – but if your SmartList is based on a View, rather than just tables, you’ll see a button labeled “Publish” – this will create an Excel document filled with the results of the SmartList query. From there you can put your spreadsheet skills to work making a more visual representation of the report to “wow” the management!
Of course, there’s still more to learn. There’s an undocumented issue with Excel exports when you rearrange the columns – and a workaround. The Designer can help you add security controls to your lists with the Security Workflow for new view creation. Import and Export of lists. Favorites. And many other features!
And yet, this is only the tip of the iceberg of what can be accomplished with . We at Turnkey Technologies are committed to helping you get the maximum benefit from your investment in an ERP system. We have a team of GP experts, like Sean, whose combined knowledge can tackle most any project or requirement you can conceive!
to find out how to unlock the power of Dynamics GP in your enterprise.
By - Microsoft Gold Partner based in St. Louis, Missouri.
