5 Replies Latest reply on Jul 3, 2008 5:17 PM by 437107

    Discoverer Report parameter based on subquery

    512041
      Hi Guys,

      I have following query which i need to convert into discoverer report

      select hpah.EMPLOYEE_NUMBER,hpah.FIRST_NAME,hpah.LAST_NAME,hpah.PERSON_NAME,
      --hpah.JOB_NAME,hpah.ASSIGNMENT_ORGANIZATION_NAME,
      hpah.ASSIGNMENT_TYPE,hpah.USER_ASSIGNMENT_STATUS,
      SUBSTR(pj.name, 1, instr(pj.name, '.', 1, 2) -1) job_code,
      SUBSTR(pj.name, instr(pj.name, '.', 1, 2) + 1) job_title,
      (select haou.ATTRIBUTE1 from hr_all_organization_units haou
      where haou.ORGANIZATION_ID = hpah.ASSIGNMENT_ORGANIZATION_ID ) Function,
      (select haou.ATTRIBUTE2 from hr_all_organization_units haou
      where haou.ORGANIZATION_ID = hpah.ASSIGNMENT_ORGANIZATION_ID ) org_desc,
      SUBSTR(hpah.ASSIGNMENT_ORGANIZATION_NAME, 1, 6) dept_code,
      SUBSTR(hpah.ASSIGNMENT_ORGANIZATION_NAME, 7, LENGTH(hpah.ASSIGNMENT_ORGANIZATION_NAME)) dept_title,
      hpah.GRADE_NAME,hpah.LOCATION_NAME,hpah.SUPERVISOR_NAME,
      hpah.PERSON_START_DATE,hpah.ORIGINAL_DATE_OF_HIRE ,
      hpah.SALARY_BASIS,hpah.BUSINESS_GROUP_ID,hpah.ASSIGNMENT_ID,
      decode( hpah.SALARY_BASIS, 'ANNUAL', ppp.proposed_salary_n,
      'HOURLY', (ppp.proposed_salary_n *2080),
      ppp.proposed_salary_n
      ) salary,
      sysdate run_date
      from
      hrfg_person_assignment_history hpah ,
      per_pay_proposals ppp,
      per_pay_bases ppb,
      per_grades pg,
      per_jobs pj
      where hpah.ASSIGNMENT_ID = ppp.ASSIGNMENT_ID
      and hpah.SALARY_BASIS = ppb.PAY_BASIS
      and hpah.BUSINESS_GROUP_ID = ppb.BUSINESS_GROUP_ID
      and hpah.GRADE_ID = pg.GRADE_ID(+)
      and hpah.JOB_ID = pj.JOB_ID (+)
      --and   hpah.EMPLOYEE_NUMBER = 100779
      and hpah.PERSON_START_DATE IN
      (SELECT MAX(hpah2.PERSON_START_DATE)
      FROM hrfg_person_assignment_history hpah2
      WHERE hpah2.EMPLOYEE_NUMBER = hpah.EMPLOYEE_NUMBER
      AND hpah2.PERSON_START_DATE <= sysdate )
      order by hpah.EMPLOYEE_NUMBER


      i need to have sysdate as parameter in my report. I want data based on the date i provide.

      how can i make this as an parameter?
        • 1. Re: Discoverer Report parameter based on subquery
          437107
          Hi Prashant,

          Subqueries are not Supported in Conditions..As of Disco Plus Rel 10.1.2.54.25
          A plausible workaround can be:
          1. Create a View with foll. Query

          create or replace view TEST_VIEW as
          select hpah.EMPLOYEE_NUMBER emp_no,hpah.FIRST_NAME,hpah.LAST_NAME,hpah.PERSON_NAME,
          --hpah.JOB_NAME,hpah.ASSIGNMENT_ORGANIZATION_NAME,
          hpah.ASSIGNMENT_TYPE,hpah.USER_ASSIGNMENT_STATUS,
          SUBSTR(pj.name, 1, instr(pj.name, '.', 1, 2) -1) job_code,
          SUBSTR(pj.name, instr(pj.name, '.', 1, 2) + 1) job_title,
          (select haou.ATTRIBUTE1 from hr_all_organization_units haou
          where haou.ORGANIZATION_ID = hpah.ASSIGNMENT_ORGANIZATION_ID ) Function,
          (select haou.ATTRIBUTE2 from hr_all_organization_units haou
          where haou.ORGANIZATION_ID = hpah.ASSIGNMENT_ORGANIZATION_ID ) org_desc,
          SUBSTR(hpah.ASSIGNMENT_ORGANIZATION_NAME, 1, 6) dept_code,
          SUBSTR(hpah.ASSIGNMENT_ORGANIZATION_NAME, 7, LENGTH(hpah.ASSIGNMENT_ORGANIZATION_NAME)) dept_title,
          hpah.GRADE_NAME,hpah.LOCATION_NAME,hpah.SUPERVISOR_NAME,
          hpah.PERSON_START_DATE,hpah.ORIGINAL_DATE_OF_HIRE ,
          hpah.SALARY_BASIS,hpah.BUSINESS_GROUP_ID,hpah.ASSIGNMENT_ID,
          decode( hpah.SALARY_BASIS, 'ANNUAL', ppp.proposed_salary_n,
          'HOURLY', (ppp.proposed_salary_n *2080),
          ppp.proposed_salary_n
          ) salary,
          sysdate run_date
          from
          hrfg_person_assignment_history hpah ,
          per_pay_proposals ppp,
          per_pay_bases ppb,
          per_grades pg,
          per_jobs pj
          where hpah.ASSIGNMENT_ID = ppp.ASSIGNMENT_ID
          and hpah.SALARY_BASIS = ppb.PAY_BASIS
          and hpah.BUSINESS_GROUP_ID = ppb.BUSINESS_GROUP_ID
          and hpah.GRADE_ID = pg.GRADE_ID(+)
          and hpah.JOB_ID = pj.JOB_ID (+)
          --and hpah.EMPLOYEE_NUMBER = 100779
          order by hpah.EMPLOYEE_NUMBER

          2. Create another table say TEST_TAB

          create table TEST_TAB (emp_no, start_dt) as
          (
          SELECT hpah2.EMPLOYEE_NUMBER , MAX(hpah2.PERSON_START_DATE) PERSON_START_DATE
          FROM hrfg_person_assignment_history hpah2
          GROUP BY hpah2.EMPLOYEE_NUMBER
          )

          3. Add both of them in Disco Admin and create a join between TEST_VIEW.emp_no and TEST_TAB.emp_no

          4. Bring both these Objects in a new report in Disco Plus
          5. Create a New Parameter (Base it on PERSON_START_DATE Col of TEST_TAB)
          6. Choose Option Create Condition with operator <=
          7. Checkmark "Require User to Enter a Value
          8. Execute Report

          I believe this should work. Either ways let me know if it helps or u get another workaround. (This scenario looks quite interesting)

          Thanks,
          Chinmay
          • 2. Re: Discoverer Report parameter based on subquery
            512041
            Thanks Chinmay,


            Can i create a VIEW or i have to create a table because this oracle HR table and it will change a lot based on the transaction done to PERSON object.??
            • 3. Re: Discoverer Report parameter based on subquery
              Tamir Lazover
              Hi,
              You can also take the parameter out of the subquery and then you have no problem using it in the discoverer.
              You can either create a view and import it to the administrator or you can create a custum folder and put in the below select.
              On the worksheet you'll need to create the condition with the parameter on the "Employee_Number" and on the date you wanted to restrict.

              SELECT HPAH.EMPLOYEE_NUMBER,HPAH.FIRST_NAME,HPAH.LAST_NAME,HPAH.PERSON_NAME,
              --HPAH.JOB_NAME,HPAH.ASSIGNMENT_ORGANIZATION_NAME,
              HPAH.ASSIGNMENT_TYPE,HPAH.USER_ASSIGNMENT_STATUS,
              SUBSTR(PJ.NAME, 1, INSTR(PJ.NAME, '.', 1, 2) -1) JOB_CODE,
              SUBSTR(PJ.NAME, INSTR(PJ.NAME, '.', 1, 2) + 1) JOB_TITLE,
              (SELECT HAOU.ATTRIBUTE1 FROM HR_ALL_ORGANIZATION_UNITS HAOU
              WHERE HAOU.ORGANIZATION_ID = HPAH.ASSIGNMENT_ORGANIZATION_ID ) FUNCTION,
              (SELECT HAOU.ATTRIBUTE2 FROM HR_ALL_ORGANIZATION_UNITS HAOU
              WHERE HAOU.ORGANIZATION_ID = HPAH.ASSIGNMENT_ORGANIZATION_ID ) ORG_DESC,
              SUBSTR(HPAH.ASSIGNMENT_ORGANIZATION_NAME, 1, 6) DEPT_CODE,
              SUBSTR(HPAH.ASSIGNMENT_ORGANIZATION_NAME, 7, LENGTH(HPAH.ASSIGNMENT_ORGANIZATION_NAME)) DEPT_TITLE,
              HPAH.GRADE_NAME,HPAH.LOCATION_NAME,HPAH.SUPERVISOR_NAME,
              HPAH.PERSON_START_DATE,HPAH.ORIGINAL_DATE_OF_HIRE ,
              HPAH.SALARY_BASIS,HPAH.BUSINESS_GROUP_ID,HPAH.ASSIGNMENT_ID,
              DECODE( HPAH.SALARY_BASIS, 'ANNUAL', PPP.PROPOSED_SALARY_N,
              'HOURLY', (PPP.PROPOSED_SALARY_N *2080),
              PPP.PROPOSED_SALARY_N
              ) SALARY,
              SYSDATE RUN_DATE
              -----ADDED FIELDS FOR THE CONDITIONS:
              ,HPAH2.P_START_DATE
              FROM
              HRFG_PERSON_ASSIGNMENT_HISTORY HPAH ,
              PER_PAY_PROPOSALS PPP,
              PER_PAY_BASES PPB,
              PER_GRADES PG,
              PER_JOBS PJ,
              (SELECT MAX(PERSON_START_DATE) P_START_DATE,EMPLOYEE_NUMBER
              FROM HRFG_PERSON_ASSIGNMENT_HISTORY
              GROUP BY EMPLOYEE_NUMBER) HPAH2
              WHERE HPAH.ASSIGNMENT_ID = PPP.ASSIGNMENT_ID
              AND HPAH.SALARY_BASIS = PPB.PAY_BASIS That's a wrong join
              AND HPAH.SALARY_BASIS_ID = PPB.PAY_BASIS_ID
              AND HPAH.BUSINESS_GROUP_ID = PPB.BUSINESS_GROUP_ID
              AND HPAH.GRADE_ID = PG.GRADE_ID(+)
              AND HPAH.JOB_ID = PJ.JOB_ID (+)
              AND HPAH.PERSON_START_DATE IN HPAH2.P_START_DATE
              AND HPAH.EMPLOYEE_NUMBER=HPAH2.EMPLOYEE_NUMBER
              --AND HPAH2.P_START_DATE <= &P_DATE
              --AND HPAH.EMPLOYEE_NUMBER = &P_EMPLOYEE_NUMBER
              ORDER BY HPAH.EMPLOYEE_NUMBER

              Good Luck
              • 5. Re: Discoverer Report parameter based on subquery
                437107
                Hi Prashant,

                You can either create a View or a Custom Folder as desribed by Tamir-L.
                Good Luck !

                Thanks,
                Chinmay
                PS: The Doc "Using Database Contexts with Discoverer" is quite informative - I am gonna try them right away on our Test System. Thanks for sharing the same Rod !!