KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Report not working #1599

Open Richb201 opened this topic on on Aug 27, 2020 - 8 comments

Richb201 commented on Aug 27, 2020

Here is the full MyReport and the MyReport,view. It is a work in process and I am starting at the top to get the first report working. The part I am trying to get going is threeway title RA, which is right near the top in both .php files.

<?php
use \koolreport\processes\Filter;
use \koolreport\processes\Group;
use \koolreport\processes\ColumnRename;
use \koolreport\widgets\google;

class MyReport extends \koolreport\KoolReport
{

  use \koolreport\codeigniter\Friendship;
  use \koolreport\inputs\Bindable;
   use \koolreport\inputs\POSTBinding;

    public function settings()
    {

        return array(
            "dataSources" => array(
                "substantiator" => array(
                    "connectionString" => "mysql:host=mysql;dbname=mysql_databases",
                    'host' => 'mysql',
                    'username' => 'admin',
                    'password' => 'sadfs$.df3fg',
                    'dbname' => 'substantiator',
                    'charset' => 'utf8',
                    'class' => "\koolreport\datasources\MySQLDataSource"
                ),
                "activity_datasource"=>array(
                    "class"=>'\koolreport\datasources\CSVDataSource',
                    "filePath"=>"/app/assets/output/activity_Applerichb201@gmail.com",
                    "fieldSeparator"=>",",
                ),
                "bus_comps_datasource"=>array(
                    "class"=>'\koolreport\datasources\CSVDataSource',
                    "filePath"=>"/app/assets/output/buscomps_Applerichb201@gmail.com",
                    "fieldSeparator"=>",",
                ),
                "contemporaneous_datasource"=>array(
                    "class"=>'\koolreport\datasources\CSVDataSource',
                    "filePath"=>"/app/assets/output/contemporaneous_Applerichb201@gmail.com",
                    "fieldSeparator"=>",",
                ),
            ),

        );

    }
    function setup()
    {
//        echo 'this is report setup<br>';
        $campaign=$_SESSION['campaign'];
        $email=$_SESSION['userid'];

//create threeway title RA
        $sql="
      
        SELECT e.employee, e.employee_title, e.w2_wages, t.risk, 
             CASE 
                 WHEN risk='low' THEN  e.w2_wages
                 WHEN risk='medium' THEN  e.w2_wages*.5
                 ELSE 0
              END as qualified
        FROM employees as e, titles as t
        WHERE e.email='$email' AND e.campaign='$campaign' 
        AND t.email='$email' AND t.campaign='$campaign'
        AND e.employee_title=t.title
        ORDER BY e.employee
        ";
        $this->src('substantiator')
            ->query($sql)
            ->pipe($this->dataStore("Three Way Risk Analysis"));

        $this->src('substantiator')
            ->query("SELECT bus_comp FROM business_components WHERE campaign='$campaign' AND email='$email'")
            ->pipe(new ColumnRename(array(
            "bus_comp"=>"Business Component")))
            ->pipe($this->dataStore("business_components"));

        $this->src('substantiator')
            ->query("SELECT project FROM project WHERE campaign='$campaign' AND email='$email'")
            ->saveTo($PR)
            ->pipe($this->dataStore("project"));

        $this->src('substantiator')
            ->query("SELECT cost_center_number, cost_center_name FROM cost_center WHERE campaign='$campaign' AND email='$email'")
            ->pipe(new ColumnRename(array(
                "cost_center_number"=>"number","cost_center_name"=>"name")))
            ->saveTo($CC)
            ->pipe($this->dataStore("cost_center"));


        $this->src('substantiator')
            ->query("SELECT description FROM campaigns2 WHERE company_division='$campaign' AND email='$email'")
//            ->pipe(new ColumnRename(array(
//                "description"=>"company")))
            ->saveTo($CA)
            ->pipe($this->dataStore("campaigns2"));

        $this->src('substantiator')
            ->query("SELECT products FROM campaigns2 WHERE company_division='$campaign' AND email='$email'")
 //           ->pipe(new ColumnRename(array(
 //               "description"=>"company")))
            ->saveTo($PR)
            ->pipe($this->dataStore("campaigns22"));


        $this->src('substantiator')
            ->query("SELECT URL FROM images WHERE campaign='$campaign' AND email='$email'")
            // ->pipe(new ColumnRename(array(
            //     "description"=>"company")))
            ->saveTo($IM)
            ->pipe($this->dataStore("images"));

        $this->src('substantiator')
        ->query("SELECT e.employee, e.employee_title, e.w2_wages, risk, 
             CASE 
                 WHEN risk='low' THEN  w2_wages
                 WHEN risk='medium' THEN  w2_wages*.5
                 ELSE 0
              END as qualified
        FROM employees as e, titles as t
        WHERE e.email='$email' AND e.campaign='$campaign' 
        AND t.email='$email' AND t.campaign='$campaign'
        AND e.employee_title=t.title
        ORDER BY e.employee
        ")
            ->pipe($this->dataStore("titles"))
            ->pipe(new Group(array(
                "by"=>"job_title",
                "sum"=>"w2_wages"
            )));

        $this->src('substantiator')
            ->query("SELECT description, file_url FROM ppts WHERE campaign='$campaign'AND email='$email'")
            // ->pipe(new ColumnRename(array(
            //     "description"=>"company")))
            ->saveTo($PP)
            ->pipe($this->dataStore("ppts"));

     }

    protected function defaultParamValues()
    {

        return array(
            "datePicker" => $this->dataStore("campaigns23")->get(0,"taxyear")
        );
    }

    protected function bindParamsToInputs()
    {
        return array(
            "datePicker",
        );
    }
}

here is the ENTIRE MyReportView. It is also a work in progress.

<?php
//MyReport.view.php
use \koolreport\widgets\koolphp\Table;
use \koolreport\processes\Join;
use \koolreport\widgets\google\ColumnChart;
use \koolreport\widgets\google\PieChart;
use \koolreport\bootstrap4\Theme;
use \koolreport\inputs\DateTimePicker;
use \koolreport\inputs\DateRangePicker;

$campaign=$_SESSION['campaign'];
$email=$_SESSION['userid'];
?>
<html>
<head>
    <title>Research Study Toolkit</title></title>
</head>
<>
<style>
h1, h2, h3, h4, h5, h6 {
margin: 0;
font-family: inherit;
font-weight: bold;
color: inherit;
text-rendering: optimizelegibility;
}

    .koolphp-table td {
        font: 13px/20px normal Helvetica, Arial, sans-serif !important;
        color: #4f5155 !important;
    }
</style>

<br><br><br><br><br><br><br><br>

<img src="<?php echo base_url(); ?>assets/themes/default/images/new_blue_logo_sub240x240.png" style="float:right;margin-top:5px;z-index:5" alt="logo"/>
<img src="<?php echo base_url(); ?>assets/title.png" style="float:left;margin-top:5px;z-index:5" alt="logo"/>
<br>



<body>

<?php
    echo "<br><br><br><br><br><br><h3>Report for:  ".$_SESSION['campaign']." for tax year ended (YYYYMM)";
//    \koolreport\inputs\DateTimePicker::create(array("name" => "dateRange","format"=>"YYYYMM"));
?>

<form method="post">
    <?php
    DateTimePicker::create(array(
        "name" => "datePicker",
        "maxDate" => "@endDatePicker",
        "format" => "YYYYMM",
        "themeBase" => "bs4",
    ));
    ?>
    <button type="submit">Submit</button>
</form>


<?php
//got a new date so write it out to the mysql server
if (!empty($_POST["datePicker"]))
    echo $_POST["datePicker"];        //entered date  //so write out date
?>

<img src="<?php echo base_url();  ?>assets/BUSCF015.JPG" />

<p style="page-break-before: always">
    p.toc_list {
    font: 15px Arial, sans-serif;
    }
<h1 style="font-weight: bold;color:black;font-size:40px"> Table of Contents </h1>
<div id="toc_container">
    <p class="toc_title">Contents</p>

    <ul class="toc_list">
        <li><a href="#First_Point_Header">1 Summary</a>
            <ul>
                <li><a href="#First_Sub_Point_1">1.1 3-Way Title Risk Analysis</a></li>
                <li><a href="#First_Sub_Point_2">1.2 Activity Tracker</a>
                <li><a href="#First_Sub_Point_3">1.3 Cost Center Analysis</a></li>
                <li><a href="#First_Sub_Point_4">1.4 Surveys</a></li>
                <li><a href="#First_Sub_Point_4">1.5 Consultants</a></li>
                <li><a href="#First_Sub_Point_4">1.4 Contracts</a></li>
            </ul>
        </li>
        <li><a href="#First_Point_Header">2 Methodology</a>
            <ul>
                <li><a href="#First_Sub_Point_1">2.1 Company Overview</a></li>
                <li><a href="#First_Sub_Point_2">2.2 Products</a>
                <li><a href="#First_Sub_Point_3">2.3 Research Study Toolkit Definitions</a></li>
                <li><a href="#First_Sub_Point_4">2.4 Job Title Descriptions</a></li>
                <li><a href="#First_Sub_Point_5">2.5 Development Process</a></li>
                <li><a href="#First_Sub_Point_6">2.6 Four Method Descriptions</a></li>
            </ul>
        </li>

        <li><a href="#Second_Point_Header">3 Tax Law Primer</a></li>
        <li><a href="#Third_Point_Header">4 Business Components</a></li>
        <li><a href="#Fourth_Point_Header">5 Projects</a></li>
        <li><a href="#Fifth_Point_Header">6 Activities</a></li>
        <li><a href="#Sixth_Point_Header">7 Cost Centers</a></li>
        <li><a href="#Seventh_Point_Header">8 Sampling</a></li>
        <li><a href="#Eigth_Point_Header">9 Employees</a></li>
        <li><a href="#Ninth_Point_Header">10 Contractors</a></li>
        <li><a href="#Tenth_Point_Header">11 Screenshots</a></li>
        <li><a href="#Eleven_Point_Header">12 Presentations</a></li>
        <li><a href="#Twelth_Point_Header">13 Powerpoints</a></li>
        <li><a href="#Thirteeenth_Point_Header">14 Spreadsheets</a></li>
        <li><a href="#Fourtheenth_Point_Header">15 Word Documents</a></li>
        <li><a href="#Fifteenth_Point_Header">16 SME Business Component Interviews</a></li>
        <li><a href="#Sixteeenth_Point_Header">17 SME Activity Estimates</a></li>
        <li><a href="#Seventheenth_Point_Header">18 Employee Interview Transcripts</a></li>
        <li><a href="#Eighteenth_Point_Header">19 Employee Surveys</a></li>
        <li><a href="#Nineteenth_Point_Header">20 Contracts</a></li>
        <li><a href="#Nineteenth_Point_Header">21 Organizational Chart</a>
        <li><a href="#Eighteenth_Point_Header">22 Title Risk Analysis</a></li>
    </ul>
</div>
</p>


<br>
<br>
<br>
<p style="page-break-before: always">
<h2 id="First_Point_Header" style="font-weight: bold;color:black;font-size:40px">1.  Summary </h2>
<h2> 1.1 3-Way Title Risk </h2>
<style>
    #myTable td {
        padding: 8px;
        line-height: 18px;
        text-align: left;
        vertical-align: top;
        border-top: 1px solid #dddddd;
    }
    <?php

Table::create(array(
    "dataStore"=>$this->dataStore("Three Way Risk Analysis"),
    "class"=>array(
        "table"=>"table table-hover"
    )
));
        ?>
</style>
<h2> 1.2 Activity Tracker </h2>
    <?php
    Table::create(array(
        "dataStore"=>$this->dataStore("campaigns2"),
        "class"=>array(
             "table"=>"table table-hover"
        )
    ));
    ?>
<h2> 1.3 Cost Center </h2>
    <?php
    Table::create(array(
        "dataStore"=>$this->dataStore("campaigns2"),
        "class"=>array(
             "table"=>"table table-hover"
        )
    ));
    ?>
<h2> 1.4 Survey Analysis </h2>
    <?php
    Table::create(array(
        "dataStore"=>$this->dataStore("campaigns2"),
        "class"=>array(
             "table"=>"table table-hover"
        )
    ));
    ?>
<h2> 1.5 Consultants </h2>
<?php
Table::create(array(
    "dataStore"=>$this->dataStore("campaigns2"),
    "class"=>array(
        "table"=>"table table-hover"
    )
));
?>
<h2> 1.6 Contracts </h2>
<?php
Table::create(array(
    "dataStore"=>$this->dataStore("contracts"),
    "class"=>array(
        "table"=>"table table-hover"
    )
));
?>
<h2 id="First_Point_Header" style="font-weight: bold;color:black;font-size:40px">2.  Methodology </h2>
<h2> 2.1 Division Background </h2>

<?php
Table::create(array(
    "dataStore"=>$this->dataStore("campaigns2"),
    "class"=>array(
         "table"=>"table table-hover"
    )
));
?>

<h2> 2.2 Products </h2>
<?php
Table::create(array(
    "dataStore"=>$this->dataStore("campaigns22"),
    "class"=>array(
        "table"=>"table table-hover"
    )
));
?>

<h2> 2.3 Study Toolkit Definitions </h2>
<h3>Project Basis</h3>

<div>
    This method is based on development projects. In this method, costs associated with a specific project are accumulated and totalled. Three types of costs are allowed for the research credit 1) wages 2) supplies 3) contract research. But before these 3 costs can be totalled, a project must be considered qualified. A project is considered qualifed if it is undertaken to be useful in the develoment of a Business Component. Pure research that is not for the creation or improvement of business component is not considered an "allowed reason".
</div>
<h3>Cost Center Basis</h3>

This method is based on a company's departments or divisions. Using this methodology a company will include all costs (wages, supplies, and contract research) for a department that it has determined is doing "qualified research". Just like in the project methodology, the research muct be associated with a business component. Software work that your company does for your customers might also be qualified, but depends on the "retention of risk" and who has the rights to the resultingh intellectual property. Analysis of contracts for qualification is well byiond the scope of this study.

<h3>Activity Basis</h3>

Since the Research Credit is based on the activities of the researchers (and their direct supervisors and their direct support staff), this is the most straight forward method to accumlate the costs that are qualified for the credit. For example if software engineer A is developing new Machine Readable Code that is for a new or improved business component (that meets the half a dozen or so other requirements), his wages are qualified for the credit. In addition to his wages, the wages of his direct manager and his direct support staff are also qualified. Employees who test the qualified MRC are also usually qualified.

<h3>ASC 730</h3>

Recently the IRS added ASC730 to the "allowed" methods for calculating the Research Credit. This method is based on a complex calculation using "audited financials" prepared by a CPA firm. As such this method is mostly used by large software firms with the capability to hire knowledagable CPA firms and is not addressed by the Software Research Study Toolkit.

<h3>Software Research Study Toolkit</h3>
The Software Research Study Toolkit uses both a decentralized timekeeping system and interviews with SME's to build this report. It addresses software for sale and internal use software in a unified fashion. Completely designed from the "ground up" to run on AWS Servers, the Software Research Study Toolkit is the most advanced system of its type. The Software Research Study Toolkit addresses 3 of the 4 methodologies that are most often used by small software businesses in developing a Research Credit Study.

<h3>Responsibility for Data</h3>
This study was generated by Research Credit Study Toolkit to support the calculation and substantiation of the IRC 41 Increasing Research Credit. The Research Credit is a non-refundable tax credit awarded to companies and individuals who perform qualified research activites in the US. While this report attempts to structure and arrange the data in a format likely to be useful in an audit by the IRS or state tax authorities, it offeres no guidance about the applicability of data entered into it. Ultimate responsibility for the filing is retained by the individual who signs the tax return. It is advised that this report be used to supply background information to a qualified CPA,  Taxation Engineer, or Enrolled Agent. The Software Research Study Toolkit merely presents user data.

<h3>Recordkeeping</h3>
The Software Research Toolkit was developed to simplify the record keeping associated with required substantiation for filing a resarch tax credit for Software. The development of software is one of the strongest industries in the US. Almost every company of any size develops some software to either sell (or lease) or for its own needs. That development work is likely qualified for a tax credit (both federal and state) if it meets certain requirements. The Software Research Toolkit was developed to help companies document the activities that help prove qualification. It does this by grouping together software features such as surveys, interviews, activity tracking, and interview transcripts and was designed specifically to help companies perform record keeping of their projects and activities. According to Pew Research, the average US adult works 1,811 hours per year. This statistic is used throughout the Software Research Study Toolkit. In addition, in keeping with the IRS rules regarding contract labor, all wages paid to contractors directly have been pre-reduced by the statutary 35% haircut.



<h2> 2.4 Job Titles </h2>
<?php
\koolreport\widgets\google\Table::create(array(
    "dataStore"=>$this->dataStore("employees2"),
    "class"=>array(
        "table"=>"table table-hover"
    )
));
?>
<h2> 2.5 Development </h2>
<h2> 2.6 Four Methods </h2>
<p></p>
<h2 id="Second_Point_Header" style="font-weight: bold;color:black;font-size:40px">2.  Tax Law Primer</h2>
<p></p>
<br>
<br>
The IRC 41 code and regs allow for a tax credit for Research and Development activities that are performed in the US. The US and many other countries (ie Japan, UK) have similair tax rules regarding R&D to encourage technological work to be performed in their states. Many US states have similair rules for their state taxes. The Federal law behind this tax credit (known as the Increasing Research Credit) had been temporary for many years, in the US. Recently this credit was made permanent.

A tax credit, unlike a tax deduction, is an amount that is subtracted directly fromn the "taxes due" line at the bottom of the tax return. Therefore, it itself, is not taxable. The Resarch credit is a non-refundable credit which means that a taxpayor will not receive a cash payment. Instead it is used to reduce the owed taxes.

To be able to claim the Increasing Research Credit a US taxpayor must be keep records in a format usable to substantiate the credit. The credit, which is filed on form 6765 and included in the yearly federal tax form, consists of a number amounts that are summed to come up wiith the final credit. This Research  Credit Study formalizes the process of gathering substantiation for companies that write software.
<p></p>
There are four types of software recognized by the IRC 41 tax law
<ol>
    <li>Internal Use Software</li>
    <li>Dual Use Software</li>
    <li>Software for Sale (or lease)</li>
    <li>Embedded Software</li>
</ol>


<h3>Four Part Test</h3><p></p>
The rules for the qualification of research (for the credit), known as the "4 part test", and are not specific to "software only" projects. The rules are:
<ol>
    <li>technological in nature test</li>
    <li>process of experimentation</li>
    <li>business component test</li>
    <li>substantially all test</li>li>
</ol>

<p>The 174 test requires that the expenditure be performed in an experimental or laboratory sense. Certain costs are specifically disallowed under 1.174-2(a)(3). These are: 1) orinary testing  for quality control 2) efficiency surveys 3) management studies 4) consumer surveys 5) advertising or promotions 6) purchase of anothers patent, model, or process 7) research in connection with literary, historical, or similair projects. </p>

<p>The Businesss Component test says that the discovered information must be useful in the development of a new product, process, software, technique, formula, or invention. By implication, research in a "broad category"  and not relating to a business component, is not qualified for the credit.</p>

<p>The Process of Experimentation requires that the research  be designed to evaluate one or more alternatives to prove the capability or method to achieve the results or the appropriate design. This assumes that appropriate design is unknown at the start of the research.A taxpayer must 1) identify the uncertainty 2) identify the alternatives intended to eliminate the uncertainty 3) conduct a process of evaulating the uncertainty.</p>

<p>The Technological in Nature test says that qualified research must be performed to eliminate uncertainty regarding the development or improvement of a business component. Proof of being issued a patent, known as the "patent safe harbor" is evidence of research qualified for the "technlogical in nature" test. But the issuance of a patent is in no way a requirement to file for the reserch credit. However, the work performed must rely on the principles of physical, biological, engineering, or computer science to satisfy this test.</p>

While all costs that qualify for the credit (aka QRE's) must meet the four part test, software that is Internal Use has an additional 3 rules to meet, known as the "high threshold of innovation" test. See IRC 1.41(c)(6)(vi) for more information. In 2015 the rules for both what is considered IUS and the high threshold of innovation test itself, changed. The Software Research Study Toolkit was designed to address these changes, not the previous requirement, aka the "innovativeness test".

There are many more details such as
<ol>
    <li>process of experimentation</li>
    <li>shrink back</li>
    <li>research after commercial production</li>
    <li>adaptation</li>
    <li>substantially all</li>
    <li>surveys, studies, managment functions</li>
    <li> combination of hardware and software</li>
</ol>

These are not addressed by the Software Research Credit Study Toolkit. Rather, these "special rules" (and others) should be considered by the qualified Tax Engineering specialist or CPA who actually ceates the form 6765 for the credit.
The determination of what is in IUS and what is not IUS can be quite complex and should be made by an experienced consultant.


Internal Use Software (IUS)

IUS is software that is developed by a firm for its own use in running its business.  Software that is used in an otherwise qualified process is not deemed, IUS.

Software for lease, license, or sale (aka regular software)

Software that is sold and installed on a PC. This may also include Webapps that run on remote servers or software that offers "computer services" to customers.

<p>
</p>
<h3>Dual Use Software</h3>

This is software that is partially IUS and partially regular software. In this special case, the costs to develop the IUS and the costs to develop the Regular Software are separated and there is a 25% safe harbor available for the qualified IUS costs.

<p>
<h3>Three Part Test for IUS</h3>
<ol>
<li>not commercially available test</li>
<li>high threshold of innovation test</li>
<li>significant economic risk</li>
</ol>






</p>

<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">3. Business Components </h2>
<p></p>
<?php
Table::create(array(
    "dataStore"=>$this->dataStore("business_components"),
    "class"=>array(
        "table"=>"table table-hover"
    )
));
?>
<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">4.  Projects </h2>
<?php
Table::create(array(
    "dataStore"=>$this->dataStore("project"),
    "class"=>array(
        "table"=>"table table-hover"
    )
));
?>
<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">5.  Activities </h2>

<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">6.  Cost Centers </h2>
<?php
Table::create(array(
    "dataStore"=>$this->dataStore("cost_center"),
    "class"=>array(
        "table"=>"table table-hover"
    )
));
?>
<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">7.  Sampling Methodology
<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">8.  Employees </h2>
    <?php
    Table::create(array(
        "dataStore"=>$this->dataStore("employees"),
        "class"=>array(
            "table"=>"table table-hover"
        )
    ));
    ?>
<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">9.  Contractors </h2>
<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">10. Screen shots </h2>
    <?php
    Table::create(array(
        "dataStore"=>$this->dataStore("images2"),
        "class"=>array(
            "table"=>"table table-hover"
        )
    ));
    ?>
    <body>
    <img src="<?php echo base_url();  ?>assets/uploads/8d7a9-Screenshot-from-2019-05-04-09-58-25.png" />
    <img src="<?php echo base_url();  ?>assets/uploads/files/step-functions-dg.jpg" />
    </body>

<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">11. Pdfs </h2>
<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">12. Powerpoints </h2>
<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">13. Spreadsheets </h2>
<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">14. Word Documents </h2>
<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">15. SME Cost Center Interviews </h2>
<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">16. SME Activity Estimates </h2>
<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">17. Employee Interview Transcripts </h2>
<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">18. Surveys
    <?php


    $category_amount = array(
        array("category"=>"Books","sale"=>32000,"cost"=>20000,"profit"=>12000),
        array("category"=>"Accessories","sale"=>43000,"cost"=>36000,"profit"=>7000),
        array("category"=>"Phones","sale"=>54000,"cost"=>39000,"profit"=>15000),
        array("category"=>"Movies","sale"=>23000,"cost"=>18000,"profit"=>5000),
        array("category"=>"Others","sale"=>12000,"cost"=>6000,"profit"=>6000),
    );

    $category_sale_month = array(
        array("category"=>"Books","January"=>32000,"February"=>20000,"March"=>12000),
        array("category"=>"Accessories","January"=>43000,"February"=>36000,"March"=>7000),
        array("category"=>"Phones","January"=>54000,"February"=>39000,"March"=>15000),
        array("category"=>"Others","January"=>12000,"February"=>6000,"March"=>6000),
    );
    ?>
    <div class="report-content">
        <div class="text-center">
            <h1>ColumnChart</h1>
            <p class="lead">
                This example shows how to use D3's ColumnChart
            </p>
        </div>
        <div style="margin-bottom:50px;">
            <div class="text-center"><strong>Basic chart</strong></div>
            <?php
            ColumnChart::create(array(
                "dataSource"=>$category_amount,
                "columns"=>array(
                    "category",
                    "sale"=>array("label"=>"Sale","type"=>"number","prefix"=>"$"),
                    "cost"=>array("label"=>"Cost","type"=>"number","prefix"=>"$"),
                    "profit"=>array("label"=>"Profit","type"=>"number","prefix"=>"$"),
                )
            ));
            ?>
        </div>
        <div style="margin-bottom:50px;">
            <div class="text-center"><strong>Stacked chart</strong></div>
            <?php
            ColumnChart::create(array(
                "dataSource"=>$category_sale_month,
                "columns"=>array(
                    "category",
                    "January"=>array("label"=>"January","type"=>"number","prefix"=>"$"),
                    "February"=>array("label"=>"February","type"=>"number","prefix"=>"$"),
                    "March"=>array("label"=>"March","type"=>"number","prefix"=>"$"),
                ),
                "stacked"=>true
            ));
            ?>
        </div>

        <div style="margin-bottom:50px;">
            <div class="text-center"><strong>Dual Axis - Sale and Sale Number</strong></div>
            <?php
            ColumnChart::create(array(
                "dataSource"=>array(
                    array("category"=>"Books","sale"=>32000,"number"=>1700,),
                    array("category"=>"Accessories","sale"=>43000,"number"=>2100),
                    array("category"=>"Phones","sale"=>54000,"number"=>3200),
                    array("category"=>"Movies","sale"=>23000,"number"=>9000),
                    array("category"=>"Others","sale"=>12000,"number"=>5300),
                ),
                "columns"=>array(
                    "category",
                    "sale"=>array(
                        "label"=>"Sale",
                        "type"=>"number",
                        "prefix"=>"$"
                    ),
                    "number"=>array(
                        "label"=>"Cost",
                        "type"=>"number",
                        "prefix"=>"$",
                        "axis"=>"y2"
                    ),
                ),
                "yAxis"=>array(
                    "prefix"=>"$"
                ),
                "dualAxis"=>true
            ));
            ?>
        </div>



    </div>
<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">20. Organizational Chart
    <?php
    \koolreport\widgets\google\OrgChart::create([
        "dataSource"=>[
            ["Name"=>"Mike","Manager"=>"","Tooltip"=>"President"],
            ["Name"=>"Jim","Manager"=>"Mike","Tooltip"=>"VP"],
            ["Name"=>"Alice","Manager"=>"Mike","Tooltip"=>""],
            ["Name"=>"Bob","Manager"=>"Jim","Tooltip"=>""],
            ["Name"=>"Carol","Manager"=>"Bob","Tooltip"=>""],
        ],
    ]);
    ?>
<p></p>
<h2 style="font-weight: bold;color:black;font-size:40px">21. Title Risk Analysis

</h2>

    <!-- <h3>List all offices</h3> -->

</body>
<p></p>
</html>

KoolReport commented on Aug 27, 2020

May I know what is not working?

Richb201 commented on Aug 27, 2020

The table is not appearing. Other ones, such as "business_components" seems to work. Just not the "Three Way Risk Analysis" datastore. I know the query works since I use it in another page to export to XLS.

David Winterburn commented on Aug 27, 2020

Please paste your query into your database interface:

SELECT e.employee, e.employee_title, e.w2_wages, t.risk, 
             CASE 
                 WHEN risk='low' THEN  e.w2_wages
                 WHEN risk='medium' THEN  e.w2_wages*.5
                 ELSE 0
              END as qualified
        FROM employees as e, titles as t
        WHERE e.email='$email' AND e.campaign='$campaign' 
        AND t.email='$email' AND t.campaign='$campaign'
        AND e.employee_title=t.title
        ORDER BY e.employee 

Replace $campaign with your current $_SESSION['campaign'] value (could echo it to get its value). Let us know the result. Thanks!

Richb201 commented on Aug 27, 2020

I did that and I get the same thing. I am pretty decent with a debugger. Here is an error that is occuring when trying to render the report. See the $exception variable.

Richb201 commented on Aug 27, 2020

It appears that class "koolreport\widgets\koolphp\Table" is not being found.



 public function loadClass($class)
    {
        if ($file = $this->findFile($class)) {
            includeFile($file);

            return true;
        }
    }

This piece of code is returning false. What does that imply? Is the setup incorrect? I am running in Docker. I tried locating it on the host but it can't be found. If it is not on the host then it is not in the container either.

richb201@richb201-XPS-13-9370:/opt/docker-substantiator2$ locate koolreport\widgets\koolphp\Table
richb201@richb201-XPS-13-9370:/opt/docker-substantiator2$ 

Please tell me how to fix this.

Richb201 commented on Aug 27, 2020

The problem seems to be base don where in the report the table occurs. I moved the same code down tow around the business_component sections, and it seems to work fine. Ever see this before?

Richb201 commented on Aug 27, 2020

SOLVED! The problem was the style tag. once removed it worked fine. Now, I need to total the qualified column and display the result on the bottom on a line called total.

Do I need to do this in the sql statement or in the koolreport array? I will also need to COUNT certain columns. I don't want to show these in the table that you see. Instead I want to show them in perhaps a D3 graphic. Do I do these counts in the sql in setup, or must I have a separate datastore with specifically for D3?

David Winterburn commented on Aug 31, 2020

Hi Richard,

It's great you found a solution for the not found class error. As for your total footer, please check the Table widget's aggregated footer property:

https://www.koolreport.com/docs/koolphp/table/#table-settings-aggregated-footer

Let us know if it meets your demand. Thanks!

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
None yet

None