## How to filter a time interval in a dimensional report

Hi,

today we will filter on a time intervall in a dimensional report with the help of two parameters. When you are developing a dimensional report (DMR or OLAP) then you have to create so called member sets, which you then can query. Example: You are creating a meber set fo rthe current year and then you can calculate the turnover for this member set. The code looks like this: total([Umsatz] within set [<MemberSet>]

With this style of developing reports you are facing a problem right now, i.e. that you have to define explicitly the items you need for the calculations. For example if you want to query februrary to november of a certain year then you have to explicitly define all these months. In case of a time dimension this is not common and also not very practical. What you want is to just select the start and end month and months in between should be selected automatically.

If you were creating a relational report then you would have used the BETWEEN operator. This is not possible in dimensional report development, but what you can do is to create the meber set with the help of some functions. We will use the following functions:

• Lastperiods(<no. of periods>, <last item>)
• Operator  ->
• Intersect (<Set1>, <Set2>)

The function lastperios (…) lets you define a set, which ends with <last item> and contains the <no. of periods> previous members. Lastperiods(12, [Dec. 2013]) would contain all months in 2013. If you chose a negative value for <no. of periods> then you receive the following months.

The operator -> picks a specific meber in a hierarchy. You can also use parameters with it, e.g. [Dimensional layer].[Date (Month)].[Date (Month)].[Month]->?pMonth? It possible to combine the function and the operator in order to receive for example 3 months before the selected month.

To eventually define our interval we a third function: intersect(…). This functions returns the intersection set of two given sets., i.e. the mebers that are existing a both sets are returned. If we have two sets: Set1=(1,2,3) & Set2=(3,4,5) then the intersection is (3) because this ist he only item which is contained by both sets.

To filter on a time inteval we can now build two sets:

• Set1 = lastperiods (-120; [Dimensional].[…..].[Month]->?pMonthStart?)
• Set2 = lastperiods (120; [Dimensional].[…..].[Month]->?pMonthEnd?)

Presented in a picture it looks like this:

To create the result set we use the intersect function on the two sets. And then we can do the calculation on the result set.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

## Zeitliche Intervalle filtern in dimensionalen Abfragen

Hallo zusammen,

heute wollen wir in einem Report ein zeitliches Intervall filtern anhand von zwei Parametern, die der User auf der Eingabeaufforderungsseite füllt. Wenn man einen Bericht dimensional entwickelt (egal ob auf DMR oder OLAP) erzeugt man sog. MemberSets, die dann ausgewertet werden. Man erstellt zum Beispiel ein MemberSet für das aktuelle Jahr und kann dann beispielsweise den Umsatz für dieses MemberSet berechnen lassen. Das geht so: total([Umsatz] within set [<MemberSet>]

Das Problem dabei ist, dass man diese Elemente explizit definieren muss. Wenn man z.B. den Februar bis November eines Jahres entsprechend auswerten möchte, dann müssen alle Monate explizit angegeben werden. Das ist im Falle einer Zeit-Dimension unüblich und unpraktisch. Man möchte sicherliche eher nur den Anfangs- und Ende-Monat angeben. Die dazwischenliegenden Monate sollen automatisch mitselektiert werden.

Um dieses zu erreichen kann man nicht den BETWEEN-Operator verwenden (wie bei relationaler Abfrageerstellung), sondern muss sich eine entsprechendes MemberSet mit Hilfe verschiedener Funktionen zusammen bauen. Dazu benötigen wir folgende Funktionen:

• Lastperiods(<Anzahl Perioden>, <Letztes Element>)
• Operator  ->
• Intersect (<Set1>, <Set2>)

Mit lastperiods können wir ein Set definieren, dass mit <Letztes Element> endet und die <Anzahl Perioden> Member davor beinhaltet. Mit lastperiods(12, [Dez. 2013]) würden wir z.B. alle Monate des Jahres 2013 erhalten. Mit dem Operator -> kann ein spezifischer Member rausgepickt werden. Man kann den Operator auch mit Parametern kombinieren, z.B. [Dimensional].[Datum (Monat)]. .[Datum (Monat)].[Monat]->?p_MonatVon?. Man kann beides auch kombinieren, um z.B. 3 Monate vor dem gewählten Monat zu bekommen. Wenn man für <Anzahl Perioden> einen negativen Wert angibt, arbeitet die Funktion vorwärts, liefert also die n folgenden Perioden.

Um unser Intervall definieren zu können, benötigen wir noch eine dritte Funktion: intersect. Hiermit erhält man die Schnittmenge zweier Sets zurück. Z.B.: Set1=(1,2,3) und Set2=(3,4,5). Dann erhält man mit intersect die Menge (3), weil nur dieses Element in beiden Sets vorhanden ist.

Nun können wir hingehen und folgende Sets bauen und danach mit intersect die Schnittmenge bilden:

• Set1 = lastperiods (-120; [Dimensional].[…..].[Monat]->?p_MonatVon?)
• Set2 = lastperiods (120; [Dimensional].[…..].[Monat]->?p_MonatBis?)

Man kann sich das Ganze auf dem Zeitstrahl veranschaulichen:

Auf das Ergebnisset, das man sich mit obigen Funktionen erstellt hat, kann man dann entsprechende Rechenoperationen durchführen.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!