Trending the Dates using multi dimensional functions

[Prashanthi]

Hi,

I have a requirement, where trending Yearly,Quarterly, Monthly as list box prompt and date prompt in the report page. I have a single query for filtering the trend (Yearly, Quarterly,Monthly ) where  I am using Token for passing trend values to crosstab as per the user selection.

But I am struck with displaying  2 years for yearly,4 quarters for Quartely and  6 months for monthly  trending based on user selection in date prompt  where I want to use only dimensional functions.

 

Thanks,
Prasanthi

[MFGF]

Hi,

I have a requirement, where trending Yearly,Quarterly, Monthly as list box prompt and date prompt in the report page. I have a single query for filtering the trend (Yearly, Quarterly,Monthly ) where  I am using Token for passing trend values to crosstab as per the user selection.

But I am struck with displaying  2 years for yearly,4 quarters for Quartely and  6 months for monthly  trending based on user selection in date prompt  where I want to use only dimensional functions.

 

Thanks,
Prasanthi

Hi,

I can't make sense of what you posted here. Can you provide more explicit details? How should your prompts work? What exact results would you need to see as a result? It's not clear what "2 years" means and how it relates to what your prompts might deliver, for example.

MF.

[Prashanthi]

Hi MFGF,

We are building report based on Dimensional  data source
we have 2 prompts
1) Trend (In Trend prompt we are giving static choices as Yearly, Monthly, Quarterly and it is single select prompt)
2) Date (we are using date prompt)

In report page we are displaying crosstab report. In cross tab report we are displaying 2 dimensions in rows, date field in columns and 2 measures.

As per our requirement we need to  achieve if we select Monthly in trend prompt and any date in date prompt then it should filter last 6 months data (i.e. Month of selected date and five prior months)
If we select Quarterly in trend prompt and any date in date prompt then it should filter last 4 quarters data (i.e. Quarter of selected date and three prior quarters)
If we select Yearly in trend prompt and any date in date prompt then it should filter last 2years data (i.e. Year of selected date and Prior year)
For Example:
In Trend prompt, we select monthly and in date prompt we select Feb 25, 2014 then the report should filter last 6 months
(2013/Sep,2013/Oct,2013/Nov,2013/Dec,2014/Jan,2014/Feb)

Facility    Audit Status Type   2013/Sep   2013/Oct   2013/Nov   2013/Dec   2014/Jan   2014/Feb
      Volume   Dollars   Volume   Dollars   Volume   Dollars   Volume   Dollars   Volume   Dollars   Volume   Dollars
abcde   Closed   0   $0   0   $0   0   $0   1   $109,693   0   $0   0   $0
   Open   2   $61,346   1   $11,291   1   $8,868   0   $0   0   $0   0   $0
xyz   Closed   0   $0   0   $0   0   $0   0   $0   0   $0   0   $0
   Open   19   $168,416   7   $256,864   1   $6,971   1   $61,943   0   $0   0   $0

In Trend prompt, we select Quaterly and in date prompt we select Feb 25, 2014 then the report should filter last 4 quarters(2013 Q2,2013 Q3,2013 Q4,2014 Q1)
               
Facility    Audit Status Type   2013 Q 2   2013 Q 3   2013 Q 4   2014 Q 1
      Volume   Dollars   Volume   Dollars   Volume   Dollars   Volume   Dollars
abcde   Closed   2   $7,549   3   $14,823   1   $109,693   0   $0
   Open   3   $27,426   3   $72,848   2   $20,159   0   $0
xyz   Closed   7   $98,997   1   $9,747   0   $0   0   $0
   Open   15   $122,819   31   $340,555   9   $325,778   0   $0

In Trend prompt, we select Yearly and in date prompt we select Feb 25, 2014 then the report should filter last 2 years(2013,2014)

Facility    Audit Status Type   2013   2014
      Volume   Dollars   Volume   Dollars
abcde   Closed   7   $136,812   0   $0
   Open   9   $125,545   0   $0
xyz   Closed   19   $255,937   0   $0
   Open   68   $890,500   0   $0

Thanks,
Prashanthi