Solved: Showing last three months

[_jl_]

Hi,

See the attachment. How can I display only last three months in a chart? At the moment I have I filter that does "caption([Year]) = ('2013')" and shows all the months from Jan to Jun. How can I filter so that it would only show current month plus two previous months?

I tried making a data item with the expression "[Month] >= _add_months([Month], -2)". Validation gives no errors but when tried to put that data item on chart's series (primary axis) and run the whole thing, it says: An error occurred while performing operation 'sqlPrepareWithOptions' status='-126'.  :-\

Pls help and thanks in advance,

jl.

[blom0344]

Validation does not include testing individual queries. If you want a running interval of 3 months, then use the current_date as a start point:

_last_of_month(current_date) yields  '2013-6-30'
_add_months(_last_of_month(current_date),-3) yields  '2013-3-31'

Use this - or an alternative -  in query comparisons..

[_jl_]

Ok now I tried to drag the Month from Data Items -list to Detail Filters and set an expression:
[Month] between _last_of_month(current_date) and __add_months(_last_of_month(current_date),-3)

Says error "Invalid coercion from 'level' to 'value' for '[Month]'....."

Did I do something wrong?

Thanks,
jl

[blom0344]

No, my fault. With Report Studio I always take for granted that a relational model is used, but you are using a dimensional one, hence the error message. If it's a DMR model, then using a joined query subject that 'stores' the running 3 month period would act as a proper filter when a data-item is used in the query..

[_jl_]

Yes it's a DMR so I've heard.

Could you clarify a bit how-to...?

[CognosPaul]

When working with a dimensional model, you should stick with dimensional functions. You should also avoid using detail filters whenever possible.

Generally date functions are also avoided, however in DMR they can be used in some cases.

The first step is to identify the current month member. Remember, you're not using detail filters for this. One of the issues I have with DMR is that the member unique names include the entire path, and if you have quarters in your model it makes it difficult to construct using macros as timestampMask doesn't support quarters.

You can generate the year MUN like this:

[Cube].[Dimension].[Hierarchy].[Years]->[All].#sb(timestampMask($current_timestamp,'yyyy'))#

The following will generate the months of the current year, and return the month in the position index of the number of the current month - 1:
item(descendants([Cube].[Dimension].[Hierarchy].[Year]->[All].#sb(timestampMask($current_timestamp,'yyyy'))#,[Cube].[Dimension].[Hierarchy].[Month]),#timestampMask($current_timestamp,'mm')#-1)

Now you can just do a simple:
lastPeriods(3,item(descendants([Cube].[Dimension].[Hierarchy].[Year]->[All].#sb(timestampMask($current_timestamp,'yyyy'))#,[Cube].[Dimension].[Hierarchy].[Month]),#timestampMask($current_timestamp,'mm')#-1) )

And that will return the expected set, which you can either slice by or use inside a crosstab or chart.

[blom0344]

Yes it's a DMR so I've heard.

Could you clarify a bit how-to...?

We only use DMR models within Analysis Studio ,where it is impossible to define MDX style constructions the way Paul descibes. That is why we use seperate query subjects for 'last xxx months/years' scenario's.  Using a dataitem from such a subject automatically introduces the join which then actively acts like a filter.
I guess you do not develop yor own models, so that would not be a viable solution (adding it to the model)

[_jl_]

"When working with a dimensional model, you should stick with dimensional functions. You should also avoid using detail filters whenever possible." ... why's that?

Tried making a new data item with that expression but it just says: The member reference 'expression="[cube_name].[Dates].[Dates].[Year]->[All].[2013]"' is not a proper name.

Same thing if I drag 'Month' object from Data Items list to Slicer and set it's expression there. I don't even really know what's the difference and it bothers me... :-[

ps. Is there some good material for free explaining clearly what is, for example:
- A difference between 'Detail Filters' and 'Summary Filters'
- Slicer
- MUN
- a Data Item

And in which situation I should/should not be using them.

BR,
jl

[CognosPaul]

Open the hierarchy and right click--> Properties on one of the year members. You will see a list that describes that member, including the Member Unique Name. You can use macro functions to recreate that mun, and it will automatically point the query to the right place.

I've discussed using detail filters on OLAP before on my blog: http://cognospaul.com/2012/01/29/why-detail-filters-should-never-be-used-in-olap/ and also on the forum: http://www.cognoise.com/index.php?topic=18290.0

DMR can work with detail filters, but it's still bad practice and doesn't let you think dimensionally.

Detail and summary filters are simply "where" or "having" clauses in the SQL. Detail before auto aggregation is always a where, and after auto aggregation is always a having. Summary filters allow you to define scope. So if you have a list that shows country and retailers, normally you don't have a way to answer "What are the values for the retailers where the country has more than 1,000,000 revenue?". Summary filters allows you to do revenue>10000000 on the scope of the country.

A slicer is the where for a cube. Imagine a Rubix cube that you can only see one side of. You can see vertically (Months) and horizontally (Products), but there is a third dimension (Country). The slicer will only show you that one country layer. Without the slicer it will total up the values for all of the layers (that is a lie, it actually uses the default member of the hierarchy which doesn't necessarily need to be the All member) (and it also doesn't necessarily need to total it).

A MUN is the unique path of a specific member. So in the imaginary Rubix cube, you're seeing products and months, but you can slice by France. The MDX would be
select dim.productsHier.ProductsLevel on columns, timeDim.TimeHier.Months on rows from cube where countryDim.CountryHier.CountryLevel->FRANCE

Data items are the objects inside the query explorer.

[_jl_]

...and back at work.

Thank you for the explanations! :) Have to read the blog and that post carefully.

Still though, haven't been able to get this thing work. What do I do wrong? Error in attachment.  :-\

[CognosPaul]

Which version of Cognos are you using? I see a PMR that has the same error which says to upgrading to the next fix pack will solve the problem.

In the meantime, try the following. Create a new query, and paste:
[Cube].[Dimension].[Hierarchy].[Year]->[All].#sb(timestampMask($current_timestamp,'yyyy'))#

In theory you should get the year. But as a sanity check, open the time dimension to the year, right click --> properties. Does the Member Unique Name on the member match [Cube].[Dimension].[Hierarchy].[Year]->[All].[2013]?

[_jl_]

Version seems to be 10.1.1.

Example MUN for 2007/Oct in attachment.

[CognosPaul]

Okay, this is a PowerCube, not a DMR. PowerCubes can be identified by the structure of the member, in this case ...[PC].[@MEMBER]...

You didn't copy the year member, so I can't give an exact example of how it looks. In general the code for the year mun is something like [20130101-20131231]

Copy the MUN from the year member, paste it into your expression. Put a #' before the start and a '# at the end:

#'[Cube].[Dim].[Hier]:->[whateverwhateverwhatever].[20130101-20131231]'#

Replace all instances of 2013 with the timestampMask macro, with quotes and plus in the appropriate place:
#'[Cube].[Dim].[Hier]:->[whateverwhateverwhatever].['+timestampMask($currentTimestamp,'yyyy')+'0101-'+timestampMask($currentTimestamp,'yyyy')+'1231]'#

Once you do this, the macro will be building the correct MUN for the current year. Just stick that in the place where the year member goes in your expression and you should be fine.

[_jl_]

Ok. Yes, sorry, just realised you were not asking month but year.

The MUN for year 2013 is:
[cube_name].[Dates].[Dates].[Year]->:[PC].[@MEMBER].[20130101-20131231]

If possible, pls post an example how would you to do it on "Sales and marketing (cube)". Maybe I'll understand by then...

[CognosPaul]

Since this is PowerCube you can probably find a way to reference the month mun directly, however I don't have Cognos available right now.

The expression I suggested before was:

Code Select Expand

item(descendants([Cube].[Dimension].[Hierarchy].[Year]->[All].#sb(timestampMask($current_timestamp,'yyyy'))#,[Cube].[Dimension].[Hierarchy].[Month]),#timestampMask($current_timestamp,'mm')#-1)

The error you received was based on the wrong mun structure for the year. All you need to do to fix it is to replace the year mun with the correct version

Code Select Expand

item(descendants(#'[cube_name].[Dates].[Dates].[Year]->:[PC].[@MEMBER].['+timestampMask($currentTimestamp,'yyyy')+'0101-'+timestampMask($currentTimestamp,'yyyy')+'1231]'#,[Cube].[Dimension].[Hierarchy].[Month]),#timestampMask($current_timestamp,'mm')#-1)

That will return the correct month member, then you just need to wrap that in lastPeriods(3,...code goes here...)

As an exercise for you, read through the macro functions and see if you can find a way to build the month mun in a similar way to how I'm building the year mun. The example I gave for the year includes hardcoded month and day values (since every year starts and ends on the same day). You will need to find a way to make those dynamic.

[_jl_]

Tried to make just a crosstab with the expression you gave me:
item(descendants(#'[cube_name].[Dates].[Dates].[Year]->:[PC].[@MEMBER].['+timestampMask($currentTimestamp,'yyyy')+'0101-'+timestampMask($currentTimestamp,'yyyy')+'1231]'#,[Cube].[Dimension].[Hierarchy].[Month]),#timestampMask($current_timestamp,'mm')#-1).

Months in the rows, and one measure in a column. No luck.

[CognosPaul]

it's $current_timestamp

That expression you referenced is only to find the current month. Once you have the current month, use lastPeriods(3,[DATA ITEM]) to show the last three months.

[_jl_]

Thank you. It is working!  ;D

Still, I would like to clarify one thing. Since this (and very many other of our reporting packages/cubes/...) is "a powercube" (at least the members have that "[PC].[@MEMBER]"-thing in the MUN), is there some functions that work only on these kind of packages, some that doesn't or some that I should not be using? Besides detail filters.

And how about when it's not a powercube or other dimensional package (however you call 'em, I'm not sure)?

Thanks again,
jl

[CognosPaul]

If you're using Cognos 10, in the functions list there is a folder for "Common Functions" and "Dimensional Functions".

When using a dimensional model (PowerCubes, DMR, and so on) stick with the dimensional functions. If needed, you can use most of the math related common functions. DMR will work with most of the common functions, but still avoid when possible.

Relational models shouldn't use dimensional functions at all.

Basically Dimensional functions are MDX, and relational functions are SQL. They may look the same, but they are different beats entirely.

I strongly recommend reading up on dimensional reports. While cubes have the potential of being lightening fast, a poorly designed query might kill your server. There are plenty of posts on this forum detailing dimensional theory and basic (and advanced) ways of using them.

[cognosbi.dwh]

How to create YTD by using Dimensional Functions in Cognos

Hi,

I have Year Hierarchy in Time Dimension, I want to show data for selected Year
For example, If I Run the Report in August & I selected the Year 2013 then I want to get data from Jan 2013 to Aug 2013
If I select the Year 2012 then I want to get data from Jan 2012 to Aug 2012

If I run the Report in next month i.e., September & I selected the year 2011 then I want to get data from Jan 2011 to Aug 2011

For Year selection I created below DataItem, it's working fine for me
.
Tuple ([Sales].[Time].[Year]->[All].?Year?,[Revenue])

I created Other DataItem SelectedMonth as

[Sales Analysis].[Time].[Month]->?Month?

Using above I created below DataItem as

periodsToDate([Sales Analysis].[Time].[Year].[Year],[Selected Month])

Here am getting the error

Can anyone suggest hot to do same for YTD

Thanks,
SSR