Extracting individual months from Time Dimension

[cognos4321]

Hello Experts,

I have a crosstab report in which I have to show individual months visible like Jan , Feb......Dec. There is a filter on Years from 2013 to 2019.There is a prompt where the user can select the starting period and Ending Period within this selected range and wants to see the data for all the months in that selected range.

               Jan|Feb|Mar|Apr|Jun|Jul|Aug|Sep|Oct|Nov|Dec
Product      Measure

The Time Dimension has Years, Quarters, Months. But the members in Months are like
Jan 2013
Feb 2013
Mar 2013
.
.
.
Dec 2020
I mean to say that there are no individual Month levels which I can drag and drop. All of them have a specific year attached to it.

Please help how can I make the report have individual months visible.
I am using Cognos 10.2 (Dimensional Model)
Any help is appreciated. It's something urgent for me.

Thanks a lot in advance.

[bdbits]

Could it be the business key on the time dimension level is the month number and the formatted date ('Jan 2013') is the caption on the level? If so, you could use roleValue() in a filter() expression to find the months to include, irrespective of years.

[venkat01]

Hi

in crosstab add a query calculation name month number and in that write an expression to_char([monthdataitem],'mm') this will give you 01,02,03....12 then take another query calculation name month in crosstab write and expression like if(to_char([monthdataitem],'mm')=1) then('Jan') else
if(to_char([monthdataitem],'mm')=2) then('Feb') else...if(to_char([monthdataitem],'mm')=11) then('Nov') else('Dec') or you can simply write if(month number=01) then('Jan')...else('Dec') whatever you'l get jan,..dec
then sort that month number column in ascending order if you want it to show on report leave as it is otherwise use box type none for this month number column and then in prompt you'l give date range so it will show those months and year range data

thanks
venkat

[cognos4321]

Thanks bdbits.

The datasource here is a TM1 Cube.
Could you please elaborate a bit more on this 'rolevalue()' in the context of my problem.

I read the information but didn't get a lot from it.

I have the following structure.
Time(Dimension)
-All Time (level)
-Year (level)
-Quarter (level)
-Month (level)

Now the Month has members as in
JAN 2013
FEB 2013
MAR 2013
APR 2013
.
.
.
.
DEC 2020

Thanks a lot

[cognos4321]

Hi Venkat,

Thanks for the reply.
Let me try what you said.

[cognos4321]

Hi Venkat,

I tried it but the Query Month shows XQE-V5-0017 error
Here is what I did

'Month Number' Query
Expression
to_char([Lease Accounting].[Time].[Time].[Month],'mm')
Validated- doesn't show any error.

'Month' Query
Expression
If ([Month Number]= 01) then ('JAN') else
If ([Month Number]= 02) then ('FEB') else
If ([Month Number]= 03) then ('MAR') else
If ([Month Number]= 04) then ('APR') else
If ([Month Number]=05) then ('MAY') else
If ([Month Number]= 06) then ('JUN') else
If ([Month Number]=07) then ('JUL') else
If ([Month Number]= 08) then ('AUG') else
If ([Month Number]= 09) then ('SEP') else
If ([Month Number]=10) then ('OCT') else
If ([Month Number]=11) then ('NOV') else
('DEC')
End

OR

If ( to_char([Lease Accounting].[Time].[Time].[Month],'mm')= '01')) Then 'JAN'
If (to_char([Lease Accounting].[Time].[Time].[Month],'mm')= '02'))Then 'FEB'
If (to_char([Lease Accounting].[Time].[Time].[Month],'mm')= '03')) Then 'MAR'
If (to_char([Lease Accounting].[Time].[Time].[Month],'mm')= '04')) Then 'APR'
If (to_char([Lease Accounting].[Time].[Time].[Month],'mm')= '05')) Then 'MAY'
If (to_char([Lease Accounting].[Time].[Time].[Month],'mm')= '06')) Then 'JUN'
If (to_char([Lease Accounting].[Time].[Time].[Month],'mm')= '07')) Then 'JUL'
If (to_char([Lease Accounting].[Time].[Time].[Month],'mm')= '08')) Then 'AUG'
If (to_char([Lease Accounting].[Time].[Time].[Month],'mm')= '09')) Then 'SEP'
If (to_char([Lease Accounting].[Time].[Time].[Month],'mm')= '10')) Then 'OCT'
If (to_char([Lease Accounting].[Time].[Time].[Month],'mm')= '11')) Then 'NOV'
Else 'DEC'
End.

Validated:  XQE-V5-0017 error......

Tried on all other specifications like '  '  or ( )

I am working on Cognos 10.2 and the datasource is TM1 Cube.

Thanks a lot.

[navissar]

If you can't change your cube I think you might need to do some manual work there.
You create one set to represent the selected range and call it Months Selected.
Then for January you create a data item, and set it up to something like
sum(<measure> within set (intersect(set([Jan 2013], [Jan 2014]...),<Months Selected>))
This ill summarize your measure across all listed January months where the months are also within the selected range. You create one like this for each month, so 12 in total. Theoretically this should work. Ideally, you'll change you source.

[venkat01]

Remove End

Sent from my HTC One XL+ using Tapatalk

[navissar]

I'd refrain from using if statements on a dimensional data source.

[cognos4321]

Hi Nimrod,

Sorry for replying late. I was trying using 'next member' function on a already existing 'dataitem1' which extracts the beginning month from the user selected parameter.
It has exp.
[Lease Accounting].[Time].[Time].[Month] -> :[TM].[Time].[Time].[@MEMBER].?pTime?

Now I kept on doing next member(Data item 1). This would create Data item2.
Now, next member(Data item 2). This would create Data item 3.
I did this 12 times for the for fetching the 12 months.

Then created another 12 data items showing the measure for the 12 month created above and dragged them into the crosstab.

Tuple([Data item1],<Measure>)
Tuple([Data item2],<Measure>)
.
.
.Tuple([Data item 11],<Measure>)

I know this is not a good approach but for now this is giving me the correct values.

BUT

The headings of these (Tuple([Data item1],<Measure>)......) included in the crosstab won't change if I make them static by using a text like Jan, Feb ,Mar.

I am trying to say if user selects the prompt from Sep2013 to Dec2014 then it shows the data of next 12 month from Sep2013 like Oct2013, Nov2013 but the headings still remain the same as Jan Feb Mar since I made them static.

I don't know how to make the headings dynamic for this approach.


Now let's come to what u suggested.
So, should I create this in promt query?
My prompt query is having an 'Year' data item having expression.
Set(2013,2014,2015,2016,2017,2018,2019)

And after creating these 12 data items should I include them as columns into the crosstab. How will the headings of those columns be dynamic. Like if the user wants the data Sep 2013 onwards then it should show Oct, Nov, Dec, Jan ,Feb,Mar , Apr.....and so on until 12 months. It doesn't need to show the year in the column headings, it's just in the prompt.

Thanks a lot Nimrod. I really need help on this.

[cognos4321]

Experts,

Is there any way we can extract Month Name from an expression like
next member(Data item 1)

and Data item1 is
[Lease Accounting].[Time].[Time].[Month] -> :[TM].[Time].[Time].[@MEMBER].?pTime?

Now 'pTime' is the parameter used in the prompt showing months from Jan 2013 till Dec 2019.

I know the extract function but it works only with date expression like
extract(month,date expression)

Wanted to make the headings of the crosstab dynamic.

[navissar]

You literally waited one hour and 18 minutes before re-posting your question.
I wish you good luck in your endeavors.

[cognos4321]

Thanks everyone for your time.
I figured it out by creating 3 data items.

Start Month- used running balance for this column in the crosstab to get the total beginning balance
[hierarchy] -> ?pStartMonth?

Months- this gave me the value for each month between the beginning balance and ending balance
lastPeriods ( -?pNumberofMonths? ,nextmember([StartMonth]) )

End Month-  used running balance for this column in the crosstab to get the total ending balance
lag([StartMonth], -?pNumberofMonths?-1)


Thanks again