Page 1 of 1
Subtotal in cube
Posted: Fri Sep 30, 2016 8:49 am
by 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
Re: Subtotal in cube
Posted: Fri Sep 30, 2016 11:49 am
by tomok
missspeedy23 wrote: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.
Re: Subtotal in cube
Posted: Mon Oct 03, 2016 8:48 am
by 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.
Re: Subtotal in cube
Posted: Mon Oct 03, 2016 12:17 pm
by tomok
missspeedy23 wrote: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.
Re: Subtotal in cube
Posted: Fri Oct 07, 2016 2:50 pm
by 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.
Re: Subtotal in cube
Posted: Fri Oct 07, 2016 3:30 pm
by tomok
missspeedy23 wrote: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.
Re: Subtotal in cube
Posted: Fri Oct 07, 2016 3:47 pm
by 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!