I have a pivot table that's accessing an ODBC data source. There's a field
for career level in the table that's currently sorted alphabetically. I'd
like to sort it in order of career level, instead. I'd need to do this
manually, because there is no simple alphabetic progression i can use. The
problem is, the table must be filtered on a single location (a page filter)
because there's so much data and while filtered not all career levels appear.
One location may show 2 career levels, another country may show 3, another
may show only 1.

How can i set this up to change the career level field from:
Analyst
Consultant
Manager
Specialist

to:
Manager
Consultant
Specialist
Analyst

Thanks
rachael

Would it be practical to create a lookup table for Career Level that would
prepend a sorting field? After importing the data, use a helper column to
create a display career level.

Example:
Level€¢€¢€¢€¢€¢€¢€¢€¢€¢DisplayLevel
-------------- -------------------
Manager€¢€¢€¢€¢€¢€¢001_Manager
Consultant€¢€¢€¢€¢002_Consultant
Specialist€¢€¢€¢€¢€¢003_Specialist
Analyst€¢€¢€¢€¢€¢€¢€¢004_Analyst

Then the pivot table would sort properly without manual intervention.

Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"rachael" wrote:

I have a pivot table that's accessing an ODBC data source. There's a field
for career level in the table that's currently sorted alphabetically. I'd
like to sort it in order of career level, instead. I'd need to do this
manually, because there is no simple alphabetic progression i can use. The
problem is, the table must be filtered on a single location (a page filter)
because there's so much data and while filtered not all career levels appear.
One location may show 2 career levels, another country may show 3, another
may show only 1.

How can i set this up to change the career level field from:
Analyst
Consultant
Manager
Specialist

to:
Manager
Consultant
Specialist
Analyst

Thanks
rachael

I think i need more info... pivot tables ain't my forte! Are you saying that
i should create a separate column somewhere (the lookup table) that lists the
career levels in the order i want with an index attached (e.g., 01 Manager)
for proper sorting? If i do that, how would i "attach" it to the pivot table?

thanks
rachael

"Ron Coderre" wrote:

Would it be practical to create a lookup table for Career Level that would
prepend a sorting field? After importing the data, use a helper column to
create a display career level.

Example:
Level€¢€¢€¢€¢€¢€¢€¢€¢€¢DisplayLevel
-------------- -------------------
Manager€¢€¢€¢€¢€¢€¢001_Manager
Consultant€¢€¢€¢€¢002_Consultant
Specialist€¢€¢€¢€¢€¢003_Specialist
Analyst€¢€¢€¢€¢€¢€¢€¢004_Analyst

Then the pivot table would sort properly without manual intervention.

Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"rachael" wrote:

I have a pivot table that's accessing an ODBC data source. There's a field
for career level in the table that's currently sorted alphabetically. I'd
like to sort it in order of career level, instead. I'd need to do this
manually, because there is no simple alphabetic progression i can use. The
problem is, the table must be filtered on a single location (a page filter)
because there's so much data and while filtered not all career levels appear.
One location may show 2 career levels, another country may show 3, another
may show only 1.

How can i set this up to change the career level field from:
Analyst
Consultant
Manager
Specialist

to:
Manager
Consultant
Specialist
Analyst

Thanks
rachael

At first I was puzzled by your question, but then I realized that you are
accessing the data directly, not importing it to a worksheet range. That was
the approach I was tacking.

If your database is MS Access, you could create the lookup table there and
join it in your query. If your source an Oracle DB, the Oracle DECODE
function may do what you need. It's been a while since I've used SQL Server,
so I can't remember if it uses DECODE or some other function.

Otherwise, it might be easiest to base the Pivot Table on data imported into
a worksheet and use the helper column.

Any of those options workable?

--
Regards,
Ron


"rachael" wrote:

I think i need more info... pivot tables ain't my forte! Are you saying that
i should create a separate column somewhere (the lookup table) that lists the
career levels in the order i want with an index attached (e.g., 01 Manager)
for proper sorting? If i do that, how would i "attach" it to the pivot table?

thanks
rachael

"Ron Coderre" wrote:

Would it be practical to create a lookup table for Career Level that would
prepend a sorting field? After importing the data, use a helper column to
create a display career level.

Example:
Level€¢€¢€¢€¢€¢€¢€¢€¢€¢DisplayLevel
-------------- -------------------
Manager€¢€¢€¢€¢€¢€¢001_Manager
Consultant€¢€¢€¢€¢002_Consultant
Specialist€¢€¢€¢€¢€¢003_Specialist
Analyst€¢€¢€¢€¢€¢€¢€¢004_Analyst

Then the pivot table would sort properly without manual intervention.

Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"rachael" wrote:

I have a pivot table that's accessing an ODBC data source. There's a field
for career level in the table that's currently sorted alphabetically. I'd
like to sort it in order of career level, instead. I'd need to do this
manually, because there is no simple alphabetic progression i can use. The
problem is, the table must be filtered on a single location (a page filter)
because there's so much data and while filtered not all career levels appear.
One location may show 2 career levels, another country may show 3, another
may show only 1.

How can i set this up to change the career level field from:
Analyst
Consultant
Manager
Specialist

to:
Manager
Consultant
Specialist
Analyst

Thanks
rachael

Sadly, no. The data's coming from a SQL server and there's waaaay too much
too pull into a worksheet range. I tried going into the query and adding a
field (CareerLevel2) using Case When, but it doesn't seem to want to keep
that field. I see CareerLevel2 appear in MS Query, but when i try to add it
to the pivot table it disappears.

Is there anything else i can do - or do you know why the pivot table won't
keep this new field?

thanks
rachael

"Ron Coderre" wrote:

At first I was puzzled by your question, but then I realized that you are
accessing the data directly, not importing it to a worksheet range. That was
the approach I was tacking.

If your database is MS Access, you could create the lookup table there and
join it in your query. If your source an Oracle DB, the Oracle DECODE
function may do what you need. It's been a while since I've used SQL Server,
so I can't remember if it uses DECODE or some other function.

Otherwise, it might be easiest to base the Pivot Table on data imported into
a worksheet and use the helper column.

Any of those options workable?

--
Regards,
Ron


"rachael" wrote:

I think i need more info... pivot tables ain't my forte! Are you saying that
i should create a separate column somewhere (the lookup table) that lists the
career levels in the order i want with an index attached (e.g., 01 Manager)
for proper sorting? If i do that, how would i "attach" it to the pivot table?

thanks
rachael

"Ron Coderre" wrote:

Would it be practical to create a lookup table for Career Level that would
prepend a sorting field? After importing the data, use a helper column to
create a display career level.

Example:
Level€¢€¢€¢€¢€¢€¢€¢€¢€¢DisplayLevel
-------------- -------------------
Manager€¢€¢€¢€¢€¢€¢001_Manager
Consultant€¢€¢€¢€¢002_Consultant
Specialist€¢€¢€¢€¢€¢003_Specialist
Analyst€¢€¢€¢€¢€¢€¢€¢004_Analyst

Then the pivot table would sort properly without manual intervention.

Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"rachael" wrote:

I have a pivot table that's accessing an ODBC data source. There's a field
for career level in the table that's currently sorted alphabetically. I'd
like to sort it in order of career level, instead. I'd need to do this
manually, because there is no simple alphabetic progression i can use. The
problem is, the table must be filtered on a single location (a page filter)
because there's so much data and while filtered not all career levels appear.
One location may show 2 career levels, another country may show 3, another
may show only 1.

How can i set this up to change the career level field from:
Analyst
Consultant
Manager
Specialist

to:
Manager
Consultant
Specialist
Analyst

Thanks
rachael

Are you saying that after you:

€¢Click on the pivot table
€¢Click on the Pivot Table Wizard button
€¢Click the [Back] button
€¢Click the [Get Data] button
€¢Adjust the query and return data
€¢Click the [Next] button
€¢Click the [Layout] button . . .

you don't see the new field listed on the right side?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"rachael" wrote:

Sadly, no. The data's coming from a SQL server and there's waaaay too much
too pull into a worksheet range. I tried going into the query and adding a
field (CareerLevel2) using Case When, but it doesn't seem to want to keep
that field. I see CareerLevel2 appear in MS Query, but when i try to add it
to the pivot table it disappears.

Is there anything else i can do - or do you know why the pivot table won't
keep this new field?

thanks
rachael

"Ron Coderre" wrote:

At first I was puzzled by your question, but then I realized that you are
accessing the data directly, not importing it to a worksheet range. That was
the approach I was tacking.

If your database is MS Access, you could create the lookup table there and
join it in your query. If your source an Oracle DB, the Oracle DECODE
function may do what you need. It's been a while since I've used SQL Server,
so I can't remember if it uses DECODE or some other function.

Otherwise, it might be easiest to base the Pivot Table on data imported into
a worksheet and use the helper column.

Any of those options workable?

--
Regards,
Ron


"rachael" wrote:

I think i need more info... pivot tables ain't my forte! Are you saying that
i should create a separate column somewhere (the lookup table) that lists the
career levels in the order i want with an index attached (e.g., 01 Manager)
for proper sorting? If i do that, how would i "attach" it to the pivot table?

thanks
rachael

"Ron Coderre" wrote:

Would it be practical to create a lookup table for Career Level that would
prepend a sorting field? After importing the data, use a helper column to
create a display career level.

Example:
Level€¢€¢€¢€¢€¢€¢€¢€¢€¢DisplayLevel
-------------- -------------------
Manager€¢€¢€¢€¢€¢€¢001_Manager
Consultant€¢€¢€¢€¢002_Consultant
Specialist€¢€¢€¢€¢€¢003_Specialist
Analyst€¢€¢€¢€¢€¢€¢€¢004_Analyst

Then the pivot table would sort properly without manual intervention.

Does that help?

€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢€¢
Regards,
Ron


"rachael" wrote:

I have a pivot table that's accessing an ODBC data source. There's a field
for career level in the table that's currently sorted alphabetically. I'd
like to sort it in order of career level, instead. I'd need to do this
manually, because there is no simple alphabetic progression i can use. The
problem is, the table must be filtered on a single location (a page filter)
because there's so much data and while filtered not all career levels appear.
One location may show 2 career levels, another country may show 3, another
may show only 1.

How can i set this up to change the career level field from:
Analyst
Consultant
Manager
Specialist

to:
Manager
Consultant
Specialist
Analyst

Thanks
rachael