How to get the SUM of catalog variables in Report

[Ahyel Libunao1]

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

[Ahyel Libunao1]

Hello Survo, 

I created a database view for the listed tables below. However, I still cant aggregate the value field as it is string type field. Creation of new integer is not an option as well as it is in global scope.

sc_item_option_mtom

sc_item_option

sc_req_item

Thank you

Ahyel

[Mohith Devatte · Accepted solution]

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