Microsoft Dynamics GP features a wealth of outstanding reporting and Analysis Tools, including Excel-refreshable reports, SQL Server Reporting Services and SQL Server Analysis Reporting Services but the go-to tool for most GP users is the Smartlists. Combining the ease of use of an Excel-based report, with the deep drill-down capabilities of an SSRS report, Smartlist provides almost any information a user could ask for. Almost.
There are some types of reports that Smartlist cannot provide, and while these missing reports are very few, when they are missing, they can cause a great deal of grief, to say nothing of the extra work involved in getting to the answer thru another method. To address these rare situations, the Smartlist Builder Suite is available as an option. Comprised of four great tools (Smartlist Builder, Excel Report Builder, Navigation List Builder, and the Drillback Builder), the suite is designed to allow end-users the ability to create those reports they cannot live without.
All four tools are based on “wizards” or GUI-interfaces, so not real programming and development experience is required. This is a welcome change from some other reporting and analysis tools, which require some very specialized knowledge and skillsets. We have found that most end-users need only a few hours of training with the Smartlist Builder before they are off and running, creating and publishing Smartlists on their own. To really speed up the process, we have also identified 4 key tips to getting the most from the Microsoft Dynamics GP Smartlist Builder.
1. Where is my data?
Understanding where Dynamics GP stores its’ data is probably the most challenging aspect of creating new Smartlists. While Microsoft does not publish a true “data map”, there are two tools available in Dynamics GP that can really help locating the various database tables that hold the data you are looking for: Table Descriptions and the Report Writer.
Table Descriptions provides details on all of the database tables (both Microsoft Dynamics GP core and add-on tables, and the various ISV modules in your installation). TO access the Table Descriptions, click on the “Microsoft Dynamics GP” button, then “Tools”, “Resources Descriptions” and finally “Tables”.
From this window, select the following items to locate the table that holds your data:
1. Table. Use the Ellipsis button to bring up a complete list of all the database tables. These are organized by Product and Series, and you can choose how you want the results sorted.
2. Use the “Find Button” to search for a particular data field, and Dynamics GP will show you every table that includes that value in its’ name
After locating the table that holds your data, the Table Descriptions” window will show you the details of the table, including every field within the table, the type and length of the individual fields and the Key Segment value(s) for that table.
2. Use the Report Writer
The Dynamics GP report Writer can provide a great shortcut to identifying the table(s) needed for creating your new Smartlist. Many times, we are asked to help create a new Dynamics GP Smartlist that is similar to an existing GP report, but our clients want the ability to use Smartlists Filters, drillbacks and export to Excel feature. To create this Smartlist version of a report, start by printing the Dynamics GP report to the screen. Next, click on the “Modify” button to open the report definition in the Report Writer.
From the Report Writer, click on the “Windows” in the top toolbar, and select “Report Definition”.
From the “Report Definition” window, you can see all of the tables used by the Report Writer to create the report, and this information can then be used in the Smartlist Builder to add in the tables needed to create the Smartlist.
3. Table Relationships
Most Smartlists created in the Dynamics GP Smartlist Builder will require multiple database tables to linked together to create your new Smartlist. (After all, if the data was in just a single table, the standard Smartlists would probably already include that view). In tip #2, we showed hot identify which tables need to be linked to get the result desired, and Tip #1 showed the Key segments needed to link the tables together, but Smartlist Builder also needs to understand the type of relationship between records in the tables.
This is done by identifying whether the “Link Method” is going to be an “Equals” or a “Left Outer” relationship. (No need to panic, this sounds far more complicated that it really is). These two options are based on standard SQL-server Query statements, and essentially indicate either a “One-to-One” relationship (“Equals”) or a “One-to-Many” relationship (“Left Outer”). Left Outer links allow a single record in the Primary Table (say the Customer Master table) to be linked to multiple records in the Secondary Table (i.e. the Receivables Open Transactions table).
4. Restrict the Results of your Smartlist
The “Search” function in Dynamics GP Smartlist is designed to restrict the results shown in the Smartlist Viewer, and allows up to four different search criteria to be entered by the user. Smartlist Builder allows for some of those restrictions to be “baked in” to the Smartlist, so that all four of the “Search” criteria fields are still available to the end-user. Consider building a Smartlist that will use data from the Receivables Transaction Open table, which holds records for all 6 transaction types (Invoices, Payments, Debit Memos, Credit Memos, Warranty, Services / Repairs, and Returns) but the Smartlist you are building only applies to Payments. By building this restriction into the Smartlist with Smartlist Builder, end users of the Smartlist will not have to use one of their four “Search” values for this restriction.
Smartlist Builder is a great tool for providing access to the wealth of data and information stored within Dynamics GP, and by incorporating these 4 tips, your own experiences within the Smartlist Builder tool will be more productive and efficient.
