Project and Portfolio Management Practitioners Forum
cancel

Joining TM_TIME_SHEET_LINES and WP_TASKS (PPM 7.5)

Highlighted
Nancy F.
Contributor.

Joining TM_TIME_SHEET_LINES and WP_TASKS (PPM 7.5)

I am creating some custom reports in Crystal Reports and need to join together time reporting data and project data. According to the Data Model Guide for PPM 7.5 (page 50), I should be able to join TM_TIME_SHEET_LINES.WORK_ITEM_ID with WP_TASKS.TASK_ID. Unfortunately, WORK_ITEM_ID is a string, while the other field is a numeric. I don't have the option of converting either field before the join. It looks like the Data Model Guide is wrong. Can anyone suggest an alternate approach for making this join?
7 REPLIES
Erik Cole
Acclaimed Contributor.

Re: Joining TM_TIME_SHEET_LINES and WP_TASKS (PPM 7.5)

Hi Nancy,

Is Crystal balking at doing an implicit conversion? Maybe you can create a view of TM_TIME_SHEET_LINES that explicitly converts and join to that view...?
Semenov Alexand
Acclaimed Contributor.

Re: Joining TM_TIME_SHEET_LINES and WP_TASKS (PPM 7.5)

Good Day.
I resolve this issue by:
"to number({TM_TIME_SHEET_LINES.WORK_ITEM_ID})=WP_TASKS.TASK_ID"
This string need add to formula name for example 'Links' and apply this in select expert "is true"
Good Luck
Nancy F.
Contributor.

Re: Joining TM_TIME_SHEET_LINES and WP_TASKS (PPM 7.5)

The issue is that the join needs to be done BEFORE any formulas or selection criteria can be applied. Crystal Reports will not permit the two tables to be joined because the field type is different. By definition, shouldn't the Data Model Guide only show table joins where the field types are identical?
Semenov Alexand
Acclaimed Contributor.

Re: Joining TM_TIME_SHEET_LINES and WP_TASKS (PPM 7.5)

Hi.
You can't join this tables with varios field formats without formula from my replay.
As next recommend: try create command with 2 tables joined with help SQL query.
This realy working and no any workarounds to solve this issue.
Try ask from Crystal forums or helpdesk.
If needed i can send you test query or join command.
Good Luck
Jyotir ++
Super Contributor.

Re: Joining TM_TIME_SHEET_LINES and WP_TASKS (PPM 7.5)

Hi ,

You better create a View first as per fomatting of required data and then try to use that in Report.

I hope it will help you.

Regards,
Jyotir
Nancy F.
Contributor.

Re: Joining TM_TIME_SHEET_LINES and WP_TASKS (PPM 7.5)

Yep. It looks like we'll have to create a custom view (although I still find it odd that we would have to create a custom view to join two tables that, according to HP's own documentation, should be directly join-able). Thanks for the help.
Erik Cole
Acclaimed Contributor.

Re: Joining TM_TIME_SHEET_LINES and WP_TASKS (PPM 7.5)

There are other instances where tables are "joinable" on fields that are not the same type because, for example, request Ids or user Ids can be saved in a varchar field (parameter13 or whatever) on a request and you need to join to a numeric column in another table. Often you can get "not a number" errors in these joins, depending on how the query analyzer rewrites your query, or they can execute fine. But I think the intent of the data model is more to show the relationship.