REPORT

[sspk]

HI ALL,
I have query items like d_year,d_month,tr_1,tr_2,....tr_24.(i am writing in short forms)
when d_year=2007 and d_month=11 then tr_1=Nov-07,tr_2=Oct-07....tr_24=DEC-05
when d_year=2007 and d_month=12 then tr_1=DEC-07,tr_2=Nov-07,tr_3=Oct-07....tr_24=Jan-06.
Now  i have to calculate a calender qtr(it should be dynamic)so that
when d_month=2007 & d_month=11 then q4-07 should be tr_1(Nov-07)+tr_2(Oct-07)
when d_month=2007 & d_month=12 then q4-07 should be tr_1(DEC-07)+tr_2(Nov-07)+tr_3(Oct-07)
I am trying to create a calculation in FM like
IF( TO_CHAR( [TODS].[CURRENT_DATA_MONTH].[DATA_MONTH_DATE] ,'Mon-YY')='Dec-07' )THEN
([TODS].[FACT_TABLE].[trX_1] +[TODS].[FACT_TABLE].[trX_2] +[TODS].[FACT_TABLE].[trX_3] )
ELSE IF (  TO_CHAR( [TODS].[CURRENT_DATA_MONTH].[DATA_MONTH_DATE] ,'Mon-YY')='Nov-07')THEN
([TODS].[FACT_TABLE].[trX_1] +[TODS].[FACT_TABLE].[trX_2] )
ELSE
([TODS].[FACT_TABLE].[trX_1] )
but this is not dynamic  i even i am not getting the solution also, can anyone help me out in this.Please it's urgent

[Suraj]

The best place to do this is in the database.
that way report queries don't take long time to run even after you make it work.

[almeids]

As Suraj suggests it would be much simpler to have calendar quarter columns added to the table you are using (which already appears to be dynamically recalculated for "as-of" time views), or to source your report from less-processed data where the time dimension is vertically represented only.
Having said that, there is no reason what you are trying to do shouldn't work.  Remove the year from your calculation logic, it belongs in your filter.  The calculations only need to know which month the record represents since that determines the content of the monthly value columns.  You may also need to put more logic into your filters to only select certain records and prevent doublecounting (more than double, actually).  You haven't described the specifics of your report or its selection criteria, but if for example you are calculating a Q4 value for 2007, you only need the December record - if you try to add up the records for October through December you'll drastically overstate the values (unless you take a diagonal slice, only considering the October value in the October record, November value in November record, and December in December).
Basically, you need to pick whether you will get the components of your quarter totals vertically, horizontally, or diagonally and this depends on the nature of the data and the requirements of the report.