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

 

[Pat Hartman]

I have a slightly different example. It uses a temp table that is used to convert the "labels" of the crosstab to a number. Access reports are limited in width to 22" and so they are limited in the number of columns that can be displayed. So, this technique is limited by that constraint. If you need to support more columns than what can be comfortably displayed in 22", then you need to export to Excel and create the report there.

The examples to look at are the second and third menu items. The columns in the example are types of expenses and for convenience, the report is limited to 10 columns wide and so only the amount of data that will fit is selected. If you are willing to go to a second page across, you can get 10 columns on each page or 10 + 12 if you don't repeat the property name on the second page.

My common use for the technique is to show some number of rolling years or months rather than something like what the example shows which can't be logically controlled.

Bound Denormalized Form

I've left the old versions here but ALWAYS go to the last post to get the current version. This database contains two examples (using the same technique) of how to take data that is properly normalized and display it so that it looks like a spreadsheet (rows displayed as columns) but is...

www.access-programmers.co.uk