6 Replies Latest reply on Aug 14, 2015 1:34 PM by Thomas Dodds

    Grand Total on MTD Columns

    2694355

      Hi All,

       

      we have a requirement where we have to do a Grand Total on MTD Columns. I created a adhoc report with lets say Sales measure and Sales MTD

      with Year,Month and Day. I filter on a specific Year and Month on a tabular report and apply Grand Total.

      On applying the grand total gives sum of all the rows for the Sales and Sales MTD at the bottom which for Sales MTD I just want the Last row in the tabular report. To achieve that I go to Edit Formula on the column and select Aggregation Max as the default. this works. But the requirement is that

      OBIEE should automatically do this instead of manually doing the changes.

      Please let me know there is some configuration we can change somewhere .

       

      Thanks ,

      Krishna

        • 1. Re: Grand Total on MTD Columns
          Felipe_Idalgo

          Hi,

           

          Basicly total reflets your condition. If you have column calculated direct on database total created on analisys just sum that.

           

          When you set aggregation like MAX, OBIEE only returns MAX line, in other words, your last line.

           

          You can create a MTD column using Times Series Functions (See ToDate Function) and your total will be expected

           

          OBIEE: Time Series Functions OBIEE

           

          Mark right if help you.

           

          Felipe Idalgo

          • 2. Re: Grand Total on MTD Columns
            Thomas Dodds

            Use the LAST() function to return the correct MTD for your total.

             

            NOTE:  To limit the FIRST/LAST function, you must define dimension-specific aggregation rules in the repository.

            • 3. Re: Grand Total on MTD Columns
              2694355

              Hi,

              Thanks for the response. I have created the MTD column using the time series function TODate and the measure is lets say which

              has the aggregation value as SUM defined in the RPD.

              But the problem when I apply Grand total in the presentation layer, it sums up all the rows and give the Grand Total. Instead I want the last Row displayed as Grand Total. If we apply aggregation Last in the RPD, first of all the Rolling sum functionality will be lost in the MTD and we will not be able to use ToDate function on the measure.

               

              let me know if we have any other approach.

               

              Thanks,

              Krishna

              • 4. Re: Grand Total on MTD Columns
                Thomas Dodds

                Is your table column marked to use report aggregation?  Use Last ...

                 

                2015-08-13_15-01-45.jpg

                 

                First MTD column uses Aggregation Rule (Last) ... second is using default

                2015-08-13_15-04-49.jpg

                 

                Ignore:  To limit the FIRST/LAST function, you must define dimension-specific aggregation rules in the repository.

                Apologies for that ...

                1 person found this helpful
                • 5. Re: Grand Total on MTD Columns
                  2694355

                  Hi Thomas,

                   

                  Thanks for the response I have tried that and working as expected. But this is a manual setting which the power users have to do everytime if they want the Grand total on MTD Columns. Is there a way of customization anything which OBIEE will automatically do it.

                   

                  Thanks,

                  Krishna

                  • 6. Re: Grand Total on MTD Columns
                    Thomas Dodds

                    I don't know of any automatic way... other than modifying your DW design to match the requirement (the use of information drives DW design, not the other way around)