6 Replies Latest reply: Aug 3, 2011 4:44 AM by user12286993 RSS

    Help need for cal script on CCONV

    user12286993
      Hi All expert,

      I've installed the currency database and it is working fine, but when I tried to get the exchange rate dynamically based on "Year" and "Version", i got syntax error, following is my code.


           SET UPDATECALC OFF;

           DATACOPY RPTValue to USDValue;
           FIX("USDValue")
                CCONV @MEMBER (@CONCATENATE (@NAME(@CURRMBR("Year")),@NAME(@CURRMBR("Version"))))->"USD";
           ENDFIX;

           CALC ALL;

      In my currency database, currency type was defined as "2010Act, 2010Bud, 2011Act, 2011Bud, etc), it is because we need to have different exchange rate by year and version.

      Thanks for any recommendation

      Billy
        • 1. Re: Help need for cal script on CCONV
          user12286993
          Anyone can help? I'm stuck with dynamic calculation on specific year and version, thankyou very much for your help!!
          • 2. Re: Help need for cal script on CCONV
            Vasavya Chowdary
            ooh Boy is that you ..currency category ..!!!
            ok give at-least some points dude for my earlier posts



            Here u go ...i saw have created currency category

            create *4* dimensions in exchange rate database
            period
            Jan
            feb

            cur catogory
            Your reporting currecny

            cur name
            USD
            EUR

            and if u have curr type like
            avg
            opening
            closing if so or not u need only 3 dimension




            SET AGGMISSG ON;
            SET UPDATECALC OFF;

            Fixing ur dimensions to clear and load data to level zero members

            FIX(@LEVMBRS("Ur dimension",0),@LEVMBRS("Ur dimension",0),@LEVMBRS("Ur dimension",0),@LEVMBRS("Ur dimension",0),@LEVMBRS("Ur dimension",0),@LEVMBRS("Ur dimension",0))

            FIX("ACTUAL","M1":"M12","YEAR")

            CLEARDATA "LCY"->"Month or where u load for a month";


            FIX("Month")

            DATACOPY "currecny " TO "reporting currecny";
            ENDFIX
            EndFIX

            Here your YTD calculations

            FIX("YTD", "ACT","FY09")
            "Feb"=@sumrange(Month,"Jan":"Feb");
            "Mar"=@sumrange(Month,"Jan":"Mar");
            "Apr"=@sumrange(Month,"Jan":"Apr");
            "May"=@sumrange(Month,"Jan":"May");
            "Jun"=@sumrange(Month,"Jan":"Jun");
            "Jul"=@sumrange(Month,"Jan":"Jul");
            "Aug"=@sumrange(Month,"Jan":"Aug");
            "Sep"=@sumrange(Month,"Jan":"Sep");
            "Oct"=@sumrange(Month,"Jan":"Oct");
            "Nov"=@sumrange(Month,"Jan":"Nov");
            "Dec"=@sumrange(Month,"Jan":"Dec");
            ENDFIX
            ENDFIX


            /*FOR*/

            FOr month calculation

            FIX(@LEVMBRS("Ur dimension",0),@LEVMBRS("Ur dimension",0),@LEVMBRS("Ur dimension",0),@LEVMBRS("Ur dimension",0),@LEVMBRS("Ur dimension",0),@LEVMBRS("Ur dimension",0))

            FIX("ACTUAL","M1":"M12","Your year")
            FIX("Reporting currency","Month")
            CCONV "where u load exchange rate in currency database"->"USD";
            ENDFIX
                           I have fixed USD for ur convenience                                                                                                                         
            ENDFIX
                      
            Same below for YTD aggregation

            FIX(@LEVMBRS("Ur dimension",0),@LEVMBRS("Ur dimension",0),@LEVMBRS("Ur dimension",0),@LEVMBRS("Ur dimension",0),@LEVMBRS("Ur dimension",0),@LEVMBRS("Ur dimension",0))


            FIX("Your reporting currecny","YTD","your year")
            FIX("ACTUAL","YTD")
            CCONV "where u load exchnage rate in currecny database "->"USD";
            ENDFIX
            ENDFIX

            ENDFIX
                                               
            ENDFIX

            This will agg ur data values for upper level member

            SET UPTOLOCAL ON;
            FIX("LCY")
            AGG("UR dimensions");
            ENDFIX

            SET UPTOLOCAL OFF;
            FIX("reporting currecny")
            AGG("UR dimensions);
            ENDFIX
            • 3. Re: Help need for cal script on CCONV
              user12286993
              Hi KVC,

              Thanks for your detailed response, you are so nice and your help is appreciated, I've updated the status my last question and gave you some points.

              I tried to follow your approach, but i got the following error

              *Error: 1012026 [CCONV] command can only be fixed on a CURPARTITION member*

              I have to keep both the LC and USD values, so i have created currency partition and when I tried to FIX, the error message is because I try to FIX not only on USDValue, but with other dimensions.

              Our requirement is simple, user might load actual and budget data in different year and month at one time, we have to dynamically get exchange rate based on these factors, but seems function CCONV does not support @MEMBER or @CURRMBR, so how can you all expert to handle this dynamic calculation? thanks.

              Four dimenions in my currency db
              1. Month (Jan, Feb, Mar, etc)
              2. CurCategory (PL, BS)
              3. CurName (USD, EUR, JPY, etc)
              4. CurType (2010Act, 2010Bud, 2011Act, 2011Bud, etc)

              But since CurType is an optional dimension, we need to specify this dimension when calling fucntion "CCONV", meaning @CURRMBR is required, I'm not sure how to handle this with this limitation.

              I'm newbies in Essbase, your advice is valuable, thanks.

              Regards,
              Billy
              • 4. Re: Help need for cal script on CCONV
                Vasavya Chowdary
                u have to link ur PL database to currency database for that .. ok tel me how can we do this ..drop me email

                rahul_2418@yahoo.com we will do that together
                • 5. Re: Help need for cal script on CCONV
                  user12286993
                  Hi KVC,

                  Thanks, have sent you an email with some screen dumps, really appreciate your help and advice.

                  Regards,
                  Billy
                  • 6. Re: Help need for cal script on CCONV
                    user12286993
                    Dear all,

                    With great help from KVC, I'm able to calculate the translated values, the script provided by KVC is working perfectly and the reason why I got error message on "FIX" is in the essbase.cfg file, I set CCTRACK as TRUE, after I reset to FALSE, it is working fine with FIX according to the script provided by KVC.

                    Thanks KVC again !!

                    Regards,
                    Billy