11 Replies Latest reply: Feb 14, 2013 8:41 AM by Programmer Analyst RSS

    Functions in OBIEE

    Programmer Analyst
      Hello,

      I have a function in Discoverer. This function returns a value and I need to implement the same in OBIEE.
      Can anybody please tell me how to use functions in OBIEE?
        • 1. Re: Functions in OBIEE
          Programmer Analyst
          any updates please?
          • 2. Re: Functions in OBIEE
            Christian Berg
            Quick&dirty: Put it into an EVALUATE wrapper.
            • 3. Re: Functions in OBIEE
              Programmer Analyst
              I tried but it didnt work. I am not sure if I am doing it correctly so can you please elaborate a little please?
              • 4. Re: Functions in OBIEE
                Christian Berg
                "it didn't work" is anything but precise so I suggest you take a look here: http://gerardnico.com/wiki/dat/obiee/evaluate
                • 5. Re: Functions in OBIEE
                  Programmer Analyst
                  thanks for the reply Christian but the implementation looks like its done in analytics. My issue is I have a function that concatenates after calculating certain measures into one string. I think this needs to be done in physical layer, please correct me if I am wrong. So, is there a way we can do anything in physical layer to get the string and breaking up the string is not a big of a deal,that can be done anywhere.

                  Please help!!
                  • 6. Re: Functions in OBIEE
                    Christian Berg
                    I have a function that concatenates after calculating certain measures into one string.
                    That still isn't a very precise explanation of what you're actually trying to achieve, but here's what I think you're talking about in a SampleApp:
                    <saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlVersion="201201160" xmlns:sawx="com.siebel.analytics.web/expression/v1.1">
                       <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;A - Sample Sales&quot;">
                          <saw:columns>
                             <saw:column xsi:type="saw:regularColumn" columnID="c34249c052e5dee1c">
                                <saw:columnFormula>
                                   <sawx:expr xsi:type="sawx:sqlExpression">'Total Revenue = ' || cast(sum("Base Facts"."1- Revenue" by) as varchar(50)) || ' and Revenue by Brand = ' || cast(sum("Base Facts"."1- Revenue" by "Products"."P4  Brand") as varchar(50))</sawx:expr></saw:columnFormula>
                                <saw:tableHeading>
                                   <saw:caption fmt="text">
                                      <saw:text>temp</saw:text></saw:caption></saw:tableHeading>
                                <saw:columnHeading>
                                   <saw:caption fmt="text">
                                      <saw:text>temp</saw:text></saw:caption></saw:columnHeading></saw:column>
                             <saw:column xsi:type="saw:regularColumn" columnID="c6eefeb886be32732">
                                <saw:columnFormula>
                                   <sawx:expr xsi:type="sawx:sqlExpression">"Products"."P4  Brand"</sawx:expr></saw:columnFormula></saw:column></saw:columns></saw:criteria>
                       <saw:views currentView="0">
                          <saw:view xsi:type="saw:compoundView" name="compoundView!1">
                             <saw:cvTable>
                                <saw:cvRow>
                                   <saw:cvCell viewName="titleView!1">
                                      <saw:displayFormat>
                                         <saw:formatSpec/></saw:displayFormat></saw:cvCell></saw:cvRow>
                                <saw:cvRow>
                                   <saw:cvCell viewName="tableView!1">
                                      <saw:displayFormat>
                                         <saw:formatSpec/></saw:displayFormat></saw:cvCell></saw:cvRow></saw:cvTable></saw:view>
                          <saw:view xsi:type="saw:titleView" name="titleView!1"/>
                          <saw:view xsi:type="saw:tableView" name="tableView!1">
                             <saw:edges>
                                <saw:edge axis="page" showColumnHeader="true"/>
                                <saw:edge axis="section"/>
                                <saw:edge axis="row" showColumnHeader="true">
                                   <saw:edgeLayers>
                                      <saw:edgeLayer type="column" columnID="c34249c052e5dee1c"/>
                                      <saw:edgeLayer type="column" columnID="c6eefeb886be32732"/></saw:edgeLayers></saw:edge>
                                <saw:edge axis="column"/></saw:edges></saw:view></saw:views></saw:report>
                    • 7. Re: Functions in OBIEE
                      Programmer Analyst
                      No I am talking about a database function.
                      eg.

                      CREATE OR REPLACE FUNCTION THIS_EXAMPLE (FPARA IN VARCHAR2,
                      SPARA IN VARCHAR2)
                      RETURN VARCHAR2
                      IS
                      PRAGMA AUTONOMOUS_TRANSACTION;
                      FVAR VARCHAR2 (4000);
                      SVAR VARCHAR2 (4000) := ' ';
                      TVAR VARCHAR2 (4000) := ' ';
                      ETC....

                      BEGIN
                      ----
                      ----
                      ---
                      LOOP
                      ----
                      ---
                      END LOOP
                      -----
                      ---
                      --
                      FVAR:=SVAR||TVAR||...NVAR
                      COMMIT
                      RETURN FVAR;
                      EXCEPTION: BLAH BLAH
                      END;

                      So, I am talking about this kind of function. I need to get the return value into my physical layer.
                      This is my issue. Please let me know if you need further explanation, I ll be more than happy to explain.
                      • 8. Re: Functions in OBIEE
                        Christian Berg
                        EVALUATE('THIS_EXAMPLE(%1,%2)', "SomePresTable"."SomePresColumnRepresenting_FPARA", "SomePresTable"."SomePresColumnRepresenting_SPARA")
                        • 9. Re: Functions in OBIEE
                          Programmer Analyst
                          Thanks for the reply
                          1) Does pres mean presentation? If yes, I actually need it in physical layer.
                          2) Back to my previous post, as I said I tried using Evaluate and it didnt work. What I mean by 'it didnt work' is When I try to update the row count with that formula, I get this error message:

                          There was an error while updating row count for "TMP Data Sources".."TEMP"."temp":
                          *[nQSError:17011] SQL statement execution failed.*
                          *[nQSError: 17001] Oracle Error code: 907, message: ORA-00907: missing right parenthesis at OCI call OCIStmtExecute: select count(*) from (Evaluate('this_example(%1,%2)',TEMP.temp1, TEMP.temp2) as TEMPattribs) T1.*
                          • 10. Re: Functions in OBIEE
                            Christian Berg
                            If you want to do it in the RPD, then the place to be is the BMM layer. The formula will remain more or less the same:


                            Evaluate('thisdoessomething(%1,%2)', "Sample App Lite Data"."".""."D02 Time Month Grain"."Fscl_Year", "Sample App Lite Data"."".""."D02 Time Month Grain"."Fscl_Half" )
                            • 11. Re: Functions in OBIEE
                              Programmer Analyst
                              I fixed my issue by using a query in physical layer. I called the function directly in the physical layer.