Find occurrences of a Dimension code without hierarchy

DeepakK
DeepakK
in Modeling

Hello all,

I'm stuck on something. I have a Job dimension that has Job code and Job name. (100 - Tech I)

That Job flat list is not in a hierarchy.

But I've created another list called JobL5 that has the Department as a Parent. Each dept will have several jobs listed underneath. And the same job can be in several depts.

I want to create a dashboard where I can click on the Job code and have another grid show me all the different depts that have that job.

Any thoughts?

Answers

  • rob_marshall
    rob_marshall

    @DeepakK

    I assume the Display Name for the Job in the hierarchy is list formatted to the Job Flat list? If not, please do that. Same thing with the Department L4, also assuming you have a Dept Flat list.

    Create a SYS Job Code L5 properties module that is only dimensionalized by Job Code L5. Create a line item called Parent with the formula being parent(item(Job Code L5)). Create another line item called Link to Department Flat with the formula being: Department L5.Display Name[lookup: Parent]

    Create a module that is dimensionalized by Job Code L5 and Department Flat. Create a line item (boolean formatted) with the formula: SYS Job Code L5.Link to Department Flat = item(department Flat) and use this for your filter.

    Pretty sure that will work.

    Rob

  • DeepakK
    DeepakK

    @rob_marshall I tried this and it's not working quite right.

    When you say Display Name for Job in hierarchy is List formatted, is that the list property in Job hierarchy?

    I have a properties module with Item(Job L5) which I'm using instead. Will that not work?

  • rob_marshall
    rob_marshall

    @DeepakK

    I believe you said the Job L5 is a numbered list, is that correct? If so, you will/should have a Display Name property to display the name - that should be list formatted to Job Code Flat. Your next question is why am I asking for the Display Name to be list formatted instead of Text. Well, first, text is a performance hog. Second, if the name of the job code ever changes, you change it in one place (Job Code Flat) and it will automatically change it every where. If that display name is text formatted, then you will have to run a job/action to update the name. Lastly, having it as list formatted back to the flat list gives you a breadcrumb to get any metadata information stored about that job code on the SYS Job Code module without having to use a finditem()

    And I think I told you incorrectly above, the filtering module should be Job Code Flat by Department flat.

Categories