How to get the SUM of catalog variables in Report

Ahyel Libunao1 · ‎07-07-2022

Hello,

I have a report requirement that need to get the SUM of all variables values using native reporting.

The variable name is Total Cost for Dispatch. This variable is not available (tried dotwalk as well) from the Aggregate field in the report, hence, i cannot get the SUM. I tried to create a database view, still I wasnt able to get the SUM as the variables cannot be selected from the Aggregate field. Is there other to do this please?

Thank you

Ahyel

Mohith Devatte · ‎07-08-2022

Hello,

I saw the combination of data base that you posted .But i don't think it is possible to do it via MTOM table also i had the exact requirement and i ended up creating a custom field on RITM form which will be hidden though.

But what you can do is when ever there is a insert of your RITM for your catalog item wirte a after insert BR and get all the variable values using "current.variables.your_variable_name" and then do sum of it in BR and update it in the custom field on RITM form.

Male sure that you trigger this BR only for your particular catalog item and do reporting on the custom field.

Hope this helps . i will let you if i find any thing that we can do with that data base view combo

Please accept the solution of you find it helpful

View solution in original post

Community Alums · ‎07-07-2022

Hello,

Have you tried List Calculation option? Column Name -> Right Click -> Configure -> List Calculation. You will then get a popup and one of the option is sum.

Thanks
Akshay Kangankar
Please Mark ✅ Correct/helpful, if applicable,

Ahyel Libunao1 · ‎07-07-2022

Hi Akshay,

Yes, it cannot be computed as well due to the value field in sc_item_option is in string. Creating a new integer field in that table is not an option as well as it is in Global scope.

Thank you

Ahyel

Alan42 · ‎03-21-2024

I have this issue. Will I have to recreate the item in a more limited scope to get the total value calculation to work?

suvro · ‎07-07-2022

In which table are you reporting ?

Report on sc_item_option table