KoolReport's Forum

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

Horizontal vs vertical display in Excel #2028

Open Richb201 opened this topic on on Apr 13 - 25 comments

Richb201 commented on Apr 13

I am creating an excel like this:

I'd like it to appear like this

wages (qualified wages) $xxxxx supplies (used in research) $xxxx Computer Rental $xxxx

     subtotal                      $xxxxx

three previous years $xxxxx 3 YEARS times 23% $xxxxxx

How do i accomplish goting fro horizontal to vertical?

Richb201 commented on Apr 14

Here is how i want it to appear. How to do this? I have all the amounts (xxx's) in php variables.

Richb201 commented on Apr 14

This IS a premium support request.

Sebastian Morales commented on Apr 15

Pls try to use the Tranpose or Transpose2 process to transpose your data from vertical to horizontal or vice versa. Then use excel template to format your exported data. Rgds,

Richb201 commented on Apr 15

Thx. Can you point me to a page where Transpose and Transpose2 are documented?

Richb201 commented on Apr 16

I can't find the Transpose process on your website? Where is a description?

Richb201 commented on Apr 23

It has been 8 days since I asked you where is the documentation for Transpose and Transpose2. Can I assume that there is none???

Sebastian Morales commented on Apr 23

Here're documentation and example for the Transpose process:

https://www.koolreport.com/docs/processes/transpose/

https://www.koolreport.com/examples/reports/processes/transpose/

To see Transpose2's effect just replace the process name from Transpose to Transpose2. Check the transposed results by printing the result datastore in your report view.

Richb201 commented on Apr 23

in https://www.koolreport.com/examples/reports/processes/transpose/ I see that I need to include the following at the top of my report.

"use \koolreport\processes\Transpose;" I don't have that directory. I do have: use \koolreport\core\src\processes\Transpose; But that is not working. I am getting this error: A PHP Error was encountered Severity: Error

Message: Trait 'koolreport\core\src\processes\Transpose' not found

Filename: assets/MyExport6765.php

Line Number: 15

Backtrace:

Richb201 commented on Apr 23

While I have your attention :), is there some function that will allow me to append a record onto a datastore? In my .view I am doing a bunch of php calculations. I then need to append this data to my table so that I can export it to Excel. Seems like a long way just to create an excel file?

I don't seem to have access to $this -> db in my view to be able to do this: $this->db->insert('form6765_temp', $data);

Richb201 commented on Apr 24

I have all the variables needed in php. The ultimate question is "can I just create a datastore directly without using a mysql table"? Here is what I have in settings() I have

            "data"=>array(
                "class"=>'\koolreport\datasources\ArrayDataSource',
                "data"=>'$data',
                "dataFormat"=>"table",
            )

and in my code I build the array:

            $data = array(
                'userid'             => $email,
                'campaign'           => $campaign,
                'taxyear'             => $taxyear,
                'wages'               => $wage_qres,
                'supplies'            => $supplies,
                'computer_rental'     => $rental,
                'contract_research'     =>$contract_dollars,
                'total_QREs'            =>$total_QREs,
                'minus1year'            =>$minus1year,
                'minus2year'            =>$minus2year,
                'minus3year'            =>$minus3year,
                'line30'                =>$line30,
                'line31'                =>$line31,
                'line32'                =>$line32,
                'total credit'          =>$line34

            );

and then finally I try to create the datastore


        $this->src('data')
            ->pipe(new Transpose())
            ->pipe($this->dataStore('6765form'));

these two are blank

    protected function defaultParamValues()
    {

    }

    protected function bindParamsToInputs()
    {
    }

But this fails with an error: Datasource not found 'data' . I will ultimately export this to an Excel file.

Sebastian Morales commented on Apr 26
  1. The process name is koolreport\processes\Transpose, not' koolreport\core\src\processes\Transpose, because the source directory of the Core framework is koolreport\src.

  2. I think this line in your datasource setting has a problem:

    "data"=>'$data',

Should not it be;

    "data"=>$data, //no string quote around $data array

Let us know if you have any issue. Tks,

Richb201 commented on Apr 26

Hi. When I use $data instead of '$data' I get this error: A PHP Error was encountered Severity: Notice

Message: Undefined variable: data

Filename: assets/MyExport6765.php

Line Number: 38

Here is settings:

   public function settings()
    {

        $CI = & get_instance();
        $CI->load->model('MyModel')->library('session');

        return array(
            "dataSources"=>array(
                "substantiator" => array(
                    "connectionString" => "mysql:host=database-2.cwymdn16cxes.us-east-1.rds.amazonaws.com;dbname=substantiator",
                    "username" => $_SESSION['username'],
                    "password" => $_SESSION['password'],
                    "charset" => "utf8"
                ),
            ),
            "data"=>array(
                "class"=>'\koolreport\datasources\ArrayDataSource',
                "data"=>$data,
                "dataFormat"=>"table",
            )
        );
    }

I am trying to load up my dataStore, data, from some php variables.

Sebastian Morales commented on Apr 26

There're two problems with your report's setting:

  1. "data" as a datasource name should be inside "dataSources"

  2. Pls build $data array before using it in your "data" datasource.

Richb201 commented on Apr 26

Ok, 1. I can change the name. 2. Where should I build it? This report is being called from another report if a user clicks the link. Is there any type of "before load" functionality? Right now that link is in the view file of the first report. I don't know the tax year in advance. The 2nd report runs on whatever tax year that they chose in the first report.

Let's say I build it in the first reports view file. Then (I am assuming) it would not be available in the 2nd report. Unless I stick it in a session variable. In that way the 2nd report will be able to access it. Is this what you recommend?

Richb201 commented on Apr 26

I have modified the setting()

public function settings()
{

    $CI = & get_instance();
    $CI->load->model('MyModel')->library('session');

    return array(
        "dataSources"=>array(
            "substantiator" => array(
                "connectionString" => "mysql:host=database-2.cwymdn16cxes.us-east-1.rds.amazonaws.com;dbname=substantiator",
                "username" => $_SESSION['username'],
                "password" => $_SESSION['password'],
                "charset" => "utf8"
            ),
        ),
        "dataS"=>array(
            "class"=>'\koolreport\datasources\ArrayDataSource',
            "data"=>$this->session->get_userdata('DATA'),
            "dataFormat"=>"table",
        )
    );
}

I have moved the $data array into a session variable called DATA. I am still getting this error: Severity: Notice Message: Undefined index: taxyear Filename: core/DataStore.php Line Number: 270

How can I dump the dataStore DataS, so I can see what is happening to taxyear? There are two dataStores being created in this case a) substantiator b) DataS. Am I doing that correctly?

Sebastian Morales commented on Apr 27

It should be: `

    return array(
        "dataSources" => array(
            "substantiator" => array(
                "connectionString" => "mysql:host=database-2.cwymdn16cxes.us-east-1.rds.amazonaws.com;dbname=substantiator",
                "username" => $_SESSION['username'],
                "password" => $_SESSION['password'],
                "charset" => "utf8"
            ),
            "dataS" => array(
                "class" => '\koolreport\datasources\ArrayDataSource',
                "data" => $this->session->get_userdata('DATA'),
                "dataFormat" => "table",
            )
        ),
    );
As for parameter taxYear you could pass it in the url (http://...?taxYear=2021) and catch it in the linked report using $_GET["taxYear"]. You could dump a datastore data by print_r($this->dataStore("myDS")->data()) in a report's view.
Richb201 commented on Apr 28

what should be '????

Sebastian Morales commented on Apr 29

Your settings' return

Richb201 commented on Apr 29

I don't understand what you mean.

Richb201 commented on Apr 29

I am having so much trouble with this.

$survey_total=$this->dataStore('srat')->filter("taxyear","=",$_SESSION['last_TY'])->sum('qualified_dollars');

Is there anything wrong with this line? This filter appears to be causing an error.

 PHP Error was encountered
Severity: Notice

Message: Undefined index: taxyear

Filename: core/DataStore.php

Line Number: 270

Backtrace:

File: /app/vendor/koolreport/core/src/core/DataStore.php
Line: 270
Function: _error_handler

File: /app/assets/MyReport.php
Line: 324
Function: filter

File: /app/vendor/koolreport/core/src/KoolReport.php
Line: 100
Function: setup

File: /app/application/controllers/Configure.php
Line: 2337
Function: __construct

File: /app/index.php
Line: 325
Function: require_once
Richb201 commented on Apr 29

I modified the above statement to be:

$survey_total=$this->dataStore('srat')->pipe(new Filter('taxyear',"=",$_SESSION['last_TY']))->pipe(new Group("sum"=>"qualified_dollars"));

I am trying to only look at the current taxyear, and to sum the qualified_dollars column and put the total in $survey_total

I am getting an error: Type: ParseError

Message: syntax error, unexpected 'last_TY' (T_STRING), expecting ',' or ')'

Sebastian Morales commented on May 4

First of all, there can be no pipe or process for a Datastore object. Second, this command of yours is correct grammatically:

    $survey_total=$this->dataStore('srat')->filter("taxyear","=",$_SESSION['last_TY'])->sum('qualified_dollars');

But its error "Undefined index: taxyear" indicates that your datastore has no column name = "taxyear". Remember that PHP's array index is case sensitive so "taxyear" != "Taxyear". Use the following command to see a datastore's all columns:

    print_r($this->dataStore('srat')->meta()["columns"]);
Richb201 commented on May 4

OK. In MyReport.php I am building srat. See below:

       $sql="
        SELECT taxyear, employee_email, employee_title, cost_center_name, consultant, w2_wages, qualified_dollars
        FROM survey_results_activities_temp
        ";
        $this->src('substantiator')
            ->query($sql)
            ->pipe($this->dataStore("srat"))  ;

        print_r($this->dataStore('srat')->meta()["columns"]);
        

When the print_r runs, nothing is bring printed. I also attached the view of srat from the debugger.

KoolReport commented on May 4

Please use this code at the view you will see data.

print_r($this->dataStore('srat')->meta()["columns"]);

In the setup() function, you setup the pipeline of data but there is no data is piped yet so you will get empty result if you call above code.

Richb201 commented on May 4

What I actually need is to load some variables with sums of certain columns:

    $survey_total=$this->dataStore('srat')->pipe(filter('taxyear',"=",$_SESSION['last_TY']))->sum("qualified_dollars");
    $threeway_total=$this->dataStore("ThreeWayRiskAnalysis")->filter("taxyear","=",$_SESSION['last_TY'])->sum('QREs');
    $wage_qres=($survey_total+$threeway_total)/2;  //take avg of 3 way and interviews

I really don't want to modify the srat or ThreeWayRiskAnalysis since they get used in a different report.

How do I do this?

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
help needed

None