flatten pivotTable

[aaron.kempf]

hey i want to 'flatten the hierarchies' in a pivotTable and basically
repeat the duplicated rows

so right now i have

US NORTH
SOUTH
WEST
EAST
CANADA WEST
EAST

I want to change this into

US NORTH
US SOUTH
US WEST
US EAST
CANADA WEST
CANADA EAST

Isn't there any easy way to do that? I swear that it is an option
somewhere; i just can't find it.

In the programming world we call this 'flattening the hierarchial
recordset' aka when you use ADO to consume OLAP data (vs using ADOMD to
consume OLAP data)

but since I'm consuming pivotTables from 4 different sources; it isn't
really a choice for me to source the data directly
(and i must import pivotTable numbers into a database)

 

[Ken Wright]

I think you should Access for this <g>

If it's for an import you won't need it to stay linked, so copy and paste
the entire sheet as values to break the Pivot link. Select the entire
table, do Edit / Go to / Special / Blanks, type = and then without hitting
enter hit the UP arrow once, and then hit CTRL+ENTER.

Repeat the copy paste to get rid of the formulas

 

[aaron.kempf]

looks good thanks ken.. i'll give that a shot

at first i coudlnt find the 'tabular format (vs outline format) i was
hoping that would be what i wanted

i tink that pivotTables in Access let you do this easier.. wonder if
there is a way to turn an excel pivotTable directly into an Access
PivotTable??

 

[Dave Peterson]

Just to add to Ken's post...

It's usually easier to clean up the pivottable (remove the subtotals) than to
convert to values, then clean up.

 

[onedaywhen]

If it's for an import you won't need it to stay linked, so copy and paste
the entire sheet as values to break the Pivot link.

Yeah, do doubt he'll be doing this manually every week <g>.
Jamie.

--

 

[aaron.kempf]

I figured this out, i just fixed it by importing into a database and
then writing a subquery to flatten

INSERT INTO dbo.F_CSHEADCOUNT
(StandardTitle, ClosedMonth, ActualPeople,
ApprovedPosition)
SELECT COALESCE ([Standard Title],
(SELECT MIN([Standard Title])
FROM staging_csHeadcount subQ
WHERE subQ.ID =
(SELECT
MAX(ID)
FROM
staging_csHeadCount subQ2
WHERE
LEN([Standard Title]) > 0 AND subQ2.ID < staging_csHeadcount.ID AND
subQ2.[Standard Title] NOT LIKE '% TOTAL')))
AS fixedStandardTItle, [Closed Month], [Actual
People], [Approved Position]
FROM dbo.staging_csHeadcount
WHERE (NOT ([Standard Title] LIKE '% Total')) OR [Standard Title]
IS NULL