How to pull part of a pivot table field

[Rhino_Dance]

I'm working in a pivot table in Excel 2010 with a field called 'GLNum' that holds a General Ledger Number which looks like this: '101-4567-999'. I'd like to create some sort of calculation in the pivot table which will return the first 3 characters of the GLNum. I tried to do this as a Calculated Field by entering left(GlNum,3) but this returns '0' for all entries. A Calculated Item doesn't work either as that only allows you to manipulate actual dimension values, like adding 5 different GLNums together as a group.

Any ideas on how I can accomplish this?

There are reasons why I want to do this in the pivot table and not in the underlying data which go beyond the scope of this posting. Thanks for any suggestions.

 

[Fazza]

first hit was http://office.microsoft.com/en-us/excel-help/connect-to-import-external-data-HP010342297.aspx

the image at the first step shows an option 'from other sources'. Where does that go?

 

[Fazza]

from a quick glance, these may not help..

Create or delete a PivotTable or PivotChart report - Excel - Office.com

Create a PivotTable report - Excel - Office.com

 

[Rhino_Dance]

I appreciate everyone's help. The data that I'm pulling into the pivot table is actually from a SQL Server MDX cube. (MS Analysis Services). So that's why I can't manipulate it and that's why I'm interested in data manipulation directly in the pivot table. I realize that the main avenue to explore in solving this problem is using MDX (Mulit-Dimensional eXpression) language. But my first question is how I would take the 3 leftmost characters even if I was using a 'normal' pivot table that sourced its data from a table of data located directly in the spreadsheet. If I can first answer that question then I can use that answer to tackle the MDX question. So I'm basically wondering if there's a way to pull these 3 characters without using MDX. Thanks.

 

[Rhino_Dance]

Fazza,
Thanks for your help. I just got it to work by going directly thru MS Query first, rather trying to construct the pivot table first. At the very least I can pull my cube data down to an Excel sheet and then use MS Query to requery the data and pull off the 3 left most characters by using Sql.

Is there a way to award point for answers on this board? I don't see that capability anywhere...

 

[Fazza]

Well done. No award points but there is something about liking: I think a simple thank you is enough, though.

With the use of MS Query to pull the data and then a requery with MS Query, couldn't that be done in one step? Just add the extra field LEFT(GLNum,3) in the first query's select clause.

BTW, going straight to the pivot table has been around for 15+ years so there should be something like that in Excel 2010: just a matter of finding it!!

cheers

 

[Rhino_Dance]

Fazza, Thanks again. Please read 4 posts back. My recent work is sourcing the data from a cube and I'm experimenting with ways to pull off the 3 characters. But I wanted to know how to do it whether I'm pulling from a cube or whether I'm pulling from a local or Sql data table. This discussion has actually given me a great solution to an entirely separate problem as well. Thanks again.

 

[Fazza]

great to hear, thanks