Using Cross Tab Query In Report

[Oreynolds]

Hi,

I have built a dynamic cross tab query to return the last 3 years of invoicing totals per customer which works fine. I intended to then use this for a report but didn't realise its not as simple as this.

My dynamic crosstab query is as follows:

PARAMETERS [Forms]![YearOnYearReportFilters]![Combo1] Short, [Forms]![YearOnYearReportFilters]![txtType1] Short, [Forms]![YearOnYearReportFilters]![txtType2] Short, [Forms]![YearOnYearReportFilters]![txtType3] Short;
TRANSFORM CCur(Nz(Sum(CCur(Nz([InvoiceAmount],0))),0)) AS [Value]
SELECT qryInvoicesYearOnYearFiltered.CustomerName
FROM qryInvoicesYearOnYearFiltered
GROUP BY qryInvoicesYearOnYearFiltered.CustomerName
PIVOT IIf([InvoiceDate] Between FYStartMinusTwo() And DateAdd("yyyy",-2,Date()),Year(FYEndMinusTwo()),IIf([InvoiceDate] Between PrevFYStart() And DateAdd("yyyy",-1,Date()),Year(PrevFYEnd()),IIf([InvoiceDate] Between FYStart() And Date(),Year(FYEnd()),9999)));

I understand to use in a report you have to fix the column headings using the PIVOT IN clause. I have tried this using the following line: in place of the above PIVOT clause:

PIVOT Format([InvoiceDate],"yyyy") IN ("2021","2022","2023");

This works fine but obvioulsy since I am fixing the headings to specific years then the report is no longer dynamic. Can anyone offer any advice as to how to make the PIVOT IN clause dynamic?

Thanks

 

[Ranman256]

Pivot is very hard to do in report since it keep changing.

Tho you CAN make a report table that has all columns,
then make an append query to add records to the report table, (fills the existing columns)
then report on the report table, since all columns are there.

or just always show pivot results in a datasheet. (easiest)

 

[ebs17]

Sample