locked
Adding Custom fields to the timesheet report RRS feed

  • Question

  • We have several custom fields we want to add to one of the timesheet reports(SQL not OLAP)

    Is there a way to this this besides edting the SQL query in the connection?

    The custom fields do appear in the OLAP DB buy the numbers are way off so we cant use that one

    We are using Project Server 2010, running Dec 2010 CU

    I am referrinf to the samle templates that open up in Excel 2010 


    Friday, May 13, 2011 11:42 PM

Answers

All replies

  • Hi,

    If the SQL query in the ODC file is limiting the data coming back, then no, you need to change the ODC file. If your worried about breaking it for other users, then create another ODC file and make the changes to that one, then point your SQL Query directly at it.

    As for the OLAP cubes being off, remember the ReportingDB is near real time, whereas the OLAP cubes process on a user defined frequency, you may need to up the processing time, or manual rebuild prior to running your report.

    Hope this helps,


    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Friday, May 13, 2011 11:50 PM
  • thanks I am new the reporting.

    If I have a report made and open it up in Excel can I do what you described by Selecting Change Data Source Connection>> properites > Definitions Tab then

    alter the select statement?

     

    Are the custom fields added by modifying the select statement? If so where do you get the field names to add to the statement.

    I may have this totally incorrect, please let me know if I do.

    As for OLAP even after an error free manualy build its  still way off

    The connection type Project Server Time Sheet Data

     

    Saturday, May 14, 2011 2:16 PM
  • Hi,

    This blog post by Andrew Lavinsky takes you through the process of altering an ODC file.

    http://blogs.catapultsystems.com/epm/archive/2010/01/18/modifying-the-default-odc-files-to-filter-on-specific-projects.aspx

    Altering the SQL statement is about changing the data returned in the query. I would suggest downloading the Project 2010 SDK (http://www.microsoft.com/downloads/en/details.aspx?FamilyID=46007f25-b44e-4aa6-80ff-9c0e75835ad9&displaylang=en) which includes the complete schema of the reporting database, allowing you to determine which extra fields you want to get at. Any custom fields you may have added usually show up in the views, specifically the _userview views. Changing the SQL to look at these will most likely get you the info you are after.

    Hope this helps, if your still lost, ping us the name and type of custom fields you are looking at getting out and we can write the SQL for you and post it back :)

     


    Alex Burton
    www.epmsource.com | Twitter
    Project Server TechCenter | Project Developer Center | Project Server Help | Project Product Page
    Sunday, May 15, 2011 7:38 AM
  • thats for the reply

    If you modify the SQL liked displayed in the article on a saved report is that doing the same thing?r 

    Is the excel sheet just a vehicle for the ODC and that is what  you filter data with?

    I guess whats throwing me off about this process is that know/finding the custom fields seems ,like you need DB access, is that correct?

     


    Whats the easiest way to find what the actual names are of the custom fields?
    Sunday, May 15, 2011 1:29 PM
  • I ran this query to get the custom name, am I on the right track?

    SELECT CustomFieldName, ColumnPoolTableName, ColumnPoolColumnName
    FROM MFN_Epm_GetAllCustomFieldsInformation()
    WHERE EntityName = N'EntityType'

    Monday, May 16, 2011 1:43 PM
  • While it is possible to use Excel without and ODC, it really doesn't make sense. When an Excel workbook is connected to an odc, then it uses the connection and query defined in the odc.


    Gary Chefetz, MCITP, MCP, MVP msProjectExperts
    Project and Project ServerFAQs
    Project Server Help BLOG
    Wednesday, May 18, 2011 2:04 PM