defining slicer to filter out data

[avuong6]

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.
 

[sdf]

CAPTION([FIELD]) not startwith ' '

[avuong6]

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.

[MFGF]

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.

[avuong6]

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.

[MFGF]

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

Code Select Expand

is not null, try replacing that part of the expression with

Code Select Expand

<> ''

MF.

[sdf]

ideally,

filter ( descendants ([Sales].[Product].[By Product Hierarchy])) <> ' '

as a new query, and use this as query source for your value prompt.

[MFGF]

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.

[avuong6]

<> ' ' worked.

[MFGF]

<> ' ' worked.

Which expression?

Code Select Expand

filter ( descendants ([Sales].[Product].[By Product Hierarchy])) <> ' '

or

Code Select Expand

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.

[avuong6]

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.