This discussion is archived
5 Replies Latest reply: Dec 13, 2012 5:29 AM by 863908 RSS

Partial Clear for APR:JUN

863908 Newbie
Currently Being Moderated
Hi All,
Can we clear partial data in ASO for more than one month. Pertucularly I want to clear data for StartPeriod:ClosedPeriodis it possible?

I have writtent the following maxl, and it gives me syntax error:

alter database 'ACT'.'ACT' clear data in region '{(([Apr]:[Mar]), ([FY09]))}' physical;

but

alter database 'ACT'.'ACT' clear data in region '{([Apr]:[Mar)}' physical;
this executed without error

Thanks in advance

Edited by: 860905 on Dec 11, 2012 6:32 PM
  • 1. Re: Partial Clear for APR:JUN
    SurendarBheemreddy Explorer
    Currently Being Moderated
    Hi,

    Try below MAXL code

    alter database 'ACT'.'ACT' clear data in region '{("Apr":"Mar"), ("FY09")}' physical;

    Thanks.
  • 2. Re: Partial Clear for APR:JUN
    JohnGoodwin Oracle ACE
    Currently Being Moderated
    alter database 'ACT'.'ACT' clear data in region 'Crossjoin({Apr:Mar},{FY09})' physical;

    ?

    Cheers

    John
    http://john-goodwin.blogspot.com/
  • 3. Re: Partial Clear for APR:JUN
    GlennS_3 Oracle ACE Director
    Currently Being Moderated
    I believe John has given you the correct syntax. The reason your and Goodfriend's would not work, is two fold. First, You were both trying to do a tuple with multple members from the same dimension. You can't do that, it has to be a set, hence the {Apr:Mar} syntax in John's code. Next, if you are using a set you have to cross join that set with another set as John did.
    I do question the Apr:Mar Is April really in the hierarchy before March? IF not, it still moght not work unles you reverse them to be Mar:Apr and yes you can use your substitution variables in the code. Sometimes however depending on the names the values in the variables have to look like [Apr]
  • 4. Re: Partial Clear for APR:JUN
    863908 Newbie
    Currently Being Moderated
    Thanks John and Glenn for your help . It works perfectly :)
  • 5. Re: Partial Clear for APR:JUN
    863908 Newbie
    Currently Being Moderated
    Hi All,

    I want to add one more thing. In Account(dynamiv hierarchy is enabled) dimension there is member Cashflow. I want to clear data for the level 0 descendants of Cashflow. So I run the following query:

    alter database 'ACT'.'ACT' clear data in region ' Crossjoin({(([FY09]),([Actual]))}, {Descendants([CASH_FLOW],10,LEAVES)})';

    But it gives me the follwing error:

    Dynamic members are not allowed in data clear region specification.


    Any help please.

    Thanks in advance

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points