MDX Script Logic -> How create a select between two FOR

[I816327]

Hi Gurus,

I got perfomance problem becouse my secuence of FOR are including all members. I need to restrict the members including in the second FOR. (BPC 7.5 NW, SP05)

Please look at my scenarios:

// Current Scenario -

---

*SELECT (%ALLCECO%, "[ID]", CENTRODECOSTO, [DISTRIB] = "D")

*XDIM_MEMBERSET CENTRODECOSTO = %ALLCECO%

*SELECT (%ALLCECOREC%, "[ID]", CECOINT, [OHEAD3] = "VO")

*XDIM_MEMBERSET CECOINT = %ALLCECOREC%

*FOR %MES% = %MESES%

• FOR %CECO_EMI% = %ALLCECO%

• FOR %CECO_REC% = %RR%

• NEXT

• NEXT

*NEXT

// I need to do the next..

*SELECT (%ALLCECO%, "[ID]", CENTRODECOSTO, [DISTRIB] = "D")

*XDIM_MEMBERSET CENTRODECOSTO = %ALLCECO%

*FOR %MES% = %MESES%

• FOR %CECO_EMI% = %ALLCECO%

• SELECT (%RR%,"[ID]", CECOINT, [DISTR] = %CECO_EMI%)

*

• FOR %CECO_REC% = %RR%

• NEXT

• NEXT

*NEXT

But I can not do the last one. I can not use a variable %Ceco_Emi%. Someone knows how can do it?

Thanks a lot

Regards

Leticia

[I816327]

Hi Gersh,

First that all thanks for answer me.

The unique thing that I need to do is restrict the second variable (%RR%) in relation with the value whose comming from the first FOR (%Ceco_emi%).

-

---

The model has:

COSTCENTER -> %CECO_EMI%

CECOINT - > %CECO_REC% , has a propertie call DISTR where has dependence with COSTCENTER.

-

---

How is it work?, like this

If I replace... this..

*SELECT (%ALLCECO%, "ID", CENTRODECOSTO, DISTRIB = "D")

*XDIM_MEMBERSET CENTRODECOSTO = %ALLCECO%

*SELECT (%ALLCECOREC%, "ID", CECOINT, OHEAD3 = "VO")

*XDIM_MEMBERSET CECOINT = %ALLCECOREC%

by this..

XDIM_MEMBERSET CENTRODECOSTO = "CDX0001"

*SELECT (%ALLCECOREC%, "ID", CECOINT, DISTR = "CDX0001")

*XDIM_MEMBERSET CECOINT = %ALLCECOREC%

it is working perfectly, but I have 40 values like CDX0001, so I can not put fixed.

For this reason I need to use something like this (use a select in the middle):

FOR %MES% = %MESES%

• FOR %CECO_EMI% = %ALLCECO%

• SELECT (%RR%,"ID", CECOINT, DISTR = %CECO_EMI%)

*

• FOR %CECO_REC% = %RR%

• NEXT

• NEXT

*NEXT

Also, I tried with this but I got a dump.

FOR %MES% = %MESES%

• FOR %CECO_EMI% = %ALLCECO%

• FOR %CECO_REC% = %RR%

• When %CECO_REC%.DISTR

• IS %CECO_EMI%

// logic using allocation

*

• ENDWHEN

• NEXT

• NEXT

*NEXT

Do you have any advice?

Thanks

Leticia

[Former Member]

Haven't tried myself but give a try with *XDIM_FILTER between the for loops.

Another option is to try *SUB() / *ENDSUB

Edited by: ShilpaK on Mar 2, 2011 8:58 PM

[former_member200327]

Hi Leticia,

It looks like you want to make a transitive selection and BPC doesn't support it just by using *SELECT. Transitive selection can be done for 2 levels (like you need), but implementation depens on what key word you are using it for. For *WHEN it's different than for *RUNALLOCATION and for *LOOKUP, etc.

So, it looks like you want to use it in *WHEN. In that case I'd suggest the following construction:

*SELECT (%ALLCECO%, "ID", CENTRODECOSTO, DISTRIB = "D")
*XDIM_MEMBERSET CENTRODECOSTO = %ALLCECO%

*WHEN CECOINT.DISTR 
*IS %ALLCECO%
*REC(...)

*ENDWHEN

Your code shows 3 levels of FOR/NEXT. I don't think it's possible to derive third level with current script capabilities.

What's a litle surprising is that in commenst you said 'logic using allocation'. That doesn't belong inside WHEN/ENDWHEN; only *REC can be inside that statements. If you have *RUNALLOCATION then you should use same idea of restricting Properties directly in RUNALLOCATION.

Hope this helps,

Gersh