Lisa,
Microsoft had to get involved and re-arranged the Dimension IDs in the back-end. This was a take over client situation and the former VAR created the ticket. Here were the emails I was copied on if it is any help... (Keep in mind this is client specific so the update statements will not pertain necessarily to your dimension ID's. This is only to lead you in the right direction.
Bug #473870: You may not be able to pull data for an AA dimension in MR if the dimension's order was changed in GP
Bug #473872: If you have deleted or cleared a Transaction Dimension in GP, you will not be able to pull AA data on any dimensions created after this.
I was able to come up with a workaround to get past the bugs and get the expected data on the report. I will explain in detail but if you have any questions please give me a call and I can work with you to get things working. The problem is that MR will not return data from AA budgets when dimensions have been deleted as this causes the AA Dimension IDs to not be in numerical order. You can see this within SQL by running the following query – select * from aag00400
In the data returned you will see that the aaTrxDimID column has values of 5, 7, 8, 13, 15 and 16 (at least the data we have is set like this). The bug is that MR will only work if those would be 1, 2, 3, 4, 5 and 6. However we can’t just simply change the existing Dimension IDs as every existing transaction\budget within AA is tied to that ID so that relation would be lost. What we need to do is create some “dummy” dimensions in AA to fill in the missing numbers. We will mark these dimensions as Inactive and will not create any codes for them so they can’t actually be used within GP but they will allow MR to work until the bug is fixed and included in the next release. At that time these “dummy” dimensions can be deleted (technically they could be left forever as they won’t cause any problems).
Here are the steps to create these dimensions:
1. Make a backup of the company database
2. In GP go to Cards | Financial | AA | Transaction Dimension
3. Enter DUMMY1 for the Trx Dimension and Description 1 fields (you can use whatever name\description you want)
4. Select the Inactive button and click Save
a.
5. Repeat steps 2 and 3 until you have created 10 dimensions (I did DUMMY1 – DUMMY10)
6. Now go into SQL and run this query - select * from aag00400 - you will see these new dimensions. Now we need to set all the IDs to be 1-16
7. Run this query - update AAG00400 set aaTrxDimID = 1 where AATrxDim = 'dummy1' – this assumes you used the dummy1 naming convention listed in step 2. If not change your query slightly.
8. Run these additional queries to change everything to what we need.
a. update AAG00400 set aaTrxDimID = 2 where AATrxDim = 'dummy2'
b. update AAG00400 set aaTrxDimID = 3 where AATrxDim = 'dummy3'
c. update AAG00400 set aaTrxDimID = 4 where AATrxDim = 'dummy4'
d. update AAG00400 set aaTrxDimID = 6 where AATrxDim = 'dummy5'
e. update AAG00400 set aaTrxDimID = 9 where AATrxDim = 'dummy6'
f. update AAG00400 set aaTrxDimID = 10 where AATrxDim = 'dummy7'
g. update AAG00400 set aaTrxDimID = 11 where AATrxDim = 'dummy8'
h. update AAG00400 set aaTrxDimID = 12 where AATrxDim = 'dummy9'
i. update AAG00400 set aaTrxDimID = 14 where AATrxDim = 'dummy10'
9. Run – select * from aag00400 - now the aaTrxDimID column should be 1-16
10. Technically there is a second but related bug. Notice the aaOrder column. We also need that to be 1-16
11. In GP go to Cards | Financial | AA | Transaction Dimension Order
12. Move the dimensions up\down until they look like this:
a.
13. Now run – select * from aag00400 again and it should look like this:
a.
14. Finally, test your report in MR and you should get the AA budget data. One thing to make sure of that I think Erik mentioned originally is that you do have to have amounts assigned down to the account level in your AA budgets.
I know that sounds like a lot but at a high level all we’re doing is creating some extra dimensions to fill in the missing IDs and then putting them in order. Please let me know if there are any questions.
Share
Report
1,075
All responses (
Answers (
