It’s very common for our customers to add new budgets to Microsoft Dynamics GP. With the Legacy Provider in Management Reporter (MR), the new Budget ID would show in the Column Definition “Book Code/Attribute Category” field automatically. With the MR Data Mart, there are a couple of steps below that need to be followed first.
After creating the new budget in Dynamics GP, follow these steps to see it in MR:
1. First, verify that the Dynamics GP Budget table and the MR Data Mart table are both correct.
Log into SQL Management Studio and run these two statements:
a. For the Dynamics GP database:
select * from GL00201 where BUDGETID = ‘XXXXXX’ and YEAR1 <> ‘YYYY’
(Replace “XXXXXX” with your new Dynamics GP Budget ID and “YYYY” with your correct Fiscal Year). If there are any records returned, update that table so those records have the correct fiscal year, i.e. “2018.” (Update GL00201, set Year1 = ‘2018’ where BUDGETID = ‘BUDGET 2018’).
b. For the MR Data Mart database:
select * from DATAMART.FACT where SOURCEKEY like ‘%XXXXXX%’
(Replace “XXXXXX” with your new Dynamics GP Budget ID). If it does not return any records, the data mart does not have the new budget amounts yet. You can run through the MR Config Console validation steps to pull these in, without having to rebuild the actual data mart. Select all below, except “Remove”. (You will need the SQL sa password for these):
2. Next, run a Check Links for the Budget Master table:
a. Log into the company in Dynamics GP.
b. Go to the main Microsoft Dynamics GP dropdown, then Maintenance, then Check Links:
c. Then select “Budget Master” from the Financial module group and click “OK”:
3. Now log into MR in that company (without the –curr).
a. Go to Tools, Refresh Cached Financial Data:
b. Click “OK” when finished (should take a second or two only):
4. Now log back into MR and you will see the Budget ID in the Column Definition “Book Code/Attribute” field.
If you have further questions, or need assistance with your MR reports, send us an email at Support@Crestwood.com.