Follow us: Subscribe via RSS Feed Connect on YouTube Connect on YouTube

Pages

Tuesday, 3 May 2016

Placeholder Column In Oracle Reports

It works like global variable it can be used across the report
Formula column is function which is return only one values but if we want to return multiple values from the formula columns we can use the place holder columns


Data Model:



Take place holder column inside of group and go Property inspector and give below details as followed



Take formula column inside of group and go Property inspector and give below details as followed



Open the PL/SQL formula for formula column write the following code as showed below



PL/SQL Code:
FUNCTION cf_gradeformula
   RETURN CHAR
IS
BEGIN
   BEGIN
      SELECT dname
        INTO :cp_dname
        FROM dept
       WHERE deptno = :deptno;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         :cp_dname := 'No Dept';
      WHEN TOO_MANY_ROWS
      THEN
         :cp_dname := 'Multiple Dept';
      WHEN OTHERS
      THEN
         :cp_dname := 'Un known';
   END;

   BEGIN
      IF :sal >= 10000
      THEN
         RETURN 'A';
      ELSIF :sal >= 5000
      THEN
         RETURN 'B';
      ELSIF :sal >= 3000
      THEN
         RETURN 'C';
      ELSE
         RETURN 'D';
      END IF;
   END;
END;

Layout Model:




Report output:


Share this article :

0 comments:

Post a Comment