Subtotal in cube

[missspeedy23]

I need to do a subtotal on my cube against cost centre. There isn’t a natural consolidation element that will do this and I’m struggling to find a way to do this either using a websheet or MDX, can anyone help?

I need a total for each cost centre. The employees/jobs have an “all” level but this just repeats for each element and doesn’t give me a one line total.

I've attached screen shots of what it looks like currently and what I need it to do. Any help much appreciated!

Cognos TM1 10.1.1

[tomok]

The employees/jobs have an “all” level but this just repeats for each element and doesn’t give me a one line total.

It won't if you use the "All" element for just one of those instead of both.

[missspeedy23]

It won't if you use the "All" element for just one of those instead of both.

I can't use the "All" in the other dimensions because where there is only one element and the total it looks strange as it repeats the total and I literally just want all of the leaf elements in each dimension with one total.

[tomok]

It won't if you use the "All" element for just one of those instead of both.

I can't use the "All" in the other dimensions because where there is only one element and the total it looks strange as it repeats the total and I literally just want all of the leaf elements in each dimension with one total.

I didn't say use the "All" element in both, I said use it in just one of them.

[missspeedy23]

I didn't say use the "All" element in both, I said use it in just one of them.

I have tried to use the All in just one (tried each one differently) and it doesn't do what I want hence trying it in all. I've attached what each "all" does and none of them do what I want which is just one total amount.

[tomok]

none of them do what I want which is just one total amount.

Then why not just create your own total with a SUM formula in Excel. Leave all the dimensions as leaves and then write a formula at the bottom. Tip: make the formula extend one row beyond the end of the active form.

[missspeedy23]

Then why not just create your own total with a SUM formula in Excel. Leave all the dimensions as leaves and then write a formula at the bottom. Tip: make the formula extend one row beyond the end of the active form.

That just gives me the total of everything whereas I need a total of each individual cost centre in the first dimension row. But that has solved a different problem I had as I wasn't sure if you could do SUM's in a websheet so thanks for that!