Columns pulling data between two months

[Former Member]

Hello,

I would like to create a variable, such that it will pull data between current month and next 11 months on webi report. This data should get generated in form of columns.

eg: If we consider today's month, then once variable is placed on report, it should generate 11 columns starting from March 2011 to Feb 2012 and corrosponding data should be visible under each column.

What is the way to achive this requirement?

*Note: Data for next 4 years is available in my database.

Regards,

Chinmay

[Former Member · Accepted solution]

Is it a must that it has to be at a variable level? Can't it be at query level?

[Former Member · Accepted solution]

Ok, at a variable level this is what you have to do:

1. Insert a new cell

2. Press Formula Editor button and insert this formula:

 =If ( [Query1].[monthNumber] Between (MonthNumberOfYear(CurrentDate()) ;MonthNumberOfYear(CurrentDate())+11 ) And [Query1].[yearNumber] = Year(CurrentDate()) Or ( [Query1].[monthNumber] = MonthNumberOfYear(CurrentDate()) - 1 And [Query1].[yearNumber] = Year(CurrentDate())+1 ) ; 1 ;0)

3. This formula will return 1 if your month column must be shown, it will return 0 if it must not be shown. To make it happen press the create Variabl button and name this for example: "Show Month Year"

4.This new variable named "Show Month Year" will appear in the Data Tab (panel at the right).

5. Select your table. Make sure it appears selected.

6. Press Show/Hide filter Pane Control. It will show an empty pane above your table. Drag and drop in this Pane your new Variable.

7. It will display a filter Editor Window. In the textbox named "Type a value then add it" input the value 1. Press the greater than button and press Ok. It will make that only the current month and 11 next months show.

Edited by: PadawanGirl on Feb 4, 2011 5:02 PM

[Former Member · Accepted solution]

With this solution I'm assuming you are not using prompts in your query but using all the data from all the 4 years.

[Former Member · Accepted solution]

If you do are using just one prompt it can work anyways but it will delay retrieving a lot of data so I better recommend you to read this post:

/people/erika.atencio2/blog/2011/02/24/date-objects-operations-in-webintelligence

Edited by: PadawanGirl on Feb 24, 2011 4:45 PM

[Former Member · Accepted solution]

Hello Padwan,

Thank You very much for your suggestions and the soultion that you have mentioned in your blog perfectly worked for me.

Thanks a lot for knowledge sharing and your help

Regards,

Chinmay Athavale