Hi,
I have been fighting this problem for 6 months and just downloaded powerpivot hoping it would help but I am still unable to create my report. I have two tables, one is GL postings by cost center, activity, and gl acct (detailed) and a budget file in excel also by cost center, activity, and gl acct. My budget data had each month as a column and my GL postings was row driven for the amounts.
The budget is not always populated in the GL accounts that actually have expenses posted to them. I tried creating a combo field consisting of cost center, activity, and gl acct by using &""& calcs and then did vlookups to merge my data together and then from there cut/pasted in a standard report that was simply cost center, activity, gl acct, period x actuals, period x budget, variance, YTD actuals, YTD budget, variance. And then parsing my combo field down again and dealing with the records that fell out because the combo was not in one or the other table.
So I thought powerpivot will help and I even changed the budget file so it is row driven too. so both of my tables now have company =1001 period = xxxx/xx cost center = xxxx activity = abcd gl account =123456, budget or actual, direct or indirect for expense type, and amount. and I cannot create relationship in excel due to duplicated values existing in columns, I still cannot get a pivot table to update my report.
I was hoping one day to have a drilldown to the journal entry detail for the actuals, but I cannot even cross this hurdle.
Can anyone please help?
I have been fighting this problem for 6 months and just downloaded powerpivot hoping it would help but I am still unable to create my report. I have two tables, one is GL postings by cost center, activity, and gl acct (detailed) and a budget file in excel also by cost center, activity, and gl acct. My budget data had each month as a column and my GL postings was row driven for the amounts.
The budget is not always populated in the GL accounts that actually have expenses posted to them. I tried creating a combo field consisting of cost center, activity, and gl acct by using &""& calcs and then did vlookups to merge my data together and then from there cut/pasted in a standard report that was simply cost center, activity, gl acct, period x actuals, period x budget, variance, YTD actuals, YTD budget, variance. And then parsing my combo field down again and dealing with the records that fell out because the combo was not in one or the other table.
So I thought powerpivot will help and I even changed the budget file so it is row driven too. so both of my tables now have company =1001 period = xxxx/xx cost center = xxxx activity = abcd gl account =123456, budget or actual, direct or indirect for expense type, and amount. and I cannot create relationship in excel due to duplicated values existing in columns, I still cannot get a pivot table to update my report.
I was hoping one day to have a drilldown to the journal entry detail for the actuals, but I cannot even cross this hurdle.
Can anyone please help?
