Adding totals for non-measurements?

[Solo4357]

I have a report that looks like this:

Fullname    OHCOSTS  Customer     Billed
John        5000     Cutco        500
                     BigCorp      400
   SubTotal 10000                 900

Bill        4000     MickeyD      4000
                     JimBob's     500
   Subtotal 8000                  4500

Grandtotal  18000                 5400

However that's not correct. Because OHcosts are not a measurement, but a dimension, I want to total just the value singly. So it should look like this:

Fullname    OHCOSTS  Customer     Billed
John        5000     Cutco        500
                     BigCorp      400
   SubTotal 5000                  900

Bill        4000     MickeyD      4000
                     JimBob's     500
   Subtotal 4000                  4500

Grandtotal  9000                  5400

So while billed is correct, OHcosts are not. It can't be helped that the ohcosts are dimension attribute, but is there a way to sum them? Cdec lets me sum them but sums it up for every row there is for each customer. Sum distinct isn't available... is there a way?

 

[xlbo]

Looks like you have OHCOSTS set up as a data item rather than a group item

Try creating a group on OHCOSTS and see if that sorts out your issue

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Solo4357]

That's my problem, I can't the way the it's set up in the cube. Doesn't come across as a measurement.

 

[xlbo]

Apologies - didn't read the issue properly

you need to use INSCOPE to determine where you are in each group and then an IIF statement to suppress the row value where you are in scope of customer but not of FullName

then sum on that calc and it should work

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question