'Amount' in MS Dynamics GP Financial Cube

(0) Share

Report

Posted on by Community Member

Hi All,

I have deployed MS Dynamics GP cubes that with GP from Microsoft. Cube isn't including the Begning Balance of Year 2008. So I have added that Amounts in 'GL Transactions' table. At this piont Amounts from Year 2008 are correct now but its not adding up in the next Period.

Following image shows Debit, Credit and Amount before adding the Begning Balance. And its adding up in the next Period 1 of Year 2009. 5852.3.PNG

After adding the 'Begning Balance' Amounts, it's not adding up in the Period 1 of Year 2009.

Guys hellp me out here.

Thanks in advance,

Attiqe

*This post is locked for comments

All responses (10)

Answers (3)

Kevin Day 610 on at

Like (0)

Report

RE: 'Amount' in MS Dynamics GP Financial Cube

Just wanted to follow up. For some reason, the company needed to have some "Open" Transactions in Order to pick up the Opening Balance for 2018. So basically we made a 1.00 Journal Entry in this company Debiting and Crediting the same GL Account and then next time it ran, the Beginning Balances showed up. So it seems like there is just a bug in the logic of the SSIS that loads. I'm guessing that we may have to do the same thing next year since all that will roll forward are these 2 Balance Sheet accounts again.
Kevin Day 610 on at

Like (0)

Report

RE: 'Amount' in MS Dynamics GP Financial Cube

Looks like this one is pretty old but it is the only thing remotely close to the issue I am having.

Basically, the problem is that one of our companies is not showing a Beginning Balance for 2018 in the DynamicsGPWarehouse. We have 64 companies total, all of which are good but this one. We have several companies that are very low in transactions and this is one of them. In the DynamicsGPWarehouse, there are only 88 Rows total for this company going back to 2016. There is a Beginning Balance Record for these accounts for 2016 and 2017, but it is missing 2018.

What I noticed with this company is that someone forgot to close the year 2016 at the beginning of 2017. Now when it came time to close 2017, they closed 2016 and 2017 back to back without running the load in between (how would they know anyways...). But everything seems normal and fine in actual Great Plains in terms of the Balances in this company for 2018.

I used your steps about deleting records from GLTransactions for just this company and updating the LastUpdated table for the company to 1/1/1900 thinking that I can just re-load from the beginning of time (like I said, there are only 88 rows anyways), but when I do that, I get all my "History" records but it doesn't re-load any Beginning Balance records at all. So not just (still) missing 2018's records now, but I get none at all. By the way, there are literally 2 Balance Sheet accounts with a Balance and that Balance did not even change from 2017 to 2018 that I am looking for here.

I even tried to SQL Trace this but it will take me days to figure out all that code and what it is doing. I tried, found the spots where it is supposed to insert Beginning Balance records, to no avail. It's just to much code to get through and I'm just wasting my time.....

Does anyone have any suggestions on basically starting from scratch on this one company? I just want it to load everything from scratch.

Thanks,

Kevin
Community Member on at

Like (0)

Report

Re: 'Amount' in MS Dynamics GP Financial Cube

Thanks David, I owe you. Let me know if you need me anytime, I will do everything in my power.

Email: attiqeurrehman@gmail.com

LinkedIn: pk.linkedin.com/.../bb
Verified answer

GPDavid 2,250 on at

Like (0)

Report

Re: 'Amount' in MS Dynamics GP Financial Cube

It's a 2-step process, and it appears you've completed the first step (setting it to "Yes" for UseForReporting). You also need to modify the BudgetForecast column to say "Budget" or "Forecast". There are two fields in the GLTransactions table with names "Budget" and "Forecast". Depending on which one you specify, the data load will populate those budget values into either of those two columns.
Community Member on at

Like (0)

Report

Re: 'Amount' in MS Dynamics GP Financial Cube

That is precisely the case and I will do the same thing I have told you in the above post. Thanks for confirming theory. I have one last question. How do I setup the Budgets because Budgets Amt is zero always. I did changed the values in GLBudgetSetup.

Let me know what can done here.

Thanks,

Attiqe
Verified answer

GPDavid 2,250 on at

Like (0)

Report

Re: 'Amount' in MS Dynamics GP Financial Cube

Do you have transactions in the year 2007? Why is there an Opening Balance at the end of 2007? Again, the cubes calculate opening balances indepently of GP and based solely on transactions that came from GP. If you have no transactional data in 2007, then GP will not calculate an Opening Balance for 2008. I am wondering if the Opening Balance for 2008 that you are looking for is a field that someone manually created in GP with no transactional detail to support it. If this is the case, then you will need a customization to either bring in that record separately or to hard-code your own set of records for each account.
Community Member on at

Like (0)

Report

Re: 'Amount' in MS Dynamics GP Financial Cube

David,

You are absolutely right. I have checked the "StartingCutoffDate" and "GLCutoffDate" from SystemVersion.

SSIS package still isn't getting the 'Opening Balance' from Period 12 of Calendar Year 2007 and Fiscal Year 2008. I have even tried to create new Cubes from the Setup but results are same.

Any ideas ?

Thanks,

Attiqe
GPDavid 2,250 on at

Like (0)

Report

Re: 'Amount' in MS Dynamics GP Financial Cube

The cubes calculate their own opening balance based on transactions that have been brought into the data warehouse. They do not use the opening balance records from GP. You will need to make sure your package cutoff date is early enough to bring in all transaction records from GP to ensure opening balance amounts are correct.

This can be verified by checking the SystemVersion table in the data warehouse and ensuring that the "StartingCutoffDate" and "GLCutoffDate" are early enough to include all transactions.

If you do make a change here, then you will need to reload the GLTransactions table in full (since it is incremental, it won't know to go back to get additional records unless you tell it to). You can do this by deleting the contents of this table, then also making sure you update the dbo.LastUpdated table for the records that say "GLTransactionsOpen" and "GLTransactionsHistory". Set the DateUpdated value to '01/01/1900' and the LastRow/TempLastRow fields to zero. Rerunning your package will then reload the table.

David
Verified answer

Community Member on at

Like (0)

Report

Re: 'Amount' in MS Dynamics GP Financial Cube

David,

Thanks for your reply, I think I have figured it out. As I have told you earlier in my post that SSIS package isn't adding the 'Opening Balance' of Year 2008. When I added that via INSERT statement the numbers balanced up for Year 2008 but not for the other Years 2009 and so on. So I figured out to update every record with the missing 'Opening Balance' against each 'Opening Balance' and 'Account Index'.

This will solve my problem, but right I am diagnosing it that by updating 'Opening Balance' did I changed something else because of it. Let me know if you have any better solution.

Thanks again,

Attiqe

P.S: This is the SQL script that I developed for only one account with hard coded values. I will improve the SQL once I am sure that I am reached at the right answer.

DECLARE @DebitAmount numeric(19, 5), @CreditAmount numeric(19, 5), @OrigDebitAmount numeric(19, 5), @OrigCreditAmount numeric(19, 5), @NativeAmount numeric(19, 5)

SET @DebitAmount = 13429.04

SET @CreditAmount = 0

SET @OrigDebitAmount = @DebitAmount

SET @OrigCreditAmount = @CreditAmount

SET @NativeAmount = @DebitAmount - @CreditAmount

UPDATE GLTransactions

SET DebitAmount = DebitAmount + @DebitAmount, CreditAmount = CreditAmount + @CreditAmount, OrigDebitAmount = OrigDebitAmount + @OrigDebitAmount, OrigCreditAmount = OrigDebitAmount + @OrigDebitAmount, NativeAmount = NativeAmount + @NativeAmount

WHERE CompanyID = 'OPRG' AND AccountIndex = 638 AND BalanceType = 'Opening Balance'
GPDavid 2,250 on at

Like (0)

Report

Re: 'Amount' in MS Dynamics GP Financial Cube

Maybe it's difficult for me to see via the screenshots, but what is different between the two?

Amount is simply Debit-Credit. If you use Amount against Fiscal Periods dimension, it will simply show you the Net Change for every period with one key exception: the first period will contain an "Opening Balance" record that, when summed up with the Net Changes for that period, will actually result in the Period Ending balance being displayed for that period.

If you want to see Net Change only, you should use the "Bal Type" attribute under GL Trans dimension to filter out only records with "Transaction" type.

If you want to see rolling summary of period balances by period, you'll need to switch from using the Fiscal Periods dimension to using the Date hierarchy under Master Date. Both Periodicity and the measure that shows "Beg. Bal - YTD" will require you to use this hierarchy to effectively see rolling balances.

David