SUm Measures based on Same dimension Value

[Former Member]

Hello all,

I have looked for an answer to this, but not getting what I am looking for.

this is an example of data

  Dimens1    Dimens2       Measure1     Measure2     Measure3
   a             b               1            2            3
   a             b               2            3            3
   a             b               1            4            1
   c             d               2            4            5
   c             d               3            2            1
   c             d               2            2            4


What I want is:

  Dimens1    Dimens2       Measure1     Measure2     Measure3
   a             b               4            9          7
   c             d               7            8          10

I have tried using 

=Sum(Measure1) forEach(Dimens1)

Do I have to do something like this for each Measure?

It didnt make any changes. Could anybody help me achieve this?

Thanks in advance,

Niraj

Edited by: NirajK on Mar 15, 2011 10:04 PM

[zeshanshafqat79]

Create 3 new variables

for Example

variable 1: ABC and their type measure and place only 1 variable name Measure1

variable 2: GHJ and their type measure and place only 1 variable name Measure2

Variable 3: XYZ and their type measure and place only 1 variable name Measure3

then use =Sum(ABC) forEach(Dimens1;Dimens2) in one column

=Sum(GHJ) forEach(Dimens1;Dimens2) in 2nd column

=Sum(ABC) forEach(Dimens1;Dimens2) in 3rd column

your isuee will solved

[Former Member]

Hi,

In Web Intelligence:

First Create three variables like

variable 1. Give name as Measure1 and enter =Sum([Mea 1]) ForEach([Dim 1]) in formula field 
	variable 2. Give name as Measure2 and enter =Sum([Mea 2]) ForEach([Dim 1]) in formula field
	variable 3. Give name as Measure3 and enter =Sum([Mea 3]) ForEach([Dim 1]) in formula field

Now drop dimentions and variables - (Dimension1, Dimension2 ) and (Measure1, Measure2, Measure3) in WebI report

or

In Universe Designer:

Create Derived table with this sql code

select sum(Try."Mea-1") as Measure1, sum(Try."Mea-2") as Measure2, sum(Try."Mea-3") as Measur3, Try."Dim-1" as Dimension1, Try."Dim-2" as Dimension2 from Try group by Try."Dim-2",Try."Dim-1"

[Former Member]

are your measures 1,2, and 3 blue? Then they are dimensions not measures. It has to be corrected in Universe.

[Former Member]

Hi Niraj,

It is Universe level problem, make the objects as measures

Double click on Measure1 object---->Properties tab--->Select measure button -

---

> and function SUM

Same for Measure2, Measure3.

if it is not solved

create a variable in report level (webi rich client, webi, deski):

create a variable -

---

> Name: Measure10 -

---

>Formula: Sum(Measure1)

Same for Measure2, Measure3.

Aggregation not occured for Dimension where dimension in Universe, if you make dimension in universe level make sure variable for convert dimension to measure in report level.

All the best,

Praveen

[former_member142702]

What kind of aggregation did you define for your measure objects in your universe? Is it database delegated?

Regards,

Stratos

[Former Member]

Hi Stratos,

Thanks for the reply!!!

I am not sure how the Measure objects were defined in the Universe, can I check in the report panel anywhere. I dont have access to the Universe, and getting an answer from backend developers may take some time due to the process of submitiing a request.

Maybe there are a couple of formulas i could try before having to get any help from the backend Developers.

Appreciate any help/advice.

Thanks,

Niraj