Summing by Column

Question

Suppose we have the following columns:

       X           Y         Z
    Category     Date      Amount
       A         January   10
       A         February  20
       A         March     30
       B         January   34 
       B         February  45
       B         March     65
       C         January   87
       C         February  98
       C         March     100
       D         January   80
       D         February  90

I want to sum the Amount column by Category and Date . So for Category A, we would have the sum of the amount be 10+20+30 = 60 for the dates between January and March. In Oracle BI, how would we do this? Note that Some categories might have missing dates. So I want to sum the Amounts for the only the the available dates between January and March. Category D, for example, has March missing. So the total amount would be 80+90 = 170.

When I do the following, I just get the sum of all the amounts:

   sum("Z"."Amount")

Answer 1

If the required result has to be achieved through OBIEE Answer, then it can be done in following way. Create a table with columns - Category, Date, Amount. Go to Results tab. Edit view of the table.

Click on Total By icon above Category column. Both After and Report-Based Total (when applicable) should be ticked.

The result will be coming as shown.

Category   Date      Amount
    A     January     10
          February    20
          March       30
A Total               60
    B     January     34
          February    45
          March       65
B Total               144
    C     January     87
          February    98
          March       100
C Total               285
    D     January     80
          February    90
D Total               170
          

Answer 2

You can do this quite simply by editing the column formula from within the Criteria. When you look at it to begin, your Amount column formula probably looks something like "Z"."Amount". You can edit this slightly to change the aggregation level:

sum("Z"."Amount" by "X"."Category")

That should give you something like:

Category    Date    Amount
A           Jan     60
A           Feb     60
A           Mar     60
B           Jan     144
B           Feb     144
B           Mar     144