Help with a datagrid

#1

I have the following table (ignore the constant - it’s for states).
The bold rows are the subtotals/totals for each of the Level 1 categories.

image
image.png932×746 36.2 KB

I have 3 questions

  • As you can see, there are 2 Level 1 categories that do not have sub-categories. I do not want them to have a single row which is the same as the total. It is possible to collapse just these ones with the right click menu, but this does not stick when I save. How can I get this collapsing for just these rows to stick / how can I script it to collapse on data load?
  • I would like to have the sub-category total rows at the top of each section, and the grand total row at the bottom of the table. Doesn’t seem possible.
  • I would like the grand total column (currently col 1) to be the last column. Again, I can’t see how to do this.

All help appreciated as the client is delighted by the other impossible charts that I’ve been able to easily do in Dundas, and it would be a shame to let them down over a simple table.

Thanks

#2

I also want to know how to do all those.

#3

The Show Totals At Bottom setting in the table properties will shift grand totals to the bottom/right of the table (depending on whether it is a a row or a column). This should be good for your use case.

image

#4

Thanks @christian.pervan, the problem is that it moves the subtotals too and I don’t want that.

#5

Ah, I see. Currently, natively grand totals and subtotals are bound to the same alignment as far as that setting is concerned. I have added you to a feature request concerning the ability to separately shift where grand total should be placed on the table - it is issue 55135. As far as current alternatives goes, I can think of two things.

  1. Pivoting your data so that Location 1, Location 2… etc are all separate measure columns instead of a single hierarchy dimension, which can be done at the cube level. Then using a calculated element to create a measure simply called Grand Total which is calculated off the 4 location columns. Of course, this would only really work if the number of location columns was fixed and never changed, otherwise you’d have to modify the grand total calculated element expression to account for that.

  2. Creating separate table which has the same row-wise hierarchy setup without the column hierarchy (i.e. essentially grant totals only), then aligning it to be on the right side. Then you can hide grand totals on the location hierarchy of your original table.

Shifting gears for a moment, for your first point, I should ask - are you using a standard hierarchy or a ragged hierarchy? A ragged hierarchy would allow you to not have to worry about collapsing/expanding the Communication/Outcomes hierarchies.

#6

Thanks Christian

I’ve done that. Gone with separate tables for each section so the total is at the top.
Then added separate tables to the bottom and sides for grand totals.

It is a normal hierarchy, but I’ve done each in a separate table and aligned them too.

Thanks