How to use a Count in ScriptLogic

[Former Member]

Hi there,

I'm currently doing a implementation of SAP BPC 7.0 for MS. At this moment I'm working on ScriptLogic, and I got one question regarding on, how I can do a Count(like SQL) on the members of a given dimension, and with the number the query returns, I need to divide it into other number that is stored on one account.

Any help will be appreciated on this matter.

Rergards,

Rodrigo

[Former Member · Accepted solution]

Hi Rodrigo,

My idea is you can use dummy account that stored every when you selected. After that with *GO you can use that value in when and do dividing.

example :

*when account

*is SomeWhat1

*rec(expression=1,account=#Temp1)

*is ........ (as you like or for looping you can use for function)

*endwhen

*go

*when account

*is #Temp1

*rec(expression=%value%/..................,account=.............)

*endwhen

hope helps..

[Former Member · Accepted solution]

Thanks for sharing your idea on this.

I'll be trying this on my BPC during the day and I'll let you know how it works.

Regards,

Rodrigo

[Former Member · Accepted solution]

Well, The results where that the idea proposed gives me and count of the members that exist on the fact table using a condition, and is throwing a huge number.

The idea is to count only the members that exist on the dimension, not in the fact table.

Any other ideas or suggestions are welcome.

Regards,

Rodrigo

[Former Member · Accepted solution]

I Found this info on this SAP Document....http://tinyurl.com/285va4g

*FACTOR

This instruction can be used to define any arithmetic expression ( written in the

parameter) and may contain operands, parentheses, constants and one or both of the keywords

USING and TOTAL, representing respectively the amount coming from the u201CUSINGu201D region (i.e.

the amount of the driver) and the amount coming from the u201CTOTALu201D region (i.e. the sum of the

drivers). For example:

*FACTOR=USING/TOTAL

Another keyword supported by this parameter is COUNT, which represents the number of

members into which one amount must be allocated. For example, when allocating evenly a yearly

value into all months of a year, the administrator may just use the COUNT keyword.

*FACTOR=1/COUNT

Now, the question will be how can I apply this COUNT in what I need to do.

[Former Member · Accepted solution]

Hi Rodrigo,

Are you already declare all specific dimension in when criteria?? if not, it will looping through all data you have stored.

let say u have 4 dimension

Acc

SubAcc

Cat

Time

each data will stored exactly one data in each time.. so if your calculation will works in every single month.

can you give me script that you have been working out..

thanks and hope helps..

[Former Member · Accepted solution]

Hi Rodrigo,

If you want to count the number of members of a dimension, then you can write the following statement:

SELECT (%NMEMBERS%,"count()","Dimension","Condition=xxx")

The above statement does a select to the table <mbr>Dimension, and count (count(*)) the number of members that accomplish the condition=xxx, and the result is assigned to the variable %NMEMBERS%.

Then you can use the variable in any statement.

Tell me if this works for you.

Regards.

Jorge Orozco

[Former Member · Accepted solution]

Thanks for that answer Jorge, I tested what you posted and it's working the way I need it.

There you got your 10 points, hope it helps you.

Regards,

Rodrigo