kirchaj
Posts: 215
|
| Posted: 08/12/2016, 8:11 AM |
|
ok, I am stumped on this one. I have a system where order items are stored in one table and payments are stored in another table. I have created an invoice that displays both of these in separate report grids. I have tried combining them into one report but because of the looping there are duplicate entries.
So, is there a way to create one report that computes each of the table entries separately and then can provide a grand total (hopefully zero)?
Or is there a way to get values from both report tables? So I can give the grand total?
|
 |
 |
saseow
Posts: 744
|
| Posted: 08/12/2016, 8:30 AM |
|
Perhaps creating a VIEW of both your current reports in the database and using that as your data source.
|
|
|
|
kirchaj
Posts: 215
|
| Posted: 08/12/2016, 11:02 AM |
|
that's a good idea but I am not sure how to handle the grouping. I may have 10 items they purchased and only 1 payment. Every way I have tried the payment info keeps showing up with every item. Thus adding the payment multiple times.
|
|
|
|
solesz
Posts: 137
|
| Posted: 08/15/2016, 2:49 AM |
|
I do not clearly understand the problem, but LEFT JOIN, or SELECT DISTINCT or GROUP BY in the datasource definition (mysql) solves all unwanted repetition on database side.
I would avoid using ccs report's form aggregations because in case a few hundreds or thousands record it puts extra load onto the application server.
So keep the aggregation on database.
|
|
|
|
kirchaj
Posts: 215
|
| Posted: 08/15/2016, 11:29 AM |
|
I was able to use the mysql sum(variable) command to get the sub-totals I needed and then could compute from there. thanks so much for your help. You got me on the right track.
Tony
|
|
|
|
solesz
Posts: 137
|
| Posted: 08/16/2016, 10:21 AM |
|
Hi, just a bit of additional info, maybe it is obvious, but can be a small learning:
Groping records involves functions on countable type fields, like sum() - used in your solution - avg() and so on.
This what is called aggregation.
Ccs "report" can also do it for you (grouping and aggregation). The pro of this solution is the interchangeability of programming language under the application, the con is the speed in case of bigger resultset after selection.
|
|
|
|
