Drill Problem with Month Prompt

building reports webintelligence
1

My Required Report objects are:

Year, Month, Active Merchants Objects and Month Prompt

Report o/p is :

                             2008                    2007
                             Sep 	            Sep 	Variance

ActiveMerchants - 100 25 75
Requirement :

If i drill up on September then 2008Q3 data should come. But the problem is the Data is not getting changed based on the Month, Quarter,Half Year and Year. (Because of month prompt)

Below is the SQL with Month Prompt :-----------------------------------------
SELECT
V_DIM_CAL_MONTH.YEAR,
V_DIM_CAL_MONTH.MONTH_NAME,
Sum(distinct(DW_MTH_SUM_MERCHANT_STATS.ACTIVE_MERCHANTs)),
V_DIM_CAL_MONTH.HALFYEAR,
V_DIM_CAL_MONTH.QUARTER
FROM
V_DIM_CAL_MONTH,
DW_MTH_SUM_MERCHANT_STATS
WHERE
( V_DIM_CAL_MONTH.MONTH_IN_YYYYMM=DW_MTH_SUM_MERCHANT_STATS.MONTH_ID )
AND
(
( V_DIM_CAL_MONTH.MONTH_NAME =‘SEPTEMBER’ )
AND
( V_DIM_CAL_MONTH.YEAR between (to_char(sysdate,‘YYYY’)-1) and (to_char(sysdate,‘YYYY’)) )
)
GROUP BY
V_DIM_CAL_MONTH.YEAR,
V_DIM_CAL_MONTH.MONTH_NAME,
V_DIM_CAL_MONTH.HALFYEAR,
V_DIM_CAL_MONTH.QUARTER

Without Month Prompt in the Report :------------------------------------------

Report Objects : Year,ActiveMerchants

Report Output :

Year ActiveMerchants

2008 150
2007 100

If i drill on year column(2008) then data is coming for Half Year:

Half Year ActiveMerchants

2008H2 150

If i drill on Halfyear column(2008H2) then data is coming for Quarter:

Quarter ActiveMerchants

2008Q3 150

If i drill on Quarter column(2008Q3) then data is coming for Month:

Month ActiveMerchants

SEPTEMBER 100
AUGUST 50

SQL for the Report is :

SELECT
V_DIM_CAL_MONTH.YEAR,
Sum(distinct(DW_MTH_SUM_MERCHANT_STATS.ACTIVE_MERCHANTs)),
V_DIM_CAL_MONTH.HALFYEAR,
V_DIM_CAL_MONTH.QUARTER,
V_DIM_CAL_MONTH.MONTH_NAME,
FROM
V_DIM_CAL_MONTH,
DW_MTH_SUM_MERCHANT_STATS
WHERE
( V_DIM_CAL_MONTH.MONTH_IN_YYYYMM=DW_MTH_SUM_MERCHANT_STATS.MONTH_ID )
GROUP BY
V_DIM_CAL_MONTH.YEAR,
V_DIM_CAL_MONTH.HALFYEAR,
V_DIM_CAL_MONTH.QUARTER,
V_DIM_CAL_MONTH.MONTH_NAME

Database Output for the above SQL is:

Year Active Merchants Half Year Quarter MonthName
2008 100 2008H2 2008Q3 SEPTEMBER
2008 50 2008H2 2008Q3 AUGUST
2007 25 2007H2 2007Q3 SEPTEMBER
2007 75 2007H2 2007Q3 AUGUST

Please let me know how to acheive the drill properly with month Prompt ?

It is very Urgent!

Thanks in Advance
Lakshmi.K

lakshmi.k (BOB member since 2008-08-25)

2

Would it not be easier for you to just leave the prompt out of the query and use a drill filter on the actual report?

BoBaJoB :uk: (BOB member since 2005-10-13)

3

Right, I’ve just cancelled the training class and sent them off for an early lunch, so we can explore your problem…though I can’t quite see what the problem is :?:

dessa :madagascar: (BOB member since 2004-01-29)

4

If I had a pound for every “Urgent” on here, I would be a very wealthy man…

:rotf:

Mak 1 :uk: (BOB member since 2005-01-06)

5

Hi Thanks for the reply.

I tried according to your suggestion to use Drill Filters. Now the data is coming correct but the problem is variance calculation.

Report o/p is :

                        2008                2007 
                        Sep                 Sep                  Variance

ActiveMerchants - 100 25 75

The variance for this is sep 2008-sep 2007. If i drill up, the variance should be 2008Q3 - 2007 Q3 and for half Year the variance is 2008H2 - 2007 H2 , ec.

We used formula for Variance as :
=[Active Merchants] Where ([Year]=[Current Year])-[Active Merchants] Where ([Year]=[Last Year])

Current Year and Last Year are report level variables.

How can we change dynamically the variance for Quarter, Year and Half Year.

Thanks and Regards
Lakshmi.k

lakshmi.k (BOB member since 2008-08-25)