|
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 |
| All times are GMT +1. The time now is 06:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com