DatePicker + Macro to create a picklist with all items of a dimension before or after selected date

    DatePicker + Macro to create a picklist with all items of a dimension before or after selected date

    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 fnsl,

    first I would give up the splitting of the 4 period dimensions years, quarters, months and days. I would replace it using a single dimension years => date

    second :

    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.



    both facts (attribute filter + many dimensions) does not have in common : the performance issue comes from elsewhere ; how many elements do you intend to show at once? and how many cells from the cube should be displayed?

    actually I would have done the same with the attribute filter
    laloune

    Post hoc, non est propter hoc
    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)

    Source Code

    1. $dp_stichtag = retrieve_variable('dp_stichtag');
    2. $jahr = date('Y',($dp_stichtag-25569)*86400);
    3. $monat = (int)date('m',($dp_stichtag-25569)*86400);
    4. $arrayBeginn = array();
    5. $arrayEnde = array();
    6. // Ganze Jahre Beginn-Datum
    7. for ($i=2000;$i<$jahr;$i++) {
    8. $arrayBeginn[] = $i;
    9. }
    10. // Monate Beginn-Datum
    11. for ($i=1;$i<=$monat;$i++) {
    12. if ($i < 10) {
    13. $fuehrendeNull="0";
    14. } else {
    15. $fuehrendeNull="";
    16. }
    17. $arrayBeginn[] = $jahr."-".$fuehrendeNull.$i;
    18. }
    19. // Monate Ende-Datum
    20. for ($i=$monat;$i<=12;$i++) {
    21. if ($i < 10) {
    22. $fuehrendeNull="0";
    23. } else {
    24. $fuehrendeNull="";
    25. }
    26. $arrayEnde[] = $jahr."-".$fuehrendeNull.$i;
    27. }
    28. // Ganze Jahre Ende-Datum
    29. for ($i=$jahr+1;$i<2100;$i++) {
    30. $arrayEnde[] = $i;
    31. }
    32. // Zuweisung zur Verwendung im Bericht
    33. define_variable('beginn',$arrayBeginn);
    34. define_variable('ende',$arrayEnde);


    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

    Post was edited 1 time, last by “fnsl” ().