Former Member

# Total value of a particular dimension using script logic

Hi experts,

can anyone tell me whether script logic is able to compute the total of a specify dimension? how is it being done?

For example, I have the following in DB:

Account | Category | Entity| Time | Tcode| Inputcurrency |

Vol1 | Budget | LocA | 2005.Feb | A | LC | \$20

Vol1 | Budget | LocA | 2005.Feb | B | LC | \$50

Vol1 | Budget | LocA | 2005.Feb | C | LC | \$70

Vol1 | Budget | LocB | 2005.Feb | A | LC | \$30

Vol1 | Budget | LocB } 2005.Feb | B | LC | \$90

I need to compute total vol by entity, so the results should

total vol for LocA = \$20 + \$50 + \$70 = \$140

total vol for LocB = \$30 + \$90 = \$120

Then this total vol will be use for another computation.

10|10000 characters needed characters exceeded

### Related questions

Former Member
Posted on Dec 23, 2008 at 10:54 AM

Hi,

If you have put this script in default logic, it may not work, because only the entity you have send data will be part of the scope. You may have to add all entities by redefining the scope with an *XDIM_MEMBERSET or execute your script from the datamanager and select all entities.

In addition to that, the following two lines containt a #tot which is not defined anywhere ==> they won't do anything.

- LOOKUP(#tot)

- *rec(location=#tot)

Regards,

Marcel

10|10000 characters needed characters exceeded
• Former Member
Posted on Dec 22, 2008 at 07:23 AM

Hi Puya,

By reporting on the higher members of the all dimensions, except entity, you can compute total volume by entity.

In you example, say we have D as parent of A,B,C. So

Vol1 | Budget | LocA | 2005.Feb | D | LC will be \$140

Kranthi

10|10000 characters needed characters exceeded
• Former Member
Posted on Dec 22, 2008 at 08:33 AM

Hi,

You could try the following code to calculate your product total on a memory variable "#TOT" and then re-use it later in in the code

*WHEN Prod

*IS *

*REC(Prod="#TOT")

*ENDWHEN

*GO

*WHEN Prod

*IS "#TOT"

...

*ENDWHEN

Second option would be to use the *CALC_DUMMY_ORG instruction so that the system will calculated all the parents of the dimension you specifiy and you can then access these parent members by adding a "#" in front of the member name. This will only work for one dimension in a *COMMIT section

Regards,

Marcel

10|10000 characters needed characters exceeded
• Former Member
Posted on Dec 23, 2008 at 08:47 AM

Hi Marcel,

attached my logic:

*when account

*is "vol1"

*when entity

*is *

*rec(entity=#ltotal)

*endwhen

*endwhen

*go

*when account

*is "vol1"

*when entity

*is #ltotal

*rec(location=#tot)

*endwhen

*endwhen

*commit

I post the following records

VOL1,BUDGET,INPUT,LOCA,2005.JAN,USD,ASED,-2.0000000

VOL1,BUDGET,INPUT,LOCA,2005.JAN,USD,TPED,-5.0000000

I get the records with the correct result:

VOL1,BUDGET,INPUT,LOCA,2005.JAN,USD,NA,TOTLOC,-7.0000000

However, when I change the value of the first record as follows:

VOL1,BUDGET,INPUT,LOCA,2005.JAN,USD,ASED,3.0000000

VOL1,BUDGET,INPUT,LOCA,2005.JAN,USD,TPED,-5.0000000

the result is wrong:

VOL1,BUDGET,INPUT,LOCA,2005.JAN,USD,NA,TOTLOC,-10.0000000

The total should be 8 not 10.

So I try to add in *clear_destination:

*when account

*is "vol1"

*when entity

*is *

*rec(entity=#ltotal)

*endwhen

*endwhen

*go

*when account

*is "vol1"

*when entity

*is #ltotal

*rec(location=#tot)

*endwhen

*endwhen

*clear_destination

*destination entity="totloc"

*commit

This results in:

VOL1,BUDGET,INPUT,LOCA,2005.JAN,USD,ASED,0.0000000

VOL1,BUDGET,INPUT,TOTLOC,2005.JAN,USD,NA,-12.0000000

VOL1,BUDGET,INPUT,LOCA,2005.JAN,USD,TPED,0.0000000

Instead of clearing the amount in TOTLOC, it clears the value I have send and the total is still incorrect.