Set dinamically last month of current and previous year

[Rosanero4Ever]

Hi all,

I'm using Cognos BI 10.2.1 and in Report Studio, using a dimensional view, I need to create a simple report able to monitor the revenue about the last month comparing current year and previous year, for each articles category. So, If current month/year is Nov/2014 the report should appear as displayed in the attached image.
At the time, I drag & drop each month member of my Time dimension, but it is boring because I must perform this operation each month ???
How can I set the report in order to calculate dinamically last month of current and previous year?
Does exists a functionality in Report Studio able to reduce the use of dimensional functions or complex formulas?
Thanks so much in advance

[MFGF]

Hi all,

I'm using Cognos BI 10.2.1 and in Report Studio, using a dimensional view, I need to create a simple report able to monitor the revenue about the last month comparing current year and previous year, for each articles category. So, If current month/year is Nov/2014 the report should appear as displayed in the attached image.
At the time, I drag & drop each month member of my Time dimension, but it is boring because I must perform this operation each month ???
How can I set the report in order to calculate dinamically last month of current and previous year?
Does exists a functionality in Report Studio able to reduce the use of dimensional functions or complex formulas?
Thanks so much in advance

Hi,

Firstly a couple of questions.

1. What is the "latest" month in the month level of your date hierarchy - is it some month off in the future, or does it change as time goes by?
2. If the answer to 1 is that your month level contains months in the future, what does the Member Unique Name (MUN) of a month member look like? If you can give us an example we can help you build a macro to retrieve the current month.

So, there could be two approaches to solving this, depending on the answer to question 1 above

If the answer to 1 is that the latest month in your month level is the "current" month (ie Nov/2014 right now), you can use this approach:

The expression to get the latest month from your month level is
closingPeriod ([your Month level])

If you want the month prior to this, you can use the prevMember() function too - ie

prevMember(closingPeriod ([your Month level]))

I'm assuming this will the the expression for your first column.

To get the same month in the previous year, you can use a parallelPeriod() function

parallelPeriod([your Year level], 1, prevMember(closingPeriod ([your Month level])))

I'm assuming this will be the expression for your second column.

The above expressions are based on the latest month in your month level being the "current" month. If this is not the case, post up the MUN of a month member and we will be able to help you further.

Cheers!

MF.

[Rosanero4Ever]

Hi,

thank you so much for your answer.
I'd like the month prior to current. So I believe the statement
prevMember(closingPeriod ([your Month level]))
should be right for my target.
I'll try this solution and I'll tell you the result
Many many thanks ;D

[Rosanero4Ever]

Another related question...
What fomulas can I use to consider not only the prior month to the current but all the previous months?
So, I would analyze (if we are at November) months from January to October
In a relational world I apply a filter
Month<month(current_date)
How can i perform this job in a dimensional world?
Many thanks

[MFGF]

Another related question...
What fomulas can I use to consider not only the prior month to the current but all the previous months?
So, I would analyze (if we are at November) months from January to October
In a relational world I apply a filter
Month<month(current_date)
How can i perform this job in a dimensional world?
Many thanks

Hi,

You'd probably need to use a periodsToDate() function for this.

To get all months up to your target month in the current year, you would use:

periodsToDate([your Year level], [your target month])

I'd suggest it's worth spending an hour or so looking at the dimensional functions available in the expression editor. Each function has a "Information" description that tells you what it does and how to use it. The Information section for periodsToDate() says this, for example:

periodsToDate ( level , member )
Returns a set of sibling members from the same level as "member", as constrained by "level". It locates the ancestor of "member" at "level" and returns that ancestor's descendants at the same level as "member" (up to and including "member"). Typically used with a time dimension. This function appears in the Rolling and Moving Averages interactive sample report.


Example: periodsToDate ( [great_outdoors_company].[Years].[Years].[Year] , [2004/Mar] )
Result: Returns values for [2004/Jan], [2004/Feb], [2004/Mar]

Cheers!

MF.

[Rosanero4Ever]

Thanks!