SAC : How to apply different formulas on different rows in SAC story (Canvas/Table Object)

[raj_vangala]

Hi

I'm building a P&L report in SAC Canvas/Table object. I have Sales, Cost of Sales ,Gross Margin, Labour Costs, and MCP as rows and Act, Bud, and Variance as columns. When calculating variance, for Sales row it should be (Act - Bud) but for Cost Of Sales & Labour Costs rows it should be (Bud-Act).

See below illustration:

The story has created on top of a planning model. In BEx query we used to have cell definitions to override the result of a back end formula with a new formula during the display at each cell level. But I'm not sure how to do this in SAC story

Has anyone got experienced this scenario?

It would be really a great help if you could provide a possible solution.

Best Regards

Raj

[steffen_heine]

Hi Raj,

you could for the variance add a column in the table:

In that column you can create the calculations similar to Excel for each cell:

Of course the table needs to be very static with that approach and shouldn't change too often.

Otherwise you can use the SAC Excel add-in.

Regards,
Steffen

[raj_vangala]

Hi Steffen

Thanks for your quick response.

Actually I've tried your approach but I couldn't add formulas in it. The story has built on the Canvas but not Grid, are you suggesting to use Grid layout?

I can add a column even in canvas layout but I can't add the calculation the way you mentioned - am I missing anything.

SAC Excel add-in:

I've also tried to use Excel add-in but I can't create restricted measure(or key figures) in Excel. I need to restrict a set of accounts(or nodes) in each row but I don't think this feature is available in Excel add-in.

Please confirm.

Best Regards

Raj

[steffen_heine]

Hi Raj,

for me this works on Canvas pages. Important is that you add the custom cell column via that + button.

For Excel the formulas need to be created on the model level.

Regards,

Steffen

[raj_vangala]

Thanks, Steffen.

Now I can create a new column as you mentioned and add formulas. It is a good feature but with limited flexibility.

I can only add this column at the end of the table and can't export the values to Excel along with other columns in the story. So unfortunately it is not serving the purpose.

And shifting to Excel add-in is a huge development as we need to create all the restricted and calculated measure at the model with so many reports to be recreated in Excel.

Once again Thank you very much for your time and help . Much appreciated.

Best Regards

Raj

[Jay_Gandhi]

Hi there,

If you're using a New Model then you could instead use a standard variance calculation, however, taking the Account Type into consideration. E.g. if Expenses go up then it's a negative variance, however, if revenues go up then it's a positive variance.

Perhaps this blog post can provide some benefit: https://blogs.sap.com/2021/04/26/how-to-create-a-better-worse-variance-report-with-the-new-model-in-...

Jay

[sandip_kumar003]

Hi,

You can use Excel formulas in tables

Best Regards,

Sandip Kumar.

[raj_vangala]

Hi Jay

Thanks for a wonderful solution, it almost solved the problem but the Sub Totals are going for a toss, please see below:

Gross Margin supposed to be 50 but its now 250 and MCP supposed to be 30 but now 270. Could you please suggest how to get the correct subtotals.

Best Regards

Raj

[raj_vangala]

Solved the subtotal issue by using the hierarchy nodes - with the latest enablement of account dimension can have multiple hierarchies and flipping the signs(+/-) using account type able to resolve the issue.

Thanks Jay and Steffen for your help.

Regards

Raj