Category Archives: VISIBILITY

Phil 3.13.14

8:00 – 5:00 SR

  • Backups
  • Charts! Made one, decided that it might be easier to update the Trend Widget to point at different data sources. Wound up allowing edits on the XML in the desktop “save as” option. Built a release and sent to Bill V.
  • Deploying new FA
  • JavaScript

Dong Shin 03.12.2014

  • deployed FA and the new view __view_financial_data
    • committed need to rollover to next month
  • working on queries/scripts for reports
    • create queries for 2013, 2014 , each appropriation, and each capability – 30 total
    • created script to run the queries and save to Visibility database

Phil 3.12.14

8:00 – 4:00 SR

  • Backups
  • Deployed new FA, and a *huge* view
  • Lenny is having a problem where EAs that have been claimed are getting unclaimed.
  • Printed slideshow screenshots that we need to match
  • Had the evil “Main application must be in the list of application paths” message. This page had the answer. It looks like the mxml file had been dropped from the project file.
  • Found the code and the format for building reusable charts. Dong’s building the queries. We’ll try them out tomorrow.
  • JavaScript

Dong Shin 03.11.2014

  • trouble-shoot Req mapping problem, various of mistakes… noted on Phil’s blog
  • working on queries for reports
  • created Req Data Analysis panel and added to FA
  • __view_financial_data will be added to site

Phil 8.11.14

8:00 – 5:30 SR

  • Backups
  • More Server Cert work
  • Discussions with Lenny
  • Need to add a search to the page that will find text in paragraphs and link back to the Physics Shape. All paragraphs that don’t match the text are hidden? Wonder how that will affect the scrollto. Also need an introduction paragraph and a title.
  • When a cognos data is manually mapped, obligations and outlays should be added to monthly financial data
  • Second year query needs to be fixed for month order
  • Need to add a pop-up warning about claiming a req where the subBC doesn’t match.
  • RQ notes
    • Add a show/hide hidden fields. This does mean that when checked, an item disappears
    • Add “strong hide” that keeps the line in the DB, but never shows the line
    • Carry through ignore from previous steps. In other words, ignore “hidden” items from subsequent queries
  • JavaScript
    • Figured out how to grab a redirect request and use it in the context of the operation. Also how to prevent default behavior.
    • Added content. Discovered more about stylesheets.
    • The descriptor text for Physics shapes was disappearing after a mousup event on the scrollable list. Added a refresh call to the ShapeManager.
  • Helped Dong a bit with data providers.

Dong Shin 03.10.2014

  • Lenny reported that Req 173756 doesn’t show Obligation Amount / Date in Claimed section… will take a look tomorrow
  • working on little Flex App to verify the Financial Data
  • reworked Financial Data query for report to generate all the data for all years
  • __view_financial_data contains….
    • SELECT ‘Obligation Goal’ AS oo_type,
      p.project_number, p.title, p.begin_year, p.end_year, p.center_name, p.center_number, p.appropriation,
      p.type, p.capability, p.expenditure_center, p.investment_portfolio, p.program_element, p.facts_pe, p.sub_center_name, p.sub_center_number,
      bc.req_id, bc.req_type, bc.total_budget, bc.funded_budget, bc.fiscal_year, oo.year as year_count,
      (bc.funded_budget * obligation_month_1 / 100) as month_1,
      (bc.funded_budget * obligation_month_2 / 100) as month_2,
      (bc.funded_budget * obligation_month_3 / 100) as month_3,
      (bc.funded_budget * obligation_month_4 / 100) as month_4,
      (bc.funded_budget * obligation_month_5 / 100) as month_5,
      (bc.funded_budget * obligation_month_6 / 100) as month_6,
      (bc.funded_budget * obligation_month_7 / 100) as month_7,
      (bc.funded_budget * obligation_month_8 / 100) as month_8,
      (bc.funded_budget * obligation_month_9 / 100) as month_9,
      (bc.funded_budget * obligation_month_10 / 100) as month_10,
      (bc.funded_budget * obligation_month_11 / 100) as month_11,
      (bc.funded_budget * obligation_month_12 / 100) as month_12
      FROM projects p
      LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
      LEFT JOIN obligations_outlays_goals AS oo ON p.appropriation = oo.appropriation
      UNION
      SELECT ‘Outlay Goal’ AS oo_type,
      p.project_number, p.title, p.begin_year, p.end_year, p.center_name, p.center_number, p.appropriation,
      p.type, p.capability, p.expenditure_center, p.investment_portfolio, p.program_element, p.facts_pe, p.sub_center_name, p.sub_center_number,
      bc.req_id, bc.req_type, bc.total_budget, bc.funded_budget, bc.fiscal_year, oo.year as year_count,
      (bc.funded_budget * outlay_month_1 / 100) as month_1,
      (bc.funded_budget * outlay_month_2 / 100) as month_2,
      (bc.funded_budget * outlay_month_3 / 100) as month_3,
      (bc.funded_budget * outlay_month_4 / 100) as month_4,
      (bc.funded_budget * outlay_month_5 / 100) as month_5,
      (bc.funded_budget * outlay_month_6 / 100) as month_6,
      (bc.funded_budget * outlay_month_7 / 100) as month_7,
      (bc.funded_budget * outlay_month_8 / 100) as month_8,
      (bc.funded_budget * outlay_month_9 / 100) as month_9,
      (bc.funded_budget * outlay_month_10 / 100) as month_10,
      (bc.funded_budget * outlay_month_11 / 100) as month_11,
      (bc.funded_budget * outlay_month_12 / 100) as month_12
      FROM projects p
      LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
      LEFT JOIN obligations_outlays_goals AS oo ON p.appropriation = oo.appropriation
      UNION
      SELECT ‘Committed’ AS oo_type,
      p.project_number, p.title, p.begin_year, p.end_year, p.center_name, p.center_number, p.appropriation,
      p.type, p.capability, p.expenditure_center, p.investment_portfolio, p.program_element, p.facts_pe, p.sub_center_name, p.sub_center_number,
      bc.req_id, bc.req_type, bc.total_budget, bc.funded_budget, bc.fiscal_year,
      (IF(MONTH(committed_date)>=10, YEAR(committed_date)+1, YEAR(committed_date)) – bc.fiscal_year + 1) AS year_count,
      SUM(IF(month(committed_date)=10, committed_amount, 0)) as month_1,
      SUM(IF(month(committed_date)=11, committed_amount, 0)) as month_2,
      SUM(IF(month(committed_date)=12, committed_amount, 0)) as month_3,
      SUM(IF(month(committed_date)=1, committed_amount, 0)) as month_4,
      SUM(IF(month(committed_date)=2, committed_amount, 0)) as month_5,
      SUM(IF(month(committed_date)=3, committed_amount, 0)) as month_6,
      SUM(IF(month(committed_date)=4, committed_amount, 0)) as month_7,
      SUM(IF(month(committed_date)=5, committed_amount, 0)) as month_8,
      SUM(IF(month(committed_date)=6, committed_amount, 0)) as month_9,
      SUM(IF(month(committed_date)=7, committed_amount, 0)) as month_10,
      SUM(IF(month(committed_date)=8, committed_amount, 0)) as month_11,
      SUM(IF(month(committed_date)=9, committed_amount, 0)) as month_12
      FROM projects p
      LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
      LEFT JOIN committed_amounts AS ca ON ca.budget_center_id = bc.uid
      GROUP BY budget_center_id, IF(MONTH(committed_date)>=10, YEAR(committed_date)+1, YEAR(committed_date))
      UNION
      SELECT
      IF (oo.type = ‘Reported Outlay $:’, ‘Outlays’, IF(oo.type = ‘Reported FACTS Obligated $:’, ‘Obligated’, ‘PM Actuals’)) AS oo_type,
      p.project_number, p.title, p.begin_year, p.end_year, p.center_name, p.center_number, p.appropriation,
      p.type, p.capability, p.expenditure_center, p.investment_portfolio, p.program_element, p.facts_pe, p.sub_center_name, p.sub_center_number,
      bc.req_id, bc.req_type, bc.total_budget, bc.funded_budget, bc.fiscal_year, oo.year_count,
      month_1, month_2, month_3, month_4, month_5, month_6, month_7, month_8, month_9, month_10, month_11, month_12
      FROM projects p
      LEFT JOIN budget_centers AS bc ON p.uid = bc.project_id
      LEFT JOIN obligations_outlays AS oo ON p.uid = oo.project_id AND bc.uid = oo.funding_id
      WHERE (oo.type = ‘Reported Outlay $:’ OR oo.type = ‘Reported FACTS Obligated $:’ OR oo.type = ‘PM Actuals (Invoiced) Outlay $:’)

Phil 3.10.14

8:00 – 4:00 SR

  • Backups
  • Started server certs renewal
  • There are still some problems with lost req’s. Got a screenshot that has all the pieces.
  • Javascript
    • Find out while null objects are being returned in events. It turns out that the “id” tag cannot have any special characters in it, or Y.one()/Y.all() will fail. Added a “safeIdString” variable to the PhysicsShape class that runs a regex [^0-9-a-Z] on all names and copies the result to the variable. This still means that the id tag for paragraphs, etc needs to be safe, but it also allows the shapes to show the full name.
    • Discovered that my Chrome browser on my dev machine is broken. Tooltip formatting is wrong and hover does not work at all. Need to reinstall. And I.E. is still slooooooow with all this

Dong Shin 03.07.2014

  • deployed SQL’s update the obligations/outlays to 100% – works!
  • found that some of the COGNOS contracts data have budget_center_id set to 0, it should be null or some budget_center_id. This caused the data not to show up in claimed/unclaimed contracts in Req Info panel
    • cleaned up 0’s and some of the data that were deleted
    • need a SQL to clean up before mapping?
  • looked at Carla’s project saving error, caused by long budget center name from budget_center_names table. cleaned up the table.

Dong Shin 03.06.2014

  • trying to figure out if months can be updated in a single query……. instead of separate queries for each month!
    • UPDATE obligations_outlays oo
      SET month_12 = CASE
      WHEN (month_11 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_11
      WHEN (month_10 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_10
      WHEN (month_9 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_9
      WHEN (month_8 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_8
      WHEN (month_7 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_7
      WHEN (month_6 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_6
      WHEN (month_5 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_5
      WHEN (month_4 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_4
      WHEN (month_3 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_3
      WHEN (month_2 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_2
      WHEN (month_1 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_1
      ELSE month_12 END,
      month_11 = CASE
      …………………….
      month_2 = CASE
      WHEN (month_1 >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)) THEN month_1
      ELSE month_2 END
      WHERE oo.year_count = 1
      AND (oo.type = ‘Outlay $ (Reported in FACTS)’ OR oo.type = ‘Reported FACTS Obligated $:’)
  • finally got the query working! – that update first month second year when last month first year is at 100%
    • UPDATE obligations_outlays oo1
      LEFT JOIN obligations_outlays oo2
      ON (oo1.project_id = oo2.project_id
      AND oo1.funding_id = oo2.funding_id
      AND oo1.type = oo2.type
      AND oo1.uid <> oo2.uid)
      LEFT JOIN budget_centers bc
      ON( oo1.funding_id = bc.uid)
      AND (oo1.type = ‘Outlay $ (Reported in FACTS)’ OR oo1.type = ‘Reported FACTS Obligated $:’)
      AND (oo2.month_12 >= bc.total_budget)
      SET oo1.month_1 = oo2.month_12
      WHERE oo1.year_count = 2 AND oo2.year_count = 1
      AND (oo1.type = ‘Outlay $ (Reported in FACTS)’ OR oo1.type = ‘Reported FACTS Obligated $:’)
  • added months updates queries tor year 2 in Post Processing
  • talked Kelley yesterday and passed along the contact info, she said she would email me the details, never did
    • emailed Kelley this morning and got response, forward it to Chris

Phil 3.6.14

8:00 – 4:00 SR

  • Backups
  • Renewed the viztool urn and deleted visibility. Well, tried, anyway.
  • JavaScript
    • Add scrollto text based on item clicked event – Done, but now the scrolling function seems kinda messed up. The scroller wants to bounce back to some point that seems to be related to long contact drag times. Short flicks work?
    • See if scrolling can fire an event that can be used to get the current visible text (id? label?) that can be used to determine what to show in the network chart. Alternatively, listen to click events from the wrapping node or potentially the <p> items. Done!
    • Break slideshow into sections that are relevant to the clicked items.

Phil 3.5.14

8:00 – 4:00 SR

  • Backups
  • Resubmitted more paperwork for to keep my server access
  • Fixed some queries with Dong. Did you know that in SQL 10+0 != 10+NULL?
  • JavaScript
    • Finally fixed the ‘max pixels’ bug in the PhysicsShape. Vectors were being calculated for anchor objects, when they should have been zero.
    • Boo. You can’t animate colors on a shape.

Dong Shin 03.05.2014

  • put old Reqonciler back to resolve the datagrid issue
  • updated 2nd year query – success after some fiddling…
    • clean obligation/outlays in the first step using SET month = 0, not SET month=NULL.
    • changed the query to update Obligations, not Outlays
  • query to update Obligations/Outlays when it reaches 100%?
    • UPDATE obligations_outlays oo
      SET month_12 = GREATEST(IFNULL(month_1,0), IFNULL(month_2,0), IFNULL(month_3,0), IFNULL(month_4,0), IFNULL(month_5,0), IFNULL(month_6,0),
      IFNULL(month_7,0), IFNULL(month_8,0), IFNULL(month_9,0), IFNULL(month_10,0), IFNULL(month_11,0), IFNULL(month_12,0))
      WHERE oo.year_count = 1
      AND GREATEST(IFNULL(month_1,0), IFNULL(month_2,0), IFNULL(month_3,0), IFNULL(month_4,0), IFNULL(month_5,0), IFNULL(month_6,0),
      IFNULL(month_7,0), IFNULL(month_8,0), IFNULL(month_9,0), IFNULL(month_10,0), IFNULL(month_11,0), IFNULL(month_12,0)) >= (SELECT bc.total_budget FROM budget_centers bc WHERE bc.uid = oo.funding_id)
      AND (oo.type = ‘Outlay $ (Reported in FACTS)’ OR oo.type = ‘Reported FACTS Obligated $:’)
  • query to find second year that needs to be set to 100%
    • SELECT IFNULL(oo1.month_1,0), IFNULL(oo2.month_12,0)
      FROM obligations_outlays oo1, obligations_outlays oo2, budget_centers bc
      WHERE oo1.year_count = 2 AND oo2.year_count = 1
      AND oo1.project_id = oo2.project_id AND oo1.funding_id = oo2.funding_id AND oo1.type = oo2.type
      AND (
      oo1.type = ‘Outlay $ (Reported in FACTS)’
      OR oo1.type = ‘Reported FACTS Obligated $:’
      )
      AND oo2.month_12 >= bc.total_budget
      AND oo1.funding_id = bc.uid

Phil 3.4.14

8:00 – 4:00 SR

  • Heading in to work on the servers at 2:00, due to the snow. Javascript this morning.
  • Adding scrolling – done! Very nice. You can tell the scroller to go to a particular section. You can even name the section.
  • Turning slideshow into a module so that it can be faded up and down as a whole, and that I can put the Ken Burns effect in.
  • Reqonciler ignore selection doesn’t work – data grid problem?
  • Bring in second year updates query.

Dong Shin 03.04.2014

  • deployed database updates and new queries
    • Reqonciler has some datagrid problem – not updating hide_flags and weird mouse over highlighting, but no problems here back at the office…
    • second year processing missing, need to add this to post-processing – in reqonciler_processing.sql
      • UPDATE obligations_outlays o1,
        (SELECT month_12 FROM obligations_outlays oo2 WHERE type = ‘Outlay $ (Reported in FACTS)’ AND year_count = 1) AS o2
        SET o1.month_1 = o2.month_12 + o1.month_1,
        o1.month_2 = o2.month_12 + o1.month_2,
        o1.month_3 = o2.month_12 + o1.month_3,
        o1.month_4 = o2.month_12 + o1.month_4,
        o1.month_5 = o2.month_12 + o1.month_5,
        o1.month_6 = o2.month_12 + o1.month_6,
        o1.month_7 = o2.month_12 + o1.month_7,
        o1.month_8 = o2.month_12 + o1.month_8,
        o1.month_9 = o2.month_12 + o1.month_9,
        o1.month_10 = o2.month_12 + o1.month_10,
        o1.month_11 = o2.month_12 + o1.month_11,
        o1.month_12 = o2.month_12 + o1.month_12
        WHERE type = ‘Outlay $ (Reported in FACTS)’
        AND (year + year_count) <= (IF(MONTH(CURDATE()) > 9, YEAR(CURDATE()) + 2, YEAR(CURDATE()) + 1))
        AND year_count = 2
  • working on query for Chris/Lenny – show PM Actuals and Planned Outlay with differences in $ and % for current month
    • capability AS Capability, bcc.budget_center_name AS ‘Budget Center’, req_id AS ‘Req ID’, req_description AS ‘Req Title’,
      vrt.req_id_total AS ‘Req Funded Amount’,
      IF(MONTH(CURDATE())=10,SUM(IF(o.type=’Planned Outlay $:’,month_1,0)),IF(MONTH(CURDATE())=11,SUM(IF(o.type=’Planned Outlay $:’,month_2,0)),IF(MONTH(CURDATE())=12,SUM(IF(o.type=’Planned Outlay $:’,month_3,0)),IF(MONTH(CURDATE())=1,SUM(IF(o.type=’Planned Outlay $:’,month_4,0)),IF(MONTH(CURDATE())=2,SUM(IF(o.type=’Planned Outlay $:’,month_5,0)),IF(MONTH(CURDATE())=3,SUM(IF(o.type=’Planned Outlay $:’,month_6,0)),IF(MONTH(CURDATE())=4,SUM(IF(o.type=’Planned Outlay $:’,month_7,0)),IF(MONTH(CURDATE())=5,SUM(IF(o.type=’Planned Outlay $:’,month_8,0)),IF(MONTH(CURDATE())=6,SUM(IF(o.type=’Planned Outlay $:’,month_9,0)),IF(MONTH(CURDATE())=7,SUM(IF(o.type=’Planned Outlay $:’,month_10,0)),IF(MONTH(CURDATE())=8,SUM(IF(o.type=’Planned Outlay $:’,month_11,0)),IF(MONTH(CURDATE())=9,SUM(IF(o.type=’Planned Outlay $:’,month_12,0)),0)))))))))))) AS ‘Outlay’,
      IF(MONTH(CURDATE())=10,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_1,0)),IF(MONTH(CURDATE())=11,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_2,0)),IF(MONTH(CURDATE())=12,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_3,0)),IF(MONTH(CURDATE())=1,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_4,0)),IF(MONTH(CURDATE())=2,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_5,0)),IF(MONTH(CURDATE())=3,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_6,0)),IF(MONTH(CURDATE())=4,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_7,0)),IF(MONTH(CURDATE())=5,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_8,0)),IF(MONTH(CURDATE())=6,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_9,0)),IF(MONTH(CURDATE())=7,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_10,0)),IF(MONTH(CURDATE())=8,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_11,0)),IF(MONTH(CURDATE())=9,SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_12,0)),0)))))))))))) AS ‘PM Actuals’,
      IF(MONTH(CURDATE())=10,SUM(IF(o.type=’Planned Outlay $:’,month_1,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_1,0)),IF(MONTH(CURDATE())=11,SUM(IF(o.type=’Planned Outlay $:’,month_2,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_2,0)),IF(MONTH(CURDATE())=12,SUM(IF(o.type=’Planned Outlay $:’,month_3,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_3,0)),IF(MONTH(CURDATE())=1,SUM(IF(o.type=’Planned Outlay $:’,month_4,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_4,0)),IF(MONTH(CURDATE())=2,SUM(IF(o.type=’Planned Outlay $:’,month_5,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_5,0)),IF(MONTH(CURDATE())=3,SUM(IF(o.type=’Planned Outlay $:’,month_6,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_6,0)),IF(MONTH(CURDATE())=4,SUM(IF(o.type=’Planned Outlay $:’,month_7,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_7,0)),IF(MONTH(CURDATE())=5,SUM(IF(o.type=’Planned Outlay $:’,month_8,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_8,0)),IF(MONTH(CURDATE())=6,SUM(IF(o.type=’Planned Outlay $:’,month_9,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_9,0)),IF(MONTH(CURDATE())=7,SUM(IF(o.type=’Planned Outlay $:’,month_10,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_10,0)),IF(MONTH(CURDATE())=8,SUM(IF(o.type=’Planned Outlay $:’,month_11,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_11,0)),IF(MONTH(CURDATE())=9,SUM(IF(o.type=’Planned Outlay $:’,month_12,0))-SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_12,0)),0)))))))))))) AS ‘Diff $’,
      IF(MONTH(CURDATE())=10,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_1,0))-SUM(IF(o.type=’Planned Outlay $:’,month_1,0))*100,1),’%’),IF(MONTH(CURDATE())=11,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_2,0))/SUM(IF(o.type=’Planned Outlay $:’,month_2,0))*100,1),’%’),IF(MONTH(CURDATE())=12,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_3,0))/SUM(IF(o.type=’Planned Outlay $:’,month_3,0))*100,1),’%’),IF(MONTH(CURDATE())=1,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_4,0))/SUM(IF(o.type=’Planned Outlay $:’,month_4,0))*100,1),’%’),IF(MONTH(CURDATE())=2,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_5,0))/SUM(IF(o.type=’Planned Outlay $:’,month_5,0))*100,1),’%’),IF(MONTH(CURDATE())=3,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_6,0))/SUM(IF(o.type=’Planned Outlay $:’,month_6,0))*100,1),’%’),IF(MONTH(CURDATE())=4,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_7,0))/SUM(IF(o.type=’Planned Outlay $:’,month_7,0))*100,1),’%’),IF(MONTH(CURDATE())=5,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_8,0))/SUM(IF(o.type=’Planned Outlay $:’,month_8,0))*100,1),’%’),IF(MONTH(CURDATE())=6,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_9,0))/SUM(IF(o.type=’Planned Outlay $:’,month_9,0))*100,1),’%’),IF(MONTH(CURDATE())=7,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_10,0))/SUM(IF(o.type=’Planned Outlay $:’,month_10,0))*100,1),’%’),IF(MONTH(CURDATE())=8,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_11,0))/SUM(IF(o.type=’Planned Outlay $:’,month_11,0))*100,1),’%’),IF(MONTH(CURDATE())=8,CONCAT(ROUND(SUM(IF(o.type=’PM Actuals (Invoiced) Outlay $:’,month_12,0))/SUM(IF(o.type=’Planned Outlay $:’,month_12,0))*100,1),’%’),0)))))))))))) AS ‘Diff %’
      WHERE fiscal_year=2014
      GROUP BY capability, bc.sub_budget_center_number, req_id