Dynamic Column Headings from CrossTab query 1

[1DMF]

Hello,

How do you generate a dynamic report using a crosstab query where the columns change dependent of date ranges used to create query. ?

DUANE -> I found this post in another forum while hunting Google, but the link doesn't work, do you have another link please?

There is a much more flexible and efficient method of dynamic crosstab
reports at

http://www.invisibleinc.com/divFiles.cfm?divDivID=4.

Check out
the Crosstab.mdb. This solution uses much less code is runs much faster.

--
Duane Hookom
MS Access MVP

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[dhookom]

If you want date intervals for crosstab headings in a report, just check the FAQs of this forum.

Otherwise my signature leads to some of my samples and others can be found at Roger Carlson's Site.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

for the month one I decided to force 12 months so then the report could be bound to the columns.

However, I now have a similar report but the columns are people's names and will be dynamic

Thanks for the links.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[dhookom]

Did you check out this demo?

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Thanks, bit confused by it though.

It seems to be hard coded to a max of 6 columns, I need something which is completely dynamic and will change over time.

I'll dig further into the code tomorrow to see if this can be negated or allowed to be excluded from the selection criteria.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[dhookom]

Your report page will only fit a specific number of columns unless you have a method for stretching your paper ;-)

Design your report for the max number of columns that will fit. If you design your report for 6 but need 10, the last 4 will render under the first 6. This is kinda like column wrapping.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Duane, I can't thank you enough for your help and inspiration, I have a fully dynamic report working like a charm!

OK, yes it's limited to 8 columns and a Total column, I couldn't get my head round your code, or understand the embeded form withing the report.

You're a clever guy and I will always be playing catch up!

But it gave me enough to understand the problem and a potential solution, so I added an extra column to the report table called 'Alias' and an 'Alias' table with the column letters required. Then after the report records have been generated, but before the CrossTab Query is run I execute the following code...

Public Sub setAlias(sColName As String, sTabName As String)

    Dim rs As Recordset
    Dim iNum As Integer
    
    Set rs = CurrentDb.OpenRecordset("SELECT DISTINCT [" & sColName & "] FROM [" & sTabName & "] ORDER BY [" & sColName & "]")
    
    rs.MoveLast
    If rs.RecordCount > 8 Then
        MsgBox "Max 8 unique columns allowed, seek support"
        Exit Sub
    End If
        
    rs.MoveFirst
    
    iNum = 65
    
    Do While Not rs.EOF
        CurrentDb.Execute ("UPDATE [" & sTabName & "] SET [Alias] = '" & Chr(iNum) & "' WHERE [" & sColName & "] = '" & rs.Fields(sColName) & "'")
        rs.MoveNext
        iNum = iNum + 1
    Loop
    
    Set rs = Nothing
        
End Sub

I then use the following CTQ to generate the report source

TRANSFORM Format(Sum([Cnt]),"Fixed") AS Expr1
SELECT rptOfficers.Ord, rptOfficers.Cat, Sum(rptOfficers.Cnt) AS Total
FROM rptOfficers RIGHT JOIN rptAlias ON rptOfficers.Alias = rptAlias.Alias
GROUP BY rptOfficers.Ord, rptOfficers.Cat
ORDER BY rptOfficers.Ord
PIVOT rptAlias.Alias;

This give the A-H column aliases, then in the report I place the A-H controls.

For the column headings I place 8 unbound controls and use the following source....

=DLookUp("Officer","rptOfficers","Alias = 'A'")

etc...

OK I have one problem, for each column alias that has no data, I get blank rows with just the alias column letter.

How do I supress these in the report without it complaining the control source doesn't exist?

Currently it's only one, and so I just get one row at the top blank, it simply looks like the extra spacing was meant to be there, but I know if there was only 3 'Officers' for example it might look a bit odd.

The report fits easily on one sheet of A4 landscape, so I guess the space isn't a massive problem, but would be nice to tidy it up if possible.

Regards,
1DMF

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[dhookom]

I wouldn't mess with the solution I provided. Did you look at the table ztblExplanation that contains some description of the solution?

My column headings is a simple multi-column subreport.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

Yes I found some text talking about the issues and what you did to solve them, but I was strugling with the table structure and how I altered it to report againt my table.

My column headings is a simple multi-column subreport

Glad you find it simple, I'd never even known about crosstab queries until a week ago!

I think in this instance , the old addage holds true 'If it aint broke!'





"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts

 

[dhookom]

The subreport for the headings is not based on a crosstab. It's record source is tblCustomers linked to tblEmpCustAlias which provides the Level and ColumnAlias for sorting and grouping.

Duane
Hook'D on Access
MS Access MVP

 

[1DMF]

I know the subreport isn't based on a CTQ ? why did you think I thought that.

Anyway, tblCustomers etc. means nothing to my databse, this is what i mean about understanding how I replace all that so it links to the table I want to use and the CTQ for generating the columns.

The person requesting the report is happy with what has been provided, so I'll go with what I have for now.

Maybe one day i'll get my head round your app and will be able to alter it to fit my needs.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you."

"If a shortcut was meant to be easy, it wouldn't be a shortcut, it would be the way!"

Google Rank Extractor -> Perl beta with FusionCharts