Dong Shin 09.09.2013

  • working on Query Builder
  • working on importing Obligations/Outlays from COGNOS
    • set up VisibilityScriptingServer
      • change project_portfolio to project_portfolio_enh in visibility.properties
      • updated pom.xml to generate VisibilityScriptingServer-1.0.jar
      • rebuilt with the latest code
    • created sample xlsx data for Obligated and Outlay data
    • saved following queries in VSS – get_cognos_outlays and get_cognos_obligations
    • query to get monthly outlay data – 59958230400 is for 1900-01-01 dates which should be ignored
      • SELECT sub_budget_center, requisition_id,
        MONTH(committed_date), YEAR(committed_date)
        committed_date, SUM(committed_amount)
        FROM `contracts_cognos`
        WHERE to_seconds(committed_date) > 59958230400
        GROUP BY sub_budget_center, requisition_id, MONTH(committed_date), YEAR(committed_date)
        ORDER BY sub_budget_center, requisition_id, YEAR(committed_date), MONTH(committed_date)
    • query to get monthly obligation data
      • SELECT sub_budget_center, requisition_id,
        MONTH(obligation_date), YEAR(obligation_date)
        committed_date, SUM(obligated_amount)
        FROM `contracts_cognos`
        WHERE to_seconds(obligation_date) > 59958230400
        GROUP BY sub_budget_center, requisition_id, MONTH(obligation_date), YEAR(obligation_date)
        ORDER BY sub_budget_center, requisition_id, YEAR(obligation_date), MONTH(obligation_date)