Attempting to add certain calculated columns to the Rows / Columns in a pivot query, the following message is displayed:
"Cannot drop a measure into a row or column."
SuccessFactors Pivot Reports
Reproducing the Issue
- Create a List report / query (Either with Live Data or with Advanced Reporting Data source)
- Create numerical calculated columns - The 'Data Type' of the calculated column must be 'Number'
- Create a pivot query and use the list report created in step 1 as the source for the pivot query
- 'Show Design View' in the pivot designer
- Try adding the calculated column to the ROWS or COLUMNS section
The behavior is expected with Numerical calculated columns.
Numerical calculated columns (The 'Data Type' of the calculated column must be 'Number') cannot be used as Rows or as columns in pivot reports.
They can only be used as measures.
A workaround would be to convert the calculated column's datatype to 'Text'.
This however would limit the aggregations (Pivot Function) to the ones available for text fields only. (Concatenate , Count, etc.,)
If there is a need to use the calculated column as a row / column and use it as a numerical measure at the same time:
- Create two versions of the calculated column with the same underlying logic
- One should be of Data Type 'Number' and the other one should be of Data Type 'Text'
- Use the 'Text' calculated column in ROWS / COLUNMS
- Use the 'Number' calculated column as a Measure and perform the desired numerical aggregation ( Sum, Average, Maximum, etc.,)
Cannot drop a measure into a row or column
Calculated Column in Pivot
Report Canvas , KBA , LOD-SF-ANA-ORD , Online Report Designer , Problem