BI #MULTIVALUE error when grouping

[former_member956652]

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_member956652 · 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.

[former_member956652]

Mahboob, If I change from detail to dimension then I get a #DATASYNC error. Below are screen shots of the query results and the individual query setups. Thanks!

[mhmohammed]

Hi Tom,

I see that you created Detail variables for some columns to be displayed in the tables, I assume that may be causing the issue. E.g. The 4 different L02 Related Comm Name values you see under Coastal Council (L01 Comm Name), are they coming from the same query or different query? You can only show detail objects in a table, if it's relation to it's Associated Dimension is 1:1.

Apart from that, if you've all dimensions coming from the all the queries, you don't have to create Detail objects.

Thanks,
Mahboob Mohammed

[mhmohammed]

Hi Tom,

If you've data coming from multiple queries, did you merge dimensions on Level 0, Level 1, Level 2 and Level 3? If not, please do that, and use the merged objects in the variables for grouping, to create a Section on, and in the table.

Thanks,
Mahboob Mohammed

[former_member956652]

Denis, thanks for fixing my tags!

Mahboob, thanks for your answer. Yes, I merged all of the common dimensions and created detail variables, which are displayed in the tables. The first screen shot above shows that the queries are working, but the problem I'm having is with "Set as Section" option. It breaks when I add the 2nd Section, which is the first Section within a Section. The screenshot below shows the merges.