Using Cross Tab Query In Report (1 Viewer)

Oreynolds

Member
Local time
Today, 18:16
Joined
Apr 11, 2020
Messages
157
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:

Code:
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:

Code:
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

Well-known member
Local time
Today, 13:16
Joined
Apr 9, 2015
Messages
4,337
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

Well-known member
Local time
Today, 19:16
Joined
Feb 7, 2020
Messages
1,991
Sample
 

Attachments

  • CrossTabReport2k.zip
    64.6 KB · Views: 85

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:16
Joined
Feb 19, 2002
Messages
43,512
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.

 

Users who are viewing this thread

Top Bottom