Management Reporter Budget Data Not Displayed

A client recently created a budget using Excel Based Budgeting in Dynamics GP. The budget was based on a prior year with nominal increases to revenue and expense accounts.  The budget could successfully be opened in both Excel and Dynamics GP.

Unfortunately, when we added the budget to a column definition in Management reporter to do budget to actual comparisons, no budget data displayed.

So, we went into troubleshooting mode:

1  Validate budget in Dynamics GP Budget Maintenance Window
2. Management Reporter > Tools > Refresh Cached Financial Data
3. Check Column Definition thoroughly
4. Stop and Restart Management Reporter Services (Configuration Console)
5. Run ERP Data Validation under ERP Integrations (Configuration Console)
6. Rebuild the Data Mart

No Joy!

I particularly dislike rebuilding the Data Mart, and if I had this to do all over again, I would have held fire on this step. Having to rebuild the Data Mart, in my opinion is like using a hammer to force a fit.

As I was walking from IT to Finance to explain we hadn't resolved the problem, it occurred to me FRx used to have a similar problem, prior to the introduction of Management Reporter... Something to do with the YEAR1 field in the GL00201 table. 

Sure enough, a quick query on the GL00201 table found the value in the YEAR1 field in the GL00201 table for this budget was 0 in every row. This is not a good thing.

For the uninitiated, financial reporting tools like FRx and Management Report use the notion of a BASE period to fundamentally format financial statements in a very flexible manner; users need not change components of financial reports as time elapses. 

So, missing the fiscal year data, found in the YEAR1 file leaves no way for Management Reporter to know what period the financial data for the budget is in.

The query below will help identify problem records.

--Identify problem records in the Budget Master Detail file

select BMH.YEAR1, BMD.YEAR1,* from GL00200 BMH

left join GL00201 BMD on BMH.BUDGETID = BMD.BUDGETID 

where BMD.YEAR1 = 0

The query below will fix the problem records.

--Correct problem records in the Budget Master Detail file

update BMD set BMD.YEAR1 = BMH.YEAR1 from GL00201 BMD join GL00200 BMH on BMH.BUDGETID = BMD.BUDGETID where BMD.YEAR1 = 0

In extreme cases, it is possible the GL00200 table will also have a 0 value in the YEAR1 column. If this happens, you will need to update this value prior to running the scripts above. 

To be clear YEAR1 is the four character year you are budgeting for (i.e. 2015), and you should replace XXXX in the query below with the actual BUDGETID.

Update GL00200 set YEAR1 ='2015' where BUDGETID = 'XXXX'

My recommendation, should you encounter this problem I would change the troubleshooting order as follows:

1  Validate budget in Dynamics GP Budget Maintenance Window
2. Management Reporter > Tools > Refresh Cached Financial Data
3. Check Column Definition thoroughly
4. Run queries to determine if GL00201.YEAR1 data contains 0 values.
5. Stop and Restart Management Reporter Services (Configuration Console)
6. Run ERP Data Validation under ERP Integrations (Configuration Console)
7. Rebuild the Data Mart