8 Replies Latest reply on Aug 5, 2014 3:59 PM by Chandra kanth

    How to display zero value data of month column

    Chandra kanth



      There is no sales data in few months of a particular year of TIME table.


      Eg: Aug 2009 and Nov 2009 does not have the sales data.


      In the report when i display monthly data of 2009 year,

      OBIEE by default excludes those two months which is not having

      data and displays data remaining 10 months which is having.


      My requirement is I want to display those two months also with "0" value.

      How to do this. Please advice. I am using OBIEE 10g under windowsXP.




        • 1. Re: How to display zero value data of month column
          Maciej Kozakiewicz

          I've been using 11g and don't know if it works with 10g but you may give it a try:

          1. Go to Edit Analysis Properties -> Data -> Include Null Values

          2. In measure Column Properties select Data Format -> Override Default Data Format -> Treat Numbers As "Custom"

               In 'Custom Numeric Format' insert: #,#.0;-#,#.0;0


          Hope it'll solve your problem.


          Important note: after exporting to excel zeros will be replaced with nulls.



          • 2. Re: How to display zero value data of month column
            Chandra kanth



            It is not working. Basically the MONTH name column is not displaying Aug,Nov months. Because there is no sales data in those two months. How it will display "0"? Initially how to display those two Month names in month column?




            • 3. Re: How to display zero value data of month column
              Venkat Krishna

              Souppose Your Table A is Time dimension and Table B is Fact


              You need to change the join condition to left outer join in the BMM layer. That way you can show all the months. Hope that helps.





              • 4. Re: How to display zero value data of month column
                Chandra kanth

                Hi GURUS,


                Please answer anyone. Request you all please help me.


                I am unable to get full month list. Please look into the following design of BMM layer described with short description.


                There is a ORDERS table with order_date  (MM/DD/YYYY) column physical layer and also other order related columns. There is no "TIME" table in Physical layer.

                I have Created TIME table in BMM layer with columns Calender Year,Calender Quarter, Calender Month, Calender Day (Detail level).


                For the logical table TIME, the logical table source is ORDERS from physical layer which consists of Order date column.


                For each column i have chosen Existing Logical Column as source that is order_date column from TIME table in BMM layer.


                For calender year i have written formula: EXTRACT( YEAR  FROM OE.TIME.ORDER_DATE), similar way for other columns also.


                I have created dimension with this and made it TIME DIMENSION.


                Created logical levels (year, quarter,month,day) and assigned respective columns as said above.


                I have two scenarios: 1. Monthly results and 2. Yearly results


                1. Monthly:  Month names (Aug, Nov) are not displaying in Month column. Will AGO function effect due to not having data in a particular month?

                    There is a discrepancy in the month AGO results. I would like to know how to display those two month names. Please help in this regard.



                2. Yearly:


                When i create Time series function AGO ( AGO(OE.ORDER_ITEMS.SALES, OE.TIMEDim."YEAR", 1) it's giving improper results. Checked chronological key at each level.


                CAL YEAR    SALES            YEAR AGO SALES

                2,009        4,208,033    

                2,010        3,546,232                        4,119,025

                2,011        3,436,817                        3,546,232

                2,012        3,719,964                        3,401,943


                See the above results.


                conclusion: Please let me know is there any approach problem as described above. As there is no "Time" table in Physical, i have created in BMM layer only.

                If approach is right then why is the discrepancy in the result.




                • 5. Re: How to display zero value data of month column
                  Chandra kanth

                  Hi all,

                  Expecting a reply from you.



                  • 6. Re: How to display zero value data of month column
                    asim cholas

                    Hello Chandra,


                    The only way we can achieve this in OBIEE side via Data densification. If you have only one report or less than five reports you can do this in report level by modifying logical SQL , Or if you have N number of reports to densify you can use a CROSS Join in RPD with date dimension. Check the below urls. In report level you can add a date table as a cross join in the logical SQL. If you need any further info kindly let me know.


                    BI Direct: Data Densification in an OBIEE 10/11g






                    • 7. Re: How to display zero value data of month column

                      Hello Chandra,


                      Please try the following Options Year Ago showing Wrong values:

                      1) Configured the Levels in the Hierarchy with proper number in each Level.

                      Example: Double click on Year Logical Level of Time Dim, Make sure u have given appropriate number in "Number of elements at this Level" say 10 or 20. Sames goes for Month Level also.

                      2) As For the Months with Zero Sale, In version there is a option to "Include rows / columns with Null Values". This means the two months will also show but with Blank Value, which you can change using Nvl function. Also note that doesn't have the option to Include Null value


                      Please Try and let me know!



                      • 8. Re: How to display zero value data of month column
                        Chandra kanth

                        HI all,


                        Thanks for reply and sorry for the delay, because i was trying another approach.

                        With approach i was mentioned in my previous thread, which has limitations.

                        That approach is limited to display sales result w.r.t year, quarter, month, day.

                        But it can be used for TIME SERIES functions because the chronological column

                        must have its values stored in a table column and must not be the result of a function.


                        I have created sql generated table for TIME in the database and imported to physical layer of RPD.

                        Rest of work is remains same. Getting proper TIME Series results. These are the new learning's.


                        Thanks to all of you for the support and your valuable suggestions.