Management Reporter 2012 - How to Delete Report Queue Manually?

All the Report Process are stored in the Management Report Database under the table "Repository".
To manually remove reports that are stuck in Queue status, use the below SQL scripts.

1. Log in to SQL Management Studio with admin privilege.
   
   
2. Run the below script against Management Reporter Database
   
   USE ManagementReporter
   -- View the Records
   Select * from repository where StatusType = 30
   or
   Select * from repository where StatusType = 30 and Name '<Report Name>'
   
   -- Delete the Records
   Delete Repository where StatusType = 30 and Name '<Report Name>'
   
   
3. Open Management Reporter and Check if this has cleared all the Report Queue.
   Tools >> Report Queue Status.

Post GP Upgrade Checklist

Validate Upgrade Completion:

• Ensure that the upgrade process has completed successfully without any errors or interruptions.
• Verify that all necessary components of Dynamics GP, including databases, modules, and customizations, have been upgraded to the desired version.

Backup Data and Configuration:

• Perform a comprehensive backup of all Dynamics GP databases, configurations, and customizations.
• Store backups in a secure location to ensure data integrity and facilitate restoration if needed.

Test Functionality:

• Conduct thorough testing of Dynamics GP functionality to ensure that all modules and features are working as expected in the upgraded version.
• Test core processes such as financial transactions, inventory management, sales, purchasing, and reporting.

Verify Customizations and Integrations:

• Validate that any customizations, integrations, or third-party add-ons are compatible with the upgraded version of Dynamics GP.
• Test custom reports, workflows, scripts, and any other tailored functionalities to confirm their continued functionality.

Review Security Settings:

• Review and update user roles, permissions, and security settings as necessary.
• Ensure that security configurations are aligned with organizational requirements and compliance standards.

Check Data Integrity:

• Perform data integrity checks to identify and resolve any inconsistencies or discrepancies in the upgraded data.
• Utilize built-in tools or scripts to verify data integrity across all modules and databases.

User Training and Communication:

• Provide training sessions or documentation to familiarize users with any changes or new features introduced in the upgraded version.
• Communicate effectively with stakeholders about the upgrade process, including timelines, expected downtime, and potential impacts on workflows.

Performance Tuning:

• Monitor system performance after the upgrade to identify any bottlenecks or issues.
• Implement performance tuning strategies such as database optimization, indexing, and configuration adjustments to enhance system responsiveness.

Documentation and Reporting:

• Document the upgrade process, including steps taken, issues encountered, and resolutions applied.
• Generate post-upgrade reports to track key metrics such as system performance, user feedback, and adherence to project timelines.

Continuous Monitoring and Support:

• Establish ongoing monitoring procedures to detect and address any post-upgrade issues proactively.
• Provide dedicated support channels for users to report any issues or concerns related to the upgraded Dynamics GP environment.

        By following this checklist, you can ensure a smooth transition and maximize the benefits of your Dynamics GP upgrade while minimizing disruptions to your organization's operations.

Microsoft Dynamics GP 2015 - Identity Management

One of the new feature added to the newly released GP 2015 is Identity Management where customers and partners waited to get this feature included.

So what is Identity Management ?

The identity management enhancement enables you to log into your Microsoft Dynamics GP Web Client user account with your organizational account (Azure Active Directory (AD) account). This alleviates the need to log in separately to access your network, SQL Server database and Microsoft Dynamics GP, relying instead on a single secure log in to deliver access to your GP workspace.

What's New in Microsoft Dynamics GP 2015

Source : Microsoft

System Wide Features

• Service Based Architecture 2015
• Identity Management 2015
• Enhanced Workflows
• GL Approval
• Payables Batch Approval
• Vendor Approval
• Receivables Batch Approval
• Employee Skills Approval
• Direct Deposit Approval
• Employee Profile Approval
• Expense Report Approval
• Copy Home and Area Page to Other Users
• Navigate to MR directly from GP

Financials

• Intercompany Enhancements
• Payment Terms Enhancements
• FA Year End Closing Reports

Distribution

• Email Enhancements for Historical documents
• Payables Warnings
• Invoice for Web Client

HR, Payroll & Self Service

• Project Employee Expense Report
• Employee Profile Edit
• Employee Profile Workflow
• Manager Term Profile
• Employee Skill & Training
• Employee Skills Workflow
• Managers Skill & Training
• Employee Benefits Inquiry
• Employee Paystubs Inquiry

Business Intelligence

• Refreshable Excel Reports

Watch out for detail write up on Dynamics GP 2015 enhancements in my next article.

Microsoft Dynamics GP Roamdmap

Source : Microsoft

Microsoft Dynamics GP 2015 is now available

Source : Microsoft

Benefits of Microsoft Dynamics GP 2015 include:

• Expanded workflows to help streamline financial, purchasing, sales, payroll, and project expense related approvals. These new native workflows enable customers to quickly customize their solutions and accelerate business approvals.

• Single sign on through integration with Microsoft Azure Active Directory to help users work seamlessly across Microsoft Dynamics GP, Office 365, and other cloud-based applications.

• Service based architecture to lower the cost and time required for customers and partners to create and modify business solutions built on Microsoft Dynamics GP.

• Microsoft recently released Business Analyzer R7, a "companion" tablet application available from the Windows, iOS, and Android app stores.

• As part of Dynamics GP 2015 release, Microsoft is also introducing a lower-cost self-service user license type for users who need only very light functionality within Dynamics GP. The self-service user CAL is a named user that will be applied per individual.

Microsoft Dynamics GP 2015 Functionality Discontinuation

Source Microsoft

Microsoft Operations would like to inform you that with the launch of Microsoft Dynamics GP 2015, Business Portal and Microsoft SharePoint Server 2010 runtime will be discontinued from Microsoft Dynamics GP 2015.

For Business Portal modules similar functionality will be available in within the core code base of Microsoft Dynamics GP 2015. More information on licensing guidelines will be provided as we approach the Microsoft Dynamics GP 2015 release.

As a result of the discontinuation, Business Portal and Microsoft SharePoint Server 2010 runtime will not be available to customers purchasing Microsoft Dynamics GP 2015 or those upgrading to the same. This functionality discontinuation impacts Microsoft Dynamics GP 2015 under the Perpetual Licensing model.

Further information on these modules, impact on existing customers and transition upgrades, please refer to the Microsoft Dynamics GP 2015 Functionality Discontinuation.

SQL Script to Assign SPECIFIC Items to a given Vendor in Microsoft GP

--------------------------------------------------------  
-- Assign SPECIFIC Items to a given Vendor     
-- Code by Venugopal G A     
-- mail me for any queries venuasg@gmail.com
-- Change the vendor variable to your vendorid     
--------------------------------------------------------    
DECLARE @VENDORID VARCHAR(15)
DECLARE @ITEM VARCHAR(100)
DECLARE @ITEMDESC VARCHAR(100)
SET @VENDORID = 'ACETRAVE0001' -- CHANGE TO YOUR VENDOR ID

DECLARE ITEMMASTER CURSOR FOR 
SELECT ITEMNMBR, ITEMDESC  FROM IV00101 WHERE ITEMNMBR IN ('ITEM1','ITEM2','ITEM3') -- REPLACE HERE TO ADD ITEMS
OPEN ITEMMASTER
 FETCH NEXT FROM ITEMMASTER INTO @ITEM, @ITEMDESC
 WHILE (@@fetch_status <> -1) 
            BEGIN
  INSERT INTO [IV00103] ([ITEMNMBR],[VENDORID],[ITMVNDTY],[VNDITNUM],[VNDITDSC])
  VALUES(@ITEM,@VENDORID,1,@ITEM,@ITEMDESC)
            
  FETCH NEXT FROM ITEMMASTER INTO @ITEM, @ITEMDESC
            END
CLOSE ITEMMASTER    
DEALLOCATE ITEMMASTER

SQL Script to Assign ALL ITEMS IN THE ITEM CLASS to a given Vendor in Microsoft GP

-------------------------------------------------------------------------  
-- Assign ALL ITEMS IN THE ITEM CLASS to a given Vendor     
-- Code by Venugopal G A     
-- mail me for any queries venuasg@gmail.com
-- Change the vendor variable to your vendorid     
-------------------------------------------------------------------------    
DECLARE @VENDORID VARCHAR(15)
DECLARE @ITEM VARCHAR(100)
DECLARE @ITEMDESC VARCHAR(100)
SET @VENDORID = 'ACETRAVE0001' -- CHANGE TO YOUR VENDOR ID

DECLARE ITEMMASTER CURSOR FOR 
SELECT ITEMNMBR, ITEMDESC  FROM IV00101 
WHERE ITMCLSCD IN ('CLASS ID 1','CLASS ID 2') -- REPLACE HERE TO ADD ITEM CLASS ID
OPEN ITEMMASTER
FETCH NEXT FROM ITEMMASTER INTO @ITEM, @ITEMDESC
WHILE (@@fetch_status <> -1) 
     BEGIN
 INSERT INTO [IV00103] ([ITEMNMBR],[VENDORID],[ITMVNDTY],
                     [VNDITNUM],[VNDITDSC])
 VALUES(@ITEM,@VENDORID,1,@ITEM,@ITEMDESC)
            
 FETCH NEXT FROM ITEMMASTER INTO @ITEM, @ITEMDESC
      END
CLOSE ITEMMASTER    
DEALLOCATE ITEMMASTER

Missing Sub-ledger distribution in Microsoft GP

It is noted that when you drill-down the AP transactions sometimes you notice that the distribution is missing or is blank and you may also get issues while inquiring the document through Payable Transaction Inquiry.

Cause: Most of the time this happens when the posting process is interrupted for various reasons and the user has killed the session to reset GP, during which the tables are partially updated.

Resolution: There is no direct approach as GP Checklinks will not fix this issue and alternately SQL script will do by inserting the missing records. You can refer PM80600 table to re-insert the information through SQL script into PM30600 (PM Distribution History File) for historical distribution because it is easier.  And use the GL20000 for PM Open documents going into the PM10100 table (PM Distribution WORK OPEN) - most information is available in the GL table assuming that the posting set up is either Post To or through.

If you compare the content of these tables, most of the information is almost identical.  It is just a matter of rearranging the field to suit the tables. Any missing fields can then be mapped to a constant value just like when performing Integrations.

Start with a simple select statement from the source table and making sure that the result of the query reflects the expected information before going into the destination table.

Using the record from the sample screenshot in the original post, you can find the distribution detail in GL by running the select statement below:

SELECT * FROM GL20000 WHERE ORCTRNUM = 'Trx No' AND ORMSTRID = 'Customer/Vendor ID'

What's New in Microsoft GP 2013 R2

Microsoft GP 2013 R2 has been released and has bunch of new features and enhancement in the UI, New Version looks better and powerful. You can find the what's new document published by Microsoft here.

Below are some of the features listed

• Foundation enhancements
• Purchasing Requisition functionality has been brought into the desktop side of the application with GP2013 R2
• Workflow is being completely redesigned and rebuilt to be based in Dexterity rather than SharePoint.
• The identity management feature for GP 2013 R2 has some addresses a couple of immediate needs with the web client but will also provide additional identity capabilities as we move into companion apps and a service based architecture.
• Improved Web Client Functionality.
•  Enhancement to SmartList Designer that release with SP2.  Enables user to create GoTo’s to forms, navigation lists, SmartLists and Web Pages from a SmartList record.
•  Take Company Offline.
• Print Reaming Documents for Email.
• Email or Print any reports in Microsoft Word.
• Email functionality added to the Customer Email Options to allow the end-user to point to specific email addresses to use based on the document type.
• Users now will be able to attach physical documents directly from a scanner.  
•  The OLE file attachment functionality on the notes will be replaced with Doc Attach Functionality which will work on both the Desktop Client and the Web Client. 
• For users that have OLE file attachments on Notes in Dynamics GP, HR or Manufacturing, running this utility will extract those files and them import them into the SQL tables for Doc Attach. 
• Microsoft SQL Reporting services on Transaction forms.
• With R2, we are freshening the user interface on the desktop client by adding ribbons to the forms and updating the font to a more current look.  
• Financial enhancements
• Reverse Fiscal Year - Undo the GL Year End Process.
• FA default Asset ID from Asset Class.
• Integration of Multi-Currency revaluation with AA.
• Copy and Paste to GL Entry
• Reprint Outstanding transactions report from Bank Reconciliation..
• Default sort order for payables check.
• Distribution enhancements
• Human Resources and Payroll enhancements
• Field Service Series enhancements
• Project Accounting enhancements

Optimize SmartList Performance in Microsoft Dynamics GP

SmartList Usage

SmartList and SmartList Builder are intended to be used as a query tool only.  If you decide to increase the maximum record count, be aware that query processing will be slow and you may experience errors.  Therefore, we recommend that you use the default record count limit of 1,000 when you execute queries.

SmartList Search Criteria

SmartList allows vast amounts of searching options that users can apply to the SmartList query data. With this flexibility comes the potential for very high I/O costs if the query / database design was not optimized for a specific search criterion. Review the suggestions below to reduce I/O cost when designing SmartList search criteria:

·      Avoid using the "is not equal to" filter

·      Use the "equal to" instead of "contains" filter

·      If the Smartlist includes WORK, OPEN and HISTORY tables and the user is only interested in WORK data, use the "Document Status" or similar search criteria to skip searching OPEN and HISTORY tables.

Default SmartList Objects vs SmartList Builder Objects

Default SmartLists may not always be the best option as the query is generic.  Given the amount of flexibility and amount of data to search it may be better to utilize a custom SmartList Builder that is optimized for the necessary results. 

SmartLists with Extender Data Added

Adding Extender fields from an Extender Window, Form, or Detail Form can cause a SmartList to return data slower.  The linking required to include the Extender data adds complexity to the SmartList.  It may be beneficial to look at using SmartList Builder to build a custom SmartList that includes the Extender data to optimize the SmartList.

Export of SmartList Data to Microsoft Excel
The export of SmartList data to Excel can take some time depending on the amount of data you are trying to export.  The number of columns included in the SmartList as well as the number of rows can influence the export performance.  A SmartList exports to Excel field by field to ensure the formatting is also brought across with the data.  Therefore, the export can take longer for all of the data to appear in the Excel spreadsheet.  If you are running SmartLists for the purpose of exporting the data to an Excel spreadsheet, you may want to consider the default Excel Reports that can be deployed in Microsoft Dynamics GP.  They are setup very similar to the default SmartLists, however the data is directly pulled into Excel so that you do not have to export from SmartList.  The additional benefit to this is that the data is refreshable in Excel so you do not have to continually export the data from SmartList.

PJOURNAL jobs in Microsoft Dynamics GP

                      The PJOURNAL table captures records as posting occurs in Microsoft Dynamics GP.  This table can grow to be very large depending on posting volume.  The Microsoft Dynamics GP installation creates a SQL Server Agent Job to truncate the PJOURNAL table in each company.  Verify the SQL Server Agent service is running in the SQL Server Management Studio.  Expand SQL Server Agent in the SQL Server Management Studio and verify the "Remove Posted PJOURNALs From All Companies" job exists and is enabled under the Jobs folder.   By default, the table is cleared every 30 minutes.  If the "Remove Posted PJOURNALs From All Companies" job does not exist, execute the PJJOB.SQL script in the SQL Server Management Studio to create the jobs.

  

The PJJOB.SQL script can be found in the Microsoft Dynamics\GP\SQL\Util folder. 

Database Maintenance Plans for Microsoft Dynamics GP

Once Microsoft Dynamics GP is installed and all company databases are created, a database maintenance plan should be configured for each database.  Database maintenance plans create a workflow of the tasks required to make sure that your database is optimized, is regularly backed up, and is free of inconsistencies. The Maintenance Plan Wizard allows you to easily create an Integration Services package, which is run by a SQL Server Agent job. These maintenance tasks can be run manually or automatically at scheduled intervals. Refer to the article below for recommended maintenance plans for Microsoft Dynamics GP.

CustomerSource: Download Here

PartnerSource: Download Here

Optimizing and Maintaining Performance for Microsoft Dynamics GP

Performance problems are a unique type of issue.
Therefore, Microsoft has published a detailed white paper for optimizing and maintaining Dynamics GP Performance. This white paper goes over everything related to performance for Dynamics GP covering any known issues as well as setup recommendations.

Click here to download the the whitepaper.