I have a cross tab report, where i need to show all the month related transaction data, here the user can select any month period and within that period if any of the month doesn’t have the data then also the cross - tab should also display that month data and indicate 0 as a value. In this i have 2 union queries Month and Week.
I believe your month/week is coming from a dimension table. Just do an outer join onto that dimension table and will able to see all months/weeks. When there is no data for that particular month, null will be displayed, so use nvl function to replace nulls with 0