How to create this report

[pooja]

Need help-

We have a req for Balance Sheet report which has inputs -
YEAR
MONTH - JAN(1)...
ComName - ABC

Output-
1) If Input YEAR = 2013 & Month = 3(March) selected then report output column should be-Dec'12 - JAN'13 - FEB'13 & Mar'13

2) If Input YEAR = 2012 & Month = 5(March) selected then report output column should be-Dec'11 - JAN'12 - FEB'12 - Mar'12 - April'12 - May '12

And so on. Basically Prior Year Dec to selected month of selected Year.
Please help how to develop this report. we are using Cube for this.
Much appreciate your help on this.

P

[pooja]

This is actually Rolling month for last 13 months with having 5yrs data. We are using the cube for this.
Much appreciate for your help.


P

[HalfBloodPrince]

lastPeriods (3,[2013/May]) this function will give u data for Mar , April , May

[pooja]

Thank you.

But we have 5yrs data (09-13). How to make this possible when any year, any month selected and output should be in 13 month columns?

P

[Lynn]

Try this except substitute the level of your time dimension. My example is based on the sales and marketing sample cube.

lastPeriods ( 13, [sales_and_marketing].[Time].[Time].[Month]->?SelectMonth? )

[Francis aka khayman]

#2 in your first post should be 5(May) instead of 5(March) right?

and what do you mean output in 13 months column?

your first example 12-2012 to 03-2013 is 4 months and second example 12-2011 to 05-2012 is 6 months?

[pooja]

Hi all,

Sorry for the confusion. Here are the clear picture-

1) We have 5 yrs dta (2009-2013)
2) Inputs (prompts) should have -YEAR, MONTH & COMP_NAME

FOR OUTPUT
-----------

results should populate 13 month data for any selected year/month.

Example-
If we pass the parameter for YEAR -2013 & MONTH - 2 (FEB) then results should be -

Feb'13 | Jan'13 | Dec'12 | Nov'12 | Oct'12 | Sept'12 | Aug'12 | Jul'12 | Jun'12 | May'12 | Apr'12 | Mar'12 | Feb'12

and this case applied to any selected YEAR/MONTH.
Attached is the sample. This is going to be Crosstab report.
Many many thanks for any help.

P

[Lynn]

Ok, so still looks to me that what I suggested will work for you, just using your month level rather than the Cognos sample reference. Is that not the case?

lastPeriods ( 13, [sales_and_marketing].[Time].[Time].[Month]->?SelectMonth? )

[pooja]

Thanks. So this expression for the PROMPT - correct?

but how we apply this for output Columns?

[Lynn]

Drag a query calculation to the columns and put that expression in it. It will generate a prompt and then populate the columns with the 13 periods from whatever is selected.

You can create a prompt control to associate with the parameter to make a nice prompt page if you like.

[Lynn]

Here is a dimensional function reference that is really useful. I've also attached an example using the "Sales and Marketing (cube)" package from the Cognos samples.

http://www.ibm.com/developerworks/data/library/cognos/page82.html

[pooja]

Thank you so much. I will post how it works.

[pooja]

Thank you. it works for 13-moth data.

One more help-

how we achieve below data using time dimension as prompt. Example-

if we select March'13 then out put should be Jan'13, Feb'13 March'13 ..and remaining months will have NO DATA
if we select July'13 then out put should be Jan'13, Feb'13 March'13, April'13, May'13, Jun'13,July'13 ..and remaining months will have NO DATA
and do on.....

thanks for help.

P