Answered
P&L reporting using the Pivot Table
I am sure I am not alone in wanting to present a Profit & Loss report and your Pivot table extension has an undocumented example included in the package.
Can someone demonstrate (video or document) how we can use this extension for this purpose?
Thanks in advance
Alexis
Step 1 – add your P&L line structure as inline data load in the data load script:
PLMetricName:
Load * Inline [
PLMetric, PLSortOrder
Revenue, 1
Cost of Sales, 2
Gross Profit, 3
Etc., etc.,
Direct Operating Margin, 13
];
Step 2 - in the front end create variable vPLMetricMatchList using the PLMetric values, e.g.:
'Revenue','Cost of Sales','Gross Profit',’etc.','Direct Operating Margin'
Step 3 - Create new pivot table with ‘PLMetric’ as dimension.
Step 4 – use pick(match()) to calculate the measures, e.g. Actual/Budget/Prior Year, Variances $, Variances %
=pick(match(Metric, $(vMetricMatchList)),
$(vRevenue),
$(vCOS),
$(vGrossProfit),
… //add the rest of the metrics in the same order as they appear sequentially in vMetricMatchList variable.
)
Note: You can use excel spreadsheet to manage the P&L calculations and translate them to variables in the load script, e.g:
Expressions:
LOAD
VariableName,
VariableExpression
FROM [lib://dataconnection/Variable list_example.xlsx]
(ooxml, embedded labels, table is Consolidated);
Let vNumExpressions = NoOfRows('Expressions');
For vI = 0 to (vNumExpressions - 1)
Let vVariable = Peek('VariableName',vI,'Expressions');
Let [$(vVariable)] = Peek('VariableExpression',vI,'Expressions');
Next