We had a user recently who wanted to quickly see which users had access to which companies and the Security Roles which had been granted. While Dynamics GP does have a number of security reports, they are the standard ones which, while readable in the usual screen or printer output, cannot easily be exported to Excel. However, the client is on Microsoft Dynamics GP 2013 R2 and therefore has access to SmartList Designer.
While I could have done this entirely as a SmartList Designer report, I already had the majority of the SQL needed to generate this as a SQL View which SmartList Designer can access:
CREATE VIEW uv_PI_UserAccessAndGrantedSecurityRoles AS
['User Master'].USERID AS 'User ID'
,['User Master'].USERNAME AS 'Username'
,['User Master'].USRCLASS AS 'User Class'
,ISNULL(['Class Master'].DSCRIPTN, '') AS 'User Class Description'
,ISNULL(['Company Master'].INTERID, '') AS 'Intercompany ID'
,ISNULL(['Company Master'].CMPNYNAM, '') AS 'Company Name'
,ISNULL(['Security Assignment User Role'].SECURITYROLEID, '') AS 'Security Role ID'
,ISNULL(['Security Roles Master'].SECURITYROLENAME, '') AS 'Secuity Role Name'
SY01400 AS ['User Master']
SY40400 AS ['Class Master']
ON ['Class Master'].USRCLASS = ['User Master'].USRCLASS
SY60100 AS ['User-Company Access']
ON ['User-Company Access'].USERID = ['User Master'].USERID
SY10500 AS ['Security Assignment User Role']
ON ['Security Assignment User Role'].CMPANYID = ['User-Company Access'].CMPANYID
AND ['Security Assignment User Role'].USERID = ['User-Company Access'].USERID
SY09100 AS ['Security Roles Master']
ON ['Security Roles Master'].SECURITYROLEID = ['Security Assignment User Role'].SECURITYROLEID
SY01500 AS ['Company Master']
ON ['Company Master'].CMPANYID = ['User-Company Access'].CMPANYID
Of course, the other reason I used the SQL rather than recreating entirely in SmartList Designer is that I can use this SQL in future, but a SmartList Designer is only usable on the system on which it is created as there is no import/export functionality.
The 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:
One of the problems with the standard Tax Reports ( ) 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:
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.
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 (New and Modify (ringed in red):
) and you will see two new buttons at the left of the toolbar;
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:
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
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:
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
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;
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;