For those of you who always thought pivot tables were hard, hang with me here because they’re not anymore. I’m showing you Excel 2007 where pivot tables got easier to use. All you do is drag and drop the fields into their respective sections. With just a few clicks - five to be exact - I have a report that allows the user to see by vendor class (indicating the type of vendor or who is responsible for them) the vendor and the postings to the ledger. Check this out:
I can even filter the whole report by source document type. This would show me all the voided payments for these vendors for example. That might be useful to know where rework is happening in my payables process. This is just endlessly useful stuff for analysis and the real kicker of it all is that this is live, refreshable data, accessible outside of Microsoft Dynamics GP ("Dynamics GP")! And it’s EASY too!
I’ve used ERB with pivot table output for some really cool stuff. One of the perennial favorites is giving a sales manager a pivot table that shows sales by territory, sales rep, customer, etc. They love to slice and dice by those categories and “pivot” the data on different criteria. It’s really easy to give them access to the live, refreshable pivot table without giving them access to Dynamics GP, which only requires a “light” user license. A “light” user license extends Dynamics GP, allowing users access to data without having to log into Dynamics GP. Another really awesome use for ERB with pivot table output is payroll posting. Ever have something post to your suspense account and it’s a ton of fun to figure out what it is? If you look at your payroll entry through an ERB pivot table, it’s easy as pie to see the individual amounts, departments, and codes that posted to suspense.
All you need to do this is Dynamics GP 2010, SmartList Builder/ERB and Office 2007 or 2010. There are ways you can achieve basically the same results with Dynamics GP 10 and maybe even Office 2003, but it’s just not as incredibly easy as with the newer versions. So how do you learn how to do this? There’s the free, unlimited online training classes through CustomerSource, hands-on training courses for SmartList and SmartList Builder from Wipfli, or one-on-one training with your local consultant.
Believe me when I say that when someone is used to getting a basic, boring, nearly prehistoric paper report once a month, they will be BLOWN away when it is replaced with an interactive, live, and, dare I say, pretty pivot table.
We told you in our last posting that if you own SmartList Builder, you own Excel Report Builder. Guess what – you also own two other tools! Stay tuned for future Get Dynamic blogs to learn more about the tools with SmartList Builder.
Until next time, keep getting Dynamic!
Jessie