Fastest way to split delimited string in SQL Server

Source : http://www.sqlservercentral.com

/* 
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SET NOCOUNT ON;

SELECT [Value] FROM [dbo].[split_delimited_string]
   ('1||2||3||4||5||6||7||8||9||10||11||12||13||14||15||16||17||18||19||20','||')
      WHERE Value IN (1,2,3,4,5,6,7,8,9,20)
SELECT [Value] FROM [dbo].[split_delimited_string]
   ('1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20',';') 

SET STATISTICS IO OFF;

Technorati Tags: SQL,SQL Script,Script

SET STATISTICS TIME OFF;
SET NOCOUNT OFF;
*/ 
CREATE FUNCTION [split_delimited_string] 
( 
@str NVARCHAR(MAX),  
@sep NVARCHAR(MAX) 
) 
RETURNS @value TABLE (Value NVARCHAR(MAX)) 
AS 
BEGIN 
DECLARE @xml XML
set @xml = (SELECT CONVERT(XML,'<r>' + REPLACE(@str,@sep,'</r><r>') + '</r>'))
INSERT INTO @value(Value)
SELECT t.value('.','NVARCHAR(MAX)')
FROM @xml.nodes('/r') AS x(t)
RETURN; 
END

Microsoft Dynamics GP Extender Discontinuation Announcement

Source : Microsoft

Effective February 1, 2013, the Extender Standard module for Microsoft Dynamics GP (Extender) will no longer be offered by Microsoft and all associated SKUs will be removed from the
Microsoft Dynamics GP price lists.

eOne Solutions (eOne) will continue to support the existing Extender customers. New or existing customers who wish to purchase Extender can purchase them directly from eOne.
Microsoft will not make Microsoft Dynamics GP Extender available on the Microsoft Dynamics GP 2013 price list. Customers will need to work directly with eOne to obtain Extender for
Microsoft Dynamics GP 2013. Starting February 1, 2013, Microsoft Dynamics GP customers who previously obtained licenses to Extender via the Microsoft Dynamics GP price list will
receive a reduction in the Protected List Price and will not be charged Business Ready Enhancement Plan (BREP) fees for Extender on their next BREP renewal date.
The license keys for these modules will remain on existing customers’ accounts, allowing the continued use of the modules.
This announcement provides partners with the 120-days’ notice required pursuant to the Solution Provider Agreement (SPA).

For more info on the same click here

Microsoft Dynamics GP 2013 Fixed Assets Enhancements

Compared to the older version Microsoft Dynamics GP 2013 has come up with lot of additional enhancements in Fixed Asset module some of them are listed below

Historical depreciation amounts

By marking the Reset History in Detail option in the Fixed Assets Company Setup window, each depreciation transaction taken for an asset in closed years will have an offsetting transaction created. If the Reset History in Detail option is not marked, a summary transaction is created in the last period of the most recent closed year for all depreciation taken for the asset in closed years.

Fixed Assets Depreciation Detail and Fixed Assets Depreciation Ledger reports

When printing the Fixed Assets Depreciation Detail report or the Fixed Assets Depreciation Ledger report, you can enter a cutoff date and have the depreciation amounts calculated through that date. For example, if you select End of Previous Month, the report includes depreciation transactions as of the last day of the previous month. This allows you to print the report using a defined historical date.

Book Setup window update

After an asset for a book has been depreciated, you can't change the current fiscal year in the Book Setup window.
You can specify a calendar ID for the book. The calendar ID assigned to the book will be used for processing financial transactions such as retiring, depreciating, and transferring assets.

Depreciation Process Information window

You can reverse depreciation for all assets or for a group of assets using the Depreciation Process Information window.
The Progress button was removed from the Depreciation Projection window and the Depreciation Process Information window and replaced with a progress bar.

Posting in detail to General Ledger

General Ledger can be updated with Fixed Asset Management transactions in detail if you mark the Post in Detail option in the Fixed Assets Company Setup window. When a batch is posted from the Fixed Assets General Ledger Posting window, a General Ledger journal entry is created for each Fixed Assets Management transaction in the batch. For example, two Fixed Assets Management transactions are created when you depreciate all assets for a month and then retire several assets. When you post a batch from the Fixed Assets General Ledger Posting window for those two transactions, a General Ledger journal entry is created for each Fixed Assets Management transaction.

If the option is unmarked, all transactions are summarized into a single journal entry by account. When you post a batch from the Fixed Assets General Ledger Posting window, there will be one General Ledger distribution per account in the one journal entry.

Edit distributions before posting batch to General Ledger

You can review or edit the account distributions for a batch of Fixed Asset transactions before creating the batch in General Ledger using the Fixed Assets General Ledger Posting window. If the batch information is incorrect, you can delete the batch in Fixed Assets Management.

Intercompany transfers

You can use the Fixed Assets Intercompany Transfer window to transfer assets from one company (originating company) to a different company (destination company). You can transfer a single asset or a group of assets. Asset setup information is transferred with the assets to the destination company so you don’t have to set up the assets in the destination company. During the transfer, the asset or asset group is automatically retired in the originating company. You can only transfer assets within the same instance of Microsoft Dynamics GP.

Automatically generating the next asset ID

You can mark the Auto Generate Next Asset ID option in the Fixed Assets Company Setup window to automatically generate the next asset ID in the Asset General Information window. After marking the option, you can enter the next asset ID to use when adding a new asset. If you leave this field blank, you will have to enter the asset ID in the Asset General Information window when creating a new asset.

By automatically generating the next asset ID when transferring an asset to a destination company, you can avoid duplication issues with the existing asset IDs in the destination company. The next asset ID from the destination company's Fixed Assets Company Setup window is used to generate the new asset ID. If you are not creating a new asset ID for a destination company record, the existing asset ID for the asset in the originating company is used for the asset general record in the destination company.

Fixed Assets Management calendars

Use the Fixed Assets Calendar Setup window to create multiple calendars in Fixed Assets. When you create a calendar, you can base the calendar on the Fiscal Period Setup window, the calendar year, or an existing calendar year. For example, you can create a calendar for the corporate book that needs to depreciate assets according to the year set up in the Fiscal Period Setup window while the tax book needs a calendar to depreciate assets according to the calendar year.

You also can define whether the fiscal year is longer or shorter that a twelve period year and enter the percentage of a full year's depreciation that should be taken in that short or long year.

Integrate Analytical Accounting with Fixed Asset Management

For Fixed Asset Management transactions, you can create assignments and enter transaction dimension codes for the distribution accounts that are linked to an account class. You can save transactions with analysis information to a batch prior to posting the batch. You can view analysis information created for transactions in the Analytical Inquiry windows.

Reprint Bank Reconciliation Reports in Microsoft Dynamics GP

There may be a case where you need to reprint Bank reconciliation for which Microsoft Dynamics GP allows to do the same, below are the steps to reprint bank reconciliation.

1. On the Reports menu, point to Financial, and then click Bank Posting Journals. The following reports can be reprinted:
   • Reconciliation Journal: Select this option to reprint the Reconciliation Posting Journal.
   • Bank Deposit Journal: Select this option to reprint the Bank Deposit Posting Journal.
   • Bank Transaction Journal: Select this option to reprint the Bank Adjustments Posting Journal.
   • Cleared Transactions Journal: Select this option to reprint the cleared transactions in the last bank reconciliation.
2. In the Ranges list, click Audit Trail Code, and then type the appropriate audit trail codes in the From field and the To field.
3. Click Destination.
4. Select the appropriate destination, and then click OK.

Note : Make sure you have the access to this report if not follow the below steps to grant access

1. On the Microsoft Dynamics GP menu, point to Tools, point to Setup, point to System, and then click Security Tasks.
2. In the Task ID list, click the appropriate task ID.
3. In the Category list, click Financial.
4. In the Product list, click Microsoft Dynamics GP.
5. In the Type list, click Reports.
6. In the Series list, click Financial.
7. Click to select the Reconcile Journal check box, and then click Print Operation Access.
8. In the Report Destination window, select the appropriate destination, and then click OK to print a report that displays users who have access to the report.
   Note If you receive the following message, save the changes in the window, and then follow step 1 through step 8 again:

   Select a record first

9. Follow step 1 through step 8 for the following reports:
   • Bank Adjustment Edit List
   • Bank Adjustment Posting Journal
   • Marked Transactions Report
   • Outstanding Transactions Report
10. If users are not displayed on the report that is printed in step 8, follow these steps:
    1. On the Microsoft Dynamics GP menu, point to Tools, point to Setup, point to System, and then clickSecurity Roles.
    2. In the Role ID list, click the appropriate role ID.
    3. Click to select the appropriate security task ID check box, and then click Save.
    4. Follow step a through step h for the remaining reports.

Microsoft Dynamics GP 2013 Web Client Overview

As GP 2013 introduced with web client on its cards, which is a thin client and enables the below features

• Browser delivered Dynamics GP
• Leverage Silverlight and .Net
• Logic still remains with Dexterity
• Most of the ISV Products compatible.
• Secure Connection
• Gives customer greater choice  and flexibility
• Hosting GP got easier and more cost effective

Below are some useful links on the topic

1. MS Dynamics GP”12” Web Client
2. Dynamics GP 2013 Web Client Infrastructure Overview
3. Microsoft Dynamics GP 2013 Web Client Overview

What’s new in Management Reporter 2012

Management Reporter 2012 is loaded with lot more new features and flexibility to generate and deliver reports, below are some of the features and enhansments

1. Additional report design flexibility streamlines your report design process
2. Enhanced financial report collaboration capabilities offer the ability to react quickly to changes in your business
3. Updated interaction when viewing financial reports helps users find data quickly and add additional context to their reports
4. Deeper Microsoft Dynamics ERP integration delivers additional data to be included on your financial reports
5. Management Reporter 2012, supports the following Microsoft Dynamics ERP solutions:
• Microsoft Dynamics AX 2012
• Microsoft Dynamics AX 2009
• Microsoft Dynamics GP 2010
• Microsoft Dynamics NAV 2009
• Microsoft Dynamics SL 2011
6. Report Design Flexibility
• Quickly identify accounts/dimensions that have been omitted from report building blocks
• Format headers for rolling forecasts
• Easily save and reuse dimension combinations when designing reports
• Control how dimension descriptions are formatted and displayed
7. Financial Report Collaboration
• Usability enhancements to make Report Groups more discover-able by providing access from the navigation pane
• Schedule reports to generate on a periodic basis
• Publish to a read-only (.XPS) report format
• Publish reports to multiple Microsoft SharePoint® or any network location
• Publish a personalized Microsoft Excel® (.xlsx) file
• Personalized and secured view of data for all output types
• E-mail reports via SharePoint alerts
• E-mail a link to a report from within the Report Viewer
8. Interactive Report Viewing
• Quickly create a chart based upon selected report rows and columns
• Locate key features in Report Viewer with an enhanced toolbar
• Jump to key areas of the report for fast analysis
• Find a key value in a report, such as an account value or description
• Easily page within a report
• Add comments to important rows in a report version
• Copy comments from one version of a report to another
• Collaborate on a report by providing a link to the current report and 
• selected row when launching an instant messenger client from within a report
9. Deeper Microsoft Dynamics ERP Integration
• Trickle data from Microsoft Dynamics AX and Microsoft Dynamics NAV into Management Reporter data mart for increased performance
• Create financial reports based upon data in Microsoft Dynamics NAV General Ledger
• Drill from account and budget balances on a report to related information in Microsoft Dynamics ERP
• Display or filter on properties of dimensions and transactions to design more precise financial reports.
• Automatically integrate company information from your Microsoft Dynamics ERP 
• Report on Microsoft Dynamics AX 2012 budget reservations (preencumbrances/encumbrances) based upon budget funds available calculation as defined in budget control configuration
• Manage all users (both Microsoft Dynamics AX and Management Reporter) from within Microsoft Dynamics AX
• Dynamically align reporting trees with Microsoft Dynamics AX organizational hierarchies
• Choose whether to include or exclude budget submodels when using Microsoft Dynamics AX 2012 budgets

Microsoft Dynamics GP 2013 What's New

You can download the complete document on Microsoft GP 2013 What's new here.

A few of my favorite new features in Dynamics GP 2013 are as below.

• Web Access—Provide employees easy access to Microsoft Dynamics GP 2013 with the new web client.  They can now connect and contribute no matter where they are, via PC or mobile device.
  
• Flexible deployment options—Choose an on premise, cloud or hybrid deployment model to give your business the tools it needs to adapt quickly, while keeping IT costs under control.
  
• Application Service Console—Achieve flexibility without sacrificing security, the Application Service Console allows for the management of active Dynamics GP 2013 web sessions, the termination of inactive ones and the ability to control product configuration by user.
  
• 64-bit Compatibility.

Management Reporter 2012 - How to Clear Report Queue?

It is often noted that after generating report Management Reporter 2012 keeps the logs of the same and during the course these are piled up and at time you need to clear these report queues, below steps will help you to clear the queues.

1. Log in to SQL Management Studio with admin privilege.
   
   
2. Run the below script against Management Reporter Database
   USE ManagementReporter
   DELETE RepositoryMessage
   


3. Open Management Reporter and Check if this has cleared all the Report Queue.
   Tools >> Report Queue Status.
   

Management Reporter 2012 – Fixing Sample Reports to Work with Dynamics GP 2013

Issue : It has been noticed that when you install sample reports in MR 2012 and when you generate report you notice that no report comes up and you get error message page stating to correct account dimension or report date or reporting tree.

Explanation : This is because there is a mismatch between the default settings of the sample reports and Microsoft GP whereby then Link to Financial Dimensions is broken if you observer closely when you open Row Definition. The cause of the problem is that the segments in the Reports are defined as Segment1, Segment2 and Segment3 but in Microsoft Dynamics GP they are called Division, Account and Department.

Solution :  Below is the steps to install and correct the above noted issue.

• Install Management Reporter Demo Reports for GP by downloading from the link belowMicrosoft GP Demo Reports for Management Reporter 2012
  Note: The above link also explains you to install demo reports into MR

• Run the below scripts on the Management Reporter Database in the SQL Management Studio

             UPDATE ControlTreeCriteria SET DimensionCode = 'Division' 
         WHERE DimensionCode = 'Segment1'

       UPDATE ControlRowCriteria SET DimensionCode = 'Account' 
         WHERE DimensionCode = 'Segment2'

       UPDATE ControlTreeCriteria SET DimensionCode = 'Department' 
         WHERE DimensionCode = 'Segment3'

             Note: Take a backup of the Management Reporter database before you execute the above script.

• Navigate to Tools >> Refresh Cache Financial Data under Management Reporter Designer and complete the process.

• Verify any Row Definition and you should be able to see the accounts are fixed under the column “Link to Financial Dimensions”

Now run the report, you should be able to successfully generate the reports.

Why doesn't my checkbook balance in GP match my GL balance?

A common challenge with Dynamics GP (Great Plains) is keeping the checkbook balance in sync with the general ledger (G/L) balance of the matching g/l bank account.   This article will help you keep the checkbook balance equal to the general ledger balance. Overview    This issue happens a lot partly because users do NOT understand that the checkbook is a subledger of the general ledger in Dynamics GP.   So if you do any journal entry in the general ledger that affects a bank balance (i.e. the checkbook), you will cause the checkbook to be different than the general ledger balance.  That is the most common mistake that happens. But this issue also happens because of a poor software design by Microsoft.    There exists a system control that is supposed to help you keep the checkbook balance equal to the general ledger balance, but it is an optional system control that people don't know exists.   In the setup of your general ledger bank accounts (go to Financial > Cards > How To Fix It If you notice that your checkbook balance in Dynamics GP does NOT match your general ledger balance, here's what to look for. Ways to resolve it: 1)   Did you do any regular journal entries that affected the cash account?        Use smartlist to look for this. 2)  Do you have any undeposited receipts?       When a receipt is entered, it increases your general ledger bank account balance but the checkbook does NOT get increased until you enter the deposit. 3)  Are there transactions coming from other modules that are bypassing the checkbook?       There can be a variety of things that are examples of this: a)   We had a client that was creating a debit memo in the receivables module for customer payments that bounced (NSF checks).    So in the debit memo they were crediting the bank account g/l account.    However, the checkbook would not know about this kind of transaction. b)   Did you book a transfer between 2 bank accounts but you didn't use the Bank Transfer window?   If you do any kind of bank transaction (such as an increase or decrease adjustment), and you credit or debit another bank account in the g/l debits and credits, this will mess up your checkbook balance.    Because the checkbook for the other bank account involved will not know about the adjustment you did.   So all transfers between 2 bank accounts have to be done using the Bank Transfer window in Dynamics GP. 4)   Did someone enter a bank transaction and accidently change the g/l account? When you start a bank transaction in Dynamics GP, and you pick a checkbook, the system then defaults the general ledger cash account to the general ledger cash account associated with that checkbook.    However, you can accidently override this general ledger account.   So, if you do a bank transaction, and then you change the account to the wrong account, we have noticed that the checkbook balance doesn't get updated.   Of course, the general ledger is also wrong.   But so too will the checkbook balance.