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

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

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

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

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

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

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

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