2. PHPRunner General questions
  3. how to create chart or report of products sold?
This topic is locked

how to create chart or report of products sold?

 		2/5/2017 1:46:00 PM
PHPRunner General questions
W
 wfcentral author

I have a database for a client to track orders they do manually
Let's just call the products A through Z for ease of use.
So, tables...
tbl_orders

tbl_products_to_order

tbl_products
These are all linked together in phprunner and I'm getting a report "kinda works" - it is grouped by product and totals each section.
I can also search by date range on the orders.
So, for last month I can see that
Product A - had 3 orders and the total number of A sold is 8

Product B - had 10 orders and the total number of B sold is 15

and so on...
What the client would like now is for this report to sort based on the total sold... so, in the example above B should come before A.
Also, it would be nice if I could display this in a bar graph like this... (ascii graphics posted here just to be quick - it would of course be a color bar graph)
B - ###############

A - ########

D - ######

E - ###

F - #

J -

M -
HJB 2/5/2017

https://xlinesoft.com/phprunner/docs/creating_web_chart.htm
... view screenshot #3 in regard to SORT (here descending from highest turn-over to lowest one) issue ...
W
 		wfcentral author 2/5/2017



https://xlinesoft.com/phprunner/docs/creating_web_chart.htm
... view screenshot #3 in regard to SORT (here descending from highest turn-over to lowest one) issue ...


thanks - I will look over that and try it - but after a quick look I'm not sure it addresses the problem I'm having...

  1. the sample you show is counting number of employees in a city (where each record is going to be counted as 1) in my database it is a join of three tables so that there may be an order with item A sold 5 times and another order where it sold 2 times... this should give a total of 7 not a count of "2 records"
  2. the other issue is that I will at the end need to be able to change the search to specify a date range... so, how many of each item was sold last month, last quarter or May through June...
    When the items are grouped and summed up then each resulting line in the results just gets "one date" which is probably the last record it read. As such, I cannot search this result and accurately see how many products were sold last month.
W
 		wfcentral author 2/5/2017



thanks - I will look over that and try it - but after a quick look I'm not sure it addresses the problem I'm having...

  1. the sample you show is counting number of employees in a city (where each record is going to be counted as 1) in my database it is a join of three tables so that there may be an order with item A sold 5 times and another order where it sold 2 times... this should give a total of 7 not a count of "2 records"
  2. the other issue is that I will at the end need to be able to change the search to specify a date range... so, how many of each item was sold last month, last quarter or May through June...
    When the items are grouped and summed up then each resulting line in the results just gets "one date" which is probably the last record it read. As such, I cannot search this result and accurately see how many products were sold last month.


okay - I think this might work if I use the HAVING to sort out the date items...
HJB 2/6/2017

https://xlinesoft.com/phprunner/docs/create_report_and_set_parameters.htm

... for inspiration purposes only, a "Year of Make" car sales table.

You may also want to try "Cross-Tab Report" which seems tobe the right thing here.