Report Builder Nested Groups

[Anonymous]

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).

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

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:

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:

:

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:

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.

[v-yetao1-msft]

Hi @Anonymous 

I found some info about error message , you can refer to it .

The tablix has a detail member with inner members. Detail members can only contain static members — mibuso.com

reporting services - SSRS Error - tablix ‘Tablix1’ has a detail member with inner members. Detail members can only contain static inner members - Stack Overflow

matrix - SSRS Detail members can only contain static inner members - Stack Overflow

Best Regards,
Community Support Team _ Ailsa Tao

[Anonymous]

Thanks for the message @v-yetao1-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.