how to exclude a value from a set within cross tab

[mrmo]

I have a cube that I have been asked to work with. in it is a hierarchy of stores, and a hierarchy of customer types.

I have created a slicer/set that includes some of the customer types, these don't change so no problem.
The problem I am having is I need to exclude some stores, I can't create an inclusive slicer/set.

I can't seem to come up with a way of excluding a specific value form a set that I can't explicitly define.

Can anyone offer guidance, please/

[MFGF]

I have a cube that I have been asked to work with. in it is a hierarchy of stores, and a hierarchy of customer types.

I have created a slicer/set that includes some of the customer types, these don't change so no problem.
The problem I am having is I need to exclude some stores, I can't create an inclusive slicer/set.

I can't seem to come up with a way of excluding a specific value form a set that I can't explicitly define.

Can anyone offer guidance, please/

Hi,

Not sure I'm quite following, sadly.

You have a defined an explicit set of customer types and used it as a slicer member set? Ok - that's straightforward. So in addition to this you want to display measures in context for some of the store members but not all of them, based on exclusion rather than inclusion? Am I understanding correctly? If so, are the store members in a level in a different hierarchy from the customer types members?

I'm going to make a wild guess and assume so. I'm such a reckless muppet! :) So all your store members are defined (I assume) in a Store level? If so, you can create an expression to exclude certain stores from this level set using an except() function

eg except([your Stores level or set of members], set([Your first store member to exclude],[Your second store member to exclude],[Your third store member to exclude]))

You can then use the resultant set as a second slicer member set, and you should be good to go. :)

Cheers!

MF.

[mrmo]

thanks, sorry i was in a rush and could have been clearer.

Try again.

I am new to report studio so still learning a lot!

I have sales against a number of stores currently 50ish, however some more will be opening soon.

I have figured out i can create a set of all stores today, but i would have to revisit and amend the set to include the new stores when they open. So i can use the slicer to bring back only sales against open stores in an explicitly stated set.

I can create a set of closed stores, (these are the ones i want to exclude) It doesn't happen that often and no where does the model explicitly state a store is shut, and i am not able to amend the model. (It is held in the underlying database so why wasn't it brought through????)

The model brings in the new stores as soon as they start trading.

I just can't seem to figure out if i can create a dynamic set of open stores that will doesn't involve me adding name to a list. either by saying look at the hierarchy and ignore the shut ones or another method i have thought of.

[mrmo]

This is what I am trying to achieve, but it doesn't work.

Any guidance as to what I have done wrong please.

except(set(descendants([Customers Cube].[Stores].[By Store],1)),[01 Wenvoe])

[MFGF]

thanks, sorry i was in a rush and could have been clearer.

Try again.

I am new to report studio so still learning a lot!

I have sales against a number of stores currently 50ish, however some more will be opening soon.

I have figured out i can create a set of all stores today, but i would have to revisit and amend the set to include the new stores when they open. So i can use the slicer to bring back only sales against open stores in an explicitly stated set.

I can create a set of closed stores, (these are the ones i want to exclude) It doesn't happen that often and no where does the model explicitly state a store is shut, and i am not able to amend the model. (It is held in the underlying database so why wasn't it brought through????)

The model brings in the new stores as soon as they start trading.

I just can't seem to figure out if i can create a dynamic set of open stores that will doesn't involve me adding name to a list. either by saying look at the hierarchy and ignore the shut ones or another method i have thought of.

Hi,

I think the key to this conundrum lies in whether or not there is a way to determine whether a store member is an open store or a shut store. Is there an attribute you can use? I'm thinking not based on the

no where does the model explicitly state a store is shut

section of your post above?

So currently you are manually defining an explicit set of open stores and using these in your slicer? And you keep having to revisit this set to amend it as new stores open? What is the situation when a new store appears in the level - would this normally be an open store or a shut store?

If it would be an open store (and therefore a store you'd need to add to your manual set of open stores), why couldn't you use the approach I outlined in the last post?

except([Your stores level],[your manually defined set of shut stores])

This would remove all the shut store members from the stores level, leaving all other stores in your set, and as new stores appear in the level they will appear in your slicer too (assuming they are not in the shut stores set).

I'm still not sure if I'm quite understanding, sorry. Just put it down to senility :)

MF.

[MFGF]

This is what I am trying to achieve, but it doesn't work.

Any guidance as to what I have done wrong please.

except(set(descendants([Customers Cube].[Stores].[By Store],1)),[01 Wenvoe])

Hi,

What does "it doesn't work" mean? Do you get an error? An empty result? A result containing incorrect members? Something else?

Why are you using the descendants() function here? What is [Customers Cube].[Stores].[By Store]? Is it a level? A hierarchy? What is [01 Wenvoe]? Is it a calculation? A level? A hierarchy? An attribute? A member? :)

It's very difficult to know what is wrong unless you tell us what you did behind the scenes and describe what these items are...

MF.

[mrmo]

It gets an error message. invalid coercion from memberset.

Still learning this,

The hierarchy is [Customers Cube].[Stores].[By Store], one member is '01 wenvoe'
The use of descendants is because I used this in the report to bring back the list of names in the cross tab. The assumption was I could use it to create a set? I am new to this so bare with me if I use the wrong terms.


If I write in the slicer

except(set([01 wenvoe],[[02 Gloucester]),[01 wenvoe]) this returns the sales for only [02 Gloucester]


I guess the question is how do I create the set with out listing the names.


I have tried a different approach and created a detail filter, would still like to know what I was doing wrong though.

descendants([Customers Cube].[Stores].[By Store],1)<>[01 Wenvoe]

and this does what I expect, Ie brings back all sales, I am using a net sales measure in the cross tab, and does not include any reference to [01 wenvoe]

thanks for your patience

[MFGF]

It gets an error message. invalid coercion from memberset.

Still learning this,

The hierarchy is [Customers Cube].[Stores].[By Store], one member is '01 wenvoe'
The use of descendants is because I used this in the report to bring back the list of names in the cross tab. The assumption was I could use it to create a set? I am new to this so bare with me if I use the wrong terms.


If I write in the slicer

except(set([01 wenvoe],[[02 Gloucester]),[01 wenvoe]) this returns the sales for only [02 Gloucester]


I guess the question is how do I create the set with out listing the names.


I have tried a different approach and created a detail filter, would still like to know what I was doing wrong though.

descendants([Customers Cube].[Stores].[By Store],1)<>[01 Wenvoe]

and this does what I expect, Ie brings back all sales, I am using a net sales measure in the cross tab, and does not include any reference to [01 wenvoe]

thanks for your patience

Hi,

Ok - the error first. The expression you wrote uses a descendants() function and this expects the first argument to be either a set/level or an individual member. You used the hierarchy, which is not valid as the argument. That explains your error :)

Question. Do you see levels within your hierarchy when you expand it in the package tree? If so, what are they? Eg, do you see the kind of structure shown below?



If so, you should have a level for Stores, hopefully? If you do, you can use this in an except() function as the first argument, and use your manually defined set of shut stores as the second argument.

Oh. Don't use detail filters with a dimensional package. If you want to see why, take a few minutes to read FAQ#4 in the FAQs thread:

http://www.cognoise.com/index.php/topic,27563.0.html

Cheers!

MF.

[mrmo]

https://goo.gl/photos/e7up1yCHGCvPgt977



Hopefully this will work.

this is what I have, and an expression that doesn't error, but doesn't actually exclude either,

[MFGF]

https://goo.gl/photos/e7up1yCHGCvPgt977



Hopefully this will work.

this is what I have, and an expression that doesn't error, but doesn't actually exclude either,

Hi,

The "By Store" level you used appears to have a single member within it - called "By Store" (you can see this at the top of the member tree you have expanded). This is why you are not getting the result you think you should.

I'm guessing there is another level below "By Store"? Try using this level in your expression. From what I see in the member tree, it contains your store members.

Cheers!

MF.

[mrmo]

Ok, I have one more level beneath the by store, as you guessed store.

So I have amended the slicer,



But it still doesn't seem to work??? any ideas what I am doing wrong????

I noticed that for the data item store, in the properties there is a set definition option, if I use this to exclude an item it works. Is this safe?



thanks again,

[MFGF]

Ok, I have one more level beneath the by store, as you guessed store.

So I have amended the slicer,



But it still doesn't seem to work??? any ideas what I am doing wrong????

I noticed that for the data item store, in the properties there is a set definition option, if I use this to exclude an item it works. Is this safe?



thanks again,

Hi,

What does "it doesn't seem to work" mean? Can you be specific about the results you are getting?

Ok - as a sanity check, delete your slicer member set from the Slicer area of your query. (so you have no slicer). Run the report and note the measure values in the intersections (or the totals). Go to the toolbox and drag in a new slicer member set from scratch. Code the expression as you show here. Run the report again and see if the measure values have changed.

I just tried exactly this in my instance and it shows different values before and after the slicer is defined.

One other thought - is Wenvoe a store with no measure values? If so, excluding this will not affect the measures in your report...

MF.

[mrmo]

When I said it doesn't appear to work, I meant I was expecting the slicer to remove wenvoe but it was the same as without the slicer?


On to the second part

with no slicer '01 wenvoe' net sales 142,438.24

with the following slicer

except([Customers Cube].[Stores].[By Store].[Store],[01 Wenvoe])

exactly the same value

if I define the slicer as

[01 Wenvoe]

I get one row back, and the same value.

[MFGF]

When I said it doesn't appear to work, I meant I was expecting the slicer to remove wenvoe but it was the same as without the slicer?


On to the second part

with no slicer '01 wenvoe' net sales 142,438.24

with the following slicer

except([Customers Cube].[Stores].[By Store].[Store],[01 Wenvoe])

exactly the same value

if I define the slicer as

[01 Wenvoe]

I get one row back, and the same value.

Hold on. Are you saying the stores are displayed in your crosstab? Slicers are designed to affect the measure values but not the members displayed on the edges (rows and columns). Can you be specific about what is being used in your row/column headings?

If you are currently using the Store level as a row or column heading, delete it, and replace it with a query calculation (type "other") that uses the except() expression above. Oh, and delete this from the slicer.

Otherwise, tell us in detail what's going on in the report (what you have currently and what you want to get) and we'll try to help.

Cheers!

MF.

If you are current

[mrmo]

might start to explain what has gone wrong, as said I am still learning!

Across top YTD and LYTD
down side store names,
and the measure is net sales.

I created a slicer to remove one category of customer by explicitly stating the 4 categories to keep and left out the one I didn't want from the set.

I then wanted to remove the store, this I tried to do by using another slicer, but if I understand you, because I wish to display the store names I can't use a slicer?

[MFGF]

might start to explain what has gone wrong, as said I am still learning!

Across top YTD and LYTD
down side store names,
and the measure is net sales.

I created a slicer to remove one category of customer by explicitly stating the 4 categories to keep and left out the one I didn't want from the set.

I then wanted to remove the store, this I tried to do by using another slicer, but if I understand you, because I wish to display the store names I can't use a slicer?

Hi,

As you're not displaying customer members in your rows or columns, a slicer is exactly the right thing to use to limit the context of the measures displayed to be for the four customers you desire. This part is perfect.

However, you're displaying store members in your rows, and slicer filters are designed only to define context for the measures (and only the measures) in your report. They do not have any impact on the members displayed in your row or column headings. So here you can see that a slicer filter is not the correct approach to limit the stores displayed in your rows.

One of the most difficult parts of getting to grips with dimensional reporting techniques (after working previously with relational reports) is in understanding that you can choose individual members or sets of members from a level, rather than bringing all values in via a single item and filtering (which is the relational paradigm). So here, I'm willing to bet you have brought in the entire Stores level for use in your row headings, and now you're trying to figure out how to filter out the ones you don't require? Looking at my previous sentence, hopefully you can see that this is a relational reporting technique, and we can do something much more elegant with a dimensional package.

So - the thing to do is firstly to delete the Stores level from your row headings. Then, in its place, insert a query calculation from the toolbox. Set the type to be "Other expression" and click OK. At the top of the expression dialog, give it a name (Open Stores, maybe?) then code the expression as except(<drag in your Stores level from the package tree here>, set(<drag in your first closed store member>,<drag in your second closed store member>,<drag in your third closed store member etc>))

In doing this, you are asking your data source (cube) to provide only the stores you have defined your expression to retrieve. Hopefully you can appreciate that this is a much more efficient (and elegant) process than bringing in all the stores and filtering. :)

Cheers!

MF.

[mrmo]

Thank you very much, worked perfectly and more importantly I learnt something.

[MFGF]

Thank you very much, worked perfectly and more importantly I learnt something.

Outstanding! On both counts! :)

Thanks for the update.

Cheers!

MF.