MDGP 2015 Feature of the Day: Create Refreshable Excel Reports In SmartList Designer

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series of posts on the new features of, the soon to be released, Microsoft Dynamics GP 2015. As I have done for the last two releases, I intend to shadow these posts and add my own opinions on these new feature; the series index is here.

The twentieth Feature of the Day is Create Refreshable Excel Reports in SmartList Designer. This feature will enable users to create refreshable Excel reports using SmartList Designer. Reports created from SQL views may be published to the Excel report library.   

By publishing a refreshable report, the user can open the report in Excel and the data will always be up to date:

I like the look of this feature. The out-of-the-box Excel Reports are of somewhat limited utility as they return all data for the report which means that for a transaction SmartList there can be vast sums of data returned. But with these SmartList Designers you should be able to define a filter in the report so it only includes, for example, the current or previous months transactions.

The roles targeted by this feature are:

  • All

Click to show/hide MDGP 2015 Feature of the Day Series Index

SmartList To Show Transactions Included On VAT 100 Return

Microsoft Dynamics GPOne of the problems with the standard Tax Reports (Administration area page >> Reports >> Company >> Taxes) in Microsoft Dynamics GP is that they will include only items requested for the date range, but the VAT 100 Return itself will select everything in the specified date range and also anything dated before this period which has not been included in previous VAT Returns.

This is a problem as it means you can not run a report before generating the VAT Return to see what would be picked up. However, a little thought and consideration of how the SmartList wildcards work and a workable solution presented itself.

In SmartList select the Tax Detail Transactions SmartList Favourite under Company and click on the Search button:

Search Tax Detail Transactions

Under Search Definition 1 enter Tax Return ID in the Column Name. Set the Filter to begins with and enter [^2] in the Value.

This search definition will return all transactions which do not have a Tax Return ID beginning with a 2; this assumes you name your VAT Returns along the lines of 2014-08 to have them sorted by date. If, as some clients I have seen, you create your VAT Returns with the month’s long, or short, name first then replace the 2 with JFMASOND and all items not starting with one of those letters will be returned.

Using SmartList Designer To Create Vendor EFT SmartList

One of the really nice new features in Microsoft Dynamics GP 2013 SP2 is the SmartList Designer. This is, to an extent, a cut down replacement of SmartList Builder, which includes much, although not all, of the functionality and is more user friendly.

It also affords the opportunity to all clients to create a report which has long been missing from Microsoft Dynamics GP; a Vendor EFT SmartList which shows the EFT details against the Vendor Remit To Address.

In this post I am going to step through the process of creating this report.

To start, open SmartList (Microsoft Dynamics GP menu >> SmartList) and you will see two new buttons at the left of the toolbar; New and Modify (ringed in red):

SmartList

Continue reading

MDGP 2013 R2 Feature of the Day: SmartList Designer Go To’s

The Inside Microsoft Dynamics GP blog started a series of Microsoft Dynamics GP 2013 R2 Feature of the day posts the other day. As they did with the Microsoft Dynamics GP 2013 Feature of the Day posts they are doing them as short posts containing a PowerPoint slide show. I am translating these from the PowerPoints into posts; you can find my series index here.

The first Feature of the Day covered is SmartList Designer Go To’s. SmartList Designer was introduced in Microsoft Dynamics GP 2013 SP2 and providd similar, although a much cut down, functionality of SmartList Builder.

This new feature is GP 2013 R2 adds a very important piece of missing functionlity; the ability to add Go To’s to SmartLists created in SmartList Designer.

When a user creates a new SmartList based off an existing SmartList, the GoTo’s that already exist are copied over to the new list created; when a user creates a new SmartList, there are three types of GoTo’s which can be added to the new SmartList: another SmartList, a navigation list or a web page:

SmartList Designer - Go To

Continue reading

Microsoft Dynamics GP 2013 R2 Coming Soon

The next version of Microsoft Dynamics GP, 2013 R2, is going to be released soon. I can hardly wait, but, unfortunately, I have to :(

The Dynamics GP Support and Services Blog have posted an article today outlining their intended blog post schedule where they are going to run through the upgrade process and all of the new features which are due in the coming version.

  • April 29 – Lifecycle
  • May 1 – Upgrade Planning
  • May 6 – Known Upgrade Issues
  • May 8 – Version Checks & Upgrades
  • May 13 – Database Upgrade
  • May 15 – Modified Dictionary Upgrade
  • May 20 – Upgrade Troubleshooting
  • May 22 – GP is upgraded – what’s next?
  • May 27 – Upgrading Web Client
  • May 29 – Tables Changes in R2 – Review the SDK
  • June 3 – Identity Management
  • June 4 – Azure Backups
  • June 5 – New Workflow
  • June 10 – Requisition Management
  • June 11 – OLE Document Attach
  • June 12 – Human Resource Time Entry
  • June 17 – Project Accounting Time Entry
  • June 18 – General Ledger
  • June 19 – Payables Management
  • June 24 – Receivables Management
  • June 25 – Bank Reconciliation
  • June 26 – Analytical Accounting
  • July 1 – Fixed Assets
  • July 2 – Sales Order Processing
  • July 3 – Inventory Control
  • July 8 – Purchase Order Processing/Encumbrance Management
  • July 9 – Email or Print any report in Word
  • July 10 – SmartList Designer 2.0
  • July 15 – Office 365
  • July 16 –Take Company Offline
  • July 17 – Ribbons in Desktop

Review of Microsoft Dynamics GP 2013 Reporting by David Duncan and Christopher J Liley

I have received a copy of the newly published Microsoft Dynamics GP 2013 Reporting, by David Duncan and Chrisopher J Liley, from Packt Publishing to review. This is an update of a previous book the pair wrote for Microsoft Dynamics GP 2010:

Microsoft Dynamics GP 2013 Reporting

The book covers an extensive range of the reporting tools in, or available with, Microsoft Dynamics GP 2013:

  • SmartList Builder including Excel Report Builder
  • Report Writer and Word Templates
  • Reporting Services Reports
  • Analysis Cubes
  • Management Reporter

Continue reading

Microsoft Dynamics GP SmartList Default Visible To Company

In July, as part of his weekly MS Connect suggestion series, Mark Polino raised the issue of SmartList Favourites and the default Visible To which is set to System.

Mark’s suggestion was to have the default changed to User which would leave a generally tidier list. To be honest I’d rather have a setting which allowed the default to be chosen by each client. The reason for this is we’re currently in the middle of an upgrade project where a few Microsoft Dynamics GP systems will be merged into one and it would be good to have the default Visib le to set to Company.

Following on from my recent post on a fixed width SmartList left pane I decided to have a fiddle and see if I could force the default to something else. And it turns out with some simple VBA that you can do exactly that;

SmartList

Continue reading

Microsoft Dynamics GP 2013 SmartList Fixed Width Left Pane

In July last year, Frank Hammelly posted a SmartList customisation for Microsoft Dynamics GP 2010 on his blog gp2themax.

I’ve recently been taking a look at Microsoft Dynamics GP 2013 and decided to see if I could use Modifier to reproduce this customisation for GP 2013 (I have no reason to believe the customisation posted by Frank won’t work with 2013, but I wanted to dabble with Modifer and this seemed a good starting point); the customisation can be downloaded at the bottom of this post.

The screenshots below show SmartList in both it’s default size and maximised with the left pane remaining a static size and the right pane occupying the rest of the window;

SmartList

Continue reading