Okay, all I want to do is have a report that behaves like an SQL group by statement.
data like this:
first last state sales commission
joe fox tx 10 4
joe fox tx 20 5
sue ton tn 15 7
sue ton tn 25 9
I want to see select first, last, state, sum(sales), avg(commission) group by first,last,state:
first last state sum(sales) avg(commission)
joe fox tx 30 4.5
sue ton tn 40 8
that's it! can't figure out how to do it. If I group by more than one column, then add different types of summary, it blows up into a different line for each group and type of summary.
Quote from: wcaples on 29 May 2019 10:34:03 PM
Okay, all I want to do is have a report that behaves like an SQL group by statement.
data like this:
first last state sales commission
joe fox tx 10 4
joe fox tx 20 5
sue ton tn 15 7
sue ton tn 25 9
I want to see select first, last, state, sum(sales), avg(commission) group by first,last,state:
first last state sum(sales) avg(commission)
joe fox tx 30 4.5
sue ton tn 40 8
that's it! can't figure out how to do it. If I group by more than one column, then add different types of summary, it blows up into a different line for each group and type of summary.
Hi,
You shouldn't need to do any manual grouping in your report to get this result. Reports will automatically group and summarize - or at least they should. All you'd need to do would be to ensure the aggregation for Sales is set to Total and for Commission is set to Average.
If your report isn't behaving like this, there must be something odd going on. Can you describe how you authored the report?
MF.