Set Expression for first ?n? months of each year

[cogswell]

My cube's Time dimension has Year and Month levels in the Years hierarchy. How do I create a set of the first ?n? (parameter) months of every year? I tried fiddling around with firstChild, cousin, etc. in combination with descendants, but to no avail.

[EDIT] Actually, now that I think about it, my requirements will not be satisfied by just a dynamic set of months. My cube has 3 years' worth of data right now (including the current year).

I am creating a YTD-YOY crosstab with 'Years' as the columns (with no nesting of Months). If the user selects 'July' from a value prompt, the crosstab column elements should be shown as years (2009, 2010, 2011) but the crosstab should contain only partial year data (Jan-July 2009 and Jan-July 2010). For a one-up: Optionally, how do I include the 2011 months (to-date) even though the cube only has June data because July hasn't come around yet?

To eliminate yearly maintenance, I want to avoid creating static sets for each year and shoving them into the crosstab.

Thanks to all who reply!

[cogswell]

Bump... anyone? PaulM? MFGF? Help!

[CognosPaul]

This is a perfect example of when the Generate function is useful.

Consider the following statement:

Code Select Expand


generate
(
   [great_outdoors_company].[Years].[Years].[Year]
   , head
     (
        descendants
        (
           currentMember([great_outdoors_company].[Years].[Years])
           , [great_outdoors_company].[Years].[Years].[Month])
     , #prompt('n','integer')#
     )
)

The generate function (generate(set1,set2),prompt) will evaluate each set2 for each member in set1. Set 2 is head(descendants(currentmember(year),monthlevel)). So it will find the months of the current member in set1, and return the first n values.

[cogswell]

PaulM, the Banksy of Cognos. Thanks! :D

[eliza_jane]

Hey Paul, that was a great learning for me.

If say the users need to be prompted on the month names and the report needs to display the selected months across years, how do we do that? Also if the cube is like Go Sales where the caption and business key all include the year component with the month.

[CognosPaul]

Almost anything is possible, but for something like this you'll need to change the structure of the cube. This is just one of my many complaints about powercubes (time dimensions don't meet all requirements and are unreasonably difficult to change).

There are a couple of work-arounds if you absolutely must build the crosstab with months.

The wrong way:
use a substring expression to return only the name of the month. The problem with this is that it forces Cognos to process the data locally, which can be a huge problem in environments with lots of users or if the crosstab has a lot of data.

The less wrong way:
You can create twelve new data items, with expressions like:

Code Select Expand

member(tuple(currentMeasure, item(descendants(currentMember([great_outdoors_company].[Years].[Years]),[great_outdoors_company].[Years].[Years].[Month]),1)),'Jan','Jan')

You can then treat those members as normal, creating a set and filtering them if necessary.

Ultimately, however, a redesign of the cube is necessary if you need to do this on a regular basis.

[eliza_jane]

Hi Paul, That was a great suggestion.

There are a couple of issues that I am running into though. Hope you can help me.. The expression throws an error on using currentMeasure.

"Unable to support query items from the same dimension on different report dimensions: dimension="[MEASURES]"; edge="axis(0)"; edge="axis(2)".

When I replace with the actual measure say Revenue it works.

Also, how should I filter the set?

[CognosPaul]

Can you post the full expression, or the XML? I can't really tell what's going on from just the error.

[eliza_jane]

Hi Paul, I have attached the report xml. Let me know what I am doing wrong here.

[CognosPaul]

I worked on this earlier today and to my surprise it wasn't working the way I expected it to. I wasn't getting the error you posted, but putting the members in a set just wouldn't work.

I'll continue working on it more next week, but my initial thing about needing to change the cube is even more important now.