Pivot Table External Data % Problem



Hi All,

I have the following scenario:

This is happening in both Excel 2003 & Excel 2007 using XP.
I am accessing an external data source though ODBC.
There is one table with Account Number, Date, Month, Amount.
There is another table with Account Number, Account Name.
I join the two tables using MS Query during the Pivot Table Wizard steps.
I then create the Pivot Table with Account # & Account Name as Row Labels, Month as Column Labels, and Amount as the Data.

Everything works fine, the Amounts for each month are summed and show up under the correct Month for each Account, for example:
Month
Acct# Title Data 1 2 3 4 5
12345 Cash Amount 100 100 200 300 400
54321 A/R Amount 250 250 250 250 250
56789 Sales Amount 1000 1000 1000 1000 1000

The problem comes when I want to add a field for the % of Sales. I create a new sum for Amount, then use Field Settings to Show Values as, select % Of, select the Account # for the Base field, and select the Account # for the Base Item - Account (56789).

The % for the Sales account is 100% as expected, but for all of the other accounts I get a #N/A error. For Example:

Month
Acct# Title Data 1 2 3 4 5
12345 Cash Amount 100 100 200 300 400
%ofSls #N/A #N/A #N/A #N/A #N/A
54321 A/R Amount 250 250 250 250 250
%ofSls #N/A #N/A #N/A #N/A #N/A
56789 Sales Amount 1000 1000 1000 1000 1000
%ofSls 100% 100% 100% 100% 100%

If I remove the Account Name field from the Pivot Table the #N/A goes away, and the correct % Amounts appear. If I add the field back, the #N/A appears again.

Can anyone shed any light as to why this is happening? I was thinking about joining the two tables to create a single table before I import the data, but I don't want to perform this extra step if I can avoid it, as this would create a table of static data that would need to be refreshed often.
.



Relevant Pages

  • Re: Design Question - Accounts/Transactions
    ... Design questions are difficult to give definitive answers to in a newsgroup since so often design decisions boil down to "it depends". ... I would just INCLUDE the amount in your ix_Transaction_account index. ... Unless you will have a very large number of transactions for each account, the performance will probably be OK, and you will not have denormalized your data. ...
    (microsoft.public.sqlserver.programming)
  • Re: Aging Function
    ... for each account. ... >DebitID, and Amount. ... >from Debits left join qryInvoiceReceiptTotals ... Start a transaction to ensure that all ...
    (microsoft.public.access.modulesdaovba)
  • Re: standard practice by crooks in the department.
    ... The information regarding the Fixed Deposit Accounts of Mahathir's ... The bank is ISRAELI NATIONAL BANK. ... That vast amount of monies could have been derived through the following: ... Type of Account: Fixed Deposit ...
    (soc.culture.malaysia)
  • Re: Malaysia Boleh lah....
    ... The information regarding the Fixed Deposit Accounts of Mahathir's ... The bank is ISRAELI NATIONAL BANK. ... That vast amount of monies could have been derived through the following: ... Type of Account: Fixed Deposit ...
    (soc.culture.singapore)
  • Are singapore leaders as rich as the Malaysian leaders?
    ... The information regarding the Fixed Deposit Accounts of Mahathir's ... The bank is ISRAELI NATIONAL BANK. ... That vast amount of monies could have been derived through the following: ... Type of Account: Fixed Deposit ...
    (soc.culture.singapore)