Finance | Project Operations, Human Resources, ...

Get Sum of Amount in Sub Total in Electronic Report Format

(0) Share

Report

Posted on by Arsalan Muhammad Am...

Hi Friends

I want to show the Sum of Line Item Amount in Summary as Sub Total. I am using the Electronic Report Format using Excel File Template.

Please guide me :

I want to show the SUM of Cell<InvoiceLines_Amount_Value> = @.LineBase.LineAmountInclTax in Cell<TotalsShort_SalesSubtotalAmount_Value> = model.InvoiceBase.AmountMST+model.InvoiceBase.SumTaxMST

Thanks

I have the same question (0)

All responses (5)

Answers (0)

Sort by

Vamsi Pranith 684 on at

Like (0)

Report
Copy link

Link copied!

Are you using configurable business document to print this excel report? If so, you need to go to configurable business document workspace and select the cell and map it with the formula.

If not, you will need to create a new cell in the format and bind/map with this formula that you have here.

Was this reply helpful? Yes No
Ludwig Reinhard Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hello Arsalan,

I would recommend that you have a look at the 'project invoice (excel)' configuration.

There you can find a number of examples for the different invoice layouts how subtotals and totals are calculated.

What they use there is that they have additional calculated fields in the mapping section that can be found on the right-hand side of your screenprint that is not shared. Those calculations are then bound to the different ranges/cells in your Excel document that you shared in your screenprint above.

Best regards,

Ludwig

Was this reply helpful? Yes No
Arsalan Muhammad Am... 60 on at

Like (0)

Report
Copy link

Link copied!

Hi @Vamsi

First of all, I am thankful for your kind reply.

Actually, I want to show the SUM of Cell<InvoiceLines_Amount_Value> in Cell<TotalsShort_SalesSubtotalAmount_Value>

This is not working model.InvoiceBase.AmountMST+model.InvoiceBase.SumTaxMST

So This is the Cell already in Format Cell<TotalsShort_SalesSubtotalAmount_Value>

But getting the Sum of Cell<InvoiceLines_Amount_Value> and show in Cell<TotalsShort_SalesSubtotalAmount_Value> is Tricky.

Thanks

Was this reply helpful? Yes No
Arsalan Muhammad Am... 60 on at

Like (0)

Report
Copy link

Link copied!

Hi Dr.Ludwig

Would you let me know how to use the SUMIF Function in this scenario ?

Let me know SUMIF Function is supported at FORMAT MAPPING level ?

Thanks

Was this reply helpful? Yes No
Suggested answer

Ludwig Reinhard Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi Arsalan,

The Sumif does not work in the model mapping.

It works only in the Excel configuration.

Please see the following site for more details:

docs.microsoft.com/.../er-functions-datacollection-sumif

Please try using it there.

Best regards,

Ludwig

Was this reply helpful? Yes No