Looking at the level you can't simply pull the tail (unless you know that it will
always be the second to last year that you want). If you can pull that member, you can do this:
current year: item(tail([Cube].[Date].[Date Hierarchy].[Year],2),0)
If it needs to be based on measure data, do something like this:
item(tail(filter([Cube].[Date].[Date Hierarchy].[Year],[Measure] is not null)),0)
Note, in this case filter shouldn't be too much of an issue since you're only looping over 21 members, but in general I strongly recommend against using the filter function like this.
Right click on one of the members in the list and show properties. I'm interested in seeing what the memberuniquename looks like:
This is an example from one of my cubes
If we can base the current year on the system time, we can do something like this:
#'[REPORTING].[R_TIMELINE].[R_TIMELINE].[Fiscal Year]->:[TMR].[R_TIMELINE].[R_TIMELINE].['+timestampMask(_add_days($current_timestamp,-7),'yyyy')+']'#
That returns the current year as of the date 7 days ago. It's significantly faster than doing a filter function (and even a few milliseconds faster than using the item(tail()) combo, but you have to construct the code to match the member perfectly.
------------------------------
Paul Mendelson
------------------------------
Original Message:
Sent: Thu June 02, 2022 07:57 AM
From: P S
Subject: Current Months WRT to Year as columns in cross tab
It's based on the data
------------------------------
P S
Original Message:
Sent: Thu June 02, 2022 07:44 AM
From: Paul Mendelson
Subject: Current Months WRT to Year as columns in cross tab
There are several ways of doing this, but the most important thing here is being able to identify the current year.
Once you can identify the current year, you just need to do
descendants([currentYear],[Cube].[Date].[Date Hierarchy].[Month]) (assuming you have a straight time dimension, and don't have anything weird in it)
First, what type of cube is this? Do you need it to be the current date as of the system time today, or should it be based on data? Can you paste in an example of one of the year MUNs?
------------------------------
Paul Mendelson
Original Message:
Sent: Thu June 02, 2022 07:35 AM
From: P S
Subject: Current Months WRT to Year as columns in cross tab
Hi,
Need to help in figuring these out, I'm using a dimensional data source for the crosstab report.
Cross Tab, Columns needed to the current month and year like 2022-01,2022-02,2022-03......2022-12 these months columns needed to be updated dynamically every year with respect to the current year.
As of now, getting all the months from the hierarchy
needed all months for current year as columns in the cross tab, No prompt values are included.
Above are pulled from current year quarters,these won't work for next year so needed dynamically to change months and year.
Needed help.
Thanks.
------------------------------
P S
------------------------------
#CognosAnalyticswithWatson