BI #MULTIVALUE error when grouping

[Former Member]

Hi, I am using 3 linked queries to build a hierarchical report of committees and subcommittees from a flat table of parent-child references. I've been able to get the results I want from my queries as below (1.), but when I set the first two columns as Sections I get the #MULTIVALUE error shown below (2.). If I undo the the inner Section, then it works fine (3). Any suggestions for fixing this problem with the inner section? I would like to be able to nest the Sections no matter how many levels of parent-child relationships. I only have permissions to use the BI platform and do not have access to the universes Thank you.

Figure 1.

Figure 2.

Figure 3.

[denis_konovalov]

I have fixed your tags, please do select more careful next time.

[mhmohammed · Accepted solution]

Hi Tom,

I see that you've all the objects common to all the queries, so why do you need to create Detail objects? What are the formulas for Level 0 Comm, Level 1 Related Comm Name, Level 1 Comm, and others? Do they have some logic? If yes, what is it? If they don't, why don't you use one of the merged dimensions?

If you need logic for Level 0 Comm, it should be a Dimension, and it should be using the merged dimensions.

Thanks,
Mahboob Mohammed

[Former Member · Accepted solution]

Hi Mahboob, thanks for your help and your patience. I changed the report objects to the dimensions instead of the detail, as you suggested, and that fixed the #MULTIVALUE problem. Unfortunately, now there is a problem that the Level 3 committees are not aggregating in the correct Level 2 Committee Section ("Administrative Committee", "Technical Committees"); all of the Level 3 committees are in the first section and none in the second; see attached. I'll mark your answer as accept. I'm going to dig into my current problem a little more and will post another question later, if needed. Thanks again.