Using A Filtered Slicer to Dynamically Slice A Crosstab Results in null value

[SpareTire]

Hi All,

Trying to determine whether the issue I am having is a reporting one or related to the pacakge itself. My end goal is to aggregate information at the top member of a hierarchy by filtering out lower levels using their attributes (without bringing those lower levels into the report).

To make this simplistic I first concentrated on using one member (before expanding to multiple).

I first tested that this would work by dragging the member directly from the tree into the slicer. This results in success---See Picture 1.

I then created a filter expression based on a simple attribute (in the case project name) and ran a tabular data to ensure that it worked correctly:
(Filter([Hierarchy],[Attribute]='12000.201')
See Picture 2

I then brought that expression back as a slicer in the original query and that results in a null value. See Picture 3

My hypothesis originally was that it brought back null values because my filtered query wasn't bringing back a useable MUN. So I tried a few things:

• Using the member function which resulted in an error: "Invalid coercion from 'memberSet' to 'value'"


• Using role value to bring back the MUN itself: roleValue('_memberUniqueName',(Filter([Hierarchy],[Attribute]='12000.201')) which also resulted in an error "Invalid expression for SlicerMemberSet specification, a member or set of members from same dimension is expected"

I am out of ideas at this point on why its a null value. Any clues or suggestions?

[MFGF]

Hi All,

Trying to determine whether the issue I am having is a reporting one or related to the pacakge itself. My end goal is to aggregate information at the top member of a hierarchy by filtering out lower levels using their attributes (without bringing those lower levels into the report).

To make this simplistic I first concentrated on using one member (before expanding to multiple).

I first tested that this would work by dragging the member directly from the tree into the slicer. This results in success---See Picture 1.

I then created a filter expression based on a simple attribute (in the case project name) and ran a tabular data to ensure that it worked correctly:
(Filter([Hierarchy],[Attribute]='12000.201')
See Picture 2

I then brought that expression back as a slicer in the original query and that results in a null value. See Picture 3

My hypothesis originally was that it brought back null values because my filtered query wasn't bringing back a useable MUN. So I tried a few things:

• Using the member function which resulted in an error: "Invalid coercion from 'memberSet' to 'value'"


• Using role value to bring back the MUN itself: roleValue('_memberUniqueName',(Filter([Hierarchy],[Attribute]='12000.201')) which also resulted in an error "Invalid expression for SlicerMemberSet specification, a member or set of members from same dimension is expected"

I am out of ideas at this point on why its a null value. Any clues or suggestions?

Hi,

Which hierarchy is the row member from (01.01.200)? Is this another member in the same hierarchy as the member you are using in your slicer? Is it at the same level?

MF.

[SpareTire]

Hi MF,

They are both in the same hierarchy but at different levels, for example:

1st    01.01.200
2nd   01.01.200.01
3rd    12000
4th    12000.201

I am trying to filter the 4th level by attribute but aggregate at the 1st level.

[MFGF]

Hi MF,

They are both in the same hierarchy but at different levels, for example:

1st    01.01.200
2nd   01.01.200.01
3rd    12000
4th    12000.201

I am trying to filter the 4th level by attribute but aggregate at the 1st level.

Is the 12000.201 member a descendant of the 01.01.200 member? Can you check this in the member tree in your package? If it's a descendant of a different member branch you will get a null value since you're trying to show the measure value for a higher level member in the context of a member not rolling up into it.

Cheers!

MF.

[SpareTire]

Yeah it definitely is. Pic attached.

Edit:
Also, since I dragged in the 4th level members directly from that tree and saw it to be succesful in slicing my data I am convinced that it must be my formula and not an aggregation problem in the package itself. Can it be that since my cross tab has multiple other members (i.e. time dimension) I should be using a tuple instead of a member?

[SpareTire]

Something to note--it is not the formula. I put 3 crosstabs at all 3 levels and proceeded to use the formula above. I got a result at only the lowest level. The upper levels result in nulls. Pic attached.

I am not familiar with building cubes--in a relational model I would look at FM to see the determinants because I would assume that there is something off about the aggregation. Does anyone have any insight into aggregation within a cube? I thought it happened inherently.