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.
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?
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.
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.
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,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.
Expecting a reply from you.
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.
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 18.104.22.168 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 22.214.171.124 doesn't have the option to Include Null value
Please Try and let me know!
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.