You've got the right idea. We usually call this a "cross tab" report. There are several different ways to get the data into the needed columns. The first step, though is to get the rows of data that you need where multiple rows of data are condensed into 1 or 2 rows of data per customer ID. This can be done by setting up a summary report with a subsummary part (when sorted by CustomerID and a second subsummary part when sorted by SaleType. This, combined with sorting by customerID then aby SaleType will produce your customer ID sub header and the one or two rows per customer with the first column of data showing the Sale Type..
The next part the process can be set up a number of different ways. The most common methods use either a series of (usually filtered) one row portals or a series of calculation fields using ExecuteSQL() to compute and display the needed subtotals by type and year. If using portals, summary fields from the portal's are placed inside the portals to display the needed sub totals. The relationships would be based on a self join that match records at least by customer ID, but which can also match by sales type and year. (or year can be specified in a portal filter so that you don't have to create a different table occurrence for each subsequent column of data.)
This can be set up so that you specify the year for either the first or last column of data in a global field so that you can adjust what data appears in these columns as time passes or just to look at past historical data.
Thanks for this - now I know what they're called(!)
I've managed to get the rows of data to appear on the summary report, per customer.
The trick now is to use portals to summarise the data. I guess I can self join to create further TO's year on year, and pull into a portal (in a column), the relevant data::Value field?
With a different portal filter filtering for a different year, you only need one table occurrence, but either approach can be set up to work.
What I've been able to do is set up calculation fields which create further one year only columns on the data table for values in a particular year. These split one column of annual data to several:
RowTotal2014 = If (Year=2014;Value)
Then I've done a sub summary report with a group based on the sales type with the sales description and summary values per year across the row:
sRowTotal2014 = (Summary of RowTotal2014)
Where I've needed percentages of the row total against an overriding total sales figure, I've wrapped the whole thing up in a GetSummary on the sales type break field:
RowPerc = GetSummary(sRowTotal2014;SaleType)/TotalSales (a figure derived elsewhere on the table)
Thanks for your help on this Phil
I would not have set it up this way. Each time you want to add a new year, you'll have to redefine calculation fields to include it in the report.