4 Replies Latest reply: Jul 25, 2012 11:25 PM by 921550 RSS

    Business Rule form coding issue

    951469
      I have two cubes, one for salaries and one for the P&L, called finance. All members are identical between cubes, EXCEPT the department members, in the finance cube there are numbers,
      such as "662", in the Salary cube, all department members begin with "Sal_" which is "Sal_662"

      I want a salary form on save to execute calculations and then get pulled to the finance cube.

      I have a script that works from a finance cube form, but I want the salary cube form to execute the finance cube business rule. The issue is the different names.

      To do a transfer from the finance cube form, the code looks like :

      "DIRXOT" = @XREF("_Salarycube_" ,"DIRSAL","Total_Employee",(@CONCATENATE("SAL_",@NAME(@CURRMBR("Finance Department")))))

      The finance BR XREF adds the "SAL_ to the current member to reference the Salary cube department.

      Trying to reverse this, code it from the salary form point of view

      IF(@CONCATENATE("SAL_",@NAME(@CURRMBR("Finance Department"))) == [gv_Salary_SalDepartment])

      "DIRXOT" = @XREF("_Salarycube_" ,"PRDSAL","Total_Employee", [gv_Salary_SalDepartment]);

      But no validation. . . any suggestions as to what I am missing?

      thanks
        • 1. Re: Business Rule form coding issue
          921550
          Hi,
          user11241362 wrote:
          "DIRXOT" = @XREF("_Salarycube_" ,"DIRSAL","Total_Employee",(@CONCATENATE("SAL_",@NAME(@CURRMBR("Finance Department")))))
          Here it can be noticed that you haven't converted the String back to member after concatenation.
          Use @MEMBER for the same. So it would be somewhat like this:-

          "DIRXOT" = @XREF("_Salarycube_" ,"DIRSAL","Total_Employee",*@MEMBER* ((@CONCATENATE("SAL_",@NAME(@CURRMBR("Finance Department"))))))

          Hope this works.

          -SM

          Edited by: 918547 on Jul 25, 2012 10:02 AM
          • 2. Re: Business Rule form coding issue
            951469
            Thanks, i didn't pose the question quite correctly. . . The IF statement won't validate:

            IF(@CONCATENATE("SAL_",@NAME(@CURRMBR("Finance Department"))) == @NAME([gv_Salary_SalDepartment]))
            or
            IF(@CONCATENATE("SAL_",@NAME(@CURRMBR("Finance Department"))) == [gv_Salary_SalDepartment])

            I have to do a NAME comparison, and I don't know how to do that, with the mathematical operators. . .
            because there is no matching member between cubes for a numerical match. . .

            So I need to return TRUE when finance cube Member "662" equals salary cube Member "Sal_662" by adding "Sal_" to the finance member and then doing the comparison.

            I think it can be done, but not sure how. . . [gv_Salary_SalDepartment] is the global variable from the salary cube form passing the salary cube member to the business rule.

            thanks in advance. . . . .
            • 3. Re: Business Rule form coding issue
              NN
              Hi,

              You could try to use Custom Function (CDF):

              e.g.
              IF ( @JequalsIgnoreCase(@NAME[gv_Salary_SalDepartment]),@NAME(@CONCATENATE("SAL_",@NAME(@CURRMBR("Finance Department")))) )

              (you may need to make some adjustment to make it work)

              Read the following post:
              How to compare two strings in calc script?


              Or you can try something like this (not sure whether it work :)):

              IF( [gv_Salary_SalDepartment] = @MEMBER(@CONCATENATE("SAL_",@NAME(@CURRMBR("Finance Department")))) )


              Cheers,

              Lian
              • 4. Re: Business Rule form coding issue
                921550
                Hi,

                As NN rightly said, for this, you will have to use CDF and the link provided by NN would help you in same.

                However i would like to suggest, what if you fix [gv_Salary_SalDepartment] and pass only the member/s that you want to XREF and use concatenation then?
                As you have mentioned that all members are identical except for"Sal_", this can go well.
                If you don't want the RTP to be fixed, then you can also go for fixing the particular members of that dimension.

                Just trying to way another direction to it. Code won't then require the IF statement also.

                Regards,
                -SM