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
Quote from: Prashanthi on 27 Aug 2014 07:33:00 AM
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.
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