Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
rachael
 
Posts: n/a
Default pivot table sort entries that don't yet appear in table

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
  #2   Report Post  
Ron Coderre
 
Posts: n/a
Default

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

  #3   Report Post  
rachael
 
Posts: n/a
Default

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

  #4   Report Post  
Ron Coderre
 
Posts: n/a
Default

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

  #5   Report Post  
rachael
 
Posts: n/a
Default

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



  #6   Report Post  
Ron Coderre
 
Posts: n/a
Default

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

  #7   Report Post  
rachael
 
Posts: n/a
Default

Yes to all, except...

I see it appear in the layout, move it to the table as a column, click ok
and finish, wait as the table refreshes... and after it's refreshed it
doesn't appear in the table or the field list. When i go back into the query
(pivot table wizard, get data), the changes i made to the query are no longer
there. It's reverted back to what it was before.

Do you know what's going on? It's a mystery to me.

rachael


"Ron Coderre" wrote:

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

  #8   Report Post  
Ron Coderre
 
Posts: n/a
Default

We may be getting into Debra Dalgleish territory here, but....here's my last
thought:

Is the Pivot Table based on a SAVED QUERY and you are not RE-SAVING the
query after you adjust it?

--
Regards,
Ron


"rachael" wrote:

Yes to all, except...

I see it appear in the layout, move it to the table as a column, click ok
and finish, wait as the table refreshes... and after it's refreshed it
doesn't appear in the table or the field list. When i go back into the query
(pivot table wizard, get data), the changes i made to the query are no longer
there. It's reverted back to what it was before.

Do you know what's going on? It's a mystery to me.

rachael


"Ron Coderre" wrote:

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

  #9   Report Post  
rachael
 
Posts: n/a
Default

I tried saving it just in case (it wasn't previously saved, and when i
clicked Save it was saving it as a new file), and got the same results.

*sigh*
rachael

"Ron Coderre" wrote:

We may be getting into Debra Dalgleish territory here, but....here's my last
thought:

Is the Pivot Table based on a SAVED QUERY and you are not RE-SAVING the
query after you adjust it?

--
Regards,
Ron


"rachael" wrote:

Yes to all, except...

I see it appear in the layout, move it to the table as a column, click ok
and finish, wait as the table refreshes... and after it's refreshed it
doesn't appear in the table or the field list. When i go back into the query
(pivot table wizard, get data), the changes i made to the query are no longer
there. It's reverted back to what it was before.

Do you know what's going on? It's a mystery to me.

rachael


"Ron Coderre" wrote:

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

  #10   Report Post  
Ron Coderre
 
Posts: n/a
Default

I think at this point I'd scrap the original Pivot Table and build a new one
using either the saved query or the SQL from the saved query.

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


"rachael" wrote:

I tried saving it just in case (it wasn't previously saved, and when i
clicked Save it was saving it as a new file), and got the same results.

*sigh*
rachael

"Ron Coderre" wrote:

We may be getting into Debra Dalgleish territory here, but....here's my last
thought:

Is the Pivot Table based on a SAVED QUERY and you are not RE-SAVING the
query after you adjust it?

--
Regards,
Ron


"rachael" wrote:

Yes to all, except...

I see it appear in the layout, move it to the table as a column, click ok
and finish, wait as the table refreshes... and after it's refreshed it
doesn't appear in the table or the field list. When i go back into the query
(pivot table wizard, get data), the changes i made to the query are no longer
there. It's reverted back to what it was before.

Do you know what's going on? It's a mystery to me.

rachael


"Ron Coderre" wrote:

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



  #11   Report Post  
rachael
 
Posts: n/a
Default

Ok, if i create a new pivot table, that raises a different issue. The dataset
is so large that there's too much data even for a pivot table. THe pivot
table has to have a filter selected (e.g., location = Canada) before data can
be returned. When i try to just create a new pivot table off the modified sql
query, it bombs.

So... how do i create a new pivot table where a page filter is already
selected?

thanks
rachael

"Ron Coderre" wrote:

I think at this point I'd scrap the original Pivot Table and build a new one
using either the saved query or the SQL from the saved query.

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


"rachael" wrote:

I tried saving it just in case (it wasn't previously saved, and when i
clicked Save it was saving it as a new file), and got the same results.

*sigh*
rachael

"Ron Coderre" wrote:

We may be getting into Debra Dalgleish territory here, but....here's my last
thought:

Is the Pivot Table based on a SAVED QUERY and you are not RE-SAVING the
query after you adjust it?

--
Regards,
Ron


"rachael" wrote:

Yes to all, except...

I see it appear in the layout, move it to the table as a column, click ok
and finish, wait as the table refreshes... and after it's refreshed it
doesn't appear in the table or the field list. When i go back into the query
(pivot table wizard, get data), the changes i made to the query are no longer
there. It's reverted back to what it was before.

Do you know what's going on? It's a mystery to me.

rachael


"Ron Coderre" wrote:

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

  #12   Report Post  
Ron Coderre
 
Posts: n/a
Default

OK...here's something to try:

€¢Tweak the SQL so that it only returns a couple hundred records by entering
a criteria for an existing field.
€¢Create the Pivot Table the way you want it.
€¢Edit the SQL back to where it should be.

Does that help?

--
Regards,
Ron


"rachael" wrote:

Ok, if i create a new pivot table, that raises a different issue. The dataset
is so large that there's too much data even for a pivot table. THe pivot
table has to have a filter selected (e.g., location = Canada) before data can
be returned. When i try to just create a new pivot table off the modified sql
query, it bombs.

So... how do i create a new pivot table where a page filter is already
selected?

thanks
rachael

"Ron Coderre" wrote:

I think at this point I'd scrap the original Pivot Table and build a new one
using either the saved query or the SQL from the saved query.

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


"rachael" wrote:

I tried saving it just in case (it wasn't previously saved, and when i
clicked Save it was saving it as a new file), and got the same results.

*sigh*
rachael

"Ron Coderre" wrote:

We may be getting into Debra Dalgleish territory here, but....here's my last
thought:

Is the Pivot Table based on a SAVED QUERY and you are not RE-SAVING the
query after you adjust it?

--
Regards,
Ron


"rachael" wrote:

Yes to all, except...

I see it appear in the layout, move it to the table as a column, click ok
and finish, wait as the table refreshes... and after it's refreshed it
doesn't appear in the table or the field list. When i go back into the query
(pivot table wizard, get data), the changes i made to the query are no longer
there. It's reverted back to what it was before.

Do you know what's going on? It's a mystery to me.

rachael


"Ron Coderre" wrote:

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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Change Data In Pivot Table John Calder New Users to Excel 1 July 7th 05 10:41 PM
Pivot Table Sort & Top List excel_user123456 Excel Discussion (Misc queries) 0 June 23rd 05 08:53 PM
Pivot table, dynamic data formula Excel GuRu Excel Discussion (Misc queries) 3 May 3rd 05 10:45 PM
Pivot Table Problems Rachel Gonsior Excel Discussion (Misc queries) 3 March 21st 05 08:24 PM
Sort in Pivot Table Edgar Thoemmes Excel Worksheet Functions 2 December 22nd 04 04:34 PM


All times are GMT +1. The time now is 03:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"