Solved Crosstab Query - Columns are not Updating/Refreshing in the Reports (1 Viewer)

mrk777

Member
Local time
Tomorrow, 02:10
Joined
Sep 1, 2020
Messages
60
Hi Team, I have created a CrossTab query and SELECT query on the same data where I have Type of Certifications in Rows and Years in Columns as shown below:



Certification20182019202020212022
Certificate Type 7
1​
Certificate Type 4
1​
1​
1​
Certificate Type2
1​
2​
7​
3​
1​
Certificate Type 3
2​
1​

And I did this, in order to auto-refresh/re-query the data in reports. But when the new Year gets added to the Data, the query is updating, but the Report is not updating. Would you please help me. Thank you in advance.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 13:40
Joined
Oct 29, 2018
Messages
21,358
The query gets rebuilt each time you run the crosstab query. Unfortunately, the report gets bound to a specific column in your query at design time. It can only refresh the data - not new columns. If your query drops a year from the columns, you will get an error or blank in your report.

There are ways to create dynamic reports based on crosstab queries, but it takes some amount of code to do it.
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 04:40
Joined
May 7, 2009
Messages
19,169
Year gets added to the Data
you need Dynamic Report.
You limit the Number of Years to Display to a Constant of 5?
and only show the Latest 5 Years?
 

mrk777

Member
Local time
Tomorrow, 02:10
Joined
Sep 1, 2020
Messages
60
you need Dynamic Report.
You limit the Number of Years to Display to a Constant of 5?
and only show the Latest 5 Years?
How to do that? Any suggestions/help please :)
 

mrk777

Member
Local time
Tomorrow, 02:10
Joined
Sep 1, 2020
Messages
60
you need Dynamic Report.
You limit the Number of Years to Display to a Constant of 5?
and only show the Latest 5 Years?
If I would like to show the latest 5 Years dynamically... is there any way that I can achieve this?
 

Users who are viewing this thread

Top Bottom