cancel
Showing results for 
Search instead for 
Did you mean: 

Problem with XDIM_Filter Instruction!!

Former Member
0 Kudos

Hi Experts,

I am using BPC 7.5M and have been upgraded from BPC 5.1 in recent past, There is one script logic containing instruction:

FLAG_PERIOD = CATEGORY.CURRMO

*XDIM_FILTER TIME > %FLAG_PERIOD%

The CURRMO property contains values like "2011.SEP", now when I am validating the logic it is giving syntax error in validation result window, please find the result below:

Validation Result:

Application: Headcount

Logic file: HCFormulas.LGF

Validation status of executable file: Success

Validation status of syntax: Failed

- Invalid syntax found; see statements in red

*It is showing the line "XDIM_FILTER TIME > %FLAG_PERIOD%" in red font, This same was working fine in 5.1 version.

*In BPC 7.5 M, I have updated the XDIMFILTER instruction with:*_

*FLAG_PERIOD = CATEGORY.CURRMO

*XDIM_FILTER TIME = [TIME].PROPERTIES("ID") > %FLAG_PERIOD%

And it validates fine but when I ran the package calling this logic it gave error:

u201CError in step 1 of QueryCubeAndDebug: -2147467259 Query (1, 247) Parser:

The syntax for 'SEP' is incorrect.u201D

Just for you information CURRMO property in this case is having value u201C2011.SEPu201D and Time dimension ID members also contain this value u201C2011.SEPu201D.

Please Advice.

Thanks in Advance!!

Thanks & Regards,

Rohit

Accepted Solutions (0)

Answers (1)

Answers (1)

JohnL
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Rohit,

What is it exactly you are trying to do in your logic?

There are many way to things in script logic and if this is not working in 7.5 you should submit this issue to SAP Support using the SAP Service marketplace.

If you are trying to retrieve members from your Time dimension that is greater than your current month defined in the CURRMO property then you can try this command instead:

*SELECT(%MonthList%, u201CIDu201D, u201CTIMEu201D, u201C[YEAR] = (SELECT [YEAR] from mbrCategory 
WHERE [ID] = '%CATEGORY_SET%') AND [MONTHNUM] > 
(SELECT [MONTHNUM] from mbrTime WHERE [ID] = (SELECT [CURRMO] FROM mbrCategory 
WHERE [ID] = '%CATEGORY_SET%'))u201D)

*XDIM_MEMBERSET TIME = %MonthList%

To test the selection run this query in SQL Server and replace %CATEGORY_SET% with the real category name (i.e. PLAN )

SELECT [ID]  FROM mbrTime
WHERE [YEAR] = (SELECT [YEAR] from mbrCategory WHERE [ID] = '%CATEGORY_SET%') 
AND [MONTHNUM] > 
(SELECT [MONTHNUM] from mbrTime WHERE [ID] = (SELECT [CURRMO] FROM mbrCategory 
WHERE [ID] = '%CATEGORY_SET%'))

It might be easier to add a CURRENTMONTH number property to your Category dimension which will simplify the *SELECT query as follows:

*SELECT(%MonthList%, u201CIDu201D, u201CTIMEu201D, u201C[YEAR] = (SELECT [YEAR] from mbrCategory 
WHERE [ID] = '%CATEGORY_SET%') AND [MONTHNUM] > (SELECT [CURRENTMONTH] 
FROM mbrCategory WHERE [ID] = '%CATEGORY_SET%'))u201D)

Thanks,

John

Former Member
0 Kudos

Hi John,

Thanks for your reply!!

1st Part

What is it exactly you are trying to do in your logic?

There is statement XDIM_FILTER in logic to put filter on Time dimension members for processing of periods greater than category.CurrMo property

The Statement is:

FLAG_PERIOD = CATEGORY.CURRMO

*XDIM_FILTER TIME > %FLAG_PERIOD%

The CURRMO property contains values like "2011.SEP", now when I am validating the logic it is giving syntax error on *XDIM_FILTER TIME > %FLAG_PERIOD%, This statement was not showing any validation error in BPC 5.1

2nd Part

I have opened a case with SAP Support for this and they suggested touse following statement in BPC 7.5:

*XDIM_FILTER TIME = TIME.PROPERTIES("ID") > %FLAG_PERIOD% (In 7.5)

Instead of

*XDIM_FILTER TIME > %FLAG_PERIOD% (In 5.1)

The replaced statement *XDIM_FILTER TIME = TIME.PROPERTIES("ID") > %FLAG_PERIOD validates fine but when I ran the package calling this logic it gives error:

u201CError in step 1 of QueryCubeAndDebug: -2147467259 Query (1, 247) Parser:

The syntax for 'SEP' is incorrect.u201D

Do you have any idea why it is showing error here u201CThe syntax for 'SEP' is incorrect.u201D

3rd Part

I have also tried of using TIMEID instead if ID in XDIM_FILTER:

*FLAG_PERIOD = CATEGORY.CURRMO

*SELECT(%VARTID%,"[TIMEID]","TIME","ID='%FLAG_PERIOD%'")

*XDIM_FILTER TIME = [TIME].PROPERTIES("TIMEID") > %VARTID%

It validates fine but giving error while execution:

Error in step 1 of QueryCubeAndDebug: -2147467259 Query (1, 245) Parser: The syntax for ')' is incorrect.

Please advice am I writing correct syntax for passing variable in where section of select statement:

*SELECT(%VARTID%,"[TIMEID]","TIME","ID='%FLAG_PERIOD%'")

4rth Part

I tried youru2019s reply but that is also giving syntax error.

5th Part

One thing I would like to discuss is that as FLAG_PERIOD = CATEGORY.CURRMO statement is showing syntax validation error but when I run the package calling this logic it then there is no error. I checked log file and that also shows correct instruction applied:

Log file shows

Max members:

Valid TIME members: 2011.OCT,2011.NOV,2011.DEC

And it is right because CurrMo property is 2011.SEP and logic is doing what is expected to do, then why syntax error on statement u201CFLAG_PERIOD = CATEGORY.CURRMOu201D

and why this syntax error not creating any problem in logic execution.

Do you have any idea about this kind of behavior?

Please Advice!!

Thanks & Regards,

Rohit

krishna_priya1
Contributor
0 Kudos

null

Edited by: pkrishnas on Oct 25, 2011 3:39 PM