Pivot Table External Data % Problem

[Tony Morse]

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.

 

[Debra Dalgleish]

Because you've hidden the subtotals, you can't see the calculated
percentages for the total account number. Show the subtotals, and you'll
see 10%, 25%, etc.

You could create a field in MS Query to combine the account number and
name. In the heading for the first blank column, enter an expression
that combines the fields, e.g.:
Account & chr(95) & AcctName
Then use that field in the row area, instead of the two separate fields.

 

[Tony Morse]

That sort of worked - it shows the percentage on the subtotal line, but
still shows the #N/A on the "first" line. Also, I only have one line
per account number so Subtotals aren't desirable as the amount is
already a subtotal - the subtotal just repeats the same information
already presented.

 

[Debra Dalgleish]

Did you read the second paragraph of my reply? It suggested a workaround
in MS Query.

 

[Tony Morse]

Yes, sorry, I started thinking about it after I sent my reply earlier.

That seems like a good solution, but I am having problems as one field
is numeric and one is text, do you know the correct syntax to make that
work - the Help file doesn't offer too much info.

Thanks!

 

[Debra Dalgleish]

The formula in my example should work with a numeric field and a text field:
Account & chr(95) & AcctName
to combine them with an underscore character between the two fields.

What problem are you running into with your fields?

 

[Tony Morse]

I was getting a different error related to date, but I think it is
because my field name has a "#" in it (ACCT#). I tried your formula on
another column and received the following:

SQL0104 - Token & was not valid. Valid tokens: + - AS <IDENTIFIER>.

 

[Debra Dalgleish]

Maybe you can use one of the concatenation suggestions here:

http://www.sqlbook.com/SQL/SQL-CONCATENATE-24.aspx

 

[Tony Morse]

Thanks, that helped. It got me thinking about the SQL statement, and I
just edited it directly & changed the syntax ("||"), instead of putting
the formula in the column heading which avoided the error messages.

I think I'll work on moving away from MS query in the future...

You were a great help!