my company uses an unbalanced hierarchy ie for any given level there are members that are not used and the caption is blank. how do i build a slicer to filter out all the members with blank captions? i am using this list for a drop down prompt. right now the prompt box is populated with every level and every descendant which includes the blank members
see the hierarchy below. in this example, category 2 product does not use the item 2 and 3 levels. the member exists, but since it is not used, the caption is blank.
-product hierarchy
-prod hierarchy 1
-prod 1
-category 1
-item 1
-item 2
-item 3
-category 2
-item 1
-------
-------
-prod 2
-category 1
----------
hopefully i explained it correctly.
CAPTION([FIELD]) not startwith ' '
tried that couple ways and couldn t seem to get it to work.
i pulled in a query calculation into my data items of my query. selected set expression. then entered:
descendants ([Sales].[Product].[By Product Hierarchy] -> ?P_Product_Hierarchy?, 16, self beforewithmember)
then right clicked on set definition and added a filter:
selected caption radio button, does not begin with operator, and ' ' as value.
then i also tried adding a slicer with this expresssion:
caption (descendants ([Sales].[Product].[By Product Hierarchy] -> ?P_Product_Hierarchy?, 16, self beforewithmember)) not startwith ' '
this expression works correctly:
descendants ([Sales].[Product].[By Product Hierarchy] -> ?P_Product_Hierarchy?, 16, self beforewithmember)
it returns all members of the selected hierarchy. only issue is i cant eliminate the blank ones.
Quote from: avuong6 on 09 Sep 2015 08:42:34 AM
tried that couple ways and couldn t seem to get it to work.
i pulled in a query calculation into my data items of my query. selected set expression. then entered:
descendants ([Sales].[Product].[By Product Hierarchy] -> ?P_Product_Hierarchy?, 16, self beforewithmember)
then right clicked on set definition and added a filter:
selected caption radio button, does not begin with operator, and ' ' as value.
then i also tried adding a slicer with this expresssion:
caption (descendants ([Sales].[Product].[By Product Hierarchy] -> ?P_Product_Hierarchy?, 16, self beforewithmember)) not startwith ' '
this expression works correctly:
descendants ([Sales].[Product].[By Product Hierarchy] -> ?P_Product_Hierarchy?, 16, self beforewithmember)
it returns all members of the selected hierarchy. only issue is i cant eliminate the blank ones.
How about
filter(descendants ([Sales].[Product].[By Product Hierarchy] -> ?P_Product_Hierarchy?, 16, self beforewithmember),caption(currentMember([Sales].[Product].[By Product Hierarchy])) is not null)
You'd need to use this as the expression in your query calculation, and you can then delete the filter you defined (and the slicer).
MF.
just tried it and it didnt work. here my steps:
pulled in query calculation. selected set expression.
entered this code:
filter ( descendants ([Sales].[Product].[By Product Hierarchy] -> ?P_Product_Hierarchy?, 16, self beforewithmember), caption ( currentMember ( [Sales].[Product].[By Product Hierarchy] ) ) is not null)
code executes, but filter part doesnt seem to work. i still get the blank members.
how do you attach an image into a post? nothing happens after i select file to attach from my desktop.
Quote from: avuong6 on 09 Sep 2015 10:53:40 AM
just tried it and it didnt work. here my steps:
pulled in query calculation. selected set expression.
entered this code:
filter ( descendants ([Sales].[Product].[By Product Hierarchy] -> ?P_Product_Hierarchy?, 16, self beforewithmember), caption ( currentMember ( [Sales].[Product].[By Product Hierarchy] ) ) is not null)
code executes, but filter part doesnt seem to work. i still get the blank members.
how do you attach an image into a post? nothing happens after i select file to attach from my desktop.
Instead of
is not null, try replacing that part of the expression with
<> ''MF.
ideally,
filter ( descendants ([Sales].[Product].[By Product Hierarchy])) <> ' '
as a new query, and use this as query source for your value prompt.
Quote from: sdf on 09 Sep 2015 09:26:13 PM
ideally,
filter ( descendants ([Sales].[Product].[By Product Hierarchy])) <> ' '
as a new query, and use this as query source for your value prompt.
Won't work, I suspect - you are comparing MUNs to a literal value here, plus you have the close parenthesis in the wrong place, plus the filter expression <> '' doesn't relate to anything . You'd need to use a Caption() function or roleValue() to extract the caption for the filter and include this in the expression.
MF.
<> ' ' worked.
Quote from: avuong6 on 14 Sep 2015 02:48:50 PM
<> ' ' worked.
Which expression?
filter ( descendants ([Sales].[Product].[By Product Hierarchy])) <> ' 'or
filter ( descendants ([Sales].[Product].[By Product Hierarchy] -> ?P_Product_Hierarchy?, 16, self beforewithmember), caption ( currentMember ( [Sales].[Product].[By Product Hierarchy] ) ) <> ' ')I assume the latter, since the former wouldn't appear to be a valid expression?
MF.
yes latter one...
filter ( descendants ( [Sales].[Product].[By Product Hierarchy] -> ?P_Product_Hierarchy?, 16, self beforewithmember ) , caption ( currentMember ( [Sales].[Product].[By Product Hierarchy] ) ) <>' ' )
hey thanks so much for the help. really do appreciate it.