Finding a Fiscal Quarter of a task without heavy calculation in custom fields (Project server 2007) RRS feed

  • Question

  • Hi all

    Some of are users need to customize a solution in which they are checking what are the fiscal quarters that some milestones finish date are in (for example: if a milestone is between the dates of Qtr1 then a it the milestone Qtr will be calculated as Qtr1) and  comparing it the the current Qtr (meaning, if today is Qtr1 then i need to check if the milestone Qtr is < or = or > to current Qtr).

    Then, we need to check some more condition and return a status value.

    We are facing some performance issue that is probably due to the heavy calculations we have on some other custom fields (insert or delete a row on large files takes 5-6 seconds), so we want to avoid as much formula based custom fields.

    Is there a way in the project or server to use a default MS field or some other attribute to get the Qtr of a task/milestone date without checking if the date is between a period for all quarter in the next 2-3 years?

    if not in 2007, is there a way to do that in 2013 (we will be there in 3-6 months)?

    I am willing to here any idea to do so in project pro, PWA, data analysis, BI, etc.

    i will share an example:

    • SOP milestone finish is 1/10/2015
    • Pack milestone finish is 1/25/2015
    • Ship milestone is 2/15/2015

    In our fiscal calendar November - January is Q1 and February is Q2.

    That means that SOP, Pack is in Q1, Ship is in Q2.

    For this condition i will get (SOP Q = Pack Q = Current Q = Q1 2015) and < Ship Q which is Q2.

    --> Status will be "FGI"

    In other cases i will get different status but that is the idea.

    What can i do to avoid calculating the current Qtr and each MS Qtr?



    Ofir Marco , MCTS P.Z. Projects

    Monday, January 19, 2015 11:43 AM

All replies

  • Hi Ofir,

    Do you need to have this information in MS Project Pro? Or having a report is acceptable?

    Creating a SSRS report seems to be the best solution since it will solve your performance issues (we indeed know that task custom fields with formula do impact the performance) and you'll be able to use the fiscal time defined in the PWA server settings.

    Hope this helps,

    Guillaume Rouyre, MBA, MVP, P-Seller |

    Monday, January 19, 2015 12:23 PM
  • At this point we need it in MS Project Pro, this is the issue.

    if we will not have another choice then we will take this issue to be developed in SSRS as a report and remove the calculated fields. 

    We are considering it.

    If you mentioned the known performance effect of formula fields, is there a specific guidelines and limitations for how much formula fields are reasonable to have as enterprise fields?  How much hookup fields effects on the performance. etc.?

    Also, what are the guidelines about project rows, how many are OK?   what combination of calculated fields with number of rows will have a major effect on performance?

    And another last question, is it the same in 2013?  or should it effect less on performance?

    Ofir Marco , MCTS P.Z. Projects

    Tuesday, January 20, 2015 8:25 AM