Dynamics Tips

What are SSRS, SSIS and SSAS

2011-09-13T04:12:00.000+04:00

Let me give a brief introduction of SSRS, SSIS and SSAS as you can get more details on these topic if you google it.

SSRS – SQL Server Reporting Services
Microsoft SQL Server Reporting Services (SSRS) delivers enterprise, Web-enabled reporting functionality so you can create reports that draw content from a variety of data sources, publish reports in various formats, and centrally manage security and subscriptions.
Microsoft SQL Server Reporting Services enables organizations to transform valuable enterprise data into shared information for insightful, timely decisions at a lower total cost of ownership.
SQL Server Reporting Services is a comprehensive, server-based solution that enables the creation, management, and delivery of both traditional, paper-oriented reports and interactive, Web-based reports. An integrated part of the Microsoft Business Intelligence framework, Reporting Services combines the data management capabilities of SQL Server and Microsoft Windows Server with familiar and powerful Microsoft Office System applications to deliver real-time information to support daily operations and drive decisions.

SSIS – SQL Server Integration Services

It is a Data Warehousing Tool; SSIS is one of the key features introduced in SQL Server and is the new DTS (Data Transaction Services) platform. Microsoft Integration Services is a platform for building enterprise-level data integration and data transformations solutions. You use Integration Services to solve complex business problems by copying or downloading files, sending e-mail messages in response to events, updating data warehouses, cleaning and mining data, and managing SQL Server objects and data. The packages can work alone or in concert with other packages to address complex business needs. Integration Services can extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations.

Integration Services includes a rich set of built-in tasks and transformations; tools for constructing packages; and the Integration Services service for running and managing packages. You can use the graphical Integration Services tools to create solutions without writing a single line of code; or you can program the extensive Integration Services object model to create packages programmatically and code custom tasks and other package objects.

SSAS – SQL Server Analysis Services

Microsoft SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by letting you design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services lets you design, create, and visualize data mining models that are constructed from other data sources by using a wide variety of industry-standard data mining algorithms.

2011-09-06T21:36:00.000+04:00

Assign All Items to a given Vendor

----------------------------------------------------------------  

-- Assign All Items to a all Vendors     

-- Code by Venugopal G A     

-- mail me for any queries venuasg@gmail.com / venuasg@yahoo.com
----------------------------------------------------------------    

DECLARE @VENDORID VARCHAR(30)

DECLARE @ITEM VARCHAR(100)

DECLARE @ITEMDESC VARCHAR(100)


DECLARE VENDOR CURSOR FOR

SELECT VENDORID FROM PM00200
OPEN VENDOR
 
FETCH NEXT FROM VENDOR INTO @VENDORID 
 
WHILE (@@fetch_status <> -1) 
 
 BEGIN
  
  DECLARE ITEMMASTER CURSOR FOR 
  
  SELECT ITEMNMBR, ITEMDESC  FROM IV00101 
  
  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 
  
  DEALLOCATE ITEMMASTER
  
  FETCH NEXT FROM VENDOR INTO @VENDORID 
 
 END

DEALLOCATE VENDOR

Issue while deploying SSRS Reports or Business Intelligence reports for GP 2010 R2

2011-08-13T08:49:00.003+04:00

As the Process of Deploying SSRS reports or Business Intelligence Reports in GP 2010 R2 has been enhanced and it has been noticed that the system generates an error which is as below, however the error itself has a solution for it

Solution

1) Go the Microsoft SQL Reporting Server installation folder

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer

2) Look for web.config file and open it in text editor.

3)      Find the text executionTimeout="9000" and replace with
        executionTimeout="9000" maxRequestLength="20690"
4)      Save the file

5) Run the Business intelligence reports deployment, it should go smoothly now

Enjoy, Get back to find more tips

Microsoft Dynamics GP 2010 R2 is Now Available

2011-05-07T10:32:00.001+04:00

Good News, Latest Release of GP 2010 R2 Over 50 additional and interesting stuffs enhanced, my personal intrest was in Project Accounting where i can find the reports i needed.

Below is the link for all the features Microsoft Dynamics GP 2010 What's New

https://mbs.microsoft.com/partnersource/newsevents/news/MSDYGP2010R2_R2nowavailable.htm

Year End Process in Microsoft GP

2011-04-11T20:11:00.006+04:00

This article explains the year end process in Microsoft GP, The posting procedure and the closing procedure for the following modules must be performed in the following order.

Inventory
Receivables
Payables
Fixed Assets
Analytical Accounting
General Ledger

It is highly recommended before proceedings with year-end process in GP take a backup of the company and the Dynamics database, detailed steps for each module are explained in the Microsoft KB articles which can be accessed through Partner/Customer source, but the simple steps would be as below (all the steps needs to be repeated for all the above said modules)
Steps to do the year-end

Year End Procedures – Inventory
KB Article – Inventory Year end Process

Take a Company Database backup.
Post all the transaction for the module for the year.
Reconcile Inventory Quantities.
Complete a Physical Inventory count and post any adjustments.
Print any additional reports you'll need for planning or for your permanent records.
Make a backup of all Company data. (Go to Microsoft Dynamics GP >> Maintenance >> Backup).
Close the year.
Close the fiscal periods for the Inventory Series (optional).
Make a final backup.

Year End Procedures – Receivables
KB Article – Receivables Year end Process

Take a Company Database backup.
Post all the transaction for the module for the year
Make a backup of all Company data. (Go to Microsoft Dynamics GP >> Maintenance >> Backup).
Close the fiscal year in the Receivables Year-End Closing window
Close all fiscal periods for the Sales series (optional)
Make a final backup.

Year End Procedures – Payables
KB Article – Payables Year end Process

Take a Company Database backup.
Post all the transaction for the module for the year.
Print the Aged Trial Balance with Options Report to show the status of each Vendor Account.
Print the Vendor Period Analysis report.
Close All in the Payables Year-End Closing window
Close all fiscal periods for the Purchasing series (optional).
After you have completed the year-end closing procedures for all Sales and Purchasing modules, close the sales tax periods for the year.
Make a final backup.

Year End Procedures – Fixed Assets
KB Article – Fixed Assets Year end Process

Take a Company Database backup.
Post all the transaction for the module for the year.
Enter in all activity for the current fiscal year. This includes additions, changes, transfers and retirements.
Run depreciation on all assets through the last day of the current fiscal year.
Process the GL Interface (GL Posting) routine by going to Tools >> Routines >> Fixed Assets >> GL Posting.
If required print FA reports.
Verify that the Quarters are set up correctly for all fiscal years. Go to Tools >> Setup >> Fixed Assets >> Quarters.
Perform the Fixed Asset Year-End Close routine by going to Tools >> Routines >> Fixed Assets >> Year End.
Make a final backup.

Year End Procedures – General Ledger
KB Article – General Ledger Year end Process

Compete the posting procedure and the closing procedure (in the following order) for Inventory, Receivables, Payables, and Fixed Assets, if applicable.
NOTE: Year-end procedures for Payroll are only performed at the end of the calendar year.
Post the final adjusting entries in the General Ledger module. If you must make any adjusting entries to allocate revenue, expenses, or depreciation to the year that you are closing, use the Transaction Entry window or the Quick Journal Entry window. If you wish to track your adjustments separately, use the Second Closing Period feature.
Use SmartList to verify the posting type of each account. Go to Microsoft Dynamics GP >> SmartList >> Accounts. By default, only the first 1,000 accounts will be displayed. If you have more than 1,000 accounts, double click on the box at the bottom where it says "First 1,000 accounts with no search criteria" and increase the maximum records (in red at bottom). Click the header of the Posting Type column to sort by posting type. Scroll down to the change from Balance Sheet to Profit and Loss. Verify that the last Balance Sheet and the first Profit and Loss accounts are correct. Note that Great Plains treats all unit and allocation accounts as Balance Sheet, but the category will be zero.
Go to Reports >> Financial >> Accounts. Select All Accounts, then New. Type "all accounts" in the option field. If you are using 8.0 or later, put a check in the Inactive Accounts box. Select Destination >> OK >> Print. If you want to bring the balance forward, the account should be designated as Balance Sheet; if you prefer to close to a retained earnings account, the posting type must be set to Profit and Loss. You can make changes in the Account Maintenance window.
Close the last period of the fiscal year (optional). Use the Fiscal Periods Setup window. If you use FRx, keep one period in the most recent historical year open.
Perform file maintenance on the Financial Series group of modules. Go to Microsoft Dynamics GP >> Maintenance >> Checklinks. Insert all of the financial series. Print the report to the screen.
Go to Tools >> Utilities >> Financial >> Reconcile. Reconcile the year you are closing.
Verify the setup in the General ledger setup window. If you want to keep historical records, you must click to select the Accounts check box and the Transactions check box in the Maintain History area of the General Ledger Setup window.
Make a backup. (Go to Microsoft Dynamics GP >> Backup).
Print a final detailed trial balance.
Print any required year-end financial statements, e.g. balance sheet, profit and loss statement, statement of cash flows, and statement of retained earnings.
Set up a new fiscal year using the Fiscal Periods Setup window. Go to Tools >> Setup >> Company >> Fiscal Periods.
Close the fiscal year. Click Tools >> Routines >> Financial >> Year-End Closing. Specify an account in the Retained Earnings Account field (this account is where the year's profit or loss is closed to; if you want to distribute the retained earnings for the year to more than one account, you can specify an allocation account or a specific account segment). Specify the number that you want to use as the first journal entry number for the next fiscal year in the Starting Journal Entry field. Click Close Year to start the routine. If the progress window hangs at 50%, don't reboot. It may take some time for it to close each account to retained earnings. As long as your hard drive is processing, let it continue. When the year-end closing routine is complete, the Year-End Closing Report is printed. This report lists the accounts that were closed and the transactions that were created to close those accounts. The Year-End Closing Report is part of the audit trail; save it for your company's permanent records. The Year-End Closing Report cannot be reprinted.
Close all fiscal periods for all series using the Fiscal Periods Setup window (optional).
Adjust budget figures for the new year and print financial statements. You can adjust your budget using Excel-Base Budgeting, Budget Maintenance, or Single-Account Budget Maintenance. Print the Profit and Loss Statement to verify that profit and loss accounts were closed to the retained earnings account. Print the Balance Sheet to verify that balance sheet accounts indicate that the balances were brought forward. If you are using Advanced Financial Analysis to print your financial statements, you must update the report layout to reflect the current fiscal year. Click Reports >> Financial >> Advanced Financial. Select the financial statement from the Reports list | Open >>Layout. Double-click the first column heading. If the column type is set to Period Range, Year-to-Date, or Variable Year-to-Date, select the current fiscal year in the Year list | OK. Repeat for each column of each report.
Make a final backup.

Microsoft GP .ini Settings

2011-04-09T19:23:00.000+04:00

Some useful .ini settings in Microsoft GP, you can find the full list here. Some settings are not not recommended however you can you these in test envoirnment.

SQL Script to find missing or deleted Journal No in Microsoft GP

2011-04-08T09:53:00.001+04:00

Script

-------------------------------------------------------------------------  
-- Find Missing or deleted Journal Entry No in Microsoft GP     
-- Code by Venugopal G A     
-- mail me if you need query for other type of transactions or for any
-- queries venuasg@gmail.com     
-------------------------------------------------------------------------
If Object_Id('tempdb..#tempJV') is Not Null 
Drop table #tempJV

create table #tempJV
(
JVno int,
MJV int identity(1,1)
)

insert into #tempJV (JVno)
(select distinct JRNENTRY from GL20000 )

select wg.MJV as id_is_missing
from #tempJV wg
left join #tempJV ti on wg.MJV = ti.JVno
where ti.JVno is null
order by wg.MJV

SQL Script to Compare Bank Subledger balance to corresponding GL account balance

2011-04-08T09:45:00.000+04:00

Script

----------------------------------------------------------------------------
-- Compare Bank Subledger balance to corresponding GL account balance in GP.
-- Code by Venugopal G A
-- mail me for any queries venuasg@gmail.com
----------------------------------------------------------------------------
DECLARE @CHECKBOOKID VARCHAR(30)
SET @CHECKBOOKID =  'FIRST BANK' -- CHANGE THIS TO YOUR CHECKBOOK ID
--Set Bank Type and get Bank Balance
Select 'Bank' as SubType, CURRBLNC as SubBal, 0 as GLBal
Into #SubtoGLCompare From CM00100 Where Chekbkid=@CHECKBOOKID
/*Get the GL total for this Account. Change the account below or optionally add additional
actnumbr parameters to tighten or broaden the account*/
INSERT #SubtoGLCompare (SubType,SubBal,GLBAL)
SELECT   'Bank' as SubType, 0 as SubBal, SUM(Perdblnc) AS GLBal
FROM GL11110 WHERE actnumbr_2 = '1100'  -- Change this
Select SubType, sum(Subbal) as SubBal, Sum(GLBal) as GLBAL, sum(SubBal)-Sum(GLBAL) as Difference,
'Difference is Subledger minus GL.' as Instructions
from #SubtoGLCompare
Group by SubType
Drop Table  #SubtoGLCompare

SQL Script to assign all items to all sites in Microsoft GP

2011-04-08T09:29:00.000+04:00

Assign All Items to ALL Sites

--------------------------------------------------------  
-- Assign All Items to ALL Sites     
-- Code by Venugopal G A 
-- Below script will assign all the available inventory item
-- to all available sites if they are not assigned already.     
-- mail me for any queries venuasg@gmail.com     
--------------------------------------------------------    
DECLARE @ITEM varchar(100)
DECLARE @LOCATION varchar(100)
DECLARE @ITEMAlreadyExist int

DECLARE ITEMMASTER CURSOR FOR
 SELECT ITEMNMBR FROM IV00101
OPEN ITEMMASTER 
    FETCH NEXT FROM ITEMMASTER INTO @ITEM
    WHILE (@@fetch_status <> -1) 
  BEGIN
          DECLARE SITE CURSOR FOR
            SELECT LOCNCODE FROM IV40700
   OPEN SITE 
    FETCH NEXT FROM SITE INTO  @LOCATION
    WHILE (@@fetch_status <> -1) 
     BEGIN
      SELECT @ITEMAlreadyExist = isnull(count(*), 0) FROM IV00102 
      where ITEMNMBR = @ITEM and LOCNCODE = @LOCATION
                       if @ITEMAlreadyExist  = 0 
       BEGIN
                             INSERT INTO IV00102 VALUES(@ITEM,@LOCATION,'',2,'',0,0,0,0
                              ,'01/01/1900','','01/01/1900',0,0,0,0,0,0,0,0,0,0,0,0
                              ,'01/01/1900','01/01/1900','01/01/1900','01/01/1900',0,''
                              ,'','',1,0,0,1,0,0,1,2,0,0,0,0,0,0,0,1,0,0,0,3,0,0,0,''
                              ,'','','','','','','',1,1,'',1,1,0,1,1,1,0,0,0,0,0)
                END
                      FETCH NEXT FROM SITE INTO @LOCATION
                END 
          DEALLOCATE SITE
          FETCH NEXT FROM ITEMMASTER INTO  @ITEM
    END 
DEALLOCATE ITEMMASTER

SQL Script to assign all Items to a given Vendors

2011-04-08T09:11:00.001+04:00

Assign All Items to a given Vendor

--------------------------------------------------------  
-- Assign All 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'

DECLARE ITEMMASTER CURSOR FOR 
SELECT ITEMNMBR, ITEMDESC  FROM IV00101 
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 
DEALLOCATE ITEMMASTER

How to Modify Word Templates in Microsoft Dynamics GP 2010

2010-12-12T17:58:00.002+04:00

Getting used to the new Word Templates functionality can be a bit challenging at times. One of those challenges came in the form or a simple question: "How to resize the logo on the template?". One thing to keep in mind is that most objects on a Microsoft Dynamics GP Word Template document live inside of content controls. With that said, here are the steps to achieve this, taking the SOP Blank Invoice Form Template* as an example:
1. Under Report Template Maintenance, select the SOP Blank Invoice Form template from the More Reports list.

2. Click the New button to create a new template. For this example, we will create the template based on the existing template and we will call it SOP Blank Invoice Form Icon. Click Ok to continue.

3. Highlight the newly created template, then click the Modify button to open Microsoft Word.

4. Click on the Picture content control to add a logo image.

Note: This image must also exist in the Image library under Template Configuration.
5. Select the logo, right-click, and chose Remove Content Control.

6. Highlight the image and resize accordingly. The following is a sample of the resized image with some extra text to go along:

NOTE: The logo is part of the Header section of the document.
7. Save the modified template to a different directory where it can be imported from later on, for example, My Documents. Close Microsoft Word.

8. Back to Report Template Maintenance, click the Add Template button and select the newly modified template from the My Documents folder. Replace the existing one when prompted.
9. Click the Assign button, then mark the company you want to assign the template to. Then, click on Set as Default to make the new template the default template for the company. Click Save to continue. Click Save to continue.

10. Back to the Report Template Maintenance window, highlight the original template and click the Assign button and choose Company. Unmark the company and choose Save.

NOTE: This will ensure that the copied version of the original template prints always.
11. To test, select a SOP invoice and print.

Hope you found these steps simple to follow and that you are enjoying the new Word Templates feature in Microsoft Dynamics GP 2010.
Until next post!

Microsoft SQL Server Report Viewer Control Redistributable Packages and Sample Applications

2010-11-01T06:38:00.002+04:00

Microsoft Report Viewer control enables applications that run on the .NET Framework to display reports designed using Microsoft reporting technology.
The redistributable packages contain Windows Forms and ASP.NET Web server control versions of the Report Viewer.
You can find here download links of Report Viewer control redistributable packages for MS SQL Server 2005 and Microsoft SQL Server 2008 and sample applications using reportviewer control for Visual Studio 2005 and Visual Studio 2008.
Microsoft Report Viewer Redistributable 2008
File name : ReportViewer.exe
Version : 09.00.21022.08
Download Size : 2.8 MB
Microsoft Report Viewer Redistributable 2005
File name : ReportViewer.exe
Version : 2007
Download Size : 1.8 MB
Microsoft Report Viewer Redistributable 2005 SP1 (Upgrade)
File name : VS80-KB933137-X86.exe
Version : 1
Download Size : 1.7 MB
Microsoft Report Viewer Redistributable 2005 SP1 (Full Installation)
File name : ReportViewer.exe
Version : 1.0
Download Size : 1.8 MB
ReportViewer Samples for Microsoft Visual Studio 2008
File name : ReportViewerSamples2008.exe
Version : 1.0
Download Size : 172 KB
ReportViewer Samples for Visual Studio 2005
File name : ReportViewerSamples.exe
Version : 1.0
Download Size : 173 KB

FRx 6.7 SP11 Released

2010-10-23T08:18:00.002+04:00

Some of the main benefits of SP11 are as follows

Windows 2008 and Windows 7 will be supported now.
FRx WebPort will be supported with Windows 2008 and Windows 7 and Windows Vista will still not be supported.
Office 2010 will not be certified in this cycle. However, there was some limited testing and it does work. 64-bit version of Windows will still not be supported, Customers will have to use Virtual Machines. A new feature in Windows 7 allows users to download Windows XP Mode. This is a new feature in Windows 7, available in Windows 7 Professional, Ultimate, and Enterprise, provides a licensed copy of Windows XP with Service Pack 3 in Virtual Hard Drive (VHD) format. When installed with the proper integration components, it allows you to run Windows XP in its own virtual machine, separate from the host Windows 7 installation. The location for the download and additional information about XP Mode is available at this URL: http://www.microsoft.com/windows/virtual-pc/default.aspx.
The previous issues with percentage and dollar formatting using CS are resolved in this service pack, along with other fixes in the service pack listed in the Release notes.

Items Having Current Cost and No Movement

2010-10-02T07:55:00.005+04:00

At time you want to audit the inventory items which are having Current Cost and no Movement (these items normally exist in GP during the migration process or upgrade process from the old system), below query gives you the list of such items in GP. The listed items are safe to delete from the system if they are not really required (delete at you own risk).

SELECT LTRIM(RTRIM(IV00101.ITEMNMBR)) as ITEMNMBR, IV00101.STNDCOST, IV00101.CURRCOST, IV00102.QTYONHND
FROM IV00101 INNER JOIN IV00102 ON IV00101.ITEMNMBR = IV00102.ITEMNMBR
WHERE (IV00102.RCRDTYPE = 1) AND (IV00102.QTYONHND = 0) AND (IV00101.CURRCOST > 0)
AND IV00101.ITEMNMBR NOT IN (select distinct(itemnmbr) from IV30300)
Come back for more tips ..... J

How to find installed Version of eConnect

2010-10-02T07:55:00.004+04:00

I often use to get errors on eConnect whenever i deploy my integration on the client machine and hence thought of figuring out to fine the installed version of eConnect on client PC, after browsing through the documents and the database i was finally able to retrieve the version of eConnect installed.
Below is the script which gives the installed version of eConnect
exec DYNAMICS..taeConnectVersionInfoDYNAMICS
Run and execute the above script in SQL Query window, the SP retrieves three columns with DatabaseName, Version and CompanyName.
Keep visiting the site for more tips ... J

How to Resolve error for Order Management in GP Business Portal

2010-09-14T13:57:00.002+04:00

Submitted by Madankumar Kasthuri
If you get the below error message, when you try to access Order Management page in Business Portal for Microsoft Dynamics GP 10
Select the concerned user and give the Customer and Salesperson roles with some default value. Then this error would be fixed.

Standard Numeric Format Strings in SQL

2010-09-14T13:49:00.002+04:00

Article Submitted by Madankumar Kasthuri
Standard numeric format strings are used to format common numeric types. A standard format string takes the form Axx where A is a single alphabetic character called the format specifier, and xx is an optional integer called the precision specifier. The format specifier must be one of the built-in format characters. The precision specifier ranges from 0 to 99 and controls the number of significant digits or zeros to the right of a decimal. The format string cannot contain white spaces.
If the format string does not contain one of the standard format specifiers, then a FormatException is thrown. For example, the format string "z" is interpreted as a standard numeric format string because it contains one alphabetic character, but the alphabetic character is not one of the standard numeric format specifiers so a FormatException is thrown. Any numeric format string that does not fit the definition of a standard numeric format string is interpreted as a custom numeric format string. The format string "c!" is interpreted as a custom format string because it contains two alphabetic characters, even though the character "c" is a standard numeric format specifier.
The following table describes the standard numeric format strings. Note that the result string produced by these format specifiers is influenced by the settings in the Regional Options control panel. Computers using different settings will generate different result strings.

Format specifier	Name	Description
C or c	Currency	The number is converted to a string that represents a currency amount. The conversion is controlled by the currency format information of the NumberFormatInfo object used to format the number. The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default currency precision given by the NumberFormatInfo is used.
D or d	Decimal	This format is supported for integral types only. The number is converted to a string of decimal digits (0-9), prefixed by a minus sign if the number is negative. The precision specifier indicates the minimum number of digits desired in the resulting string. If required, the number is padded with zeros to its left to produce the number of digits given by the precision specifier.
E or e	Scientific (exponential)	The number is converted to a string of the form "-d.ddd...E+ddd" or "-d.ddd...e+ddd", where each 'd' indicates a digit (0-9). The string starts with a minus sign if the number is negative. One digit always precedes the decimal point. The precision specifier indicates the desired number of digits after the decimal point. If the precision specifier is omitted, a default of six digits after the decimal point is used. The case of the format specifier indicates whether to prefix the exponent with an 'E' or an 'e'. The exponent always consists of a plus or minus sign and a minimum of three digits. The exponent is padded with zeros to meet this minimum, if required.
F or f	Fixed-point	The number is converted to a string of the form "-ddd.ddd..." where each 'd' indicates a digit (0-9). The string starts with a minus sign if the number is negative. The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default numeric precision given by the NumberFormatInfo is used.
G or g	General	The number is converted to the most compact of either fixed-point or scientific notation, depending on the type of the number and whether a precision specifier is present. If the precision specifier is omitted or zero, the type of the number determines the default precision, as indicated by the following list. Byte or SByte: 3 Int16 or UInt16: 5 Int32 or UInt32: 10 Int64 or UInt64: 19 Single: 7 Double: 15 Decimal: 29 Fixed-point notation is used if the exponent that would result from expressing the number in scientific notation is greater than -5 and less than the precision specifier; otherwise, scientific notation is used. The result contains a decimal point if required and trailing zeroes are omitted. If the precision specifier is present and the number of significant digits in the result exceeds the specified precision, then the excess trailing digits are removed by rounding. If scientific notation is used, the exponent in the result is prefixed with 'E' if the format specifier is 'G', or 'e' if the format specifier is 'g'. The exception to the preceding rule is if the number is a Decimal and the precision specifier is omitted. In that case, fixed-point notation is always used and trailing zeroes are preserved.
N or n	Number	The number is converted to a string of the form "-d,ddd,ddd.ddd...", where each 'd' indicates a digit (0-9). The string starts with a minus sign if the number is negative. Thousand separators are inserted between each group of three digits to the left of the decimal point. The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default numeric precision given by the NumberFormatInfo is used.
P or p	Percent	The number is converted to a string that represents a percent as defined by the NumberFormatInfo.PercentNegativePattern property or the NumberFormatInfo.PercentPositivePattern property. If the number is negative, the string produced is defined by the PercentNegativePattern and starts with a minus sign. The converted number is multiplied by 100 in order to be presented as a percentage. The precision specifier indicates the desired number of decimal places. If the precision specifier is omitted, the default numeric precision given by NumberFormatInfo is used.
R or r	Round-trip	The round-trip specifier guarantees that a numeric value converted to a string will be parsed back into the same numeric value. When a numeric value is formatted using this specifier, it is first tested using the general format, with 15 spaces of precision for a Double and 7 spaces of precision for a Single. If the value is successfully parsed back to the same numeric value, it is formatted using the general format specifier. However, if the value is not successfully parsed back to the same numeric value, then the value is formatted using 17 digits of precision for a Double and 9 digits of precision for a Single. Although a precision specifier can be appended to the round-trip format specifier, it is ignored. Round trips are given precedence over precision when using this specifier. This format is supported by floating-point types only.
X or x	Hexadecimal	The number is converted to a string of hexadecimal digits. The case of the format specifier indicates whether to use uppercase or lowercase characters for the hexadecimal digits greater than 9. For example, use 'X' to produce "ABCDEF", and 'x' to produce "abcdef". The precision specifier indicates the minimum number of digits desired in the resulting string. If required, the number is padded with zeros to its left to produce the number of digits given by the precision specifier. This format is supported for integral types only.

Great Plains Maintenance and Recovery Procedures

2010-08-25T19:12:00.002+04:00

The Great Plains system is designed to ensure maximum accuracy and integrity of your accounting data. Occasionally, however, your data tables may become damaged. Hardware failures, power surges, and other problems can damage or destroy data.
While damage occurs infrequently, the factors that cause it are difficult to predict or control, and it's necessary to take measures to protect your data. Regularly back up your accounting data and perform table maintenance to minimize risk of data loss from table damage.Only SA or DYNSA can open the Backup Company window to make backups. The backup procedure must be run on the server.
Read the Complete article here GP Maintenance

AA Transaction Error – Primary Key Voilation

2010-08-25T14:37:00.002+04:00

There are quite a few errors comes in AA which are not highlighted in the knowledge base articles.
Transaction dimensions can't able to be saved. System had thrown you the following message

'Primary key violation error on table AAG00600. Cannot insert duplicate records on table' along with that message there was another saying
'The stored proc aagCreateTree returned the following results. DBMS: 2627, Great Plains: 0'.

Resolution
This problem is due to the aaTreeID which is the primary key violation of AAG00600. In order to rectify it follow the process below.
Run all the below steps in SQL Server Query Analyser
Step 1:- select * from dynamics..sy01500 (used to know the company id)
Step2:- select * from dynamics..aag00102 where aatableid = 600 ('600' is the source aa table ID for which the corresponding aaRowID mentioned is the last rowid inserted in AAG00600 for the corresponding is company ID)
Step3:- select * from two..aag00600 (look for the aaTree ID were the last record inserted is holding the same ID as that of the aaRowID in aag00102 table)

If the rowid and the tree id is not same for the corresponding company then you will be getting the above message which will not allow you to save the new transaction dimension for that company. So use the following code to make the rowid as same as that of the aatree id in AAG00600.

Step4:- update dynamics..aag00102 set aarowid = 14 where dex_row_id = 4

Find Missing / Deleted Journal Entry in Microsoft GP

2010-08-25T14:20:00.004+04:00

When your company auditor ask for all the missing or deleted Journal entry, it's not a big task now you can get it in minutes by running the script below in your SQL query analyser.
If Object_Id('tempdb..#tempJV') is Not Null
Drop table #tempJV
create table #tempJV
(JVno int,MJV int identity(1,1))
insert into #tempJV (JVno)
(select distinct JRNENTRY from GL20000 )
select wg.MJV as id_is_missing
from #tempJV wg left join #tempJV ti on wg.MJV = ti.JVno
where ti.JVno is null order by wg.MJV

Keep visiting this site for more Dynamics Tips... J

Dynamics Management Reporter

2010-08-24T07:51:00.001+04:00

Management Reporter is a specialized financial reporting solution that allows users to create powerful reports in minutes. This application allows executives, managers, and front-line employees to gain access to the information they need when they need it and where they need it. There are 3 basic building blocks in Management Reporter.

The first building block is called a row definition. A row definition defines the descriptive lines (for example, sales or sales returns) on a report. It also lists the account codes or dimensions that contain the values for each line item and includes row formatting and calculations.1

The second building block is called a column definition. 1 A column definition defines the time period to use when extracting data from the financial data source. It also includes column formatting and calculations.

The third building block is called a reporting tree definition. A reporting tree definition is similar to an organizational chart. It contains individual reporting units that represent each box in the chart. These units can be either individual departments from the financial data or higher-level units that summarize data from other reporting units.

Together these three building blocks give users the flexibility to define a virtually unlimited number of reports to meet their business needs. The report definition process is designed in such a way that the average end user can get access to the critical business information they need quickly and easily.

Another feature built into Management Reporter that simplifies the report creation process is the Report Wizard. The Report Wizard gives you the choice between creating one of five different financial reports including a balance sheet, three types of income statements and a trial balance.

Resetting GP 2010 System Password

2010-08-19T17:37:00.002+04:00

Forgot your GP System Password? GP Administrator relax, you can now reset System password with the below trick, but be careful using this trick
Run the below script in SQL Server Management studio
USE DYNAMICS
GO
Update SY02400 Set Password = 0X00202020202020202020202020202020
Keep watching this blog for more tricks on GP…….. J

Enhanced Email Option in Microsoft GP2010

2010-08-17T12:08:00.002+04:00

Sending Document to Customer's or Vendor's is made easy in Microsoft GP2010 with addition of Email options to the Transaction level. You can select to embed documents, attach documents, or both. If you select to send documents as attachments, you can specify which type of file formats you want to attach the documents as. The options you select in this window are available when setting up or modifying e-mail options for documents, customer records, and vendor records.

If you selected to use an option, such as the DOCX file format, and then decide not to use the option, you must update the customer and vendor records that were set up to use the DOCX file format. An addition feature include the what format you want to send the document to Customer or Vendor and an option of creating a template which can be attached to Customer/Vendor.

Download complete article Download Here

Improved Home Page Metrics for Dynamics GP2010

2010-08-14T13:46:00.004+04:00

The metrics, charts, and KPI's that are available on the Microsoft Dynamics GP Home Pages now leverage SQL Server Reporting Services (SSRS) reports. This enables greater flexibility for our customers in regards to the metrics that are displayed and how they can be accessed which are easily build on SSRS.

Metrics built using Office Web Components are still available to the users
Customers can choose SQL Server Reporting Services as their metric source
Over 100 fully customizable metrics that can appear on the Microsoft Dynamics GP Home Page
Companies can easily create their own metrics they want displayed on the Home Page
Metrics are part of the SQL Server Reporting Services Deployment Wizard
Metrics may be deployed to SQL Reporting Services in either Native or SharePoint mode
Reporting Services metrics will appear in the SQL Reporting Services Report List; making them available to add as links on the Home Page
All metrics are available to use in Business Portal as well.
Users can display multiple metrics on the Home Page at one time, or display just one at a time with the option to scroll through the metrics

How to Setup the Reports?SSRS reports setup is just a screen away provided you have installed SQL Server Reporting Services (SSRS) if not please follow the instruction provided with SQL server installation document. Assuming you have installed SQL Server Reporting Services (SSRS) follow the steps below.

Install SRSReports which are under GP2010 installation CD/DVD as additional products.
Configure SSRS reports for GP open Programs >> Microsoft Dynamics >>Business Intelligence >>Microsoft SQL Server Reporting Service Wizard.
Follow the instruction on the wizard and provide the reportserver path and select the company you want to install SSRS Reports for GP.
Once done login to GP and navigate to Tools>>Setup>>System>>Reporting Tools setup.
There is an important thing that needs to be looked into while configuring the reporting tools for GP, while entering the Report Server URL make sure you enter the URL properly
- If SQL Server 2005 Reporting Service is installed then the path should be http://localhost/ReportServer/reportingservice.asmx
- if SQL Server 2008 Reporting Service is installed then the path should be http://localhost/ReportServer/reportingservice2005.asmx
Under the Charts and KPI's Section mark only the Option Enable SQL Server Reporting Service Home Page Metrics
Note: Do not mark Charts and KPI's have been deployed to a sharepoint Server, if you mark this option then you will not be able to view the charts and KPI's under the Home page Metrics.
Click OK and close the Reporting Tools Setup Window
That's All folks.

Adding Custom Charts or KPI's to Home PageMicrosoft Dynamics GP2010 has made easy to add custom reports which is another powerful and important feature that has made GP2010 customer/user build their own analysis. Follow the below steps to add Custom Reports or KPI's to GP Home Page.

To ADD Reports / Charts Build your custom Report or Charts in SSRS.

Deploy the custom report under the Reporting Services for this open SSRS Report Manager and Click on the Company folder
Identify your custom report to make sure it is added under proper module.
Click on the module you identified and click Charts And KPI's Folder.
Deploy your reports under this Folder and give the Proper name that can be viewed under Home Page Metrics Selection in GP2010.

Hope this helps to add your Custom Charts and KPI's to GP2010 Home Page, for any queries and new Charts to be build contact venuasg@gmail.com

If Check Voided by mistake in Microsoft Dynamics GP

2010-08-13T08:14:00.002+04:00

You issue a check to a supplier and then someone erroneously voids that check. How do you recover from this without having to contact the vendor, issue a new check, and stop payment on the old one?
Follow the 7 Steps below

First Navigate to Cards>>Financial>>Checkbook
Select the checkbook from which the check was issued. If the Duplicate Check Numbers checkbox isn't checked, check it.
Click Save. Leave the Checkbook Maintenance window open. We'll be coming back to it.
Navigate to Transactions>>Purchasing>>Manual Checks and create a manual check to replace the check (in the system) that was voided. Change the Document Number to the check that was inadvertently voided. Post the transaction.
You now have a replacement check in the system for the one that was voided that can be cleared in Bank Rec when the supplier deposits the original check.
Document the heck out of this because you'll now have two checks in the system with the same check number, one voided and one not. And sure as can be, this check will be selected during audit.
Go back to the Checkbook Maintenance window and uncheck Duplicate Check Numbers.