Hi all,
I would like to "slice"(or filter) a query according to the only child of an element.
Example:
-Time Dimension
-> Year 2013
-> 2013/01
-> 2013/02
-> 2013/03
-> Current Month
-> 2013/03
'2013/03' is the only element of the "consolidated" element Current Month.
And I would liek a filter in a query that dynamically filters on the only element of "Current Month"
I tried a slicer like "set ( firstChild( [Current Month]) ) but it does not work. All other time elements are shown in my report.
Do anybody have an idea?
What happens when you drag in just the [current month] mun?
It filters on [Current Month] which is technically "correct" but it is not right in my case. Because the "attributes"of the element do not contain the same data at the consolidation level.
Sorry, I'm not understanding you say "you are slicing the data for current month which should give you information for just that Mun" but, you have to some other data items referencing at consolidation level is that a correct assumtion? In that case rather then using a slicer or filter you might want to create tuple function for objects with the current month as a filter you create an intersection. While other elements which don't come in the Current month tuple would be consolidated, hope this makes sense.
Thanks,
RK
I am getting both are returning different data?
Also, what is the source of your cube? is it a DMR or some other multidimensional data source?
If it is a DMR, you can try adding a filter for current month and remove the slicer.
Regards,
Rahul
@RKMI:
I will never need to access the consolidated level "Current month". Only the unique leaf which is updated every month.
I don't understand the "tuple" that you suggested.
@RKMI
The data source is TM1.
Just to clarify one aspect, ideally I would use a "subset" in TM1 called "current month" but because TM1 subsets aren't exposed to Cognos Report Studio, I can "mimic" subsets using a hierarchy. I have exactly the same situation with the consolidation "Current FY", which includes 12 elements (months), updated every year.
The TM1 server is knows how to create a new "current month" every month and a new "current FY", every year.
I am sorry if I am not so clear.
Hi,
Yes I have faced this same issue before with TM1, in the end we asked our TM1 developer to make a current month bucket which doesn't take consolidation;
Let's use your example current month is 032013 and current state, I'm guessing in TM1 looks like Current month is 032013 where 032013 is a consolidated from 012013,022013 and 032013. Which really is current month YTD. The solution we implemented was during cube rebuild of reporting purpose make new current month which doesn't do consolidated but takes the inputed value for current month during build. In short you might want to take this back to the TM1 cube modeller. Otherwise you do something like give me the caption of the child of the current month and -> to the time dimension where it looks for the mun based on caption. Unfornately I don't remember that specific syntax.
Thank,
RK
Thanks RK for your advice.
I may follow your idea.