– SmartList Designer for Dynamics GP –

SmartList Designer for Dynamics GP

Hello and welcome to the TMC Webinar Series. My name is John Hoyt. I’m the Solutions Consultant here at TMC. And for the webinar here, May 21st 2015, we’re going to be taking a look at SmartList Designer. The tool that’s been added on to Dynamics GP to extend the use and functionality of the SmartList inquiry tool we’ve been using for a couple of different versions now. I have a couple of slides we’ll go through and then I’ll jump into a live demo and we’ll go through the basics of the SmartList Designer product.

I want to start by bringing up the roadmap diagram for GP … We’re now in GP 2015 H1. We’re getting ready to release GP 2015 R2, that will probably be coming out next month. And that fills in with the release cycle that Microsoft has adopted for the GP product line where we’re going to be getting either a Service Pack or a R2 release about every six months or so. You can see the list of functionality that we’ve been adding over the past couple of products. And I specifically wanted to bring up this slide just so we can show you where SmartList Designer fits into the mix.

SmartList Designer is available for anybody who is on GP 2013 Service Pack 1 or more recent. So Service Pack 1, Service Pack 2, R2, and of course through GP 2015 and going forward. If you’re on the original GP 2013 and haven’t upgraded from that point, for whatever reason, you won’t be able to use SmartList Designer yet. But as soon as we can get your upgrade scheduled then you’re ready to go to whatever version you move up to, then you’ll have SmartList Designer available.

A couple of different things I want to use. And when I talk about SmartList Designer, why I think this is a tool that people should be considering and making more use of than they currently are. First and foremost, of course, just access to better intelligence means you and your people can make better decisions. The more efficient that we can make that process, the more accurate we can make that process, the better decision-making you get as a result. Better decision-making, of course, leads to a more streamlined business process. If a user can go directly to one customized SmartList that’s been built using the SmartList Designer, as opposed to what they may be doing today, which is, start with one SmartList then export it to Excel. Go back to GP, get a different SmartList, export it to Excel and then start doing analysis over in Excel. Solely because what you want is not available in the current SmartList. Well, SmartList Designer is intended to fill those gaps, let us create one single SmartList that can produce the result you’re looking for and people can then make quicker, more accurate decisions.

Within that, there’s a couple of things we can establish from SmartList Designer. The ability to create defaults so that I can put in the background, for example, various restrictions. I can put in calculated fields, etcetera. It simply want to make, again, that end user experience as optimal as we can, get people quickly to making the decisions they need to make.

And I mentioned in the roadmap slide, GP 2015 R2, coming out next month, it’s going to include an enhancement that will allow us to have workflows that incorporate SmartList Designer. And with that workflow, I can then automate the process of publishing a SmartList directly into Excel. There are ways to do that right now, with the basic SmartList Designer, and I’ll try to show you a little bit of that at the end of the demo. It is a fairly manual process and a lot of people may look at it and say, “This is more than I want to tackle.” And with R2 coming out next month, that’s what we’re going to try to automate. So now, anybody who has access to your data, through a SQL CAL License, can use a SmartList Designer, created at Excel spreadsheet and get directly to the information they’re looking for.

So some of the features SmartList Designer, I think, makes the tool rather user-friendly, everything’s done in plain English, including the names of the tables, the names of the fields inside those tables. You’re not going to have to learn anything special or any of the SQL nomenclature. I’m going to create a couple of custom calculations and there is a top trick that I want to show you as we go through that process. You can assign go-to buttons from SmartList Designer. I can point to either an existing SmartList and then from that SmartList go to an inquiry window, transaction window Master record. I can go to a Navigation List, inside my Navigation List on the left hand side.

So just like with the Stock SmartList, you can use SmartList Designer as both an inquiry tool and a starting point for a drill down. And you can incorporate in that, built-in filters. You do the filtering at the Designer level. That means when I publish the SmartList, any user that has access will still have the availability of all four of the search functions inside the standard SmartList tool itself. So I can run in some situations with a really large data set and I simply run out of the ability to filter the way I want, therefore is too limiting. With SmartList Designer and the custom filters in the background, I can move past that.

So, share information easily, works with Excel, of course, link out to the Navigation List. The pre-configured table joins, I’ll show you this in the demo but I think it’s really worth calling out now. One of the most challenging pieces in doing SQL work is knowing how to join tables together, which fields need to be linked, in what sequence, etcetera. And SmartList Designer has given you a couple of shortcuts. If I’m working within sales, for example, it may already know how to link Customer Master with the receivables’ history, that gets linked through the customer field. And so where you can simply say, “Show me what the standard table joins are.” And I can use those. And of course, real-time data access, better information, better decisions.

So, let’s start the demo. Let me switch over to my GP 2015 system. And you should now be able to see GP 2015 on the background. Could someone send me a quick chat to indicate … Yeah, you can see that?

Thank you Britney, appreciate it. Okay. So, SmartList and SmartList Designer. SmartList itself, as some of you have seen, if you’ve upgraded recently, has had a couple of changes made to it in the way it looks and feels. Some of those include things like we now have the ability to both increase or decrease the width of the folder panel. And if I go up to my SmartList options on that Favorites pane, I can choose to hide or show that. If I choose to hide function it will simply roll out of the way entirely for me, unless I’m choosing to focus on it. So if I was looking at customers, pick a view etcetera, and then once I start to work on that, I can make my folder list go away entirely. Full Screen in Real Estate now available, part of SmartList. This, again, came out in GP 2013 and is going to be a function going forward for everyone.

If I go back to my SmartList view, you can see inside sales the standard folders that Microsoft offers, customer addresses, prospects, receivables, sales, etcetera. But have also added in two others, demo and my SmartList Designer webinar 2, which is where we’re going to be working today. Those folders were created and can be modified and maintained inside the SmartList Designer. And you’ll see across the action bar at the top of SmartList right now, there’s a bunch of grayed out buttons for me because I’m focused on a folder. I’m not focused on an individual view. So things like search, refresh, columns, favorites, those don’t become visible, of course, until I’ve laid out one of my actual favorites. Now I get the ability to control all of that.

With a folder that’s been created via the SmartList Designer, in addition to those abilities, refresh, columns etcetera, I also get access to the modified button. This is a tool that I can use to open the SmartList Designer. So when I click on modify, it takes me into the SmartList Designer tool and it brings me specifically to the SmartList that we were focused on. So SmartList Designer webinar 2 comes up for me. That list name, here, will correspond to the folder name you’re going to create in the SmartList structure.

Now, SmartList gives me the ability to look inside Microsoft Dynamics GP for data. It also allows me to look inside any other product that I have registered as part of my Microsoft Dynamics GP implementation. So fixed assets is going to be in here, human resources, payroll, field service, manufacturing. I can go on and on down the list of the different modules. They’re all part of the SmartList Designer tool and I can create custom SmartList based on any of those. I can also do that for any third party products that I might have licensed. If I’m using products from Binary Stream, for example, they’ll be visible here.

As a quick compare and contrast between SmartList Designer and SmartList Builder, and I’ll come into more detail about this later. SmartList Designer is free for Microsoft, SmartList Builder is an add-on product from a company called eOne. If you need to incorporate data from a non-GP system, say you’ve got an external eCommerce site, you’ve got an external CRM system or something, SmartList Builder, from eOne, will allow you to combine that external data into SmartList and view them inside GP. The SmartList Designer is limited to only those products that are registered as part of your GP package. If you’ve got questions about what you can contact either myself or Jim later and we can give you some details and some guidance on that.

Once you’ve selected the products you want to work with, the series value here is going to correspond to what part of that product do you want to work with. It will also determine which folder, in the SmartList structure, the results will be published. If I pick sales here, that’s what it will publish. If I chose from the product list, say fixed assets, then I would see it show up in the fixed assets folder over inside the SmartList folder structure. Having given it a name, selected my product and what series I want to work with, I now get a listing in the left-hand column here, of all the different tables that GP is aware of, and I can work with any of those. If I expand out the dynamics, the table structure within dynamics is broken out into the major module groups. And what I’ve elected to work within my sample here is two different sales folders or two sales tables rather. And you can see them detailed here up in the top.

Sales transaction history is my first table. And if I scroll down to the bottom a little bit I’m also going to work with Salesperson Master. This selected field box is going to show me every value that I have selected from the different tables that I want to work with. So you can see that RM Salesperson Master has a checkbox. If I expand that out and I scroll down a little bit, the check mark here indicates that commission percentage is one of the values that I want to have exposed inside my SmartList. To add additional values, additional columns, I can simply go through and pick whichever ones are relevant to me, put a check mark next to it and that’s going to be everything I need. So I have salesperson ID, I can put first name, middle name, last name, etcetera. Every time I make that check mark, I’m simply adding in that value to the selected fields that I want to work with.

Now, in this particular SmartList, I’m working with two different tables, sales transaction history, that’s going to be the source of all my data about historical invoices. What I’m looking to create, essentially, is a commission report. And a commission report is something that you can’t do in standard SmartList because I’m either looking at the salesperson information or I’m looking at the transaction, but I can’t see both. So I’m going to create this SmartList to accomplish that task. So I need commission percentage for my salesperson, I’ll grab the name information as well. And then at across the top, I’m grabbing the different sales order types, the numbers, the documents themselves, who is the customer, extended cost, document amount and who’s the salesperson.

In the relationship section, I’ve joined those two tables together. And I’ve done that by selecting from the tables I’ve chosen above in the selected fields, that’s available here as a dropdown. So in this case, I want to join sales transaction history via the salesperson ID field. I’m going to do a LEFT OUTER JOIN, which is indicative of a value may exist in one table but may not exist in my joined table. And I’m okay with that relationship. I can also do INNER JOINS and CROSS JOINS. And I can give you the details of what those different JOINS represent, they’re in the documentation that’s provided for SmartList Designer etcetera. And I’m going to join my sales transaction history to my RM salesperson by that salesperson ID. This link, it’s the relationship between the two tables, is what allows me to pull different values from different tables together, into a single SmartList.

And then finally, I’ve added in a filter. And I can add as many filters as I want. In this case, I only want to look at documents where the SOP Type is looking at invoices. I don’t care about returns, I don’t care about back orders or orders or anything else. I just want to see the invoices out of that history. So with those basics put in place, I have the tables that I want to work with and the values within those tables, those show up in my selected field view, I’ve indicated the relationship between the two tables that I’m joining. And finally, I’ve set a filter to reduce or restrict the output.

Now, one of the really neat things about SmartList Designer, I have an execute query function. This is essentially a preview. If I hit that execute query, it’ll go through the data and try to pull together from the tables that I’ve joined and the columns that I’ve asked for, and my restrictions. And down across the bottom, I now get a preview. This is what SmartList will look like if I click the okay button and I publish this view out. And then I can scroll across and see all the details that I’ve added. And the names have been added, for example, extended cost, document amount, commission amount. All this is working pretty good.

If I hit okay now, and I go back into my primary view, you can see I now get all those same results. So, only invoices, the document numbers, the amounts, who’s the salesperson etcetera. And because I did my filtering in SmartList Designer, the search function is still fully open to me. So I can now do searching within this result, say, for a specific sales rep, for a specific territory, for a specific customer class, for a specific date range. And have all four of those values still available to me. So let’s go back into that Modify because I’ve got a couple of the basic pieces that I need here in terms of doing my calculation, which is something around the idea of commissions. But I don’t have a couple of calculations available yet.

One, I pay my people based on margin, that’s why I chose extended cost and document amount. What I need to do now is create a new field in my SmartList that will hold that calculation. And the little fx button here, at the top of selected fields, will allow me to create that calculation. You can create as many calculated fields as you want to add on to a SmartList. And the calculations can be based upon one another. So I can start to level calculations one against another. So when I choose add to create a new one, I need to give it a name, this will be the column header that will be displayed inside the SmartList.

I need to determine what type of value do I want to hold in this. And in this case I’ll do it as a currency. The three tabs across the bottom allow me to see the tables that I’m working with, the sources of data. Functions allow me to see all the built-in functions that SmartList Designer includes. There are mathematical functions, text functions, date and time can be inserted, rounding or absolute values if I need to do those, as well as some aggregates where I want to average values, count them, sum them, minimum, etcetera. And then constants allow me to add in specific constants that I want to use for whatever reason, numeric, currency, text information etcetera.

When you build your calculation and it’ll be very counter-intuitive so this is a top trick, I’d make a note of this. You need to build your calculation as though you are reading from right to left. So in this case, I want to do a margin calculation. I want to know simply what’s the difference between my document amount and my extended cost. To build that function, I need to start in reverse. So let me go to sales transaction history, if I double-click that, I now get a listing of all the values that are available in that table. The values in my calculation do not have to be limited to the fields that I’m selecting to display. In this case, they happen to do that so someone can double-check for me. But if I want to work with any of the values from the table, I can.

Just scroll down through my list. I need to take extended cost, and you can see when I double-click on extended cost, it adds that value into the expression for me. And go to my mathematical functions and I want to subtract. Go back to my tables, go back to the transaction history value and I need to scroll down … and there’s my document amount. So now the expression’s going to read for me, “Take sales transaction history doc amount and subtract sales transaction history extended cost.” Go ahead and click save and SmartList Designer has now gone through and checked the mathematical validity of my calculation. It’s simply telling me that the values it’s been asked to calculate makes sense. It’s not necessarily confirming that it’s going to produce the calculation I’m looking for. That is if I have something wrong in the calculation, it may still be valid although the results will be inaccurate. But because it’s valid, it says there’s no errors in the structure, no errors in the data it’s being asked to pull. I’m to asking for a currency to be subtracted from a date, for example.

Now I’ve added in document margin, I can go ahead and click okay, and that field gets added into my SmartList like any other field that I would have selected from a table. If I click okay now, and then go down into my Stock view … I apologize. Let me go back and look at that ’cause it did not add in my calc.

Let’s try this … There we go. So now I get the document margin that I’ve asked for. So the calculation is now complete, difference between document amount, extended cost, creates a document margin, I can now back in and modify this again and say, “Okay, let’s convert this document margin into a percentage margin.” And now I can pay my commission salespeople based upon the margin that they’ve earned for me on that particular transaction as opposed to basing them on, let’s say, the full document amount. I want a commission based on margin not based on revenue or something of that nature.

So I will pause at this second and ask if there are any questions. Let me take people off mute and I will hope that we don’t have someone on hold. And unfortunately we do. So the chat function is open. If you have any questions up to this point, please go ahead and put a question in there, I’m happy to answer those. Of course, the rest of SmartList Designer, our SmartList at this point is going to be available to me so one click x to Excel, to Word etcetera. And then all the various drop downs that I have included. None of this has changed in terms of SmartList.

Go back into that Modify one final time. And again, the details of my SmartList Designer available to me. The execute query function allows me to do the preview. And the final piece I’ll talk about in SmartList Designer, this T-SQL query. This allows me to convert what I have created above using the folder structure and selecting the tables, columns, calculation fields etcetera, that I want. All of that is now available as a T-SQL query that I can simply copy and paste. And this is what we’ve done previously to create versions of refreshable Excel reports. I need to take this SQL query and migrate it now into the connection string that I’m building inside the data tab of an Excel spreadsheet that I want to become refreshable.

As I mentioned in the slide deck, with GP 2015 R2, coming out next month, we’re going to automate that process and create a simple workflow that allows me to take anything I’ve built or modified inside the SmartList Designer and simply put it through workflow and publish to Excel. And you won’t have to do anything manual in terms of building that data connection, embedding the T-SQL query and the connection permissions etcetera, anything of that type.

So, let me go back the demo. I want to talk real briefly, we’ve got a little bit of time left. The differences between SmartList Designer and SmartList Builder. And again, SmartList Designer is the free product that Microsoft made available beginning with GP 2013 Service Pack 1 and it’s available going forward with the product from there. SmartList Builder is an add-on product from a company called eOne. They were the original developers of the SmartList product. They then sold it to Microsoft. The SmartList Builder then, again, the upgraded version, well, I think there are some advantages. SmartList Designer includes that preview capability that we were just looking at. SmartList Builder doesn’t have that. With SmartList Builder, you do your work inside the SmartList Builder tool, you save that creation, you then have to open SmartList. SmartList refreshes with new data, new information about what you’ve created and then you can view your results. Maybe it’s what you want, maybe it’s not, you’ve got to go back to the Builder. So there’s a lot of that kind of back and forth. I think the preview capabilities that Designer offers is a real advantage.

With Designer, your access is limited. You only have access to information that is directly in the GP or ISV Registration that GP knows about. SmartList Builder gives the ability to pull in data from other locations, other sources of information. SmartList Builder also has a more extensive set of built-in calculations because it’s working directly with SQL server. Virtually any calculation I can do with SQL server, I can accomplish inside the SmartList Builder. The Designer calculations are laid out and fairly thorough but they’re not quite as robust or as all-encompassing as what you can get inside SQL server.

And then I’ll close with a promo. Our next webinar is going to be coming up June 11th. It’s going to be from a company called Solver USA and they’re going to be showing us their BI360 reporting tool. And this slide is just an illustration of some of the different things that we can do within BI360. There are a couple of pieces that you can do directly out of the ERP reporting and dashboard. If you want to incorporate things like planning and collaboration or data from other sources, from CRM or eCommerce, something of that type, then we would need to talk about putting a data warehouse in place to first hold all of that combined data and then the reporting, planning dashboard tools take advantage of all of that.

I want to thank you very much for your time and attendance today. If you’re interested in learning more about the advanced features that the SmartList Builder tool offers, again, that’s the product from eOne, you can contact Jim Kaltsas, he’s our account executive. You can also reach out to me of course. We’d be happy to provide you with more information about that SmartList Builder tool and its capabilities. And I can either arrange to show you a demo of that if that would be something you’d be interested in seeing. So again, thank you very much for your time and attendance today.

Thanks for watching the video, TMC is here to help. For specific information, feel free to contact us.