How to join PMTask table with PMRevenue Budget table so it does not duplicate lines in report?
Hello,
I am trying to pull the “CuryRevisedAmount” field into the PM632000 report. I added the PMRevenueBudget table in Schema Builder and now it duplicates lines. I cannot confirm this, but it appears that when the billing rule is TE, it duplicates the task and adds the “CuryRevisedAmount” data from the Cost Budget on one line, and from the Revenue Budget on the duplicate line. I am only after the revenue side. Any ideas? Thanks!
Page 1 / 1
@tfacemyer You should be able to specify in the relations of the report to only get the Revenue side of the data. I don’t have the tables up at the moment but you would just add a line to PMRevenueBudget join that Type = ‘I’ (where I is the code for Revenue, but I can’t remember if it’s I, or R or something else)
Thanks for the quick response, Benjamin. I tried using “Type” before but then the Amount field is blank. Maybe I am not in the correct area. See screenshots below.
Relationships:
Report Preview:
@tfacemyer I’m not sure that is the correct link from PMTask to PMRevenueBudget. I see PMTask side having ProjectID and TaskID and the RevenueBudget side having ProjectID, ProjectTaskID:
The relation would look something like this:
Though you may need a different Join type
Thanks again. I tried setting up the relationships as you have shown. I have tried every single join type and always get the same error on the report that says “Response is not well-formed XML.”
@tfacemyer Might be easier if you upload the report to this post so I can check it (File > Save As… .rpx file). Otherwise I’m not sure T is a option for Type, I think it’s either Income, I, or Expense, E. It’s possible it could be blank, but it should be one or the other. You also might need to try saving the report to the site and running it from there to see if it can work. Sometimes I’ve seen Report Designer throw the not well formed XML error and then have difficulty even when things are working as expected.