Saturday, August 8, 2015

Management Reporter Installation Checklist 2.0

I have noticed numerous SOLID blog posts, which walk through the installation steps for Management Reporter!  The Dynamics Community does not disappoint; the level of sharing and collaboration frankly astounds me. These blog posts include screen shots, and detailed instructions. 


Here is an especially good example, which includes links to other related topics Azure Curve How to Install Management Reporter Server

Management Reporter installation woes still seem to be the source of many posts on the community forums.  The reason, the DEVIL is in the DETAILS.

I originally posted the checklist below to address the numerous community posted related to Management Reporter Installation issues. I have since installed Management Reporter CU13 on SQL 2014 connected to Dynamics GP 2015 using my original checklist, and found a few wrinkles.

What follows is a revised and extended checklist.  

Make sure install user is BOTH a Domain and Machine Administrator
Add Server Role - Web Server to target server
Add Server Role - Application Server to target server
Enable .Net and ASP.Net (appropriate versions)
Enable IIS 6 Metabase Compatibility Feature for IIS 7 (and tools)
Enable WCF HTTP Activation (not to be confused with TCP/IP activation)
Disable User Account Control on target server
Reboot Machine - this is an optional step but should be done if issues occur
Run installation from a folder with short path-name (something just off root)
Run installation as Administrator (elevated privileges are necessary)
Do NOT select NT AUTHORITY accounts - use an Active Directory Account 
Ensure the user you have running the application pool has "log on as batch job" rights in the local security policy of the machine.
Remember to Register Management Reporter - default installation is Evaluation Copy, no reminder to register, until the trial period expires!
Set refresh cycle for Management Reporter to something better than every second
Set up ISO codes and currency access rights for Multicurrency in all companies
Set GL Integration flag in all companies
Deploy Management Report Integration in Report Settings 
Here are a few more links to related posts to get you through the final steps
Microsoft Knowledge Base Article 861642 - Currency Issues
Batch File to Restart Management Reporter Services
Enable Dynamics GP 2015 Management Reporter Integration
Change Management Reporter Refresh Cycle

Friday, August 7, 2015

SQL Views, NULL values and SmartList tools

I discovered something some time ago, and could have sworn I wrote a blog article about it, because I thought it was a tip that should be shared with the world. Today, I stumbled across the same problem again, and went to look up my post... yes I refer back to my own posts to refresh my recollection from time to time.

Anyway, I discovered I had neglected to write an article about it, but found the issue mentioned in a broader article written by luminary Dave Musgrave called Understanding how Dynamics GP works with SQL

In this article Dave explains that since Dynamics GP grew up on databases like Btieve and Ctree, and these databases typically avoided the occurrence of NULL values in the data set by initializing number and currency fields with Zero and String fields with blanks. Consequently, Dynamics GP doesn't intuitively know how to deal with NULL values.

This is important for users who are working with SmartList tools like SmartList Builder or Designer.

One of the best inherent features in SmartLists is the ability to search fields based on values or the lack thereof. If a NULL value is returned in a SmartList field, then there is no way to search/restrict your results based on the return of a NULL value.

The easiest way to resolve this issue is during the creation of the view your SmartList is built on. If you have a field returning NULL values, it is best to transform these null values into blanks instead. 

Here is an example of how to make this work.

Select 
CASE WHEN TABLE.FIELD IS NULL THEN ''
            ELSE TABLE.FIELD
END 'Field Label'
FROM TABLE

This will result in SQL and ultimately your SmartList column returning either nothing or the actual value in the field. The value nothing can be returned by searching for blanks in the field.

Failure to do this will both clutter your SmartList and limit the its functionality, so I highly recommend taking this into account when building SQL Queries, Views and SmartLists. 

NOTE: I did in fact write a blog post about this last October, and apparently mislaid it. Here is a link.

No filters on Null Values in SmartLists