Thursday, May 24, 2012

Dynamics GP - Make Account Segments Smaller

Reduce the size of your account segments

eOne Smartconnect - Integration map fields cannot change from "List Option"

When using eOne Smartconnect, and trying to create a map, one of the source fields is set to "List Option" by default and does not change even when you manually change it using the drop down menu.

Drag the field you want to integrate from the "Source columns" on the left side of the window onto the field in the "Destination Column". This will change the field to "source column"

Tuesday, May 22, 2012

Dynamics GP - Excel Reports in Sharepoint - The data connection uses Windows Authentication and user credentials could not be delegated. The following connections failed to refresh:

You get this error when you try to run a report after you publish an excel report to sharepoint

The data connection uses Windows Authentication and user credentials could not be delegated. The following connections failed to refresh:

This solution only works if you have Kerberos enabled.

  1. Open SharePoint 2010 Central Administration
  2. Navigate to the System Settings section
  3. Click Manage services on server
  4. Click Start next to Claims to Windows Token Service
Also ensure it is actually running under services.msc

Dynamics GP - Deploy Excel Reports to Sharepoint

Before starting, create your Data connection and Reports Libraries in sharepoint first.
Go to sharepoint,
click Libraries>New>Select Reports Type>Name>Save
click Libraries>New>Select Data Connection Type>Name>Save

Make sure you're using a machine and user account that has access to the sharepoint site, otherwise it won't be able to upload the reports.

Go to DynamicsGP>Tools>Setup>System>Reporting Tools Setup
Click Excel Reports
Sharepoint site: http://yoursharepointsite
Data Connections Library: Dataconnectionlibraryname
Reports Library: Reportslibraryname
User Level Reports Directory: C:\Documents and Settings\%\My Documents\My Data
Click Deploy

And wait for reports to upload.

Dynamics GP -The Account Number for ACTINDX 0 needs to be recreated

After running check links, you get this error.
The Account Number for ACTINDX 0 needs to be recreated

Reconcile all open years in your General Ledger: Utilities - Financial - Reconcile - Year. 

Associated Errors:
Multicurrency Account Master - The Account Number for ACTINDX 0 needs to be recreated

The MC00200 table has extra records that assign currencies to accounts that do not exist

Run this script to remove any unnecessary currency assignments

delete from MC00200 where actindx not in (select actindx from gl00100)

Monday, May 21, 2012

Friday, May 18, 2012

Dynamics GP - EOne Smartconnect Source Nodes are missing

When i try to create a new map as Changes Only>Microsoft GP Change Data Source
Under Settings, there are no drop down values for Product, Series or Table.

In GP (not in SmartConnect) go to Tools > SmartConnect > Smartconnect Maintenance>Tick GP Resource Cache and process.

Monday, May 14, 2012

Dynamics GP 2010 - SSRS Payables Transaction History

To add the PO number to your SSRS Payables Transaction History Report

  1. Add another dataset "PONum" that contains all of the vouchers and PO numbers. This view from Victoria Yudin is perfect.
  2. Go into your table and create a placeholder wherever you would like your PO numbers to be.
  3. Under General>Value Click the Fx button to create a function
  4. Paste this in
    =lookup(Fields!VCHRNMBR.Value, Fields!Voucher_Number.Value,Fields!PO_Number.Value,"PONum")
  5. Save, close, Run your report and see if it works.
If you need to know more about the lookup function, try here 

Dynamics GP 2010 - SSRS Payables Historical Aged Trial Balance

The default SSRS Payables Historical Aged Trial Balance that gets deployed from GP has different parameters from the original GP report.
When you initially run the report straight out of the box with a value selected for the 
"Select Transactions for Report Using:" you would expect it to return all of the records.
This does not happen.

You also notice a bunch of weird characters 
In the "To:" parameters.

You will also notice that NONE of the fields have any sort of drop down selection menu.
  1. To get the report to work, you must fill in ALL "to:" parameters with something useful (like "ZZZZZ")
  2. Fill in the "To: Document Number" parameter with something useful (like "9999999")
  3. Leave the "from:" parameters blank
Now if you try to run the report it will return data properly.