Skip to main content

I am trying to add a parameter that would let me choose a financial period range to a generic inquiry I created that pulls cost and revenue data from a projects. It’s basically a modifed WIP report but in a format I can download into excel.

 

These are the tables that I currently have on the report:

I can’t figure out how to link the table with the FinPeriodID to the project so I can set a parameter to run the report for a specific Fin period range.

The Project does not have a Period on it so you cannot get the field from there. Looking at the tables i dont believe you can use any of them as they dont have a period field. You will need to join some table that has then you can use it

 


Hello @eucciferri35 ,

Are you reporting Cost and Revenue data for your projects from Actual transactions, or from a Project Budget Forecast?

Both Project Transactions and Budget Forecasts offer Financial Period field. The table for Project Transactions is PMTran and the tables for Budget Forecasts are PMForecast & PMForecastRecord.

  1. From the Tables tab, with cursor on PMProject table,
  2. Click Add Related Table (button).
  3. Type PMTran in lower left,
  4. Click Select Related Table
  5. Click ADD.

 

Once the table(s) you need are added, then you can use conditions and parameters to select a period or range of periods.

Laura


Apparently, I am still doing something wrong.  I added the PMTran table to my list. Before I even try to add the finperiodID as a parameter. I am getting the error message below.  I tried both an inner and left join.   I also have a grouping set to - PMProject.ContractCD

 

 

 


Hello,

I think, PMProject Table listed on the left under Parent Table, and Child Table on the right = PMTran.

The Join type will be LEFT  if you want to include all Projects in the results whether or not they have transactions.

The Join type will be INNER if you want to include only Projects with Transactions in your results.

 


Thanks @Laura02    That is exactly what I did was reverse the parent and child.  I finally got the report to come up with the PMTran table added. It was pulling all the correct data before I added the parameter.   Now I am struggling with the parameters. I decided to use end date versus financial period so it would be more flexible. I added the field to the Parameter table and then added the conditions tab - PMTran.Date  Is Less than or Equal to   = Enddate]   no matter what date I put in it’s coming back as no records. 

 

 


Hi,

I’m not sure why the date parameter is not working.  I did a quick test; with small differences from your GI… but it’s working. Here are some ideas.

First I happened to use a different Schema field representing Date format on the Parameters tab:

Second I used a two-line condition, allowing transactions to display even when the Transaction Date parameter is left blank by the user:

Let us know if these tiny changes make any difference. 🤔


@Laura02 

 

I added the PMregister to the GI and was able to get it to pull the correct info.  However once I add the Date parameter, I am back to pulling no records. It just makes no sense.  I don’t think its conditions because it makes senses to limit the records being pulled by limited the projects to active first then looking for transactions within the date range. There has to be something I am missing.  Maybe it has to do with me using the PMReportRowsMultiplier table.  without that it duplicates info coming in from the two tables and the amounts on the report are off.

 

 

 


Hi,

We can’t see Operator field on the Conditions tab.  For the Date lines, Operator should be OR.

Aside from that, make changes one at a time, then check your results, to figure out the culprit. For example:

  1. Deactivate the Date conditions, Save, and view the results. Do you see results?
  2. If you suspect PMReportRowsMultiplier table, deactivate all relations, results fields, conditions, etc. related to PMReportRowsMultiplier table. Then check: does the GI display results?

 

Laura


Thanks Laura!  I’ll do some more testing to see if I can figure out what the culprit is.


Reply