Script Logic - Filter

[Former Member]

Hi All,

i want to create a set of all ACCOUNT's of a particular ENTITY.

For example,

When am uploading transaction data as below,

ACCOUNT,ENTITY,DATASRC,INTCO,RPTCURRENCY,TIME,CATEGORY,AMOUNT

570927,32000,INPUT,NON_INTERCO,LC,2008.JAN,ACTUAL,1000

570930,32000,INPUT,NON_INTERCO,LC,2008.JAN,ACTUAL,2000

570999,32000,INPUT,NON_INTERCO,LC,2008.JAN,ACTUAL,3000

600100,42000,INPUT,NON_INTERCO,LC,2008.JAN,ACTUAL,5000

600200,42000,INPUT,NON_INTERCO,LC,2008.JAN,ACTUAL,6000

i want all the ACCOUNT(570927,570930,570999) numbers of ONE ENTITY=32000 in a variable

to use in Script Logic.

Can any one of you please explain how can i achieve this.

Thanks in advance.

Regards,

Sai

[Former Member]

thnks fo rthe ans

[Former Member]

There's nothing built into BPC that can do this for you. I agree with Joost that you may not need this to this, for most logic operations.

That said, if you do need this, I would recommend you query the DB periodically to get the info, then store this as a property value. This will only work if this is something that is reasonably static. If it's really critical ,this could be incorporated into an automated dimension processing routine that runs once a month (after loading your actuals) or whenever required.

I've done this to determine which accounts are used in each company, and then expand an input schedule to only show the accounts relevant to that company. (This could also be done via a row suppression, but the property lookup works much faster in the EVDRE, and this way the admin can control "new" accounts that users are supposed to plan to, even before the data exists. Alternatively, we can disable an account that is no longer used, even though there's data in last year's actuals.)

SQL along these lines should work, after you load all the actual data:

select distinct account, entity from tblFactFinance where category= 'actual'
union all
select distinct account, entity from tblFac2Finance where category= 'actual'
union all
select distinct account, entity from tblFactWBFinance where category= 'actual'
order by entity, account

And if you are a real SQL geek (I mean that in a good way) you can have the SQL concatenate the results of all accounts into a single field of comma-separated account IDs, so you get one row for each entity. The post that result as a member property in your entity dimension.

Beware that if you're dealing with 100's of accounts or more, in each entity. The single field may require a very long field length, and you have a max of 8000 characters in the mbrDimension table in BPC on SQL 2005. When I've implemented this, I've built properties in my account dimension, one for each entity, where I just flag each account as Y or blank. Then I can look up the member property (in an input schedule or logic). The member property name is Active1234 or Active1235, where 1234 is the entity ID. If you have 100's of company codes, this may be a maintenance nightmare.

[Former Member]

Can you give a little more information why you want to do this and how this will be used. Because if you setup a *XDIM_MEMBERSET ACCOUNT= <all> the logic already runs for all accounts that are filled with data. Aalthough this is not the best option for performance reasons, but anyway why would you use these accounts in a variable like you describe?

-Joost

[Former Member]

hi Joost,

Thanks for your reply.

here is my requirement,

SOURCE Destination

ACCOUNT ENTITY ACCOUNT ENTITY CRITERIA

32000 32105 42%

32150 28%

user will select ALL for ACCOUNT and ENTITY as 32000 and CRITERIA.

i have written Allocation logic as below, which is written in default.lgf file

*FOR %CRI%=CRITERIA1,CRITERIA2 AND %ACT%=32105,32150

*RUNALLOCATION

*FACTOR=USING/100

*DIM ACCOUNT WHAT=<ALL>;WHERE=<<<;USING=%CRI%

*DIM ENTITY WHAT=32000;WHERE=%ACT%;USING<>32000

*ENDALLOCATION

*NEXT

CRITERIA1=42% and CRITERIA2=28% are members of ACCOUNT dimension

here am trying to allocate all act's amount of entity(32000) into same act

of entity's(32105,32150) based on Criteria(42%,28%)

the code is working fine but if in case any data already exist in

entity(32105,32150) it is making it zero instead of adding the allocated data with the uploaded data.

suppose i have uploaded data as below

ACCOUNT,ENTITY,DATASRC,INTCO,RPTCURRENCY,TIME,CATEGORY,AMOUNT

570927,32000,INPUT,NON_INTERCO,LC,2008.JAN,ACTUAL,100

570930,32000,INPUT,NON_INTERCO,LC,2008.JAN,ACTUAL,200

570999,32105,INPUT,NON_INTERCO,LC,2008.JAN,ACTUAL,300

after running the allocation logic from import package it is making ACCOUNT(570999)

as ZERO, i dont know y (i guess it should be 310)

Can you please exaplain what's wrong in the code.

Thanks in advance

Regards,

Sai