Show Last 12 months in crosstab report - HELP

[lorenmario]

Hello!
I have a question here, I'm creating a report using a relational source and got a prompt value that loads the years/months (ex.: 2013/06). The user wants to bring the prompt month he selected and the previous 12 months ...
When he selects eg 2013/06, will also appear in the columns of the crosstab the previous 12 months. The user wants to be able to do comparative analysis.

Does anyone know how to do this?
Please help me!!

Thank you!
;)

[jworkm]

Maybe use the following function in a filter, where "date_expression2" is the date selected in the prompt, and "date_expression1" is the data field for the date in your source data. 

_months_between ( date_expression1, date_expression2 )
Returns a positive or negative integer number representing the number of months between "date_expression1" and "date_expression2". If "date_expression1" is earlier than "date_expression2", then a negative number is returned.


So the filter would look something along the lines of  "_months_between ( [datasource].[date], ?dateprompt? ) between -11 and 0"

Alternativly you could build a date table that has the offsets between the current day and each day in history (and future) pre-calculated. i.e. if todays date is 6/27/2013, then the table would look like this:

DATE-----------DAY_OFFSET-------MONTH_OFFSET-------YEAR_OFFSET
6/28/2013            1                             0                           0
6/27/2013            0                             0                           0
6/26/2013           -1                             0                           0
6/25/2013           -2                             0                           0
....
5/31/2013         -27                            -1                           0


With that table built, you could go a few different routes. You could change the prompt to accept the  MONTH_OFFSET value instead of the date, and then use filters to specify that "MONTH_OFFSET BETWEEN ?offsetmonths?-11 and ?offsetmonths?". This simplifies the filters you need to make. If you want to keep it so that the prompt asks for a date instead of an offset number, you can modify the prompt to display the date but use the offset value, or you can run a query on the selected date to pull the offset number, then use those in the filter.

[lorenmario]

Hi friend, thank you for answering me.
I created a prompt page with a date prompt, related to column [Date], (date format is Jun 27, 2013).

In crosstab report I put the field Month and Date (nested in columns).

I added the filter you suggested:
MONTHS_BETWEEN ([Date] =? P_data?) between-11:00

But not validated, where am I going wrong?
: (
Please help me!

[jworkm]

MONTHS_BETWEEN ([Date] =? P_data?) between-11:00"

if "[Date]" is the feild containing the date and "? P_data?" is the prompted date, the filter would be:

_months_between ( [Date], ? P_data? ) between -11 and 0