2. PHPRunner General questions
  3. Reports – calculated fields.
This topic is locked

Reports – calculated fields.

 		7/17/2008 2:36:58 PM
PHPRunner General questions
J
 jskewes author

I have a Sales Log application.

Two tables;

Log - containing 'year' 'make' 'model' 'customer' 'sales rep' ect.

Staff - containing 'sales rep' 'new car goals' 'used car goals'
I have reports built for New Car Sales and for Used Car Sales, groups by Sales Rep and summed on New/Used.
So a report would show; George 6 new, 6 used, 12 total.
What I need to do is have a calculated field that shows 'percentage of goal'.

So if George's goal is 10 new his 'percentage of goal' would be %60.
Can anyone tell me how I can go about this?
Thanks,

/john
J
 		Jane 7/18/2008

Please post SQL query of your report here.
J
 		jskewes author 7/18/2008

Please post SQL query of your report here.

Hi Jane,

Here is the query - I also posted the project in a demo acct. Thank you.

http://demo.asprunner.net/jskewes_lyonwaug...port_report.php
SELECT

`Index`,

SalesRep,

`New-Used`,

StockNum,

`Year`,

Make,

Model,

TradeIn,

TradeYr,

ACV,

`FE-Gross`,

`BE-Gross`,

DelvDate,

RDR,

SoldDate,

`Comment`,

Delivered,

Customer

FROM `_log`
J
 		Jane 7/21/2008

Here are some tips:

  1. create two views in the database: newcars and usedcars.

    Here is a sample:
    create view `newcars` as select SalesRep, count(`Index`) as CountCars from _log where `New-Used`='NEW' group by SalesRep



2. and then create custom view in the PHPRunner and use this query on the Edit SQL query tab:

SELECT `newcars`.SalesRep, concat( `newcars`.`CountCars` , ' NEW, ', `usedcars`.`CountCars` , ' USED' )

FROM newcars

INNER JOIN usedcars ON ( `newcars`.SalesRep = `usedcars`.SalesRep )
J
 		jskewes author 7/21/2008

Hi Jane,

Thank you very much - that works. I should be able to work out the rest.

Last question. I am trying to get the resulting report to show all records and do not seem to be able to do so.

The groups per page: defaults to - 5.
Again, thank you.

/john
J
 		Jane 7/22/2008

Hi,
open generated ..._report.php file, find following code:

if(!$_SESSION[$strTableName."_pagesize"])

$_SESSION[$strTableName."_pagesize"]=5;



and replace it with this one:

if(!$_SESSION[$strTableName."_pagesize"])

$_SESSION[$strTableName."_pagesize"]=-1;
J
 		jskewes author 7/22/2008

Perfect. Thank you.

/john

Hi,

open generated ..._report.php file, find following code:
and replace it with this one: