2. PHPRunner General questions
  3. [SOLVED]  Report Totals
This topic is locked
[SOLVED]

 Report Totals

 		6/6/2020 10:41:32 AM
PHPRunner General questions
R
 RBrogen author

Hi Everyone,
I have a report I'm building Fig 1 below and it works great except I want to add a Y AXIS column and total each row. I was able to get the data to display on the same report but I have to drop the option down and select Annual Total. Any guidance on where I can look to combine these two so I the end result is that an "ANNUAL TOTAL" column shows to the right of DEC column? Any assistance would be greatly appreciated.

FIG 1 MONTHLY SALES REPORT:


FIG 2: ANNUAL TOTALS
M
 		MikeT 6/6/2020

Not really elegant and tested, but something along this line could work:
Assumed a Sales Table with this structure:

PK | SaleDate | SaleAmount


SELECT YearNum AS 'Year',

Jan As 'January',

Feb As 'February',

Mar As 'March',

Apr As 'April',

May As 'May',

Jun As 'June',

Jul As 'July',

Aug As 'August',

Sep As 'September',

Octa As 'October',

Nov As 'November',

Deca As 'December',

Total

FROM (SELECT YEAR(SaleDate) AS YearNum,

SUM(CASE WHEN MONTH(SaleDate) = 1 THEN SaleAmount END) AS 'Jan',

SUM(CASE WHEN MONTH(SaleDate) = 2 THEN SaleAmount END) AS 'Feb',

SUM(CASE WHEN MONTH(SaleDate) = 3 THEN SaleAmount END) AS 'Mar',

SUM(CASE WHEN MONTH(SaleDate) = 4 THEN SaleAmount END) AS 'Apr',

SUM(CASE WHEN MONTH(SaleDate) = 5 THEN SaleAmount END) AS 'May',

SUM(CASE WHEN MONTH(SaleDate) = 6 THEN SaleAmount END) AS 'Jun',

SUM(CASE WHEN MONTH(SaleDate) = 7 THEN SaleAmount END) AS 'Jul',

SUM(CASE WHEN MONTH(SaleDate) = 8 THEN SaleAmount END) AS 'Aug',

SUM(CASE WHEN MONTH(SaleDate) = 9 THEN SaleAmount END) AS 'Sep',

SUM(CASE WHEN MONTH(SaleDate) = 10 THEN SaleAmount END) AS 'Octa',

SUM(CASE WHEN MONTH(SaleDate) = 11 THEN SaleAmount END) AS 'Nov',

SUM(CASE WHEN MONTH(SaleDate) = 12 THEN SaleAmount END) AS 'Deca',

Sum(SaleAmount) AS TOTAL

FROM sales

GROUP BY YearNum) AS BLAH
R
 		RBrogen author 6/6/2020



Not really elegant and tested, but something along this line could work:
Assumed a Sales Table with this structure:

PK | SaleDate | SaleAmount


SELECT YearNum AS 'Year',

Jan As 'January',

Feb As 'February',

Mar As 'March',

Apr As 'April',

May As 'May',

Jun As 'June',

Jul As 'July',

Aug As 'August',

Sep As 'September',

Octa As 'October',

Nov As 'November',

Deca As 'December',

Total

FROM (SELECT YEAR(SaleDate) AS YearNum,

SUM(CASE WHEN MONTH(SaleDate) = 1 THEN SaleAmount END) AS 'Jan',

SUM(CASE WHEN MONTH(SaleDate) = 2 THEN SaleAmount END) AS 'Feb',

SUM(CASE WHEN MONTH(SaleDate) = 3 THEN SaleAmount END) AS 'Mar',

SUM(CASE WHEN MONTH(SaleDate) = 4 THEN SaleAmount END) AS 'Apr',

SUM(CASE WHEN MONTH(SaleDate) = 5 THEN SaleAmount END) AS 'May',

SUM(CASE WHEN MONTH(SaleDate) = 6 THEN SaleAmount END) AS 'Jun',

SUM(CASE WHEN MONTH(SaleDate) = 7 THEN SaleAmount END) AS 'Jul',

SUM(CASE WHEN MONTH(SaleDate) = 8 THEN SaleAmount END) AS 'Aug',

SUM(CASE WHEN MONTH(SaleDate) = 9 THEN SaleAmount END) AS 'Sep',

SUM(CASE WHEN MONTH(SaleDate) = 10 THEN SaleAmount END) AS 'Octa',

SUM(CASE WHEN MONTH(SaleDate) = 11 THEN SaleAmount END) AS 'Nov',

SUM(CASE WHEN MONTH(SaleDate) = 12 THEN SaleAmount END) AS 'Deca',

Sum(SaleAmount) AS TOTAL

FROM sales

GROUP BY YearNum) AS BLAH



Thanks for taking the time to reply Michael. I think I see what you are aiming at here doing the calc in the sql query itself. I ran a quick test on that and was not able to get it to work ... I was hoping that since I already have the report displaying the data I want, just in 2 versions of the report that I would be able to modify the PHP or code just before display to show the totals. Thanks again for your input!
M
 		MikeT 6/6/2020

It should work with this table-def, only tested with an SQL-client not in phpr.

CREATE TABLE IF NOT EXISTS `sales` (

`PK` int(11) NOT NULL AUTO_INCREMENT,

`SaleDate` date DEFAULT NULL,

`SaleAmount` int(11) DEFAULT NULL,

PRIMARY KEY (`PK`)

) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8;


But of course you'd have to adapt to the structure of your db-tables, depending on how the sales are assembled for this (I guess with a join).

Anyway, good luck.
R
 		RBrogen author 6/7/2020

For anyone else trying to get this to work, below is the code for what I did and a screenshot of the result. There are probably more/better ways to make this work but this is how I finally got it to display the information the way I wanted it to.
I decided to trick the system and add a 13th month and by doing so allowed me to have the 13th month equal Annual Total, I then replaced the value for the AdjustedSales in the second select with the sum value of for the total for the year. Thank you to all who provided input.


Select ReportYear, ReportMonth, Sum(AdjustedSales) As AdjustedSales

From (

SELECT

YEAR(A.Order_Date) As ReportYear,

MONTH(A.Order_Date) As ReportMonth,

Sum(A.Order_TotalBaseSale - (A.Order_PercentDiscount A.Order_TotalBaseSale)/100) As AdjustedSales

FROM ORDERS A

WHERE (YEAR(Order_Date) > 2011) AND (Order_Status <> 'Cancelled')

Group By YEAR(A.Order_Date), MONTH(A.Order_Date)
UNION ALL
SELECT

YEAR(B.Order_Date) As ReportYear,

(13) As ReportMonth,

SUM(Order_TotalBaseSale - (B.Order_PercentDiscount B.Order_TotalBaseSale)/100) As AdjustedSales

FROM ORDERS B

WHERE (YEAR(Order_Date) > 2011) AND (Order_Status <> 'Cancelled')

Group by Year(B.Order_Date), MONTH(B.Order_Date)
)t

Group by ReportYear, ReportMonth

Order by ReportYear DESC, ReportMonth ASC