Filtering to bring back different levels in a hierarchy

[Revelator80]

Good afternoon,

I have a ragged hierarchy for departments from an OLAP cube (SSAS). I have a requirement for a dashboard to be produced that can be filtered to bring results back for
1. the entirety of a family (Parent>Children>Grandchildren>(x)),
2. just for the Parent and also
3. just for the Children>Grandchildren>(x).

Can anybody advise how to go about producing this, ideally with a single prompt that can cover a number of queries that bring the data back at the different levels?

Many thanks

David

[MFGF]

Hi,

Do you want your users to choose all of the relevant members, or you you want an automated system for bringing back the desired members? How do you envisage the users will choose whether they just want the parent, or the entire family, or the descendants? Will there be two prompts? Obviously the user needs to choose a starting point member (which is one prompt) - so will there be a second prompt to control what results get returned (parent/family/descendants?) I'm struggling to see how this might be done in a single prompt from a logic standpoint... Can you advise in a little more detail how you envisage it working?

Cheers!

MF.

[Revelator80]

Hi,

Right then, I'll try and explain it but please bear with me for using the wrong terms!

We have data in an parent-child dimension for government departments and their ALBs so, for example:

ID   Name                    ParentID
1    Cabinet Office       1
2    Govt Digital Serv   1
3    DirectGov              2
4    10 Downing St.     1
5    Ministry of Justice 4
6    HMP Liverpool       5

This is in an OLAP cube built in SSAS with a dynamic ragged hierarchy which has a Framework package over the top of it (although, as you know, this doesn't do anything).

The requirement is for a dashboard with a single prompt where a user could select "Cabinet Office"; in the dashboard are three crosstabs that show the following:

1. Top 10 suppliers spend for Cabinet Office AND all of it's descendants (Cabinet Office, Government Digital Service, DirectGov & 10 Downing St. in the example above).
2. Top 10 suppliers spend for Cabinet Office itself (without the descendant's spend)
3. Top 10 suppliers spend for the descendants WITHOUT Cabinet Office (Government Digital Service, DirectGov & 10 Downing Street)

There are various other elements that will be going into there ("dahsboard" is a very loose definition!) but if I can crack this then I'll be able to get on with the rest... So far, I've been able to hard code the slicers for all and for the department itself (by selecting the relevant level manually) and can create a set that only contains the ALBs without the department itself in there. What I can't do is dynamically do this based on a prompt.

While I'm insistent I won't be beaten by this, I'm going to concede that it has at least won a moral victory!

Thanks for any guidance you can give.

David

[Revelator80]

Quick update - I've now managed to isolate the MUN of the "child" instance of the department that is selected in the prompt. In my mind, I should theoretically now be able use that in an except function to exclude it from the list of ALBs and also in the slicer to bring back just the departments own spend...

I can't however make it recognise the string as a MUN and I can't find a way around this. Is there one?