Clarity PPM1

Expand all | Collapse all

Project Finish Date in Clarity

Jump to Best Answer

Andrew Mowlam09-30-2013 05:04 AM

  • 1.  Project Finish Date in Clarity

    Posted 09-27-2013 03:35 AM
    Hi All,

    I want to know how the “Project Finish Date “ in Clarity is stored.

    I know that the finish date in Clarity UI is always displayed one day less ‘Finish Date - 1’ format as of the value in DB.

    But currently I have two projects in question ,


    Project A is having Finish Date same as in UI and DB  Finish date(UI) = ‘12/20/13’ and Finish Date(DB) = ‘20-DEC-13’.

    Project B is having Finish date-1 in UI but finish Date in DB  Finish date(UI) = ‘12/31/2014’ and Finish Date(DB) = ‘01-JAN-15’.

    I’m checking the Table INV_INVESTMENTS in DB for both the projects.

    Any reason why this is happening?

    Thanks


  • 2.  RE: Project Finish Date in Clarity
    Best Answer

    Posted 09-27-2013 03:44 AM
    I hazzard a guess that in the case of the investment where database finish date and UI finish date are the same has a non-midnight finish time (e.g. 5pm) while the other is UI date+1 but with midnight timestamp.


  • 3.  RE: Project Finish Date in Clarity

    Posted 09-27-2013 04:00 AM
    Thanks for the Clarification.

    Yes I see the time stamp and that is the reason for Finish Date+1 case.

    So just to be more specific, Clarity will not store Project Finsih Date +1 in DB, it will be the same as in UI for all the projects but all this will depend on the TIME Stamp of the date. Right?

    Thanks


  • 4.  RE: Project Finish Date in Clarity

    Posted 09-27-2013 04:03 AM
    just for clarification, how do you think this occured?
    (via tasks, allocations etc)


  • 5.  RE: Project Finish Date in Clarity

    Posted 09-27-2013 04:48 PM
      |   view attached
    Did some simple tests in v12.1 and in them the database timestamp is withing the working day and not at midnight.

    The project dates in these tests were the same in the database and in the GUI and OWB and appear to be independent from tasks dates when there are no assignment.
    When there are resources assigned and ETC set on the assignments then the project dates reflect the dates of the tasks and assignments.

    When the loading pattern contour the finish time is the finish time of the working day.

    When the loading pattern is front the finish time is the time when the work effort on the assignment ends.

    Waht was your version


    Martti K.


    .

    Attachment(s)



  • 6.  RE: Project Finish Date in Clarity

    Posted 09-30-2013 05:04 AM
    Thanks Martti for the very informative PDF.


  • 7.  RE: Project Finish Date in Clarity

    Posted 09-30-2013 06:23 AM
    Sorry to say, my conclusion was that with those actions the timestamp on finish was always at the end of the last shift of the working day in the calendar not at midnight.

    Typically you would have midnight for the finish of timeperiods because they go by 24 hour days.

    Martti K.


  • 8.  RE: Project Finish Date in Clarity

    Posted 10-17-2019 01:59 PM
    @Martti Kinnunen, and what technical action can I take to solve this problem with the dates?


  • 9.  RE: Project Finish Date in Clarity

    Posted 10-17-2019 02:24 PM
    In the discussion above the project, task and assignment dates should be the same and any of those not the next day.
    It appeared that the next day was when there were no resources with effort assigned to tasks.

    Do you have resources assigned to tasks with effort?

    If your problem is with reports first query the database to see the hours in the date field. If it is 00:00:00 modify the query to be less than the previously used date. If you cannot modify the date for the query. modify the criteria. If you cannot modify the criteria like you select Month from a list you either have to get the times of the dates changed in the database in a supported manner, the report query rewritten or criteria part rewritten.


  • 10.  RE: Project Finish Date in Clarity

    Posted 10-18-2019 01:54 AM
    Where do you need to solve this?  NSQL portlets?  Queries on the DWH schema?  With which DBMS vendor?

    The answer can differ a bit depending on your situation, but your overall strategy in all cases is to wrap finish dates in a function or calculation, that reduces them by a small fraction of a day and then rounds down.

    If using the Clarity schema, they pretty much are all guaranteed to have the PMO Accelerator installed (for modern ux and/or data warehouse), and so may take advantage of the function COP_CALC_FINISH_FCT() provided:



    In Oracle you can subtract a small amount ("1 second" ~= 0.00001) from a date and then round it down with trunc() to simulate the same thing on schemas that have no comparable function:



    For Microsoft SQL Server, you may want to attempt something similar with the DateAdd() family of functions ("add" a small negative number of minutes to the date, like -1, and then depending on your version of MSSQL, cast/convert/datediff it to drop the time portion).


  • 11.  RE: Project Finish Date in Clarity

    Posted 10-18-2019 11:41 AM
    @Nick Darlington ​my problem is with the delivery reports.
    I believe the way is through Portlets




  • 12.  RE: Project Finish Date in Clarity

    Posted 10-30-2019 09:34 AM
    Friends.
    To clarify on this topic, do I have to make the change in clarity itself?
    Or any changes in DB SQL 2012?
    Can someone please enlighten me what I should do.


    Thanks.

    Alexandre


  • 13.  RE: Project Finish Date in Clarity

    Posted 10-30-2019 10:16 AM
    You would apply the change to any queries of reports/portlets on the finish date columns they are outputting.

    You would not apply changes to the data in the database itself.


  • 14.  RE: Project Finish Date in Clarity

    Posted 11-01-2019 11:18 AM
    @Nick Darlington, understand. Could you please tell me the step by step for me to perform this setup.



    Thanks Alexandre



  • 15.  RE: Project Finish Date in Clarity

    Posted 11-01-2019 12:06 PM
    "Step by Step" ?

    1. Identify the custom portlet / report where you believe you have the problem
    2. Look at the SQL that drives that portlet / report
    3. If the SQL does not apply the logic that has been suggested, then correct it


  • 16.  RE: Project Finish Date in Clarity

    Posted 25 days ago
    @David Morton ​, I can not understand where I should configure.
    Please see the attached screen.

    Thanks

    Alexandre



  • 17.  RE: Project Finish Date in Clarity

    Posted 24 days ago
    Friends, please, does anyone have any additional information to give me regarding this case?



  • 18.  RE: Project Finish Date in Clarity

    Posted 23 days ago
    Edited by Alexandre Gavazzi 23 days ago
    @David Morton
    Caros amigos, você pode me dizer se o sql abaixo é o que eu preciso para alterar, por favor.


    SELECT
    @SELECT: DIM: USER_DEF: IMPLIED: ENT: QueueNumber () over (order by a.id desc): id_ord @,
    @SELECT: DIM_PROP: USER_DEF: IMPLIED: ENT: a.code: delivery_id @
    , @ SELECT: DIM_PROP: USER_DEF: Implicit: ENT: a.code: code_delivery @
    , @ SELECT: DIM_PROP: USER_DEF: Implicit: ENT: a.name: delivery_name @
    , @ SELECT: DIM_PROP: USER_DEF: Implicit: ENT: a.term: delivery_end @
    , @SELECT: DIM_PROP: USER_DEF: IMPLIED: ENT: a.code_task: code_task @
    , @ SELECT: DIM_PROP: USER_DEF: IMPLIED: ENT: a.task_name: task_name @
    , @ SELECT: DIM_PROP: USER_DEF: End_Task @
    , @ SELECT: DIM_PROP: USER_DEF: IMPLIED: ENT: a.bnbti_state: bnbti_state @
    , @ SELECT: DIM_PROP: USER_DEF: IMPLIED: ENT: a.obs: obs @
    FROM (SELECT DISTINCT
    id.id id,
    ent.code code,
    I name ent.bnbti_nent_ent,
    ent.bnbti_dt_start start,
    ent.bnbti_dt_terminacao I finish,
    ent.bnbti_ent_cronogram timeline_delivery,
    ent.task_id_task delivery_id
    ent.bnbti_cod_task delivery_task cod,
    tar.prExternalID task_code,
    tar.prname task_name,
    tar.prstart start_task,
    tar.prfinish task_end,
    state.then,
    CASE
    WHEN ent.id IS NULL THEN 'INCLUSION'
    WHEN ent.id IS NOT NULL AND
    ent.bnbti_nome_ent <> tar.prname THEN 'indicated'
    WHEN ent.id IS NOT NULL AND
    ent. bnbti_dt_termino <> tar.prfinish THEN 'Change'
    END obs
    FROM odf_ca_bnbti_delivery then
    FULL EXTERNAL PRTASK tar
    ON tar.prid = ent.bnbti_id_task
    joined left odf_ca_task ct
    ON ct.id = tar.prid
    WHERE tar.prprojectid = @where: param: xml: string: / data / id / @ value @
    and ct. bnbti_entrega = 1
    and filter
    UNITY
    SELECT DISTINCT
    Id.id id,
    ent.code code,
    I name ent.bnbti_nent_ent,
    ent.bnbti_dt_start start,
    ent.bnbti_dt_terminacao I finish,
    ent.bnbti_ent_cronogram timeline_delivery,
    ent.task_id_task delivery_id
    ent.bnbti_cod_task delivery_task cod,
    tar.prExternalID task_code,
    tar.prname task_name,
    tar.prstart start_task,
    tar.prfinish task_end,
    state.then,
    'Exclusion' obs
    FROM odf_ca_bnbti_delivery then
    let PRTASK tar into tar.prid = ent.bnbti_id_tarefa
    WHERE ent.odf_parent_id = @where: param: xml: string: / data / id / @ value @
    AND tar.pruid is null
    ) a
    Where
    a.bnbti_estado is not in ('CANCELADA') or a.bnbti_estado is null

    Obrigado 

    Alexandre

    -------------------------------------------
    Mensagem original:
    Enviado: 12-12-2019 14:57
    De: Alexandre Gavazzi
    Assunto: Dados de término do projeto no Clarity

    Friends, alguém tem alguma informação adicional para me fornecer sobre esse caso?

    Mensagem original:
    Enviado: 11-11-2019 10:31
    De: Alexandre Gavazzi
    Assunto: Dados térmicos do projeto no Clarity

    @David Morton, não é possível entender onde devo configurar.
    Por favor, veja uma tela em anexo.

    Obrigado

    Alexandre

    Mensagem original:
    Enviado: 11-01-2019 12:05
    De: David Morton
    Assunto: Data de término do projeto com clareza

    "Passo a passo"?

    1. Identifique o portlet / relatório personalizado em que você acredita ter um problema.
    2. Observe o SQL que aciona esse portlet / relatório.
    3. Se o SQL não aplicar uma lógica sugerida, corrija-a.
    Mensagem original:
    Enviado : 11-01-2019 11:17
    De: Alexandre Gavazzi
    Assunto: Dados térmicos do projeto no Clarity

    @Nick Darlington, entende. Você poderia me dizer o passo a passo para executar esta configuração?



    Obrigado Alexandre

    Mensagem original:
    Enviado: 30-10-2019 10:16
    De: Nick Darlington
    Assunto: Dados térmicos do projeto com clareza

    Você aplica alterações em qualquer consulta de relatórios / portlets nas colunas de dados térmicos que eles estão produzindo.

    Você não aplica alterações aos dados no próprio banco de dados.
    Mensagem original:
    Enviado: 30-10-2019 09:34
    De: Alexandre Gavazzi
    Assunto: Data de término do projeto no Clarity

    Friends.
    Para esclarecer esse tópico, tenho que fazer uma alteração na clareza?
    Ou alguma alteração no DB SQL 2012?
    Alguém por favor pode me esclarecer o que devo fazer.


    Obrigado.

    Alexandre
    Mensagem original:
    Enviado: 18-10-2019 11:40
    De: Alexandre Gavazzi
    Assunto: Dados de término do projeto no Clarity

    @Nick DarlingtonMeu problema é com os relatórios de entrega.
    Eu acredito que o caminho é através dos portlets


    Mensagem original:
    Enviado: 18-10-2019 01:53
    De: Nick Darlington
    Assunto: Data de término do projeto com clareza

    Onde você precisa resolver isso? Portlets NSQL? Consultas no esquema DWH? Com qual fornecedor do DBMS?

    A resposta pode diferir um pouco, dependendo da sua situação, mas sua estratégia geral em todos os casos é agrupada como dados térmicos em uma função ou cálculo, que reduz em uma pequena fração do dia e depois se fecha.

    Se você estiver usando o esquema do Clarity, é praticamente garantido que o PMO Accelerator estará instalado (para ux e / ou data warehouse modernos) e, portanto, poderá aproveitar as funções COP_CALC_FINISH_FCT ():



    No Oracle, você pode subtrair uma pequena quantidade ("1 segundo" ~ = 0,00001) de dados e arredondar-com-trunc () para simular a mesma coisa nos esquemas que não possuem função comparável:



    Para o Microsoft SQL Server, convém tentar algo semelhante com a família de funções DateAdd () ("adicione" um pequeno número negativo de minutos à data, como -1 e, dependendo da sua versão do MSSQL, faça a conversão / conversão / datado para soltar a parte do tempo).
    Mensagem original:
    Enviado: 17-10-2019 13:59
    De: Alexandre Gavazzi
    Assunto: Data de término do projeto no Clarity

    @Martti Kinnunen , e que ação técnica posso tomar para resolver esse problema com as datas?
    Mensagem original:
    Enviado: 30-09-2013 06:23
    De: Martti Kinnunen
    Assunto: Data de término do projeto com clareza

    Lamento dizer, minha conclusão foi que, com essas ações, o registro de dados e a hora estavam sempre no final do último turno do dia útil do calendário, e não à meia-noite.

    Normalmente, você usa meia-noite para reduzir o tempo de duração, porque passa 24 horas por dia.

    Martti K.