Curious as to what Workflow options are available in Dynamics GP? Check-out the full post HERE! Microsoft has done a fantastic job with the workflow system and our post touches on the pros and cons as well as what features and workflow options you should expect if/when you decide to jump on board with workflows.Read More
Quick show-off of how to utilize SSRS to deploy improved Fixed Asset Projection reports in Dynamics GP 2018. Versions are a month over month report as well as a summary/detailed projection listing with G/L account.Read More
1099 reporting out of the box is great… However… These 1099 reports are unmatched! Save time, get cleaner reports & learn some great reporting tricks & habits along the way.Read More
It is a typical KPI request to be able to report on unit account values. Let's go ahead and say that we're looking at a profit and loss report, by product and the business wants to see units sold that are included in that margin.
In a perfect world (and using some tools that are not named Management Reporter), if you're capturing the Product A quantity in the Sales Order Processing (SOP) module in GP, you'd be able to reference the sum of the sales instead of using a unit account. However... We don't live in a perfect world whilst using Management Reporter thus Unit Accounts are required.
So what we'd see is in GP, we'd setup a unit account for something like "Units Sold - Product A"
In Management Reporter, we'd then have a row definition that looked something like...
Sales Product A
- Expenses Product A
/ Units Sold - Product A
In a typical fashion, in order for this report to be up to date, the accounting team would need to make a routine journal entry to the unit account within GP. This process leaves way for manual errors, timing issues & the potential for management to lose confidence in their reports.
Using the method below, we complete streamline this process by automating the unit account journal entry within GP.
Part 1: Associate a unit account with an inventory item in GP.
Note: For example deliverable, one item may be used at multiple locations (segment 1 of COA). Therefore, we just assign the Natural account to the item & then let the integration define the remaining COA segments)
Approach: We accomplish this using
Part 2: Setup integration that captures posted activity in GP & creates journal entry in GP.
Note: For example deliverable, we're pulling the prior day's activity & creating a journal entry first thing in the morning.
Note: Integration script defines COA segments based on the SITE of the sale on SOP invoice & then the natural account that we associated to the item.
Approach: We accomplish this using
ODBC Connection to Company Database - Selecting Prior Day Sales
Part 3: Enable map/integration schedule. This is an out of the box functionality of SmartConnect.
Part 4: Create reconciling reports using SmartList/SmartView. These reports can be used at month-end to reconcile financials.
Note: This report is best suited to be built using a Sales Line Item report, filter on the posting date & ensures that returns are shown as negatives
Part 5: Notify accounting team that all they need to do moving forward is post their batches & reconcile once @ month-end!
Integrated transactions in GP would look similar to...
Note: All prior day activity are rolled into 1 line. Option to separate for each sales transaction would be available if desired.
A few other notes to consider in this process...
- Returns must be accounted for
- When deploying, it is recommended to have the accounting team reconcile more frequent. As they gather an understanding of process and ensure that numbers are tying out, reconcile less frequent.
- Failed integrations should auto-notify an integration PowerUser. PowerUser could re-run integration if/when needed
This solution is built in a manner that's easy to implement & maintain and quickly accomplishes the goals of time savings, accurate data & providing a more robust GP system.
Thinking about implementing for your business and looking for additional details? Already implemented and have feedback? Let us know in the comments below!
A common request we receive is: "We want to tie down security of our users but have no clue as to who has access to what". To be frank, the reporting for this in GP is not exactly easy to come by. While there are a number of ISVs available that will provide you with tools and analytics for this information, we have a quick fan-favorite solution via SQL to gather this data.
This information can also be found on the Microsoft Developer Blog here.
First thing's first, in order to get the following script to populate data, the Clear Data Procedure on the Security Resource Descriptions table within GP. This can be done from Microsoft Dynamics GP>>Maintenance>>Clear Data
Select 'Display' in the toolbar change to 'Physical', Series = 'System' & Table = Security Resource Descriptions.
After inserting the table and running the clear data procedure, the SQL query will now populate the users>>roles>>tasks>>operations which can be used as a starting point to identify and cleanup security/internal control concerns.
SELECT DISTINCT S.USERID UserID, S.CMPANYID CompanyID, C.CMPNYNAM CompanyName, S.SecurityRoleID, coalesce(T.SECURITYTASKID,'') SecurityTaskID, coalesce(TM.SECURITYTASKNAME,'') SecurityTaskName, coalesce(TM.SECURITYTASKDESC,'') SecurityTaskDescription, coalesce(R.DICTID,'') DictionaryID, coalesce(R.PRODNAME,'') ProductName, coalesce(R.TYPESTR,'') ResourceType, coalesce(R.DSPLNAME,'') ResourceDisplayName, coalesce(R.RESTECHNAME,'') ResourceTechnicalName, coalesce(R.Series_Name,'') ResourceSeries FROM SY10500 S -- security assignment user role LEFT OUTER JOIN SY01500 C -- company master ON S.CMPANYID = C.CMPANYID LEFT OUTER JOIN SY10600 T -- tasks in roles ON S.SECURITYROLEID = T.SECURITYROLEID LEFT OUTER JOIN SY09000 TM -- tasks master ON T.SECURITYTASKID = TM.SECURITYTASKID LEFT OUTER JOIN SY10700 O -- operations in tasks ON T.SECURITYTASKID = O.SECURITYTASKID LEFT OUTER JOIN SY09400 R -- resource descriptions ON R.DICTID = O.DICTID AND O.SECRESTYPE = R.SECRESTYPE AND O.SECURITYID = R.SECURITYID
Without the Clear Data procedure ahead of time, the resource descriptions table would be empty and provide no detail on the security operations.
Security is never fun and sometimes hard to grasp your arms around. We hope this report helps. Please leave us any questions or feedback below & be sure to our out website @ ALaCarteGP.com.
Until Next Time!
We've run into multiple occasions where we've seen environments with top-notch resources, up to date servers and not an overly abundant amount of data yet they still seem to experience performance issues while generating reports.
The report itself would take 30+ seconds to generate but then the opening of the MR Report Viewer application would drag on and one and sometimes take upwards of a minute to open and display the generate report. When the Report Viewer application opens, it calls the reports in the report library (to an extent) and typically isn't a big deal. However, when you are working in an environment where Management Reporter reports are heavily relied on, the number of reports in the repository builds quickly. You'll also find that there is no option within Management Reporter or the Report Viewer (as of CU14) to clear the repository reports or set any amount of time to store the historical reports before clearing. This is where a bit of SQL magic comes in handy.
The following query defines an integer that is used to define how many days worth of reports that you'd like to keep in your repository. In our example, we decided that only 10 days worth were necessary and if any reports past 10 days were ever needed that we'd simply re-generate the report.
Please note that this query may take an extended period of time to run, especially when being used as a first time. We also highly recommend running this in a Development environment to ensure the desired outcome is what's expected before moving into production.
DECLARE @DaysBack integer Set @DaysBack = 10 delete reporting.reportlinetransaction where ReportID in (select id from reporting.report where RepositoryID in (select id from reporting.repository where StatusType = 0 and [Type] = 10 and CreateDate <= DATEADD(day,@DaysBack,getdate()))) delete reporting.ReportLineFinancial where reportid in (select id from reporting.report where RepositoryID in (select id from reporting.Repository where StatusType = 0 and [Type] = 10 and CreateDate <= dateadd(day,@DaysBack,getdate()))) delete reporting.ReportLineAccount where reportid in (select id from reporting.report where RepositoryID in (select id from reporting.Repository where StatusType = 0 and [Type] = 10 and CreateDate <= dateadd(day,@DaysBack,getdate()))) delete reporting.report where RepositoryID in (select id from reporting.Repository where StatusType = 0 and [Type] = 10 and CreateDate <= DATEADD(day,@DaysBack,getdate())) delete reporting.repository where statustype = 0 and [Type] = 10 and CreateDate <=DATEADD(day,@DaysBack,getdate())
After running this query, you should find that when generating your next report that Report Viewer will open up in a fraction of a time as before.
That's all for today everyone. Please feel free to leave any comments or questions below and check out ALaCarteGP.com.