add an additional field to pivottable

[Marckas]

Hello all,
I have an existing pivot table feeding information for an access query. I have just added another field to the query but I cant seem to add it to the pivot table. Please help.

 

[SkipVought]

Hi,

What application?

Where is your source data?

Geverally, in Excel, if you add fields to a PT, you must hit the BACK button, in the PT wizard, to get to the Source Data and modify the Range Reference.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Marckas]

Thanks for your help! The data source is ms access database with linked tables to sql server. There is a field in project server called "EnterpriseText2" which I have added to the query in the access database called portfolio analyzer. I have added a linked table from project server that contained the desired field called "EnterpriseText2". Then I added the field to the query that is feeding the pivot table. The problem I am encountering is that when I try to select the data source for the pivot table so I could pick up the new field, I go through choosing the data source which is MS Access Database. I select the updated query in the access database, and then when I click on finish, it gives me an error" ODBC - connection to SQL Serverppdapa15' failed. I know why I am getting the error since whenever someone opens up portfolio analyzer, they would have to log in to project server (SQL Server) using the abbottreader user name and password. I wanted to ask how I could bypass this error message so it would populate the pivot table or have it invoke a username and password dialogue boz so one could log in to project server. It does invoke the username and password dialogue box only if i select the SQL Server driver as a data source in which i had to point out to which server to look at and then enter the user name and password.

 

[SkipVought]

What is your SQL? Please post. Need to see how you are JOINING the tables.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

It also seems that you either need to have the UID & PWD in the connect string OR supply these credentials at run time.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Marckas]

SELECT A_Project_Type.Project_Type AS [Proj Type], A_Client_Group.Client_Group AS [Client Group], Left([PROJ_NAME],InStr([proj_name],".")-1) AS Project, [App Name].TEXT_VALUE AS [APP NAME], [IT Dept].TEXT_VALUE AS [IT DEPT], B_Resource_Summary_Monthly_Costs.TASK_NAME AS Task, B_Resource_Summary_Monthly_Costs.Funding, B_Resource_Summary_Monthly_Costs.Task_Type, A_RESOURCES.Cost_type, A_RESOURCES.RES_NAME AS Resource, A_RESOURCES.A_Resource_Type.Res_Type AS [Res Type], A_RESOURCES.Department, A_RESOURCES.Emp_status AS [Emp Status], B_Resource_Summary_Monthly_Costs.SumOfAct_Cost, B_Resource_Summary_Monthly_Costs.SumOfCost, B_Resource_Summary_Monthly_Costs.start_year AS [Year], B_Resource_Summary_Monthly_Costs.Quarter, B_Resource_Summary_Monthly_Costs.Month, dbo_MSP_ASSIGNMENTS.PROJ_ID AS [Proj UID], B_Resource_Summary_Monthly_Costs.AssignmentUniqueID AS [Assn UID], B_Resource_Summary_Monthly_Costs.TASK_UID AS [Task UID], B_Resource_Summary_Monthly_Costs.SumOfBase_Cost, A_RESOURCES.Res_Role, A_RESOURCES.dbo_MSP_RESOURCES.Res_Type, B_Resource_Summary_Monthly_Costs.Thread
FROM (((((dbo_MSP_ASSIGNMENTS LEFT JOIN A_RESOURCES ON (dbo_MSP_ASSIGNMENTS.PROJ_ID = A_RESOURCES.PROJ_ID) AND (dbo_MSP_ASSIGNMENTS.RES_UID = A_RESOURCES.RES_UID)) LEFT JOIN A_Client_Group ON dbo_MSP_ASSIGNMENTS.PROJ_ID = A_Client_Group.PROJ_ID) INNER JOIN A_Project_Type ON dbo_MSP_ASSIGNMENTS.PROJ_ID = A_Project_Type.PROJ_ID) LEFT JOIN B_Resource_Summary_Monthly_Costs ON (dbo_MSP_ASSIGNMENTS.ASSN_UID = B_Resource_Summary_Monthly_Costs.AssignmentUniqueID) AND (dbo_MSP_ASSIGNMENTS.PROJ_ID = B_Resource_Summary_Monthly_Costs.ProjectUniqueID)) LEFT JOIN [App Name] ON dbo_MSP_ASSIGNMENTS.PROJ_ID = [App Name].PROJ_ID) LEFT JOIN [IT Dept] ON dbo_MSP_ASSIGNMENTS.PROJ_ID = [IT Dept].PROJ_ID
WHERE (((B_Resource_Summary_Monthly_Costs.AssignmentUniqueID) Is Not Null));

 

[SkipVought]

1. Be certain that the query runs sucessfully apart from the PiovtTable.
2. Be certain that you supply the correct login credentials.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}