Adding an extra row to a variable

[simonpeart]

Thank you for all the help so far, i do genuinely feel that i've progressed a lot with reading and posting in here.

I've attached a sample excel sheet, i've zeroed all of the data for confidentiality, to show what i've got now and better explain what i need to do.

Going from the Excel sheet, as you look at the report in WEBI, the first 2 columns are hidden and are used purely to group and sort.

Categorise is a variable that groups the 'Packaged Item Number - Position Categories' together.

'Packaged Item Number - Position Category' is an Object from the BEX.

Position Category is a variable - code is detailed below, but essentially what this does is it equals 'Packaged Item Number - Position Category' and then adds " Feed Base" onto the end of it where another BEX object named Grain Price Type contains "Feed Base".

=If ((Left([Grain Price Type Number - Grain Price Type Des (Key)];9)="Feed Base") And ([Packaged Item Number - Position Category (Key)] InList("Group 2 BMW";"Group 1 BMW";"Weetabix";"GP 4";"Weetabix";"Warburtons";"GP3 Soft Milling";"GP3 Soft Wheat"))) Then ([Packaged Item Number - Position Category (Key)]+" Feed Base")

ElseIf ((Left([Grain Price Type Number - Grain Price Type Des (Key)];7)="Tracker") And ([Packaged Item Number - Position Category (Key)] InList("Feed Wheat"))) Then "Wheat Tracker"

Else [Packaged Item Number - Position Category (Key)]

Stock all the way to Total are variables that SUM the Quantity based on a BEX object that brings out the date.

Now, what i need to do is to create 2 extra items in Position Category, the first named "Transfer Feed Base" which sums all of the Mill Wheat Feed Base items together for each column.  The second new item would be called "Milling Wheat Feed Base" and would be the negative number of "Transfer Feed Base"  So if the Transfer Feed base = 20, then Milling Wheat Feed Base = -20, same with if Transfer Feed Base = -20, then Milling Wheat Feed Base will equal 20.

Is this possible at all?  The whole idea is to get rid of an Excel sheet that takes the data from our database and does a huge amount of calculations into a WEBI report that does and looks the same.

edit: had to replace the Excel sheet for a jpeg

[Former Member]

Please provide your input data & expected output data with calculation that you are performing in excel sheet.

So that it can be easily understood and resolved.

Thanks,

Swapnil