SOLVED !! - Last 6 Months Data from Value Prompt

[xplorerdev]

Hi,

I am using Cognos BI 10.2.1.

In my report, I have a Time value prompt having Month, Quarter and Year. There is a crosstab with 5 measures. In my framework, I have a Time dimension with data from 2011 to 2015. However, my Fact table has measure values ONLY from Jan 2011 to Dec 2011. When I select Month from the value prompt, I get all the 12 months data. Everything seems to work fine.

In my new requirement, I need to add another static choice in my prompt, Last 6 Months, in my value prompt. So when I select this, ONLY the data for July 2011 to Dec 2011 should show in the crosstab.

Any help/suggestion will be highly appreciated.

Best Regards
Dev

[nblank]

Hi,

Create a custom prompt (parameter = p_period) with the use_value = 6 and the display_value = 'Last 6 months'. Another use_value = 12 (or 99 to be sure) and display_value = 'All months'
Then in the query for your crosstab edit the filter for date and add something like:

AND date >= _add_months(<date-prompt>; (-1 * p_period))

Nanno

[xplorerdev]

Hi,

Create a custom prompt (parameter = p_period) with the use_value = 6 and the display_value = 'Last 6 months'. Another use_value = 12 (or 99 to be sure) and display_value = 'All months'
Then in the query for your crosstab edit the filter for date and add something like:

AND date >= _add_months(<date-prompt>; (-1 * p_period))

Nanno

Hi Nanno,

Thanks for the reply. Unfortunately, I cannot add another prompt in my report as per the user requirement guidelines. I have a data item , MIS Period, in the crosstab query with the following definition:
CASE
WHEN (?Period?='Month') THEN ([Dimensions].[MIS Time Non Daily].[MIS Time].[Month])
WHEN (?Period?='Quarter') THEN ([Dimensions].[MIS Time Non Daily].[MIS Time].[Quarter])
WHEN (?Period?='Year') THEN ([Dimensions].[MIS Time Non Daily].[MIS Time].[Year])
ELSE (NULL) END
I have dragged this MIS Period data item into the crosstab rows. Thus, when the user selects Month from the Value Prompt (Parameter = Period), Month data corresponding to Jan-Dec 2011 gets displayed.

Similarly, I want to add another option in this MIS Period data item which will equate to last 6 months data. I tried using closingPeriod expression in the following 2 ways:
Way 1: WHEN (?Period?='Month') THEN closingPeriod([Dimensions].[MIS Time Non Daily].[MIS Time].[Month]). But this gives No Data Available since the MIS Table has data till 2015 and the Fact table has data ONLY till Dec 2011. Also, by this way, I can ONLY get the latest month data that is in database.
Way 2: WHEN (?Period?='Month') THEN ([Dimensions].[MIS Time Non Daily].[MIS Time].[Month], [2011]). Here I have hardcoded the year member, [2011]. This will again give me the last month i.e. Dec 2011. But this has tyo be changed every year then.

Thus, in a similar manner, BUT obviously with some other expression, maybe lastPeriods or something else, I would like to show last 6 months data.

I hope I have put my requirement in the correct manner :)

Best Regards
Dev

[Francis aka khayman]

why don't you google lastPeriods and try it?

[srinu_anu2007]

Try this one.

lastPeriods (6,[Jun 2011] -> ?timeprompt?)

[xplorerdev]

Thanks for the suggestion Khayman.
I did try lastPeriods in the MIS Period data item in the following manner:
WHEN (?Period?='Last 6 Months') THEN lastPeriods(6,[Dimensions].[MIS Time Non Daily].[MIS Time].[Month]->?Period?)
but this gave No Data Available because it might be picking up the last 6 months from the MIS Time table i.e. July 2015 to Dec 2015. For these months there is no data in the fact table.

Thanks for the expression srinu_anu2007.
Tried your expression. The hourglass ran for a while and I thought it would show up properly, BUT then I got the following error: :(
XQE-PLN-0206 Invalid expression '[Jun 2011]' on left hand side of '->' for a member parameter for data item 'MIS Period', expected a level or hierarchy reference.

I think there is something more to this expression which I cannot figure out. Need to apply my brains.

Please suggest/advice if you have anything in mind. I have also attached a screenshot of how my MIS Time looks in my framework. Maybe that can be of some help.

Best Regards
Dev

[Francis aka khayman]

lastPeriods(6,[Jun 2011])

[xplorerdev]

lastPeriods(6,[Jun 2011])

Khayman, ur the Man :) Thanks sir.

I modified the expression in the MIS Date like the one below:
CASE
WHEN (?Period?='Last 6 Months') THEN lastPeriods(6,[Jun 2011])
WHEN (?Period?='Month') THEN ([Dimensions].[MIS Time Non Daily].[MIS Time].[Month])
WHEN (?Period?='Quarter') THEN....
WHEN (?Period?='Year') THEN....
WHEN (?Period?='Months on Books') THEN....
ELSE (NULL) END

Its working like a charm. Thanks a lot. So now, I can display last any months data, provided, I mention the member i.e. [Jun 2011] in this case. However, this is actually hard coding. What happens when year changes to 2012 or 2013? The expression for 'Last 6 Months' will still show Jan 2011 to Jun 2011 :(. This is what my actual requirement is.  ???

I should be able to display the last 6 months of data that is present in the fact table. In the above case the Fact table had data ONLY for Jan 2011 to Dec 2011. But in production environment, data will be added at every month end. So the Last 6 Months option should, always, display the data corresponding to the last 6 months present in Fact table.

Looking forward for some more suggestions. I feel we are pretty much close here. :)

Best Regards
Dev

[Francis aka khayman]

you will need your time dimension to be 'synched' with your fact tables months.
meaning if your fact table's last month is Jan 2013, then the last value in the month level of your time dimension should also be Jan 2013.

if for some reason that is not favorable, you need to create another hierarchy that will store the same value ... usually this is also done in YTD, thingie and other relative time stuff

[xplorerdev]

Thanks for the reply Khayman. I have to halt my R&D on this topic. Something else just came up. Will keep your advice in mind. Till then. Goodbye. Thanks. :)

[xplorerdev]

Hi khayman and nblank and others who may have read this post,

I think I have found out a solution for this in my environment. I will post the details for my scenario and the steps involved to achieve Last 6 Months data to be displayed in report.

In my DB, I have one Fact_Table and one MIS_Time_Table. Fact table (Joining Key is MISDateKey - Format YYYYMMDD, Integer) has measure values from Jan 2011 to Dec 2011. However, MIS_Time_Table has entries from Jan 2005 to Dec 2015 (Joining Key is DayKey - Format YYYYMMDD, Integer).

Now, my requirement is such that right now, I should have a prompt value Last 6 Months Data which when selected should show data from July 2011 to Dec 2011. Also, when after some time, the data in Fact table increases, for e.g. lets suppose that now fact table has data for Jan 2011 to Dec 2012, so again, selecting Last 6 Months Data option should give measure values corresponding to Jul 2012 to Dec 2012. I hope you guys have got the idea here.

What I did was to create a Data Item with the following expression:
CASE
WHEN (?Period?='Last 6 Months') THEN lastPeriods(6,item(tail([Dimensions].[MIS Time Non Daily].[MIS Time].[Month]),0))
WHEN (?Period?='Month') THEN ([Dimensions].[MIS Time Non Daily].[MIS Time].[Month])
WHEN (?Period?='Quarter') THEN ([Dimensions].[MIS Time Non Daily].[MIS Time].[Quarter])
WHEN (?Period?='Year') THEN ([Dimensions].[MIS Time Non Daily].[MIS Time].[Year])
WHEN (?Period?='Months on Books') THEN ([Dimensions].[Months on Books].[Months On Books].[Months On Books])
ELSE (NULL) END

My report had the value prompt with the static choices mentioned above.

Imp Thing: I added a detail filter into the report like the following:
[Fact_Table].[MISDateKey ] = [MIS_Time_Table].[DayKey]

Thats it :)

Now the report displays data as required. Also, now I can add Last 2/3/4/5....months data etc depending on my requirement.

I hope the above may work out for someone.

**** CLOSING THIS THREAD ****

Best Regards
Dev

[MFGF]

Thanks for the update!! Very useful! :)

MF.

[xplorerdev]

Thanks for the update!! Very useful! :)

MF.

:) Hey MF.

I see Forum Citizenship: +1/-2 next to my name. Does that mean I have been applauded only once and warned twice ?  :'( I always post answers then why don't users applaud  :( :(

Best Regards
Dev

[MFGF]

:) Hey MF.

I see Forum Citizenship: +1/-2 next to my name. Does that mean I have been applauded only once and warned twice ?  :'( I always post answers then why don't users applaud  :( :(

Best Regards
Dev

Sadly, yes - this is exactly what it means. Sometimes new forum members will click every single link they can see on the page for some bizarre reason (including the "Report to Moderator" link). There's nothing we can do to stop this, though.

Thanks for your helpful posts and willingness to contribute. That gets you an applause from me, so at least you are back on an even footing now :)

MF.

[xplorerdev]

Sadly, yes - this is exactly what it means. Sometimes new forum members will click every single link they can see on the page for some bizarre reason (including the "Report to Moderator" link). There's nothing we can do to stop this, though.

Thanks for your helpful posts and willingness to contribute. That gets you an applause from me, so at least you are back on an even footing now :)

MF.

Yippeeeeeee....thanks Super Moderator :)

[MFGF]

Oh - that's my formal title. Nobody uses that, though - they just say "Oi! Muppet!" :)

MF.

[navissar]

I'm all for people closing threads like this, so there you go, you're a +1 now :)

[xplorerdev]

I'm all for people closing threads like this, so there you go, you're a +1 now :)

OMG !! Now that's what I call a jackpot. Thanks Community Leader, Nimrod :)

MF, I got another applaud :)

[Lynn]

And from me too
:)

[MFGF]

Look at that! You're now +6 / -2

Much better! :)

[xplorerdev]

And from me too
:)

Is this a dream. +6/-2 :) This is a point where I am super motivated. Thanks Lynn.

So this is an epic day. All the 3 Iron Man of this forum, The Super Moderator, The Community Leader and now its The Statesman,,joining hand together to keep the momentum going..I just hope all follow such simple rules to get + points. Sharing is the best thing to do...ever :)

Best Regards
Dev

[MFGF]

Wow! Look at that!

To quote Len Goodman, you score is now...

"SEVEN!"



MF.