Hi folks,
i am in need with a problem:
1.) Data Source are Transformer Power Cubes - so we talk about Dimensional Data
2.) I have a Report with a Crosstab and one Query. The Crosstab shows Measures for several Subcompanies (Rows) and Years (Columns). The Years are part of a hierarchical Time Dimension and are allowed to drill down/up (Year - Quarter - Month).
I want this report to show (automatically) the data for actual years and the two years before. So - today - it shows Data for dears 2007, 2008 and 2009. From the 1.1.2010 on, it should show Data for years 2008, 2009, 2010. The thing is, that i don't want to use a member filter in the Query. cause i don't know to change this reports every year. The idea is to implement an filter, depending on the currentdate. I tried around a lot with all the functions (currentdate, cast, caption...) but it seems that dimensional data has much restrictions or i was not able to identify the correct combination of functions and syntax. Or the solution can be done with a whole different way. ???
Would be happy about some ideas... Thanks. :(
Hi,
Remove the Year level from your column headings and replace it with a Query Calculation (call it Last 3 Years).
Define the expression as follows:
tail ([Your Year Level from the Time Dimension],3)
MF.
Hi,
thank you for the Info - didn't know this function! This fits in most cases, but not in all ;(
In my case there is Data in the Cube for future years, let's say 2010 and 2011. But i don't want to show this future data in the report. The highest shown Year should be the year of the Report-Creation-Date.
Additional hint for me?
Hmm - OK. Try the following:
Leave your Year level defined as the column headings, and define a detail filter as follows:
caption([Year Level from your Time Dimension]) <= extract(year,current_date)
MF.
Got it - thanks a lot! ;)
I had to do a cast/integer on the Year-Level, don't ask me why.
That's the working Filter:
cast(caption([Year Level from Time Dimension]); INTEGER) >= (extract(Year; current_date)-2)
Glad you got it working! ;D