In Search Of....Creative Management Reporter Solutions

I only have vague memories of the old "In Search Of" series hosted by Leonard Nimoy, although those long forgotten memories also recall a bit of creepiness and more than a couple nightmares prompted by the episodes.

Today, I am not looking for Bigfoot or trying to debunk the myth of the Bermuda Triangle, but I am in search of creative solutions for two Management Reporter dilemmas I have run in to recently.  I would love to hear from anyone who has found elegant/not so elegant solutions to the following issues:

• Deploying Management Reporter in environments that do not have a domain/Active Directory in place
• Dealing with large volume reporting, and the need to generate reports to the library for viewing as well as printing them for packets

I know that these are not "possible" today, but I wanted to hear from anyone who might have a workaround or even how they approached these limitations with their clients.  You can post on this blog, or email me directly at cphillips@bkd.com.

Thanks!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Bank Transfers, Interest Income, What's the Diff?

I ran across a wierdo issue, that I almost can't believe I haven't run across before.  So a special shout-out to a fellow BKDer who found this issue in Dynamics GP.  Now, maybe some of my fellow bloggers may have an answer/fix to this.  But I suspect is it just how GP categories the transactions in Bank Reconciliation.

Let's take the example of a bank transfer (Transactions>>Financial>>Bank Transfer Entry).  We record and post a Bank Transfer. Then, let's say we want to report on said transfer in Smartlist (Microsoft Dynamics GP>>Smartlist>>Financial>>Bank Transactions).

Everything looks good until we see the CM Trx Type field.  I would expect to see Transfer or something like that, but instead I see Interest Income.  This is true for both sides of the transfer.  And I know it's a transfer due to the Source Doc Number and Source Document, as well as the fact that I can drilldown on the transaction and it opens up the originating Bank Transfer.

Not a big deal, but definitely odd and something to be aware of.  In this case, I told the user to rely more heavily on source document in conjunction with CM Trx Type as well as Source Doc Number.

Anyone else come across this?  Anyone know why this happens?  I suspect it is something to do with the numbers used in the DB for the different trx type, and GP using two different types with the same number.  But if someone knows more details, I would love to hear them!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Implementation Trauma, Number Three!

I know you all have been waiting, staying up at night, just hoping that I would finally do a post on the #3 consulting and customer sins regarding software implementation. So here I am, no need to wait any longer!  It has been awhile since I did my original post regarding my personal top 5 consulting and customer sins.  I should emphasize that I came up with this list through my own personal experience, and my experiences teaching Sure Step to partners.  In no way do I want to imply that I am not guilty of these sins (as a consultant and customer bother)!  Much of my learning has been the hard way, so my hope is that through these posts I can help someone avoid the mistakes I have made :)

To revisit the list:

Top 5 Consulting Sins-

1. Assuming you are the sole reason for the success/failure of the project
2. Forgetting that customer service is important even during the heat of an implementation
3. Ignoring risks as a way to avoid difficult conversations and/or to not "rock the boat"
4. Forgoing proactive change management for many of the same reasons as #3
5. Losing yourself in the "weeds" and forgetting the reasons/goals for the implementation

Top 5 Customer Sins-

1. Assuming that the consulting team is the sole reason for the success/failure of the project
2. Approaching the consulting team as adversaries instead of partners
3. Underestimating the organizational change associated with implementing software
4. Not placing value on the time spent by employees on an implementation
5. Inadequately voicing/sharing your goals, whether due to limited budget, resources, or time (or energy!)

Let's take a closer look at number 3 on the consulting list-- Ignoring risks as a way to avoid difficult conversations and/or to not "rock the boat".  Come on now, be honest....who has avoiding an issue or a risk in hopes that it will resolve itself?  I find that I procrastinate most on risks that involve people.  For example, let's say that you are working with the customer's internal project manager. You really like them, have had lunch with them, shared stories about your kids.  But he/she seems buried in work.  And, increasingly, the follow through on tasks is delayed and/or sloppy and you find yourself spending more time managing the customer's resources.  What do you do?  What can do you do?  Ignore it, hope that it gets better?  Maybe you have tried to gently discuss it with him/her?  These are all important questions, but it really comes down to two things--

Is this a risk to the project?  YES.  Do you (or your project manager) have an obligation to proactively manage this risk?  YES.  Maybe a more direct conversation with the internal project manager is needed.  Maybe involving the customer's project sponsor and/or the project manager's supervisor in a brainstorming session to determine possible solutions to the bottleneck.  The key here, in my opinion, is to focus on the proactive, focus on the solutions and causes...not on the details of who did or didn't do what.  Sure, the details can be important in focusing the brainstorming, but try to keep it out of the he did/she did territory.  In the end, the whole project team (customer and consultants) is in it for the reason- success.  So focusing on why we manage risk (to increase the probability of success), can help everyone rise above the overwhelming details.

So what about on the customer side?  Number 3 is -- Underestimating the organizational change associated with implementing software.  Wow.  That is a big one.  Seriously.  With change comes uncertainty, about roles, about processes, about what this means for the organization.  Much like pain management, organization change management is about being ahead of the "pain" of change.  Mark works in customer service. He has heard about the new system, but really hasn't been involved in the implementation process.  What he does know, though, is that the new system is supposedly more "efficient" than the current one.  And with efficiencies, it seems like they might not need as many people working in customer service.  Not to mention, he has barely learned how to do his job in the current system after being hired a year ago-- the idea of learning a new system right before the busy season really concerns him.  These are all things that the executives, project sponsors, and managers involved in the implementation need to consider.  Discussions about what this change means to the organization, and to the people who rely on it for their livelihood, should not be avoided.  By opening up communication regarding the changes coming, the implementation becomes a company-wide effort where everyone can share in the success.  Maybe there will be less staff in customer service, but all of the new reporting tools means that new sales analyst positions will be available.  Finding this balance of the benefit to the organization and to the employees is key along with strong, positive leadership regarding the change.
 
Well, those are the sins for today.  Two more to go.  As always, please feel free to share your thoughts.  More and more, I am reminded that software functionality is only one part of the equation...the rest of it is filled with the people, processes, and methods that work with it to achieve success.

UPDATED 6/1: I need to update this with a shout out to Mark Polino, whose repost of this article reminded me of something I meant to include.  One of my favorite books, that has served me well with gaining the "gumption" to approach difficult topics (see the discussion about proactively managing risk above).  Difficult Conversations by Stone, Patton, and Heen.  Check it out if you need to up your "gumption" quotient!
Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

Account Level Security and Advanced Search, Uh-Oh

It still amazes me that although I have been training for over 10 years, I still have students "discover" new "features" in Dynamics GP.  I just had a case pop up in class last month, regarding account level security and the advanced search feature in the account lookup window.

But, first, a little background for those of you not familiar with account level security (also referred to as organizational structures).  This feature of Microsoft Dynamics GP allows you to assign users and GL accounts to branches of your organization structure, thereby restricting the accounts that a user can access.  Companies use this for a variety of reasons including:

• Ensuring that users don't post to incorrect accounts
• Securing accounts that users shouldn't be able to view (e.g., balances or other information)
• Simplifying the interface for a user so that they only see the accounts they use

So, let's assume in my example that I have configured account level security to display a restricted list of accounts for my payables clerks.



Note that when they use the Accounts lookup to select an account number, the first account listed is 000-6170-04 (the first account in their restricted list, NOT the first account in the complete chart of accounts).  This is working as designed, as we only want the payables clerks to see their expense accounts.  But....what happens if a user users the Advanced Search feature to locate an account?

So, in this example, I clicked the Advanced Search icon (the binoculars called out in the above screenshot) and chose to search by Account Number begins with 000-1.  And, surprisingly, I get back a list of accounts that meet that criteria but are NOT part of the restricted list that I am able to use due to acccount security.  If I try to pick one of these accounts, I get the following error:

So, although you can SEE the accounts, you still can't USE them if you don't have access to them.  I know you are probably wondering, well, what's the problem then?  Well, it is a minor one, but what this means is that users need to know that although the list is secure for posting, reporting, etc. it is NOT secure from viewing the account descriptions.  Account descriptions may contain sensitive information like names that users may assume can't be seen if a user doesn't have access to it.  But it can be seen.  So plan accordingly.

There is a problem report for this, but is a bit old and doesn't seem to be a popular one...so definitely contact your Partner or Microsoft support if you want to be added to the list of customers experiencing this issue.  Here are the details:

MBS Great Plains 4799 - ‘Search Accounts’ returns all accounts, not just enabled

Have a great Thursday!

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.

eConnect Integrations: 90% Data, 10% eConnect

eConnect for Microsoft Dynamics GP is fantastic.  It's easy for a .NET developer to use, it's fairly comprehensive, it's fairly well documented, and it's relatively fast.  Except for the occasional missing field, unsupported transaction or bug, I love working with it.

Unfortunately, I have found that among non-technical folks, there is often a misconception about what it takes to develop a complete eConnect custom integration.  I am guessing that they think that eConnect should be no different than Integration Manager, where in a few hours or less, you should be able to produce a fancy integration.

It's rarely that simple.  While trying to explain to a GP partner why a POP + SOP + Project Accounting integration would really take 40 hours to develop, I came up with this way of describing it:  eConnect is the easy part.  That's the 10% of the equation.  It's a standard API with good documentation.  It's a known quantity.  The other 90% is dealing with the client's source data file, developing business logic for processing the data, and handling exceptions in that data.  And don't forget testing, debugging, minor changes, installation, and documentation.

Sure, I have had situations where customers provided a very simple and clean data file that went straight into GP.  No data transformations, no lookups, no exceptions or errors--a perfect integration.  Those are simple and easy.  But those situations are generally rare--in part because such integrations can often be done with Integration Manager or even a table import (not that I'd recommend a table import...).

The real power and some of the greatest benefits of custom eConnect integrations comes with complex integrations.  And with such integrations, it isn't eConnect that provides the benefit as much all of the extra logic that can be included with custom integration.  For example, importing a customer with eConnect is essentially always the same.  Customer object, address object, populate the properties, and send it off to eConnect.  Simple.

But when the customer data is in multiple files, or different data formats, or the address isn't parsed at all or is incomplete, or the data needs transformation, or has exceptions that need to be handled, that is when additional custom .NET code can come into play to make the integration much more valuable than a generic data import.

A timely example:  I had a phone call today with a client that receives over 600,000 cash receipts every month, a number that is rapidly growing.  What they actually receive is a single giant payment from an external trading partner, and that trading partner also provides them with a 150MB text file containing the list of every customer payment.  Not only do these cash receipts need to be entered, but they also need to be applied to invoices--so think of it as over 1,000,000 transactions.  Every month.  They would like to automate this process to eliminate the 3 full days of work that are required to enter and apply the cash receipts.  In concept that doesn't sound so bad, and eConnect can obviously handle that.

So I then ask if there are any exceptions in the data that are handled manually, or problems that require attention before the cash receipt can be entered or applied.  "No, not really" the customer answered. Hmmm, that seems unlikely.

About 30 seconds later, I ask them which column in the data file is the Dynamics GP customer ID.  It turns out that the customer ID is not in the data file--only the customer name.  Sometimes the customer name is the same as GP, sometimes it's similar, and sometimes it is completely different.  The accounting staff manually looks up each customer name in GP to determine which GP customer sent the payment.  I don't know about you, but I would call that a giant 600,000 line exception.  Okay, so we can setup an easy method to map the customer name in the data file to the GP customers.  Not a problem, but note that this has nothing to do with eConnect.

I asked about the additional worksheet in Excel where some of the data had been separated.  "Oh, that's a different type of cash receipt, we handle those separately."  So now we have regular cash receipts, and special cash receipts.  Another exception, to be handled well before eConnect gets involved.

And what about the payment applications?  Any issues with those?  "No, those are pretty standard."  But, the first cash receipt we look at in GP is not applied to an invoice, because there is no invoice available for that customer.  Another exception.  And then we find an overpayment.  Another exception.  And then an underpayment.  Another exception.  After discussing overpayments and underpayments, they explain that they want debit and credit memos automatically created, and then applied to the customer's credit or debit balance.  But only if the difference is within a certain threshold, such as under $100.  If the difference is greater than $100, it should appear on an exception report requiring a review.  Makes total sense, and mimics their manual processes.  But eConnect is still only peripheral at this point.

The requirements and exception list goes on, and I now have a good idea of what needs to be done.  And you know what?  The LEAST of my concerns is eConnect.  It's dealing with the 150MB data file and the thousands of exceptions that the integration is going to need to handle during the import. 

And when an exception or error occurs, I'll need to create an exception file so that they can deal with just the 1,000 records with errors instead of swimming through the massive 600,000 line data file.  And don't forget reporting--they will need a report or log listing how the cash receipts were imported and the invoices to which they were applied.

It's a fantastic project, and I can't wait to help them turn a 3 day ordeal into a simple 15 minute process.  But roughly 90% of my work will be wrestling with the source data, while only 10% will be working directly with eConnect.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

http://www.precipioservices.com

Dynamics GP .NET eConnect Developer Wanted

5/18/2011

I hear and read news reports saying that the US economy is still in bad shape, but I am having a hard time reconciling that news with the continuous projects that I've been frantically working on for the last 6 months.  And when I've reached out to a few GP .NET developers that I know to see if they can help with a few projects, they too have typically been fully booked, working late nights just to keep up.

So, if you are a Dynamics GP .NET eConnect and VS Tools developer, or if you know of one, I'd love to chat.

I'm looking to have additional resources available for those times when 5 clients suddenly want work done immediately, or when I'm swamped and a new project pops up out of the blue.

Ideally, I'm looking for developers located in North America who are independent contractors.

He or she should be proficient with Visual Studio, C# and VB, as well as eConnect, Visual Studio Tools for GP, and SQL Server.  Strong understanding of Dynamics GP functionality, distributions, GL accounts, and debits and credits is desirable as well.

I can be reached at steveendow@gmail.com.

And a One, And a Two, SQL Report Security

One of the most valuable (and yet underutilized) benefits of Microsoft Dynamics GP is the standard SQL Reporting Services (SSRS) reports that are available. These reports are valuable for a number of reasons including:

• Delivery via web to non-GP users

• Easy export capability to Excel, Word

• Ability to modify existing reports and access additional information

• Ad-hoc reporting capabilities using standard Report Models

• Subscription capabilities within SQL Reporting Services (delivers the report to your inbox!)

I know I have other posts dedicated to my love of SSRS, so I will spare you another love letter for now. But I do want to chat a bit about SQL Reporting Services security.

It seems like Dynamics GP users fall in to three main categories when it comes to security:

1. Everyone sees everything- Most common when the finance department is small and there is not a lot of delineation of duties. So often in GP, all users are POWERUSERS.
2. Limited access for convenience only- Maybe some security is defined, but only to help simplify the user experience. From a reporting perspective, there are no concerns about “protecting” information. This is usually the case when there is a delineation of duties, but due to overlap and backups, everyone knows everything anyway.
3. Limited access out of necessity- Security is defined not only to simplify the user experience, but also to protect data from unauthorized access. This is very common when payroll and human resources are included in the system, or in larger organizations with multiple offices/companies/entities to consider.
In the first two scenarios above, SQL Reporting security is not much of a concern. But in the third scenario, we would really need to be measured in our approach to ensure data is appropriately secured. I think, sometimes, it is assumed that customers fall in to #1 or #2 because of our understanding of their GP user base. But we need to be careful and consider the user base for SQL Reporting, and ensure that the customer understands its security model as well.

SQL Reporting security can be broadly categorized in to three areas:

• Site Security

• Item Security

• Data Security

Site Security is defined in Report Manager (generally http://servername/reports) using the Site Settings link. This is where you define who has permissions to manage and administer site level settings. Users do not have to be defined in Site Security to access and use SQL Reports. They only need this level of access to allow them to manage aspects of the site like system properties and schedules.

Item Security is also defined in Report Manager. This controls access to the folders and reports on the site. This can be accessed by choosing Properties or Security from the drop down list that appears when you click on an item. Or by using the Folder Settings button. By default, when an Windows user or group is assigned to an item in Report Manager, all items below inherit the access. For example, if a user is granted access to a folder, the same user also has access to the reports in the folder. This parent-level security inheritance can be broken by item by accessing security for the item. For example, if the user shouldn’t have access to one report within the folder, you can access security for that report and delete the user (therefore “breaking” the parent-level security inheritance).

Okay, so here is where it gets confusing :) Item level security controls access to the report. HOWEVER, being able to get to the report is NOT the same as being able to generate the report. This is where data security comes in to play. This can take on a lot of angles, depending on how you have configured the data source for your report. But let’s assume that you have configured it to use Windows credentials (that are not stored in the data source).

Data Security is defined in SQL Server Management Studio. This security controls the user’s ability to access the tables, stored procedures, and views necessary to generate reports. Keep in mind that although we are defining security for use with SQL Reporting Services, the data level security you define also allows users to access the data through Excel, Crystal Reports, Access, or another ODBC data source. For this reason, it is important that Data Security must be as restrictive as your requirements necessitate to prevent unauthorized access to data. Simply restricting access at the Item Level would only limit the user’s ability to access the data using that particular medium. If Data Security gives them access to the underlying data, they could access it with Excel, Access, etc even if they can’t access the SQL Report itself.

To configure Data Security in SQL Server Management Studio, the user must first be set up under Security>>Logins using their Windows login. Then, using the User Mapping tab in User Properties, you must map the user to the appropriate company databases and the corresponding database roles (to grant access to the necessary tables, views, and stored procedures to generate the reports).

Fortunately, Microsoft Dynamics GP comes with a number of predefined roles that begin with “rpt” specifically for use with the SQL reports. These roles have access to specific tables, views, and stored procedures. And the access is restricted so that data cannot be updated, only selected/viewed. Remember, whatever access you give them at the Data level can be used with Excel, Access, etc. So it is important, if you use roles outside of the standard “rpt” roles provided by Dynamics GP , to ensure that the roles do not grant additional permissions that you would not normally allow (e.g., updating tables, accessing additional tables, etc)

When planning security for your SQL reports, it is important to consider all three levels of security. There are resources on CustomerSource to assist with this process including:

Frequently Asked Questions About SQL Reporting Services and Dynamics GP

Download the SQL Reporting Services Administration Guide (last updated for GP 10), which includes additional information on security

Christina Phillips is a Microsoft Certified Trainer and Dynamics GP Certified Professional. She is a supervising consultant with BKD Technologies, providing training, support, and project management services to new and existing Microsoft Dynamics customers. This blog represents her views only, not those of her employer.