2. PHPRunner General questions
  3. Creating a BIlling Reconcilliation Report
This topic is locked

Creating a BIlling Reconcilliation Report

 		10/22/2009 2:11:58 PM
PHPRunner General questions
M
 masq author

I currently have a table that looks like this:

SELECT

spend.clientname,

spend.weekending,

spend.spend,

spend.reporttype

FROM spend

INNER JOIN `_vendor` ON spend.vendorname = `_vendor`.vendorname

INNER JOIN reportsourcetypes ON spend.reporttype = reportsourcetypes.reporttype

INNER JOIN mediatypes ON spend.mediatype = mediatypes.mediatype

INNER JOIN jobs ON spend.jobnumber = jobs.jobnumber AND spend.clientname = jobs.clientname


In the 'reporttype' field, I have variables such as "billed" and "received".
What I'm trying to create is a report that shows something like this:


The balance column would be a calculated function of 'Received' - 'Billed'.
How do I achieve this?
J
 		Jane 10/26/2009

Hi,
there are two ways:

  1. you can calculate this value on the fly editing SQL query on the Edit SQL query tab.

    Here is a sample:

select client,

Billed,

received,

Billed-received as Balance

...

from TableName


2. calculate and save value on the add/edit pages in the Before record added/Before record updatded events on the Events tab.

Here is a sample:

$values["Balance"] = $values["Billed"]-$values["Received"];