Project Reporting Flow Intermediate Processing

After importing the input fields and augmenting them with the data from their lookup objects, the PS Cloud Analytics dataflow filters out non-relevant or repeated records, or performs other required actions.

You can use the dataflow editor in Analytics Studio to visualize the PS Cloud Analytics dataflow and its nodes. Alternatively, you can download the dataflow definition file which is a JSON file representing the dataflow and its logic. For more information on the dataflow editor and the dataflow definition file, see Configure the Dataflow in the Salesforce Help.

Warning:

Modifying the PS Cloud Analytics dataflow might result in the loss of functionality. Contact your administrator before using the data manager tool in Analytics Studio.

Currency Conversion

When currency fields are added to your dataflow from your PSA data environment, these fields are then converted to the corporate currency derived from the Integration User profile. A non-dated conversion rate set by Salesforce is also applied to these fields. These conversion rates can be viewed by navigating to Setup | Company Profile | Manage Currencies in your org.

PSA has its own multi-currency framework that uses dated exchange rates. The exchange rate used for a transaction is determined by the date of that transaction and uses the exchange rate associated with that date. Although Parenthetical Currency Conversion is not recommended for use in PSA, if enabled, this displays the non-dated currency conversion applied by Salesforce in parentheses. For more information see Setting up Currencies.

In order to report accurately on currency fields, PS Cloud Analytics removes the currency conversion applied by Salesforce in the dataflow, and retrieves the dated conversion when available from the transaction associated with the record, such as an expense.

Example Scenario

A combination of currencies might be found in an org, in which:

  • The corporate currency is USD.
  • The resource currency is USD.
  • The project currency is AUD.
  • The incurred expense currency is EUR.

In PS Cloud Analytics this would be processed using the following flow:

The Project Reporting dataset digests only currency fields that are stored either in the corporate currency set for your org, or the project currency set in your PSA data environment.

The field Expense Amount, that contains the expense in EUR incurred currency, is not digested in the dataflow because it does not match the org currency or the project currency set in your PSA data environment. Because of this, the Reimbursement Amount in Project Currency field is digested in the dataflow.

If an exchange rate is not specified on the Project Currency Exchange Rate field on a record or its associated transaction, the Analytics exchange rate is applied by default. The exchange rate on the associated transaction is used if it is associated with a billing event item. If no exchange rate is available on this field, a null value is displayed. This null exchange rate is coalesced with the standard Analytics exchange rate.

As a result of this processing, all relevant fields in this dataset are shown in your corporate currency, but use the conversion rate of the date associated with the transaction where possible. If this is not available, the Project Currency Exchange Rate associated with the record is used. If this exchange rate is not available, the Analytics currency conversion is used. This uses the rate for the date on which your dataflow was last run. This ensures that currencies remain consistent across the dataset for reporting purposes.

Deriving the Project Exchange Rate

The Project Reporting dataflow populates the Project Exchange Rate field. The process for deriving this field is shown below.

Field

Primary Currency Source

Secondary Currency Source

Type = Timecard Timecard → Project Currency Exchange Rate Non-dated Salesforce currency translation, identical to the Integration User's currency
Type = Expense Transaction → Project Currency Exchange Rate
Type = Budget Transaction → Project Currency Exchange Rate
Type = Miscellaneous Adjustment Transaction → Project Currency Exchange Rate
Type = Project N/A
Estimate Versus Actuals Value Project Currency Exchange Rate
Assignment Value N/A
Type = Milestone If there is a value available for the Actual Date on the Milestone Object, and the Include in Financials is selected, then the Project Currency Exchange rate for the transaction associated with the Milestone is used. If not, the Project Currency Exchange rate from the Milestone record is used.

Dataflow Nodes

These are the main dataflow nodes in the Project Reporting flow.

Dataflow Node

Description

BILLING_TRANS

Coalesces Project and Estimate for RPG fields derived from the Project object, and coalesces Project, Opportunity, and Estimate for Account fields.

ResourceSkillRequest_Desirable_FILTER Filters in only any Resource Request Skills records in the Project Reporting dataset where the Desirable field is set to false.

Calculated Fields

These are the fields calculated by the Project Reporting flow.

Field Label

Field API Name

Description

End Date EndDate If there is a value available for the Actual Date on the Milestone Object, and Include in Financials is selected, the Project Currency Exchange rate for the transaction associated with the Milestone is used. If not, the Project Currency Exchange rate from the Milestone record is used.
EvA Scheduled Amount ValueEVABillings When Scheduled Bill Rate is Daily Rate is selected, then the value of the Scheduled Bill Rate is multiplied by the number of Estimated Days. If not, the Scheduled Bill Rate is multiplied by the number of Estimated Hours.
Project Currency Exchange Rate pse__Project_Currency_Exchange_Rate__c See Currency Conversion. This field is not exposed in the Project Reporting dataset.
Milestone Days Late MilestoneDaysLate If Is Milestone Late is populated, the Target Date on the milestone is subtracted from the Actual Date on the milestone.
Is Milestone Late IsMilestoneLate The milestone is calculated as late if the value of the Actual Date minus the Target Date on the milestone is greater than zero.
Assignment Planned Revenue (Hours) ValueAssignmentPlannedRevenue The value of this field is calculated by multiplying Planned Hours by Bill Rate on the Assignment object.
Resource Request Opp Probability (%) ResourceRequestOpportunityProbability This field uses a lookup from the Resource Request object to the ID field on the Opportunity object. The ResourceRequest_Opportunity_JOIN node is then used to retrieve the Probability field on the Opportunity object.
Resource Request Weighted Requested Hours ValueWeightedResourceRequestSOWHours

This field is calculated using the Requested Hours (pse__SOW_Hours__c) field on the Resource Request object, and the probability of the opportunity associated with the resource request, using the following formula:

pse__SOW_Hours__c * (Probability/100)

Project Cost Amortized ValueProjectCostAmortized

Populated from the field specified in the Amortized to Date Value field on the settings record for the Project object.

The field on the Project object can be a currency or a number field. In either case, the value is converted to the currency of the Integration User. This ensures that the value is in the same currency as all other fields in the Project Reporting dataset.

Note:

This field is only available if all of the following are true:

  • You use Revenue Management.
  • You selected the Revenue Management Fields checkbox on the PS Cloud Core Analytics setup record.
  • The Amortized to Date Value field is populated on the settings record for the Project object.
Project Total Cost Amortized ValueProjectTotalCostAmortized

Populated from the field specified in the Total Amortized to Date Value field on the settings record for the Project object.

The field on the Project object can be a currency or a number field. In either case, the value is converted to the currency of the Integration User. This ensures that the value is in the same currency as all other fields in the Project Reporting dataset.

Notes:

This field is only available if all of the following are true:

  • You use Revenue Management.
  • You selected the Revenue Management Fields checkbox on the PS Cloud Core Analytics setup record.
  • The Total Amortized to Date Value field is populated on the settings record for the Project object.
Project Revenue Recognized ValueProjectRevenueRecognized

Populated from the field specified in the Recognized To Date Value field on the settings record for the Project object.

The field on the Project object can be a currency or a number field. In either case, the value is converted to the currency of the Integration User. This ensures that the value is in the same currency as all other fields in the Project Reporting dataset.

Note:

This field is only available if all of the following are true:

  • You use Revenue Management.
  • You selected the Revenue Management Fields checkbox on the PS Cloud Core Analytics setup record.
  • The Recognized To Date Value field is populated on the settings record for the Project object.
Project Total Revenue Recognized ValueProjectTotalRevenueRecognized

Populated from the field specified in the Total Recognized to Date Value field on the settings record for the Project object.

The field on the Project object can be a currency or a number field. In either case, the value is converted to the currency of the Integration User. This ensures that the value is in the same currency as all other fields in the Project Reporting dataset.

Note:

This field is only available if all of the following are true:

  • You use Revenue Management.
  • You selected the Revenue Management Fields checkbox on the PS Cloud Core Analytics setup record.
  • The Total Recognized to Date Value field is populated on the settings record for the Project object.