Dynamically Filtering of Dimensional Attributes...

[IceTea]

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.  :(

[MFGF]

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.

[IceTea]

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?

[MFGF]

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.

[IceTea]

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)

[MFGF]

Glad you got it working!  ;D