5 Replies Latest reply: Dec 13, 2012 7:29 AM by 863908 RSS

    Partial Clear for APR:JUN

    863908
      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
          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
            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
              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
                Thanks John and Glenn for your help . It works perfectly :)
                • 5. Re: Partial Clear for APR:JUN
                  863908
                  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