How to create a slicer where two different dimensional conditions must be met

[anthus]

I'm really hoping someone out there can help me.  I've been working on this issue for several weeks and cannot figure it out.  I'm using Cognos 10.2.1 FP4 and a dynamic cube.  I need to create a bar chart that shows the number of applications for the last three weeks of the last three terms.  The equivalent crosstab would be

                  Week 1        Week 2       Week 3
2014 Fall        10                20               30
2013 Fall          9                19               23
2012 Fall          8                 20              18

We collect daily snapshots in our data mart so I need to only bring back data where calendar.calendar.load_date = <a date> and term.term.term_cd = <a term>.  The issue I am having is creating a slicer where two different dimensional conditions together must be met.  I can create slicer sets for the dimensions separately but that is not giving me the correct results -- I need both load date and term combinations to be true.   Does anyone know how to do that?  A detailed filter would be as follows but we are not to use detail filters with dimensional data.  So, I need to create this as a slicer, I believe, because the load_date does not show on my bar chart/crosstab.

               ( calendar.calendar.load_date = 2014-10-14 and term.term.term_cd = 2014f )
               or
               ( calendar.calendar.load_date = 2014-10-07 and term.term.term_cd = 2014f )
               or
                ( calendar.calendar.load_date = 2014-10-01 and term.term.term_cd = 2014f )
               or
               ( calendar.calendar.load_date = 2013-10-14 and term.term.term_cd = 2013f )
               or
               ( calendar.calendar.load_date = 2013-10-07 and term.term.term_cd = 2013f )
               or
                ( calendar.calendar.load_date = 2013-10-01 and term.term.term_cd = 2013f )
               or
               ( calendar.calendar.load_date = 2012-10-14 and term.term.term_cd = 2012f )
               or
               ( calendar.calendar.load_date = 2012-10-07 and term.term.term_cd = 2012f )
               or
                ( calendar.calendar.load_date = 2012-10-01 and term.term.term_cd = 2012f )


Thanks so much!

[anthus]

I've tried several things but still not able to get this to work.  I tried a filter () and filter () in my slicer.  I tried a filter ( set from dimension 1) , (set from dimension 2) in my slicer and that didn't work either.  When I put these in as separate slicers I get data but it isn't filtering out where Term *and* Load Date are not met.

Any guru's out there than can help me?  I'm fairly new to dimensional reporting although I have take two dimensional reporting classes.  Dimensional reporting is very frustrating for me.  I've been doing relational reporting for many, many years and I'm finding it difficult to switch my way of thinking.   :-\ Thanks.

[bdbits]

Create a data item that uses a a filter() expression around an edge data item. Use the filter()ed item in the crosstab instead of the data item. The boolean expression should let you apply the logic you need.

If you need more detailed instruction, we'd have to know more about your cube structure. If you are comfortable doing so you could attach a copy of your report spec, which might help someone spot what is not set up right.

[anthus]

Thanks so much for your reply.  My cube is pretty straight forward and simple.  The term is held in one dimension with one hierarchy and 2 levels (term year, and term code) with relative time set.   The load date is held in a different dimension with one hierarchy and a level for year, month, week, and day with relative time set.  My measures are in a single fact table.  The issue I'm having is that a set can only come from one dimension.  I need it to come from 2 dimensions.  If I do a tuple for the data item using both dimensions, I get the correct results but then I cannot change the labels of the outer edge as it wants to label it as the name of the data item instead of the term value (I need it to be dynamic and not a static label).  How do I create a filter () function using two different dimensions (term and load date) and just have the term name show as the label. 

I've tried the following as slicers with no success.  To keep it simple, I was just trying to get the latest term and the latest load date using relative time. 

•   filter ( [ST Applied Metrics Dynamic Cube].[UD_Term].[Term Cd].[Term] ,  roleValue ('_businessKey' , [ST Applied Metrics Dynamic Cube].[UD_Term].[Term Cd].[Term] ) = roleValue ( '_businessKey' ,  [Current Period (2161)] )
and
roleValue ( '_businessKey' , [ST Applied Metrics Dynamic Cube].[UD_Calendar - As Of Date].[UD_Calendar - As Of Date (Calendar Year)].[Full Date] ) =
roleValue ( '_businessKey' , [Current Day] )
)

                Validates but no data returned (separately, the filters work but wrong results.  Filters together I get no data)

•   filter ( [ST Applied Metrics Dynamic Cube].[UD_Term].[Term Cd].[Term] ,  roleValue ('_businessKey' , [ST Applied Metrics Dynamic Cube].[UD_Term].[Term Cd].[Term] ) = roleValue ( '_businessKey' ,  [Current Period (2161)] )
)
and
filter ( [ST Applied Metrics Dynamic Cube].[UD_Term].[Term Cd].[Term] , roleValue ( '_businessKey' , [ST Applied Metrics Dynamic Cube].[UD_Calendar - As Of Date].[UD_Calendar - As Of Date (Calendar Year)].[Full Date] ) =
roleValue ( '_businessKey' , [Current Day] )
)

The argument at position 1 of the function 'and' is invalid in data item 'Data Item7' of query 'qry_weeklyAppNumberComparison', expected one of the following types: 'boolean'.      – Separately, the filters validate just fine but don't give me the correct results

•   tuple (
filter ( [ST Applied Metrics Dynamic Cube].[UD_Term].[Term Cd].[Term] ,  roleValue ('_businessKey' , [ST Applied Metrics Dynamic Cube].[UD_Term].[Term Cd].[Term] ) = roleValue ( '_businessKey' ,  [Current Period (2161)] )
)
,
filter ( [ST Applied Metrics Dynamic Cube].[UD_Term].[Term Cd].[Term] , roleValue ( '_businessKey' , [ST Applied Metrics Dynamic Cube].[UD_Calendar - As Of Date].[UD_Calendar - As Of Date (Calendar Year)].[Full Date] ) =
roleValue ( '_businessKey' , [Current Day] )

)
)

The argument at position 1 of the function 'tuple' is invalid in data item 'Data Item8' of query 'qry_weeklyAppNumberComparison', expected one of the following types: 'member, measure'. – validates fine as a data item but not as a slicer

•   filter ( [ST Applied Metrics Dynamic Cube].[UD_Term].[Term Cd].[Term] ,
tuple ( [Current Day] , [Current Period (2161)] ) > 0
)

I get all the data back – acts as if the slicer didn't apply




[Current Day] is my relative time of today's current load date set in the dimension
[Current Period (2161)] is my relative time for the current term we are in


Any help you can provide is greatly appreciated.

Thanks!