Project Finish Date in Clarity

[Anon Anon]

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

[Ruth Wunderle]

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.

[Anon Anon]

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

[Andrew M]

just for clarification, how do you think this occured?
(via tasks, allocations etc)

[Deactivated User]

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)

Project and task dates.pdf   562 KB 1 version

[Andrew M]

Thanks Martti for the very informative PDF.

[Deactivated User]

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.

[Alexandre Gavazzi]

@Deactivated User, and what technical action can I take to solve this problem with the dates?

[Deactivated User]

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.

[Nick Darlington]

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).

[Alexandre Gavazzi]

@Nick Darlington ​my problem is with the delivery reports.
I believe the way is through Portlets

[Alexandre Gavazzi]

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

[Nick Darlington]

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.

[Alexandre Gavazzi]

@Nick Darlington, understand. Could you please tell me the step by step for me to perform this setup.



Thanks Alexandre

[David Morton]

"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

[Alexandre Gavazzi]

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

Thanks

Alexandre

[Alexandre Gavazzi]

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

[Alexandre Gavazzi]

@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

@Deactivated User , 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.