Table E xls, does it show the totals in the gray cells?

[khuffman]

Question: On your site, under Admin Views when you go to Table E, and then click the "Download this page" link to get the xls file, in the xls file you get, do you have the total numbers for Published Publications, New Faculty Hires, Post Docs, and Grad & Undergrad graduations in the gray cells in each column? or are the gray cells empty? Our PA says we should be displaying the "Totals for Published Publications, New Faculty Hires, Post Docs, and Grad & Undergrad graduations in the gray cells. " How does your site display this Table E xls file?

[aturling]

I just checked and ours does not display the totals. I never noticed they were missing - visually, the darker cells look like section dividers.

[ercore]

NM live reporting site is populating both the Total Current Reporting Period and the Cumulative Total for the Award, for all rows on the sheet.

[khuffman]

@aturling Yes, all these years I thought also the gray marked rows were section dividers. @ercore just to verify: you say on your site, in the gray row labelled "Total New Faculty Hired" you see numbers in the gray colored row? On our site we have numbers in all white cells, but we have no numbers in the gray colored cells.

[ercore]

Oh, I missed the gray colored row part! No, ours does not total that up. Honestly, I don't think it ever did.

@khuffman Does your PA manually add the total? I imagine everyone does.

[ercore]

I am reviewing past years final templates (submitted and approved) and there are no numbers in those rows. I will double check with our PA, as well as the consortium to find out if these cells actually need to be populated.

[khuffman]

OK, thanks. I always thought the gray cells/rows were not meant to be filled in. I will verify again with our PA to make sure I correctly understood this issue on our to-do list.

[cjallen-epscor]

I've never recalled data being populated in those gray shaded areas. I too thought they were dividers.

[khuffman]

I modified our output.inc file, I made some changes to the write_excel() function. Now our Table E xls sheet shows the totals for Published Publications, New Faculty Hires, Post Docs, and Grad & Undergrad graduations. Here below is my modified write_excel() function, not sure if this is the best way to get the totals, but it works.

  public function write_excel(&$objPHPExcel, $sheet = 0){
    $data = $this->data();
        //d($data, '$data');
    $worksheet = $objPHPExcel->setActiveSheetIndex($sheet);

    $hastotal_row = array("Published Publications","Total New Faculty Hired","Total Post Docs Involved","Total Graduate Students Graduated", "Total Undergraduates Graduated");
    $people_rel_total_rows = array("Total New Faculty Hired","Total Post Docs Involved","Total Graduate Students Graduated", "Total Undergraduates Graduated");

    $base_row = 4;
        $col = 3;
        foreach ($data as $range_label => $categories){
            $row = $base_row;
            foreach ($categories as $cat_key=>$cat_data){
            unset ($tot);
                foreach ($cat_data as $row_key=>$row_data){

                    if ($cat_key == "Proposals/Grants/Contracts"){//just need to treat the second one different
                    $worksheet->setCellValueByColumnAndRow($col, $row, intval($row_data['count']));
                    $worksheet->setCellValueByColumnAndRow($col+1, $row, intval($row_data['amount']));

                    }elseif(in_array($cat_key, $hastotal_row) ){
                    $tot = $tot + intval($row_data['count']);
                    $worksheet->setCellValueByColumnAndRow($col, $row, intval($row_data['count']));
                    }else{
                    $worksheet->setCellValueByColumnAndRow($col, $row, intval($row_data['count']));
                    }
                    $row++;
                }
                if ($cat_key == "Published Publications"){
                $worksheet->setCellValueByColumnAndRow($col, $row-3, "Total: ".intval($tot));
                } elseif (in_array($cat_key, $people_rel_total_rows) ){
                $worksheet->setCellValueByColumnAndRow($col, $row-5, "Total: ".intval($tot));
                } else {
                //
                }

                $row++;

            }
            $col -= 2;
        }
  }