cancel
Showing results for 
Search instead for 
Did you mean: 

Report Builder Nested Groups

Hello all,

 

I am having trouble implementing nested groups.

 

I have a data cube that I am drawing car insurance data from. This data is sorted in my report by the city and state in which the claimant resides. (Both fields are listed in the cube under the Claimant category.) I have created a nested row group where City is under State (City is shown as Details2 in the Designer view).

jgratz_0-1664285302074.png

 

 

The problem is that no matter how I group the State field, I end up with bad results.

jgratz_1-1664285301810.png

 

 

The best result I got was when I grouped by City. When I did that, I got results that alternated between State and City rows, looking like this:

jgratz_2-1664285301868.png

 

jgratz_3-1664285302025.png

 

Now I only say this is the best because in this case, 4100 is the sum of the column it is under. (There is actually 3x more data in this report than I am showing here, but I just gave you this as a sample.) If I sort by any other variable, I get similar results but the numbers do not add up to 4100.

 

For example, if I group by Direct_Claim_Deductible_Recoverable_End_of_Year, I get this result:

jgratz_4-1664285301842.png

 

:

jgratz_5-1664285302103.png

 

jgratz_6-1664285301849.png

 

This one groups the cities under the states and allows me to expand and contract the state rows, but there are repeat state rows, the data bar is messed up, and the numbers do not add up to the total. (I am showing the whole report here)

 

Trying to send the State field without grouping does not work and gives me this error:

jgratz_7-1664285301856.png

 

 

I also noticed that each report has 17 pages, but they only show the data table taking up one page. This is true regardless of how much data is actually in the table, mind you.

 

I want all of this:

  • I want to group the cities by state and be able to expand and contract them, but I also want the numbers to add up to the total at the bottom.
  • I would also like to get the data bars to show a percentage of the total -- for the cities, a percentage of the state total, and for the states, a percentage of the whole total.
  • And I would like to eliminate the extra blank pages at the end of the report.

Any actually helpful answers would be greatly appreciated. (Seriously, I have received some most unhelpful answers in the past, so please actually read the post before responding.)

If you could help me with even one of the problems listed above, I would be grateful.

 

EDIT: I have figured out why the cities were showing up automatically: I had the Visibility set to "Show" instead of "Hide". Still doesn't explain what's going on with the State rows though.

Status: Investigating
Comments
jgratz
Helper II

Thanks for the message @Ailsa-msft. However, I did know that that error message does not appear when I assign a group. The problem is not so much the error message, but the fact that none of the groups I assign give me the desired result.