HOW TO CREATE TOTAL COLUMN FOR THE SAME ITEM

Regie Mercado

Hello and good day!

Dearest Oracle Community friends,

I am trying to create an OTBI for materials requirement below, would anyone know how to create the formula for total column with the same item name. Hope to hear from someone with knowledge on this.

Many thanks and best regards.

Regie

Nathan CCC

Hi Regie, The system is working as expected. That is not an error. That is the expected result when surpress = True. Facts already surpress by default. Your calculated total is a yellow fact measure column. So by default as expected it is already suppress to the grain of your query based on the dimensions you have include in this view.

If you want to display the total only once then you should have instead used a "subtotal" to add subtotal rows rather than adding a calculated column. Normally a designer does what you are doing here because they want to calculate a ratio. For example, what is this row amount as a percentage to some wider group of rows. In my humble opinion i do not think this is a good design to meet this requirement for showing a subtotal. Most users would expect a subtotal row rather than a column.

Notice that if you do add a subtotal but without removing your calculated column the subtotal does not "double count" when a user views online. But if you download to excel it will "double count" because in your design you have chosen to mix the "grains" to have columns at different levels in the hierarchy which is not a good practice.

Regie Mercado

Hello and good day!

Dearest Nathan,

This is what it looks if I use the subtotal, it will work. But is there any solution for my original design?

Appreciate your reply.

Many thanks and best regards.

Regie

Rajasekhar Bandaru-Oracle

Hi

Please try to use Sum(Required Quantity) group by Item name

https://docs.oracle.com/en/cloud/saas/otbi/otbi-user/edit-formulas-or-calculated-measures.html#GUID-56B8E03F-E6A6-4421-AD81-DDD72B5D68F4

Regie Mercado

Hello and good day!

Dearest Rajasekhar,

I've used this formula but tthis is the result I've got.

Hope to hear from you for a possible solution.

Thanks much and regards.

Regie

Regie Mercado

Hello and good day!

Dearest Rajasekhar,

I've tried and used this formula but this is the result I've got.

Hope to hear from you for a possible solution.

Thanks much and regards.

Regie

Nathan CCC

Hi Regie,

I am assuming the 2 columns in your sum are the 2 columns we can see in your table column header "Item Name" is "Inventory Material"."Material Name" (you renamed the column header?) and column header "Required Quantity" is ("Material Usage"."Required Quantity"?

Assume you therefore expected

work order 652 quantity 1.68 + work order 654 quantity 0.84 = total 2.52

but got total = 2,153.20?

Assume you have lots of other work orders transactions or the same work orders on different dates etc lower down with qty that share this material names?

For material name Chesa Powder the sum of

work order 652 quantity 1.68 + work order 654 quantity 0.84 + all the other Chesa Power work orders = 2153.20?

If you remove all cols except "Inventory Material"."Material Name" and "Required Quantity" you get 2153 for item name material name 10141010000008 right? So the total is correct?

select all "Inventory Material"."Material Name", "Material Usage"."Required Quantity" from …

What happens if you add year month date and report date and subinventory and work order and item name and description (whatever makes your row in this view unique) into the "by" in your sum function? Does that fix it?

sum("Material Usage"."Required Quantity" 

by 

the year month col

, the report date col

, the subinventory col

, the date col
, the work order col

, the item name col

, the item desc col
, the UOM col)

Regie Mercado

Hello and good day!

Dearest Nathan,

I am close to my requirement because it now provides the correct total, thanks to your solution except for the suppressed total column. I do not know where is the possible error why it is not suppressed (same for item name and UOM). I've used the formula below.

For the Item description no problem for suppress.

Hope you can still help me out on this.

Many thanks and best regards.

Regie

Regie Mercado

Hello and good day!

Dearest Nathan,

I've tried to re-arrange the columns and I was successful in suppressing the columns except for the "Total" column. Hope you have possible solution for this.

Many thanks and best regards.

Regie