Filter/Suppress Category in Transformer

[jodjim]

My cube has DeptCode category with several depts. I want to create a dimension which will only show DeptCode 01, for example. I tried using Exclude option but it excludes the other depts in my other dimensions. What's the best way to filter a category.

Thanks

 

[drlex]

Jodjim,
I don't believe you can do this - as you have found, exclusion will remove the information from other categories. The method I use is to insert a higher level and make an "other departments" group that holds all the other departments and then summarize this group.
lex

soi la, soi carré

 

[yksvaan]

Well you always have the total amount in dimension level by design, so the users are able to figure out how much the other depts combined measure amount is (total-dept01). There is no way around this, cubes overall sum is alwayd the same, no matter what categories are visible or not.

You could manually suppress all other depts. In case your depts can change over time (new ones), you could in your data source have the logic that all other depts that 01 has the dept-value of blank (or NULL). Then in the dimension's dept-level have the Inclusion property to be Suppress Blank Categories. Now you only see dept01, but overall sum is still altogether.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[jodjim]

drlex,

I assume you're referring inserting a higher level and grouping other dept in a catalog. Our datamart/ catalogs/iqds were developed by an outside consultant and all I can do right now is playing with categories from these pre-defined data sources. Or, maybe I don't exactly understand what you meant and know how to do it.

As of the moment, I'm suppressing categories down to the lowest level. Not a fun thing to do. (And to think there's a couple more cubes that need same procedures.)

thanks to you and yksvaan.

 

[drlex]

jodjim,
It's as yksvaan states - the totals must be the same in each dimension. If you want a department to see only 'their' results, then that is best done with apexing and user classes or .ppx reports linked to user classes.

I don't need to use the catalogue for grouping - by adding a calculated column to an iqd in transformer such as department grouping ('if department = 1 then 1 else 0') and putting this in a dimension as a level above department. I can then use summarise on the category '0' (labelling it "Other departments") so that all levels below department for departments other than 1 are only visible as the sum. (Category '1' can be relabelled as 'Department 1'). In this way, any new departments will automatically be included in the summary and saves having to manually amend the model.

lex

soi la, soi carré