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)!