Hi,

my cube has have several dimensions, two of them are years / quarters / months / day based items. They are the beginn and the end of "events".
I have a date picker in my sheet and now my report only should show "events" in a dynarange that have the beginn date before the date picker and the end date after the time picker.
Actually i solve this via attributes. Each event has date of beginn and end as YYYYMMDD and so attribute filter can calculate. But this is not a nice solution and it seems not to be performant in cubes with many dimensions.
Instead of attribute filter I want to use data filter for this.

For example 2015/03/12 is choosen.
All events that start before the date and end after this date should be shown.

My idea is to create a picklist via macro that includes all items for dimension 2015/03/*, all months of the year before march: 2015-01 2015-02 and all years before 2015: 2005, 2006, 2007 ...
and the same the other way round for the "end"-dimension.

These picklist should be stored as variables or cells in the sheet and the data filter of every dimension can use these variables for the time dimensions.

Are there some kind of "best practices" or some macro code snippets for this?

Hi laloune,

there is one thing I do not like when solving this via attribute:
events-dimension has this attribute.
In my reports i have two dynaranges (as example)
Dynarange 1: organisational area
inside Dynarange 1 is Dynarange 2: Events (and many details = attributes for each event) for Organisational Area
Also inside Dynarange 1 (not Dynarange 2) is Dynarange 3: responisble persons which number of events in organistional area for each responsible person.
There are 18 dimensions at the moment that are embedded via comboboxes and all dynaranges filter on it.

Attribute filter can only apply at events-dimension, all other dimensions can be included into each dynarange as data filter. So the report might contain dynarange 1 and dynarange 3 elements that have no content because the event dimension is also filtered with the attribute filter (that is not known to dynarange 1 and 3). So I have to include hide row etc. calls inside my sheet (and it was nice not to need this..).

Removing quarters and months might increase performance. I'll think about this. Quarterly reports are not really needed in my case.

The report is quite dynamically and shows up so some hundreds events depending on the combobox filters.
I tested this with a cube that conatins 100k events - the report loads after 10-20 seconds, that seems to be ok.

Now my idea is to create a macro that has a (maybe huge) list of elements of each time dimensions as output that can easily be included into the data filter. creating several reports would be much more easy them...

OK, solved..

I created a datepicker then I added a macro that creates an array that is used for beginning and ending dimension in Datafilter of my events.

Maybe this snippet can help someone facing the same problem:

@dp_stichtag is the variable the datepicker puts the chosen value to
the year dimensions go from 2000 to 2099 (for the example) and have year (yyyy) => quarter (QQ-yyyy) => month (yyyy-mm)



this way filtering not for days but for months seems to work quite performant. for day-filtering (e.g. show events that were active at 2015-02-07) attribute filters could be used additonally